MAXIF and return another column

[redzhell]

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.

A	B	C	D
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>

 

[sandy666]

this is M-code for PowerQuery (Get&Transform)

[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]

 

[redzhell]

this is M-code for PowerQuery (Get&Transform)

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.

 

[sandy666]

if you prefer formula that's ok

Have a nice day

 

[Aryatect]

Hi, I am sure this can be modified be shorter but this should work with NULL:

Book1
	A	B	C	D	E	F	G
1	C1 (UT_HI197)	Asset ID	HI	Pole and Cross Arm Identifier		Distinct AssetID
2	10175251M	AHA-DOB-A0241A	1.362298	M POLE		AHA-DOB-A0241A	M POLE
3	10274203C	AHA-DOB-A0242	1.58066	CROSSARM		AHA-DOB-A0242	CROSSARM
4	10274203M	AHA-DOB-A0242	0.889005	M POLE		AHA-DOB-A0243	M POLE
5	10175258M	AHA-DOB-A0243	7.401968	M POLE		AHA-DOB-A0244	M POLE
6	10175258C	AHA-DOB-A0243	6.118526	CROSSARM		AHA-DOB-A0245	CROSSARM
7	10274204M	AHA-DOB-A0244	0.941668	M POLE		AHA-DOB-A0246	M POLE, CROSSARM
8	10175264C	AHA-DOB-A0245	3.093346	CROSSARM		AHA-DOB-A0247	CROSSARM
9	10175264M	AHA-DOB-A0245	1.362298	M POLE		AHA-DOB-A0248	M POLE
10	10175267M	AHA-DOB-A0246	7.401968	M POLE		AHA-DOB-A0249	M POLE
11	10175267C	AHA-DOB-A0246	7.401968	CROSSARM		AHA-DOB-A0250	CROSSARM
12	10175270M	AHA-DOB-A0247	1.362298	M POLE		AHA-DOB-A0251	CROSSARM, M POLE
13	10175270C	AHA-DOB-A0247	3.093346	CROSSARM		AHA-DOB-A0252	L POLE, R POLE
14	10175273M	AHA-DOB-A0248	1.744747	M POLE		AHA-DOB-A0253	M POLE
15	10298647M	AHA-DOB-A0249	0.803833	M POLE		AHA-DOB-A0254	M POLE
16	10175279C	AHA-DOB-A0250	3.093346	CROSSARM		AHA-DOB-A0255	CROSSARM
17	10175279M	AHA-DOB-A0250	1.362298	M POLE		AHA-DOB-A0256	CROSSARM
18	12053027C	AHA-DOB-A0251	NULL	CROSSARM		AHA-DOB-A0257	CROSSARM
19	12053027M	AHA-DOB-A0251	NULL	M POLE		AHA-DOB-A0258
20	13161276L	AHA-DOB-A0252	NULL	L POLE		AHA-DOB-A0259
21	13161276R	AHA-DOB-A0252	NULL	R POLE		AHA-DOB-A0260
22	10175288M	AHA-DOB-A0253	7.401968	M POLE		AHA-DOB-A0261
23	10175288C	AHA-DOB-A0253	6.118526	CROSSARM		AHA-DOB-A0262
24	10175291M	AHA-DOB-A0254	5.5	M POLE		AHA-DOB-A0263
25	10175294M	AHA-DOB-A0255	1.023058	M POLE		AHA-DOB-A0264
26	10175294C	AHA-DOB-A0255	3.093346	CROSSARM		AHA-DOB-A0265
27	10316356C	AHA-DOB-A0256	4.541385	CROSSARM		AHA-DOB-A0266
28	10316356M	AHA-DOB-A0256	1.282592	M POLE		AHA-DOB-A0267
29	10319129C	AHA-DOB-A0257	5.076351	CROSSARM		AHA-DOB-A0268
30	10319129M	AHA-DOB-A0257	1.522207	M POLE		AHA-DOB-A0269
Sheet10

Cell Formulas
Range	Formula
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.

 

[Aryatect]

to use your range in G2 and copying it down:

=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.

 

[Aryatect]

A little more robust formula to handle any non-numerical value be it NULL or BLANK:

=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,""),""))

 

[DanteAmor]

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.

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

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.

 

[redzhell]

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!

 

[DanteAmor]

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!

Okay, you already have a macro in case you need it.

 

[redzhell]

Okay, you already have a macro in case you need it.

I've saved that too as never know when I'd need it. Thank you for the code