1

My example table

Part Number 00 01 02 03 04 EX Max Value
0006321 0 0 -1 0 1 0 1
0007244 7 8 0 0 0 0 8
0015600 24 0 0 0 0 0 24
0016301 0 0 0 0 0 0 0
0017004 -4 2 2 0 0 0 2
0019440 0 0 0 1 0 0 1
0020639 -4 0 5 0 0 0 5
0023448 0 6 0 0 0 0 6
0025299 -2 0 2 0 0 0 2
0025743 0 -1 0 0 0 0 0
0026453 0 0 2 0 0 0 2
0026454 -3 1 2 0 0 0 2

In the image posted above the headers 00, 01, 02, 03, 04, EX represent different stores and the values are the excess quantity of each part number at that store. As you can see, I was able to get the max value of excess quantity out of all the stores, but I would also like to show in the column next to it which store that value is associated with. My ultimate goal is to create a dataset showing which store has the highest excess quantity and how much the excess quantity is. I am unsure if there is a formula for this or possibly a pivot table function that could help me out. I am also familiar with Power Query if that could also help in some way. Thank you!

lberecek
  • 51
  • 5
  • If it is the only value then you can have a go with match(), row(), column() and index(). – Solar Mike May 03 '23 at 16:26
  • 2
    What do you wish to show when you end up with multiple stores having the same highest excess quantitiy. Let's say, the rows where all values are `0` in your sample data set. – JvdV May 03 '23 at 16:32
  • 2
    Sidenote, please add your data as markdown sample data instead so people can actually copy/paste and work with your data. – JvdV May 03 '23 at 16:32
  • @JvdV If the max value returned a 0 then it could be either blank or say something like N/A, etc. I apologize, I'm not familiar with how to do that. I attempted but was not able to get it to work. – lberecek May 03 '23 at 16:43
  • 1
    Right so you have actually avoided the question. Again; what if there are multiple stores holding the same maximum amount of excess? – JvdV May 03 '23 at 16:48
  • I'm sorry, I read it wrong and assumed you only meant 0's. If it is possible to add both even if that means adding another column then I'd like to do that. If not, and you can only use one store then either one is fine. – lberecek May 03 '23 at 16:52
  • 1
    Btw, convert a spreadsheet [here](https://tabletomarkdown.com/convert-spreadsheet-to-markdown/) for example – JvdV May 03 '23 at 16:52
  • @SolarMike Are you able to show an example of what that would look like? – lberecek May 03 '23 at 16:53
  • 2
    Does this answer your question? [Excel: Find min/max values in a column among those matched from another column](https://stackoverflow.com/questions/20904092/excel-find-min-max-values-in-a-column-among-those-matched-from-another-column) – bonCodigo May 03 '23 at 16:55

1 Answers1

2

How about below for Excel. See screeenshot

enter image description here

powerquery version

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Max", each List.Max(List.RemoveFirstN(Record.ToList(_),1)) ),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "ColumnMax", each Table.ColumnNames(Source){List.PositionOf(Record.ToList(_),[Max])})
in  #"Added Custom1"
horseyride
  • 17,007
  • 2
  • 11
  • 22