max of

[el c]

Hi, I'm new to this forum.

I have an excel sheet with over 2000 rows. I want to have the maximum number of the Quantity of each item Code when the location is the same. For example, you can see in the excel sheet i have the ItemCode :

389-12-040	1107	01	4.00​	1​	1107010	A
389-12-040	1107	01	65.00​	1​	1107010	A
389-12-040	1107	01	35.00​	1​	1107010	A

As you can see the item Code and the Location Code are the same in every row. I want to keep only the Maximum of the numbers 4.00, 65.00, 35.00 for the whole sheet. How can I do this without altering the rest of the data that is related to them?

in this link you can see the excel sheet

inventories

Φύλλο1 Item Code,Rack,Slot,Quantity,Level,[Location.Code],ABC 257-30-015,1105,1,5.00,1,1105010,A 389-12-040,1107,1,4.00,1,1107010,A 389-12-040,1107,1,65.00,1,1107010,A 389-12-040,1107,1,35.00,1,1107010,A 257-30-014,1107,9,1.00,1,1107090,A 257-30-014,1107,9,3.00,1,1107090,A 010-30-004,1115,1,3.00...

docs.google.com

 

[KRice]

There are probably better ways to do this, but see if this gives the intended results. Unique values for the matched pairs of Item Code and Location Code are first found (I concatenated those two to simplify finding unique combinations, but that complicates the next step)...see the concatenated result of those two in column I. Then the Item Code and Location Code are extracted from that concatenation (see columns J and K). Then the AGGREGATE function is used to find all instances where Item Code and Location Code match the unique combination just identified and the corresponding Quantity for each of those...and the largest of those quantities is selected. I've only included a snapshot of your file and the additional columns I created to the right of your data. If the formulas work for your purposes, simply copy them down the sheet until the column I formula no longer finds anything unique (row 459 should be the last one with an output).

inventories.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Item Code	Rack	Slot	Quantity	Level	[Location.Code]	ABC		Item / Location Code	Item Code	[Location.Code]	Max Quantity
2	257-30-015	1105	1	5.00	1	1105010	A		257-08-011 / 1115130	257-08-011	1115130	6
3	389-12-040	1107	1	4	1	1107010	A		311-12-802 / 1111180	311-12-802	1111180	15
4	389-12-040	1107	1	65	1	1107010	A		257-12-801 / 1109160	257-12-801	1109160	1
5	389-12-040	1107	1	35	1	1107010	A		257-08-010 / 1105060	257-08-010	1105060	5
6	257-30-014	1107	9	1	1	1107090	A		257-15-502 / 1105040	257-15-502	1105040	12
7	257-30-014	1107	9	3	1	1107090	A		257-12-803 / 1101080	257-12-803	1101080	6
8	010-30-004	1115	1	3	1	1115010	C		257-12-103 / 1125190	257-12-103	1125190	3
9	367-66-400	1115	3	1	1	1115030	B		311-12-142 / 1123160	311-12-142	1123160	7
10	311-12-141	1127	13	1	1	1127130	A		298-12-022 / 1123100	298-12-022	1123100	8
11	311-12-141	1127	13	7	1	1127130	A		010-30-083 / 1123020	010-30-083	1123020	46
12	311-12-141	1127	13	3	1	1127130	A		311-12-144 / 1115210	311-12-144	1115210	8
Φύλλο1

Cell Formulas
Range	Formula
I2:I12	I2	=LOOKUP(2,1/(COUNTIF($I$1:I1,$A$2:$A$2358 & " / " & $F$2:$F$2358)=0),$A$2:$A$2358 & " / " & $F$2:$F$2358)
J2:J12	J2	=LEFT(I2,SEARCH("/",I2)-2)
K2:K12	K2	=(RIGHT(I2,LEN(I2)-SEARCH("/",I2)-1))/1
L2:L12	L2	=AGGREGATE(14,6,--($A$2:$A$2358=J2)*--($F$2:$F$2358=K2)*$D$2:$D$2358,1)

 

[el c]

Thank you for your reply KRice. Can you send the example of the data in an excel file? when i copy-paste the Cell Formulas it appears error.

 

[KRice]

Click on the icon appearing in the upper left corner of the worksheet that I posted...at the intersection of the row numbers and column letters. That will copy the entire contents of the snapshot that I posted to your clipboard, and then you can paste that into your sheet. You will probably want to confirm that it works on a small portion of your worksheet first. Confirm that the cell references match yours, or change the formulas to adapt them to your data table.

 

[el c]

Thank you very much. It works now in Microsoft Excel, in google spreadsheets it doesn't work but no need for that.

 

[el c]

How can i add the A,B,C labels that i had next to the location Code?

 

[KRice]

In the first cell under your new ABC column heading, insert this formula:
=INDEX($G$2:$G$2358,MATCH(1,(J2=$A$2:$A$2358)*(K2=$F$2:$F$2358)*(L2=$D$2:$D$2358),0))
where the large range references mentioning G, A, F, and D are your original ABC, Item Code, Location Code, and Quantity columns...change these references where necessary.
After entering this in the first cell, hit F2 followed by Ctrl-Shift-Enter to convert the formula to an array formula (you should see curly brackets inserted in the formula bar. Then grab the small square "handle" appearing in the lower right corner of that cell and drag the formula all the way down that column to the end to match the length of the other data in the new table.

 

[el c]

Thank you very much, you've been very helpful.

 

[KRice]

You're welcome...and welcome to the MrExcel board. I'm glad to help.