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

 

[jasonb75]

You can use MAXIFS if you have excel 2019 or office 365, other than that you will need an array formula.

The other option would be a pivot table.

 

[Toadstool]

Hi ZGadson,

Assuming you don't have a list of each item I've created that in column F. Column G then gives you the maximum pallet height for that product.

ZGadson.xlsx
	A	B	C	D	E	F	G
1	Item	Pallet Height				Distinct List	Max Pallet Height
2	Hydrogen Peroxide Cleaner (50 gallon)	2				Hydrogen Peroxide Cleaner (50 gallon)	5
3	Ammonia (clear, 5 gallon)	2				Ammonia (clear, 5 gallon)	6
4	Disinfectant (55 gallon drum)	2				Disinfectant (55 gallon drum)	2
5	Drain Treatment 25 gallon)	2				Drain Treatment 25 gallon)	9
6	Liquid Bleach (25 gallon)	2				Liquid Bleach (25 gallon)	12
7	Liquid Laundry Detergent (25 gallon)	2				Liquid Laundry Detergent (25 gallon)	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
14
Sheet1

Cell Formulas
Range	Formula
F2:F13	F2	=INDEX($A$2:$A$999,MATCH(0,INDEX(COUNTIF($F$1:$F1,$A$2:$A$999),),0))&""
G2:G13	G2	=IF(F2="","",AGGREGATE(14,6,$B$2:$B$999/($A$2:$A$999=F2),1))

 

[jasonb75]

@Toadstool the OP asked for formulas that don't use arrays.

INDEX(COUNTIF($F$1:$F1,$A$2:$A$999),) is an array.

$B$2:$B$999/($A$2:$A$999=F2) is an array.

Personally, I would use a pivot instead of formulas. Using Item descriptions for pivot rows and Pallet height for pivot values, value field set to max of.

 

[Toadstool]

@Toadstool the OP asked for formulas that don't use arrays.
.....

I'd assumed the OP had omitted the commas of a non-essential clause so "or that use CTRL+SHIFT+ENTER" was further explanation of "formulas with an array".

 

[Peter_SSs]

Use Excel's built-in Pivot Table feature (on the Insert ribbon tab)?

Edit: Ooops, missed that jasonb75 already suggested this (twice!!).

20 07 14.xlsm
	A	B	C	D	E	F
1	Item	Pallet Height			Row Labels	Max of Pallet Height
2	Hydrogen Peroxide Cleaner (50 gallon)	2			Ammonia (clear, 5 gallon)	6
3	Ammonia (clear, 5 gallon)	2			Disinfectant (55 gallon drum)	2
4	Disinfectant (55 gallon drum)	2			Drain Treatment 25 gallon)	9
5	Drain Treatment 25 gallon)	2			Hydrogen Peroxide Cleaner (50 gallon)	5
6	Liquid Bleach (25 gallon)	2			Liquid Bleach (25 gallon)	12
7	Liquid Laundry Detergent (25 gallon)	2			Liquid Laundry Detergent (25 gallon)	44
8	Hydrogen Peroxide Cleaner (50 gallon)	5			Grand Total	44
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

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data (or use Toadstool's) to test with.

 

[jasonb75]

I'd assumed the OP had omitted the commas of a non-essential clause so "or that use CTRL+SHIFT+ENTER" was further explanation of "formulas with an array".

Looking at the rest of that line from the OP,

so formulas with an array or that use CTRL+SHIFT+ENTER would take way too long to process.

Maybe my logic is flawed, but in my opinion an array is going to take equal time to process with or without CSE, possibly using INDEX to circumvent CSE confirmation might even make it a billionth of a second slower as it is an extra function to process

 

[Peter_SSs]

Another way that should be pretty fast

Sub Max_Values()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    If a(i, 2) > d(a(i, 1)) Then d(a(i, 1)) = a(i, 2)
  Next i
  With Range("E2:F2").Resize(d.Count)
    .Value = Application.Transpose(Array(d.Keys, d.Items))
    .Rows(0).Value = Array("Item", "Max Height")
    .EntireColumn.AutoFit
  End With
End Sub

 

[zgadson]

Thanks so much guys! I think Toadstool's pivot table idea is the best way, however, I need your column F to be in the main data listed in the next column. (In your example, column B has the original heights, so I'd need column C to be the maximum height (even if the item is repeated, I still need the max height next to it.)

Thanks!
Zach

 

[Toadstool]

Hi ZGadson,

The pivot table solution was proposed by JasonB75 and Peter_SSs. My solution uses functions.

If you need the max in column C and it doesn't matter if it's repeated then my sheet would look like this:

ZGadson.xlsx
	A	B	C
1	Item	Pallet Height	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
14
15
2ndexample

Cell Formulas
Range	Formula
C2:C15	C2	=IF(A2="","",AGGREGATE(14,6,$B$2:$B$999/($A$2:$A$999=A2),1))