MAXIF and return another column

R

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.





[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]10029396C<strike></strike>
[/TD]
[TD]<strike></strike>KPO-TMU-A0008<strike></strike>
[/TD]
[TD]<strike></strike>2.43650068130681<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 180"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CROSS ARM[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]<strike></strike>[TABLE="width: 131"]
<colgroup><col></colgroup><tbody>[TR]
[TD]10029396L[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]KPO-TMU-A0008<strike></strike>
[/TD]
[TD]<strike></strike>4.20825744623599<strike></strike>
[/TD]
[TD]<strike></strike>[TABLE="width: 180"]
<colgroup><col></colgroup><tbody>[TR]
[TD]L POLE[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]<strike></strike>[TABLE="width: 131"]
<colgroup><col></colgroup><tbody>[TR]
[TD]10029396R[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]KPO-TMU-A0008<strike></strike>
[/TD]
[TD]<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>
[/TD]
[TD]<strike></strike>[TABLE="width: 180"]
<colgroup><col></colgroup><tbody>[TR]
[TD]R POLE[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>

<strike></strike>

[TABLE="width: 519"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
 
Sort by date Sort by votes
this is M-code for PowerQuery (Get&Transform)

Code: 
[SIZE=1]// Table9
let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    Replace = Table.ReplaceValue(Source,"NULL",0,Replacer.ReplaceValue,{"HI"}),
    Group = Table.Group(Replace, {"Asset ID"}, {{"Max", each List.Max([HI]), type anynonnull}, {"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Pole and Cross Arm Identifier", each List.Distinct(Table.Column([Count],"Pole and Cross Arm Identifier"))),
    Extract = Table.TransformColumns(List, {"Pole and Cross Arm Identifier", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    Sort = Table.Sort(Extract,{{"Max", Order.Descending}}),
    ROC = Table.SelectColumns(Sort,{"Asset ID", "Max", "Pole and Cross Arm Identifier"}),
    Finish = Table.Sort(ROC,{{"Max", Order.Descending}})
in
    Finish[/SIZE]
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
sandy666 said:
this is M-code for PowerQuery (Get&Transform)
Click to expand...

Hi Sandy - sorry wouldn't know what to do with this code and I would need to show formula for my workings as this is compared with the SQL script so would be gratefully if anyone can send the formula which treats those with blank cells too.
 
Upvote 0
Hi, I am sure this can be modified be shorter but this should work with NULL:


Book1
ABCDEFG
1C1 (UT_HI197)Asset IDHIPole and Cross Arm IdentifierDistinct AssetID
210175251MAHA-DOB-A0241A1.362298M POLEAHA-DOB-A0241AM POLE
310274203CAHA-DOB-A02421.58066CROSSARMAHA-DOB-A0242CROSSARM
410274203MAHA-DOB-A02420.889005M POLEAHA-DOB-A0243M POLE
510175258MAHA-DOB-A02437.401968M POLEAHA-DOB-A0244M POLE
610175258CAHA-DOB-A02436.118526CROSSARMAHA-DOB-A0245CROSSARM
710274204MAHA-DOB-A02440.941668M POLEAHA-DOB-A0246M POLE, CROSSARM
810175264CAHA-DOB-A02453.093346CROSSARMAHA-DOB-A0247CROSSARM
910175264MAHA-DOB-A02451.362298M POLEAHA-DOB-A0248M POLE
1010175267MAHA-DOB-A02467.401968M POLEAHA-DOB-A0249M POLE
1110175267CAHA-DOB-A02467.401968CROSSARMAHA-DOB-A0250CROSSARM
1210175270MAHA-DOB-A02471.362298M POLEAHA-DOB-A0251CROSSARM, M POLE
1310175270CAHA-DOB-A02473.093346CROSSARMAHA-DOB-A0252L POLE, R POLE
1410175273MAHA-DOB-A02481.744747M POLEAHA-DOB-A0253M POLE
1510298647MAHA-DOB-A02490.803833M POLEAHA-DOB-A0254M POLE
1610175279CAHA-DOB-A02503.093346CROSSARMAHA-DOB-A0255CROSSARM
1710175279MAHA-DOB-A02501.362298M POLEAHA-DOB-A0256CROSSARM
1812053027CAHA-DOB-A0251NULLCROSSARMAHA-DOB-A0257CROSSARM
1912053027MAHA-DOB-A0251NULLM POLEAHA-DOB-A0258
2013161276LAHA-DOB-A0252NULLL POLEAHA-DOB-A0259
2113161276RAHA-DOB-A0252NULLR POLEAHA-DOB-A0260
2210175288MAHA-DOB-A02537.401968M POLEAHA-DOB-A0261
2310175288CAHA-DOB-A02536.118526CROSSARMAHA-DOB-A0262
2410175291MAHA-DOB-A02545.5M POLEAHA-DOB-A0263
2510175294MAHA-DOB-A02551.023058M POLEAHA-DOB-A0264
2610175294CAHA-DOB-A02553.093346CROSSARMAHA-DOB-A0265
2710316356CAHA-DOB-A02564.541385CROSSARMAHA-DOB-A0266
2810316356MAHA-DOB-A02561.282592M POLEAHA-DOB-A0267
2910319129CAHA-DOB-A02575.076351CROSSARMAHA-DOB-A0268
3010319129MAHA-DOB-A02571.522207M POLEAHA-DOB-A0269
Sheet10
Cell Formulas
RangeFormula
G2{=TEXTJOIN(", ",1,IF(MAX(IF($C$2:$C$30="NULL",10000,$C$2:$C$30)*($B$2:$B$30=F2)),IF((IF($C$2:$C$30="NULL",10000,$C$2:$C$30)*($B$2:$B$30=F2))=MAX(IF($C$2:$C$30="NULL",10000,$C$2:$C$30)*($B$2:$B$30=F2)),$D$2:$D$30,""),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
to use your range in G2 and copying it down:

Code: 
=TEXTJOIN(", ",1,IF(MAX(IF($C$2:$C$31884="NULL",10000,$C$2:$C$31884)*($B$2:$B$31884=F2)),IF((IF($C$2:$C$31884="NULL",10000,$C$2:$C$31884)*($B$2:$B$31884=F2))=MAX(IF($C$2:$C$31884="NULL",10000,$C$2:$C$31884)*($B$2:$B$31884=F2)),$D$2:$D$31884,""),""))

Confirm with Ctrl+Shift+Enter as this is an array formula not just Enter.
 
Last edited:
Upvote 0
A little more robust formula to handle any non-numerical value be it NULL or BLANK:

Code: 
=TEXTJOIN(", ",1,IF(MAX(IF(ISNUMBER($C$2:$C$31884),$C$2:$C$31884,10000)*($B$2:$B$31884=F2)),IF((IF(ISNUMBER($C$2:$C$31884),$C$2:$C$31884,10000)*($B$2:$B$31884=F2))=MAX(IF(ISNUMBER($C$2:$C$31884),$C$2:$C$31884,10000)*($B$2:$B$31884=F2)),$D$2:$D$31884,""),""))
 
Last edited:
Upvote 0
redzhell said:
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.
Click to expand...

With more than 31,000 records and using array formulas on your sheet, your computer's performance may decrease.

Try the following macro. The result will be in columns F and G


Code: 
Sub [COLOR=#0000ff]DAM_AssetUnique[/COLOR]()
  Dim c As Range, wmax As Double, r As Range, f As Range
  Dim cell As String, ky As Variant, dict As Object
  
  Set r = Range("B2", Cells(Rows.Count, "B").End(xlUp))
  Set dict = CreateObject("Scripting.Dictionary")
  
  With dict
    For Each c In r
      If Not dict.Exists(c.Value) Then
        .Add Key:=c.Value, Item:=""
      End If
    Next
    For Each ky In .Keys
      wmax = Evaluate("=sum(max((" & r.Address & "=""" & ky & """)*(" & r.Offset(, 1).Address & ")))")
      Set f = r.Find(ky, LookIn:=xlValues, lookat:=xlWhole)
      If Not f Is Nothing Then
          cell = f.Address
          Do
              If f.Offset(, 1).Value = wmax Then
                dict(ky) = dict(ky) & ", " & f.Offset(0, 2)
              End If
              Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> cell
      End If
      dict(ky) = Mid(dict(ky), 3)
    Next
    Range("F2:G" & Rows.Count).ClearContents
    Range("F2").Resize(.Count) = Application.Transpose(.Keys)
    Range("G2").Resize(.Count) = Application.Transpose(.Items)
  End With
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (DAM_AssetUnique) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Upvote 0
Hi all,

Thanks very much for all the input.

I'm not allowed to use Macros in this instance so I have used Aryatect's formula which works a treat!
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Create report from Columns differences.
Replies
0
Views
538
HerClau
H
D
  • Question Question
Problem with Match function...
Replies
3
Views
753
DizzyDoodle
D
A
  • Question Question
Excel Challenge from a friend - I failed can you solve it?
Replies
5
Views
1K
sandy666
sandy666
W
  • Question Question
return value based on matrix column name and row name
Replies
5
Views
659
AlanY
A
W
  • Question Question
Formula to Sum Cells when value changes in another Column
Replies
6
Views
6K
WednesdayC
W

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

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