• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

zohaib

Member
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
idappleorangegrape
6​
11​
23​
47​
2​
51​
15​
4​
55​
58​
5​
7​
1​
22​
29​
21​
3​
31​
25​



sheet2
idHeader
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

Well-Known Member
You seem to have an INDEX function missing.
Code:
= INDEX(
    fruit,
    MATCH(
      MIN(INDEX(data,MATCH(ID,ID_list,0), )),
      INDEX(data,MATCH(ID,ID_list,0), ),
      0) )
Mind you, I would prefer
Code:
= LET(
  activeRow, XLOOKUP(@ID, ID_list, data),
  XLOOKUP(
    MIN(activeRow),
    activeRow,
    fruit)
  )
but then, I never did like traditional spreadsheet working!
 

Attachments

zohaib

Member
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

Well-Known Member
@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

Well-Known Member
@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"

Code:
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
 

Attachments

GraH - Guido

Well-Known Member
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

Well-Known Member
Guido
I think "Inserted Minimum" transforms the task!
Code:
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

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

For reference:
Code:
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.
 
Last edited:

zohaib

Member
@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

Well-Known Member
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.
Code:
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
 
Top