Make min/max formulas read concatenated data?

[Elihue]

Hi everyone,

How would I go about using min/max formulas with concatenated data? For example...

I have two or more cells that are concatenated and I want to use min/max on formulas on them. There will be text as well as numerical values. "Owners" which would be text and and a "height" which would be numerical. I want the min/max formula to only work on a certain portion of the numerical data in the concatenated cell.


Cell 1 (Concatenated) Cell 2 (Concatenated) Output Cell (Returns the data with the minimum number along with it's corresponding owner info) (If using min formula)
IPL, 21.33 DGL, 25.42 IPL, 21.33


The purpose of this is because I want the formula to bring the owner information along with it so you don't need to sift though tons of data to look for that number and find it's corresponding owner.

 

[Eric W]

That's not a good idea. Basic design philosophy states that there should be 1 piece of data per cell. When you put 2 pieces of data per cell, you can't use standard formulas, or formatting, or searching, and it gets really complicated, far worse than not finding the matching cell. You can group data together very easily with good design. Even better, build a table.

This formula:

<mid(b1,find(",",b1)+1,99)+0,a1,b1)
=IF(MID(A1,FIND(",",A1)+1,99)+0< MID(B1,FIND(",",B1)+1,99)+0,A1,B1)

pretty much does what you ask, but it's long, complicated, and doesn't scale well at all.</mid(b1,find(",",b1)+1,99)+0,a1,b1)

 

[Elihue]

Ok, how would I do the equivalent with a table?

For example, I have a table on one tab with my data. Then I have another table in a separate tab where I am condensing the data to be useful. On table #1 I have a group of data I want to sift through using a max formula. It's got 10 Pairs of data on it. Comm Owner 1, Comm height 1, Comm Owner 2, Comm Height 2, Comm Owner 3, Comm Height 3, continuing on until it gets to 10. On table #2 I'm trying to use a max formula to return the highest value of all of those previously mentioned pairs into one column. But I would like to somehow retain the owner as well. Could I have that show up in an adjacent column? The problem is, the max value could be anywhere in those 10 pairs of data. All the owners could have duplicates as well.

 

[Eric W]

Sure, you could do something like this:

	B	C	D	E	F	G	H	I
Owner	Height	Owner	Height	Owner	Height
One	Two	Two
Two	Six
Three	One
Four	Seven
Five	Ten
Six	Five
Seven		Nine
Eight	Four
Nine	Three
Ten		Eight

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]21.1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]32.2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]32.2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]32.2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]32.2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]21.1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]9.1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]21.1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]18.1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]21.1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]32.2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]18.1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]21.1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]17[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9.1[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]8[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]21.1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"]8[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]=INDEX(MyTable[Owner],MATCH(F2,MyTable[Height],0))[/TD]
[/TR]
[TR]
[TH]F2[/TH]
[TD="align: left"]=MAX(MyTable[Height])[/TD]
[/TR]
[TR]
[TH]I2[/TH]
[TD="align: left"]=LARGE(MyTable[Height],ROWS($I$2:$I2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]{=INDEX(MyTable[Owner],SMALL(IF(MyTable[Height]=I2,ROW(MyTable[Height])-ROW(INDEX(MyTable[Height],1))+1),COUNTIF($I$2:$I2,I2)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 85%"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


B2:C11 I defined a table named MyTable (which could be on another sheet).

If you only want the top score/owner, you could use the formulas in E2:F2. If you want to have a list of the top 3, top 5, or whatever, and make sure to handle duplicate values, you could use the H2:I2 formulas.

 

[Elihue]

Eric, unless I am miss understanding this. I don't think this will work for the way my data is structured. Here is a link to a better explanation of what I am trying to achieve.

https://www.mrexcel.com/forum/excel-questions/1057861-finding-adjacent-cell-min-max-formula.html

I'll also, send you a private message with more details.