Create list of matches from two master lists

[jobylewis]

This is kind of hard to explain. Bear with me please.

I have a list of part numbers that we sell. I have imported a list of part numbers that people want to buy. I need to do the following:
1. Compare the two lists and find matches so we can sell these items
2. Sum the number of matches at the top of a third column
3. Create a resulting list of the part numbers under that sum total

The way I have done it was relatively simple. I think there has to be a way to get what I want that I just haven't found.

I have done #1 in column C =COUNTIF($A$1:$A$36019,B2)
And #2 is in column D =IF(C2>0,B2,"")

but what I now have is:
A2:A36018 is our part numbers
B2:BXXX is their part numbers (pulled from a database that changes daily)
C2:CXXX has 0s for non-matches and 1's for matches.
D2:DXXX has a list of part numbers that matched, but as you can imagine, they are maybe 5-10 cells out of 1000, so they're hard to find.

Ideally, I'd like to have A hidden, B hidden and just have one column that says the total number and the part numbers in a list. Any ideas?

Thanks in advance for any help!

 

[jobylewis]

The master list (part numbers we sell) is 36,000 part numbers. The list we are scrubbing (finding if we sell any of their requested part numbers) is usually between 10 and 250 part numbers.

 

[Aladin Akyurek]

The master list (part numbers we sell) is 36,000 part numbers. The list we are scrubbing (finding if we sell any of their requested part numbers) is usually between 10 and 250 part numbers.

Can we sort the latter in ascending order?

 

[jobylewis]

Yes, they come sorted in most cases, but if not I can put that into the instructions for the End User. It's just an added step for the two somewhat computer novices that are going to be using this worksheet. Thanks a ton.

 

[Aladin Akyurek]

Yes, they come sorted in most cases, but if not I can put that into the instructions for the End User. It's just an added step for the two somewhat computer novices that are going to be using this worksheet. Thanks a ton.

The speed trouble emanates from mainly too many formulas. Instead of comparing the longer list with the shorter one, I should have proposed the reverse process for this comparison fulfills exactly your purpose.

aaExtractCommonList jobylewis v2.xls
	A	B	C	D
1			#OfCommonParts	2
2	InternPart#	ExternPart#	0	CommonList
3	X253	X152	1	X152
4	X152	X170		X616
5	X187	X200
6	X616	X281
7	X504	X353
8	X142	X424
9	X189	X491
10	X407	X568
11	X377	X616	2
12	X381	X630
13	X528
14	X619
15	X657
Sheet1

Formulas...

C2:

=IF(ISNUMBER(MATCH(B3,A:A,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")

Note that the condition of IF is also reduced (for speed).

D1:

=LOOKUP(9.99999999999999E+307,C:C)

D3:

=IF(ROW()-ROW($D$3)+1<=$D$1,INDEX(B:B,MATCH(ROW()-ROW($D$3)+1,C:C,0)),"")

You might also want to sort the longer list (the list in column A) to speed up the process a bit.

 

[jobylewis]

Aladin - It's a good bit faster. I really appreciate the help.