MAXIF and return another column

redzhell

New Member
Joined
Mar 8, 2018
Messages
12
Hi,

Please can somebody help?

The purpose is to get the Max of column C for each AssetID(column B) and then return whether it’s a Cross Arm or Pole but I’d need to returnall values for Poles and Cross Arm if the highest occurs more than once per AssetID.

So just for the below example, the Max is
4.20825744623599 which is true for both the L Pole and R Pole. How would I write the formula around my MAXIF so that it returns 'L Pole, R Pole'?
<strike>
</strike>

I do have a spreadsheet I can attach but can't see a way of attaching it.





ABCD
10029396C<strike></strike>
<strike></strike>KPO-TMU-A0008<strike></strike>
<strike></strike>2.43650068130681<strike></strike>
<strike></strike>
CROSS ARM

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
10029396L

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
KPO-TMU-A0008<strike></strike>
<strike></strike>4.20825744623599<strike></strike>
<strike></strike>
L POLE

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
10029396R

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
KPO-TMU-A0008<strike></strike>
<strike></strike>
4.20825744623599
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
<strike></strike>
R POLE

<colgroup><col></colgroup><tbody>
</tbody>


<tbody>
</tbody>
<strike></strike>

<strike></strike>


<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
<strike></strike>


 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,224
Try:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Asset ID</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">C</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">D</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Asset ID</td><td style="border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Value</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">10029396C</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">KPO-TMU-A0008</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">2.4365</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">CROSS ARM</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style=";">KPO-TMU-A0008</td><td style=";">L POLE, R POLE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">10029396L</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">KPO-TMU-A0008</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">4.20826</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">L POLE</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">10029396R</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">KPO-TMU-A0008</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #222222;background-color: #FAFAFA;;">4.20826</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">R POLE</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">{=TEXTJOIN(<font color="Blue">", ",1,IF(<font color="Red">C2:C4=MAXIFS(<font color="Green">C:C,B:B,F2</font>),D2:D4,""</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

redzhell

New Member
Joined
Mar 8, 2018
Messages
12
Hi Eric,

Thank you so much - I tried to use the formula for a much larger dataset but failed. I've got just over 31,000 rows so I tweaked C2:C4 and D2:D4 to extend accordingly but didn't work.

In the F column, I've got just over 13,000 rows with the unique AssetIDs that are being matched up on column B. Also Asset IDs could have anywhere between 1 to 5 rows with different properties on the D column so I would need to display all of D with the Max of C.

Hope that makes sense.

Am currently trying to figure out a way to post a snapshot of a slightly bigger dataset on here but also failing on that!
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Hi, Can you tell which version of excel are you using? See if you have TEXTJOIN function.

If you have try below, slight tweak in the formula in #2 :

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">A</td><td style=";">B</td><td style=";">C</td><td style=";">D</td><td style="text-align: right;;"></td><td style=";">Asset ID </td><td style=";">Value</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">10029396C</td><td style=";">KPO-TMU-A0008</td><td style="text-align: right;;">2.436500681</td><td style=";">CROSS ARM</td><td style="text-align: right;;"></td><td style=";">KPO-TMU-A0008</td><td style=";">L POLE, R POLE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">10029396L</td><td style=";">KPO-TMU-A0008</td><td style="text-align: right;;">4.208257446</td><td style=";">L POLE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">10029396R</td><td style=";">KPO-TMU-A0008</td><td style="text-align: right;;">4.208257446</td><td style=";">R POLE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet9</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">{=TEXTJOIN(<font color="Blue">", ",1,IF(<font color="Red">(<font color="Green">(<font color="Purple">$C$2:$C$4</font>)*(<font color="Purple">$B$2:$B$4=F2</font>)</font>)=MAXIFS(<font color="Green">$C$2:$C$4,$B$2:$B$4,F2</font>),$D$2:$D$4,""</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

redzhell

New Member
Joined
Mar 8, 2018
Messages
12

ADVERTISEMENT

Hi, Can you tell which version of excel are you using? See if you have TEXTJOIN function.

Hi - I am using 2016 so TEXTJOIN works.

I've finally worked out how to paste from Excel using the add-ins! Here's a snapshot of my data sorted via AssetID. The left handside contains all the data for each asset and the right handside contains the unique Asset IDs with what should be a concatenation of Pole and Cross Arm if joint Max HI (column C). I've copied the formula you've kindly given and just extended it to 31884 but all are coming up as errors.

What am I doing wrong?

ABCDEFG
1C1 (UT_HI197)Asset IDHIPole and Cross Arm IdentifierDistinct AssetIDReturn Cross Arm or Pole based on Max HI
210175251MAHA-DOB-A0241A1.362298087M POLEAHA-DOB-A0241A#VALUE!
310274203CAHA-DOB-A02421.580660109CROSSARMAHA-DOB-A0242#VALUE!
410274203MAHA-DOB-A02420.889005093M POLEAHA-DOB-A0243#VALUE!
510175258MAHA-DOB-A02437.401968309M POLEAHA-DOB-A0244#VALUE!
610175258CAHA-DOB-A02436.118525986CROSSARMAHA-DOB-A0245#VALUE!
710274204MAHA-DOB-A02440.941667698M POLEAHA-DOB-A0246#VALUE!
810175264CAHA-DOB-A02453.09334561CROSSARMAHA-DOB-A0247#VALUE!
910175264MAHA-DOB-A02451.362298087M POLEAHA-DOB-A0248#VALUE!
1010175267MAHA-DOB-A02467.401968309M POLEAHA-DOB-A0249#VALUE!
1110175267CAHA-DOB-A02467.401968309CROSSARMAHA-DOB-A0250#VALUE!
1210175270MAHA-DOB-A02471.362298087M POLEAHA-DOB-A0251#VALUE!
1310175270CAHA-DOB-A02473.09334561CROSSARMAHA-DOB-A0252#VALUE!
1410175273MAHA-DOB-A02481.744746795M POLEAHA-DOB-A0253#VALUE!
1510298647MAHA-DOB-A02490.80383286M POLEAHA-DOB-A0254#VALUE!
1610175279CAHA-DOB-A02503.09334561CROSSARMAHA-DOB-A0255#VALUE!
1710175279MAHA-DOB-A02501.362298087M POLEAHA-DOB-A0256#VALUE!
1812053027CAHA-DOB-A0251NULLCROSSARMAHA-DOB-A0257#VALUE!
1912053027MAHA-DOB-A0251NULLM POLEAHA-DOB-A0258#VALUE!
2013161276LAHA-DOB-A0252NULLL POLEAHA-DOB-A0259#VALUE!
2113161276RAHA-DOB-A0252NULLR POLEAHA-DOB-A0260#VALUE!
2210175288MAHA-DOB-A02537.401968309M POLEAHA-DOB-A0261#VALUE!
2310175288CAHA-DOB-A02536.118525986CROSSARMAHA-DOB-A0262#VALUE!
2410175291MAHA-DOB-A02545.5M POLEAHA-DOB-A0263#VALUE!
2510175294MAHA-DOB-A02551.023057647M POLEAHA-DOB-A0264#VALUE!
2610175294CAHA-DOB-A02553.09334561CROSSARMAHA-DOB-A0265#VALUE!
2710316356CAHA-DOB-A02564.541385053CROSSARMAHA-DOB-A0266#VALUE!
2810316356MAHA-DOB-A02561.282592096M POLEAHA-DOB-A0267#VALUE!
2910319129CAHA-DOB-A02575.076351457CROSSARMAHA-DOB-A0268#VALUE!
3010319129MAHA-DOB-A02571.522207127M POLEAHA-DOB-A0269#VALUE!

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
1509

Array Formulas
CellFormula
G2{=TEXTJOIN(", ",1,IF([COLOR=#0]([COLOR=#00]([COLOR=#00b00]$C$2:$C$31884)*([COLOR=#00b00]$B$2:$B$31884=F2[/COLOR])[/COLOR])=MAXIFS([COLOR=#00]$C$2:$C$31884,$B$2:$B$31884,F2[/COLOR]),$D$2:$D$31884,""[/COLOR])[/COLOR])}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Hi, so I got your problem, it is happening because of NULL text in some of the columns. Trying to find a way around it
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308

ADVERTISEMENT

Can you replace NULL with 1 or any big number like 10000?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,773
maybe like this?

Asset IDMaxPole and Cross Arm Identifier
AHA-DOB-A0243
7.401968309​
M POLE, CROSSARM
AHA-DOB-A0253
7.401968309​
M POLE, CROSSARM
AHA-DOB-A0246
7.401968309​
M POLE, CROSSARM
AHA-DOB-A0254
5.5​
M POLE
AHA-DOB-A0257
5.076351457​
CROSSARM, M POLE
AHA-DOB-A0256
4.541385053​
CROSSARM, M POLE
AHA-DOB-A0255
3.09334561​
M POLE, CROSSARM
AHA-DOB-A0250
3.09334561​
CROSSARM, M POLE
AHA-DOB-A0245
3.09334561​
CROSSARM, M POLE
AHA-DOB-A0247
3.09334561​
M POLE, CROSSARM
AHA-DOB-A0248
1.744746795​
M POLE
AHA-DOB-A0242
1.580660109​
CROSSARM, M POLE
AHA-DOB-A0241A
1.362298087​
M POLE
AHA-DOB-A0244
0.941667698​
M POLE
AHA-DOB-A0249
0.80383286​
M POLE
AHA-DOB-A0252
0​
L POLE, R POLE
AHA-DOB-A0251
0​
CROSSARM, M POLE
 

redzhell

New Member
Joined
Mar 8, 2018
Messages
12
Can you replace NULL with 1 or any big number like 10000?

Sorry I usually work with SQL and forgot that NULLs are texts in Excel. I couldn't replace them with any other number as that will affect the MAXIFS calculation so I have removed them and the NULL cells are just blank.

I think we're nearly there as the results are mostly coming through but would need to tweak the formula as the ones with just blank cells for certain AssetIDs are coming up as #VALUE ! error. How do I then get the CrossArm and Pole if the AssetIDs have no values in HI column (C)?
 

redzhell

New Member
Joined
Mar 8, 2018
Messages
12
maybe like this?

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Asset ID[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Max[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Pole and Cross Arm Identifier[/COLOR]
AHA-DOB-A0243
7.401968309​
M POLE, CROSSARM
AHA-DOB-A0253
7.401968309​
M POLE, CROSSARM
AHA-DOB-A0246
7.401968309​
M POLE, CROSSARM
AHA-DOB-A0254
5.5​
M POLE
AHA-DOB-A0257
5.076351457​
CROSSARM, M POLE
AHA-DOB-A0256
4.541385053​
CROSSARM, M POLE
AHA-DOB-A0255
3.09334561​
M POLE, CROSSARM
AHA-DOB-A0250
3.09334561​
CROSSARM, M POLE
AHA-DOB-A0245
3.09334561​
CROSSARM, M POLE
AHA-DOB-A0247
3.09334561​
M POLE, CROSSARM
AHA-DOB-A0248
1.744746795​
M POLE
AHA-DOB-A0242
1.580660109​
CROSSARM, M POLE
AHA-DOB-A0241A
1.362298087​
M POLE
AHA-DOB-A0244
0.941667698​
M POLE
AHA-DOB-A0249
0.80383286​
M POLE
AHA-DOB-A0252
0​
L POLE, R POLE
AHA-DOB-A0251
0​
CROSSARM, M POLE

<tbody>
</tbody>

Hi - you seem to have it right as you're getting results for ones with blank HIs like for
AHA-DOB-A0252 and
AHA-DOB-A0251
. What is the formula please?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,142
Messages
5,527,069
Members
409,742
Latest member
setam

This Week's Hot Topics

Top