Create list of matches from two master lists

jobylewis

New Member
Joined
Apr 7, 2004
Messages
11
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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi - Welcome to the board

Can you just make A and B column width = 0

Also you can setup an autofilter and just filer by the 1 for a match.
 
Upvote 0
The reason I'm trying to get just one row with a total and a list of part numbers is because I want everyone in the company to be able to do this just by pulling down the new data. I don't want them to have to know how to filter, etc. I want them to be able to open the worksheet and have it calculate after it updates with new part numbers.
 
Upvote 0
Book1
ABCD
1#OfCommonParts2
2InternPart#ExternPart#0CommonList
3X253X491 X152
4X152X1521X616
5X187X353  
6X616X6162 
7X504X568  
8X142X200  
9X189X630  
10X407X424  
11X377X281  
12X381X170  
13X528 
14X619 
15X657 
Sheet1


Formulas...

C2 must house a 0.

C3, which is copied down:

=IF((A3<>"")*ISNUMBER(MATCH(A3,B:B,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")

D1:

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

D3, which is copied down:

=IF(ROW()-ROW($D$3)+1<=$D$1,INDEX(A:A,MATCH(ROW()-ROW($D$3)+1,C:C,0)),"")
 
Upvote 0
Would this technique apply even if the cells are not adjacent to each other? I ask because our lists will never coincide like that. They're just two independent lists. Of maybe 1 million part numbers, we sell 36000 and they are asking for 1000. We may only carry 3. Therefore, the columns of numbers will rarely match any, and never match on the same row. Thanks a ton!
 
Upvote 0
jobylewis said:
Would this technique apply even if the cells are not adjacent to each other? I ask because our lists will never coincide like that. They're just two independent lists. Of maybe 1 million part numbers, we sell 36000 and they are asking for 1000. We may only carry 3...

If you're addressing my reply, the formula system will work as intended.
 
Upvote 0
Aladin, This works great, but the calculation of the cells is taking about 3 to 4 minutes. FYI, I'm testing on a 2.4GHz, Win XP Pro, 512MB RAM, Office 2003 with all updates.

Any suggestions for a faster calculation besides moving to something like Access or better?
 
Upvote 0
jobylewis said:
Aladin, This works great, but the calculation of the cells is taking about 3 to 4 minutes. FYI, I'm testing on a 2.4GHz, Win XP Pro, 512MB RAM, Office 2003 with all updates.

Any suggestions for a faster calculation besides moving to something like Access or better?

How big is the list the system has to process? Would it be possible for you to sort the 2nd list?
 
Upvote 0

Forum statistics

Threads
1,224,591
Messages
6,179,768
Members
452,940
Latest member
rootytrip

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top