Identifying and getting SUM of items that appear on both lists.

[djuunk]

Hello!

I had question about identifying ID's and getting SUM of items that appear on both lists/tables. I have hundreds of rows of data so this is example is a mere simplification of the data I am working with.
With something like ten items per table, it would be simple to do it manually and by eye going through each item on both lists (like I did for the final table I want to achieve). However, with hundreds of items, I know there must be a more effective and efficient way of identifying and finding cumulative quantity of the item.

These two tables would be the list I have of Item ID, Description, and Quantity columns in each table. I want to extract only the items that appear on both lists and list the ID and sum of quantity to a destination area/cell.
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Item Description[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]AAA1[/TD]
[TD]-[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]AAB7[/TD]
[TD]-[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]AAC5[/TD]
[TD]-[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]BAB2[/TD]
[TD]-[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Item Description[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]AAA3[/TD]
[TD]-[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]AAA1[/TD]
[TD]-[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]ACA6[/TD]
[TD]-[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]BAB2[/TD]
[TD]-[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]HAI32[/TD]
[TD]-[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]BAB2[/TD]
[TD]-[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

From these two tables, the final table I want to achieve would look like:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Item Description[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]AAA1[/TD]
[TD]-[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]BAB2[/TD]
[TD]-[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]

I have been trying to map the process in my head, but I can't seem to find a clear answer or method to do this for tables with hundreds of rows.
I don't know if I am correct or even close, but I was in the realm of thinking of combining a SUM and MATCH function to achieve my goal.

I would greatly appreciate the help. Thank you!

 

[Oeldere]

maybe like this.

Add the tables together.

After that a pivot table and deselect 1.

See the attachement.

Book1
	B	C	D	E	F	G	H	I	J
25							criteria	(Meerdere items)
26
27	Item Description	Qty	tekst	criteria			Som van Qty
28	-	3	list 1	2			ID	Item Description	Totaal
29	-	1	list 1	1			AAA1	-	6
30	-	5	list 1	1			BAB2	-	14
31	-	3	list 1	3			Eindtotaal		20
32	-	11	list 2	1
33	-	3	list 2	2
34	-	5	list 2	1
35	-	9	list 2	3
36	-	15	list 2	1
37	-	2	list 2	3
Blad11

Cell Formulas
Range	Formula
E28	=COUNTIF($A$28:$A$37,$A28)

 

[djuunk]

Hey Oeldere,

Thanks for your consideration to help. I may be a little confused just due to the weird formatting and numbers in the cells, but I will try to explain where I might need clarification.

In H25, you have a "Criteria". What is the value that I should put in I25 ("Meerdere items"), and when is this cell used in a formula or for reference?
I have easily combined the two tables onto one continuous and lengthy table. Once I insert a PivotTable, what do you mean by "deselect 1"?

Again, thanks for the help! I hope you'll be able to teach me a new skill today.

 

[Oeldere]

on the right side you will see the pivot table.

In the filter box you will put (column E).

Then you have to deselect 1 (you only want to see 2 and 3) => the values that occurs more than 1 time.

e28=Countif($A$28:$A$37,$A28)

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]ID[/TD]
[TD]Item Description[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]AAA1[/TD]
[TD]-[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]AAB7[/TD]
[TD]-[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]AAC5[/TD]
[TD]-[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]BAB2[/TD]
[TD]-[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]ID[/TD]
[TD]Item Description[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]AAA3[/TD]
[TD]-[/TD]
[TD]

11​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]AAA1[/TD]
[TD]-[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]ACA6[/TD]
[TD]-[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]BAB2[/TD]
[TD]-[/TD]
[TD]

9​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]HAI32[/TD]
[TD]-[/TD]
[TD]

15​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]BAB2[/TD]
[TD]-[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]ID[/TD]
[TD]Item Description[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]AAA1[/TD]
[TD]-[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]BAB2[/TD]
[TD]-[/TD]
[TD]

14​

[/TD]
[/TR]
</tbody>[/TABLE]

c18=SUMIF($A$1:$A$14,A18,$C$1:$C$14) copy down

 

[Aladin Akyurek]

Sheet1 (table 1)

[TABLE="class: grid, width: 291"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Item Description[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]AAA1[/TD]
[TD]qwe[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]AAB7[/TD]
[TD]dsr[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]AAC5[/TD]
[TD]ter[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]BAB2[/TD]
[TD]ner[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 (table 2)

[TABLE="class: grid, width: 318"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Item Description[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]AAA3[/TD]
[TD]-[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]AAA1[/TD]
[TD]-[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]ACA6[/TD]
[TD]-[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]BAB2[/TD]
[TD]-[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]HAI32[/TD]
[TD]-[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]BAB2[/TD]
[TD]-[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Sheet3 (result table, where only the header row is given)

[TABLE="class: grid, width: 301"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Item Description[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]AAA1[/TD]
[TD]qwe[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]BAB2[/TD]
[TD]ner[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

In A2 of Sheet3 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$A$2:$A$5,SMALL(IF(FREQUENCY(IF(1-(Sheet1!$A$2:$A$5=""),IF(ISNUMBER(MATCH(Sheet1!$A$2:$A$5,Sheet2!$A$2:$A$7,0)),MATCH(Sheet1!$A$2:$A$5,Sheet1!$A$2:$A$5,0))),ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),ROWS($A$2:A2))),"")

In B2 just enter and copy down:

=IF($A2="","",VLOOKUP($A2,Sheet1!A:C,MATCH(B$1,INDEX(Sheet1!A:C,1,0),0),0))

In C2 just enter and copy down:

=IF($A2="","",SUMIFS(Sheet1!C:C,Sheet1!A:A,$A2)+SUMIFS(Sheet2!C:C,Sheet2!A:A,$A2))

 

[djuunk]

Thank you all for your responses. I will test the methods with my next set of data.
For those who are curious how I did this the looooooong way, here is how (If you can find another shortcut from my long method, please do teach):

-I got the list of all items with ID, Description, Qty of Set1
-List of all items with ID, Description, Qty of Set2
-Consolidate Set1 into new destination
-Consolidate Set2 into new destination
-MATCH function to match Set1 IDs to Set2 IDs into a temporary "Match" column (N/As are not values in both sets. Any number >=1 means part ID is used in both sets)
-Copy/Cut and Paste values to new sheet for final organization
-VLOOKUP function for quantity of part ID in Set2
-SUM Function to add quantity of part ID Set1 and Set2
-Filter out all N/A of Match --> leaves you with only the items that are in both sets of data.

This is obviously the manual way of doing this, but it doesn't take too long. I am a beginner-intermediate level Excel user, just trying my best to create efficiency at my new job as a purchasing/data analysis specialist (entry-level to mid-level position).
Anyways, if you would like to revise my method, feel free. I would love to hear some feedback on my mental map of how I wanted to do this. I will most definitely learn the effective methods listed by you generous helpers on the next project requiring similar end-goal.

 

[Aladin Akyurek]

@djuunk

See post #6 .

 

[djuunk]

@Aladin Akyurek

Definitely will reference that. Thank you.