match values than retrieve column header of lowest value in corresponding cells

[zohaib]

Hello,

I tried using the index and match function to solve my issue but could not figure out how to do it.
=INDEX(B$3:D$3,MATCH(MIN(B4:D4),B4:D4,0),MATCH(I4,$A$4:$A$9,0))

I am trying to populate sheet 2 by pulling data from sheet 1.

Steps
Sheet2 first finds matching id in sheet1 then looks at the corresponding columns for lowest value and returns the column header. I have an example of what it should look like in sheet2 below.

sheet1
id	apple	orange	grape
6​	11​	23​	47​
2​		51​	15​
4​	55​		58​
5​		7​
1​	22​	29​	21​
3​	31​		25​

sheet2
id	Header
3​	grape
1​	grape
4​	apple
6​	apple
2​	grape
5​	orange

I think sumproduct might be able to make this work but i have no idea how to do it.

thanks,
zohaib

 

[Peter Bartholomew]

You seem to have an INDEX function missing.

= INDEX(
    fruit,
    MATCH(
      MIN(INDEX(data,MATCH(ID,ID_list,0), )),
      INDEX(data,MATCH(ID,ID_list,0), ),
      0) )

Mind you, I would prefer

= LET(
  activeRow, XLOOKUP(@ID, ID_list, data),
  XLOOKUP(
    MIN(activeRow),
    activeRow,
    fruit)
  )

but then, I never did like traditional spreadsheet working!

 

[zohaib]

Peter,

Thanks so much. i notice if number are changed in sheet1 then all of the formulas in sheet 2 show error except for the last column which uses the index column. Thanks for your help

 

[Peter Bartholomew]

From that, I take it you are not using Microsoft 365. Delete all columns except the last.

 

[zohaib]

true i am not using Microsoft 360

 

[rahulshewale1]

hi @zohaib,

See if is it ok ?

Attached Formula Solution and Power Query Solution.

 

[Peter Bartholomew]

@rahulshewale1

The formula solution, I followed without problem. I tend to use INDEX rather than OFFSET but that is only because I have been indoctrinated regarding volatility.

The PQ solution, on the other hand, left me puzzled. The Table.NestedJoin step referenced Table1 which originally contained the key column and 3 other attribute columns. When I examined the contents of the same table referenced by the Merge step, there was only one attribute column which contained the minimum value of the 3 originals. You were clearly expecting that, but I had no reason to expect the minimum rather than the maximum or even the median. What have I missed?

 

[GraH - Guido]

@Peter Bartholomew,

There is a query "Table1", only containing "Min". This one is used in the merge.

@rahulshewale1,

Can't help but think this is overcomplicated.
- Sort id and value
- Add index, to force PQ to keep this sort order in "memory" (whereas normally PQ will choose the optimal order)
- Remove duplicates on "ID"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"apple", Int64.Type}, {"orange", Int64.Type}, {"grape", Int64.Type}}),
    AddIndex0 = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    Unpivot = Table.UnpivotOtherColumns(AddIndex0, {"id", "Index"}, "Fruit", "Value"),
    SortId_Value_ASC = Table.Sort(Unpivot,{ {"id", Order.Ascending}, {"Value", Order.Ascending}}),
    AddIndex1 = Table.AddIndexColumn(SortId_Value_ASC, "Indx", 0, 1),
    RemoveDuplicates = Table.Distinct(AddIndex1, {"id"}),
    RestoreOriginalSort = Table.Sort(RemoveDuplicates,{{"Index", Order.Ascending}}),
    RemoveIndxes = Table.RemoveColumns(RestoreOriginalSort,{"Index", "Indx", "Value"})
in
    RemoveIndxes

 

[GraH - Guido]

Thx for the thumps up guys.
Going with Rahul's grouping, another option might be:
- sort id, value
- add index
- group on ID, aggregate all rows ("all"), use GroupKind.Local in case of many rows to improve performance.
- custom column: [all]{0}
- expand this record.

I have not tested, but I believe the sort order will be retained by the grouping as well.

Will also experiment with a (potentially) one step solution involving the raw data table record. I vaguely remember something of the sort.

 

[Peter Bartholomew]

Guido
I think "Inserted Minimum" transforms the task!

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"apple", Int64.Type}, {"orange", Int64.Type}, {"grape", Int64.Type}}),
    #"Inserted Minimum" = Table.AddColumn(#"Changed Type", "Minimum", each List.Min({[apple], [orange], [grape]}), type number),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Inserted Minimum", {"id", "Minimum"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Filter", each [Value]=[Minimum]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Filter] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Minimum", "Value", "Filter"})
in
    #"Removed Columns"

 

[GraH - Guido]

It does, Peter, yet it is less future proof: what if new columns are added? I know, you know ;-)

For reference:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddListValues = Table.AddColumn(Source, "ValuesAsList", each List.Skip ( Record.FieldValues(_) , 1)),
    AddPosOfMin = Table.AddColumn(AddListValues, "PosOfMin", each List.PositionOf([ValuesAsList], List.Min([ValuesAsList]))),
    GetHeader = Table.AddColumn(AddPosOfMin, "MatchHeader", each List.Skip(Table.ColumnNames(Source),1){[PosOfMin]})
in
    GetHeader

Edit: it won't handle ties, whereas the solution of Rahul as does as your last one.

 

[zohaib]

@rahulshewale1

Thanks guys. i found Peters formula to work perfectly and find it simple to understand since I am already familiar with using index and match. the offset formula works but I have not used it as much as index and match so I kept peters formula. Thanks once again for your help

 

[GraH - Guido]

Edit: it won't handle ties, whereas the solution of Rahul as does as your last one.

Just FYI @rahulshewale1 , @Peter, this PQ does handle the ties. ;-) See you around.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddListValues = Table.AddColumn(Source, "ValuesAsList", each List.Skip ( Record.FieldValues(_) , 1)),
    AddPosOfMins = Table.AddColumn(AddListValues, "PosOfMin", each List.PositionOf([ValuesAsList], List.Min([ValuesAsList]), Occurrence.All)),
    GetHeaders = Table.AddColumn(AddPosOfMins, "MatchHeader", each List.Transform([PosOfMin], each List.Skip(Table.ColumnNames(Source),1){_})),
    ExtractHeaders = Table.TransformColumns(GetHeaders, {"MatchHeader", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    ExtractHeaders