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>


 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Try:


Book1
ABCDEFG
1AAsset IDCDAsset IDValue
210029396CKPO-TMU-A00082.4365CROSS ARMKPO-TMU-A0008L POLE, R POLE
310029396LKPO-TMU-A00084.20826L POLE
410029396RKPO-TMU-A00084.20826R POLE
Sheet3
Cell Formulas
RangeFormula
G2{=TEXTJOIN(", ",1,IF(C2:C4=MAXIFS(C:C,B:B,F2),D2:D4,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
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!
 
Upvote 0
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 :


Book1
ABCDEFG
1ABCDAsset IDValue
210029396CKPO-TMU-A00082.436500681CROSS ARMKPO-TMU-A0008L POLE, R POLE
310029396LKPO-TMU-A00084.208257446L POLE
410029396RKPO-TMU-A00084.208257446R POLE
Sheet9
Cell Formulas
RangeFormula
G2{=TEXTJOIN(", ",1,IF((($C$2:$C$4)*($B$2:$B$4=F2))=MAXIFS($C$2:$C$4,$B$2:$B$4,F2),$D$2:$D$4,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top