Find Maximum Value in Column Without Using Array or CTRL+SHFT+ENTER?

[zgadson]

Hello,

Per the attached screenshot, I've got a spreadsheet that lists warehouse locations and the name of the item in each location. The sheet also has a column that says how many pallets high each item is stacked. There are 500+ different items, and a lot of them repeat because they are in multiple locations in my warehouse, so formulas with an array or that use CTRL+SHIFT+ENTER would take way too long to process. Is there an alternative way to search this whole sheet to get the maximum value for each item without using an array formula?

Thanks,
Zach

 

[sandy666]

with Power Query (no CSE)

ITDSC1	ADJ PLT HGHT		ITDSC1	Max
BOTANICAL DISINFECTANT 55 GAL	2		BOTANICAL DISINFECTANT 55 GAL	4
BOTANICAL DISINFECTANT 55 GAL	4		BOTANICAL DISINFCTNT CONC 275G	3
BOTANICAL DISINFCTNT CONC 275G	3		BOTANICAL DISINFECTANT 1 QT	4
BOTANICAL DISINFECTANT 55 GAL	3		BOTANICAL DISINFECTANT RETAIL	4
BOTANICAL DISINFCTNT CONC 275G	2		BOTANICAL DISINFECTANT 5 GAL	4
BOTANICAL DISINFECTANT 55 GAL	2		ELECTROSTATIC BACKPACK SPRAYER	4
BOTANICAL DISINFECTANT 55 GAL	2		BOTANICAL DISINFECTANT 1 GAL	3
BOTANICAL DISINFECTANT 1 QT	3
BOTANICAL DISINFECTANT RETAIL	3
BOTANICAL DISINFECTANT RETAIL	1
BOTANICAL DISINFECTANT 5 GAL	4
BOTANICAL DISINFECTANT 5 GAL	1
ELECTROSTATIC BACKPACK SPRAYER	3
ELECTROSTATIC BACKPACK SPRAYER	4
BOTANICAL DISINFECTANT 1 GAL	2
BOTANICAL DISINFECTANT 1 GAL	2
BOTANICAL DISINFECTANT 1 GAL	2
BOTANICAL DISINFECTANT RETAIL	2
BOTANICAL DISINFECTANT RETAIL	2
BOTANICAL DISINFECTANT 5 GAL	3
BOTANICAL DISINFECTANT 1 GAL	3
BOTANICAL DISINFECTANT RETAIL	3
BOTANICAL DISINFECTANT RETAIL	3
BOTANICAL DISINFECTANT RETAIL	3
BOTANICAL DISINFECTANT 1 QT	4
BOTANICAL DISINFECTANT RETAIL	4
BOTANICAL DISINFECTANT 5 GAL	3
BOTANICAL DISINFECTANT 1 QT	3
BOTANICAL DISINFECTANT 5 GAL	3
BOTANICAL DISINFECTANT 55 GAL	3
BOTANICAL DISINFECTANT 5 GAL	2
BOTANICAL DISINFECTANT 5 GAL	3
BOTANICAL DISINFECTANT 55 GAL	3
BOTANICAL DISINFECTANT 55 GAL	3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"ITDSC1"}, {{"Max", each List.Max([ADJ PLT HGHT]), type number}})
in
    Group

 

[Peter_SSs]

@zgadson
Thanks for updating your profile.

IF your Excel 365 has the FILTER function and noting from your image in post #1 that your data appears to be in a formal table, then here is another formula option to consider.

20 07 16.xlsm
	A	B	C
1	ITDSC1	ADJ PLT HGHT	Max
2	Hydrogen Peroxide Cleaner (50 gallon)	2	5
3	Ammonia (clear, 5 gallon)	2	6
4	Disinfectant (55 gallon drum)	2	2
5	Drain Treatment 25 gallon)	2	9
6	Liquid Bleach (25 gallon)	2	12
7	Liquid Laundry Detergent (25 gallon)	2	44
8	Hydrogen Peroxide Cleaner (50 gallon)	5	5
9	Ammonia (clear, 5 gallon)	6	6
10	Disinfectant (55 gallon drum)	1	2
11	Drain Treatment 25 gallon)	9	9
12	Liquid Bleach (25 gallon)	12	12
13	Liquid Laundry Detergent (25 gallon)	44	44
Max

Cell Formulas
Range	Formula
C2:C13	C2	=MAX(FILTER([ADJ PLT HGHT],[ITDSC1]=[@ITDSC1]))

.. or if you wanted to only report the maximum value against the first occurrence of each unique item in ITDSC1 then

20 07 16.xlsm
	A	B	C
1	ITDSC1	ADJ PLT HGHT	Max
2	Hydrogen Peroxide Cleaner (50 gallon)	2	5
3	Ammonia (clear, 5 gallon)	2	6
4	Disinfectant (55 gallon drum)	2	2
5	Drain Treatment 25 gallon)	2	9
6	Liquid Bleach (25 gallon)	2	12
7	Liquid Laundry Detergent (25 gallon)	2	44
8	Hydrogen Peroxide Cleaner (50 gallon)	5
9	Ammonia (clear, 5 gallon)	6
10	Disinfectant (55 gallon drum)	1
11	Drain Treatment 25 gallon)	9
12	Liquid Bleach (25 gallon)	12
13	Liquid Laundry Detergent (25 gallon)	44
Max (2)

Cell Formulas
Range	Formula
C2:C13	C2	=IF(COUNTIF(INDEX([ITDSC1],1):[@ITDSC1],[@ITDSC1])=1,MAX(FILTER([ADJ PLT HGHT],[ITDSC1]=[@ITDSC1])),"")