Simple spreadsheet help. I have two excel files, need help filtering them together for certain results.?

Question

Okay, let’s see if I can explain this clearly enough. I have 2 excel files, one is just a master listing of some product codes (master listing). The other file is a listing of some of the product codes, along with quantities next to them (inventory listing). Now, what I’m trying to accomplish is to filter these two files together and still have my master listing, but with the product codes next to them. Something like this:

Master Listing file:
500251
500313
500452
500556
500617
500721

Inventory Listing file (42,214, and 7 are quantities):
500313 – 42
500556 – 214
500617 – 7

What I’m trying to accomplish is a spreadsheet that filters the data into the master listing, something like this:
500251
500313 – 42
500452
500556 – 214
500617 – 7
500721

BTW, The quantities are in a different cell than the product codes. I use excel a lot, but this is a little over my head. I’ve also attempted at MS Access, but I’m a serious noob for that program. Any advice? Thanks!

Answer 1

cut/paste :)

hi adam,
theres 2 ways to do this.

-edit
turns out theres 3 ways...see vba in other answer
-end edit

1st and easiest is to create a pivot tale:
copy your inventory list
paste your inventory list directly underneath your master list.
fill in all blanks with 0's, and give the columns titles.
make a pivot table

2nd way is to use vlookup.
since i dont know your filename...just copy your inventory list and paste to Sheet2

but lets just use your example data:
your master list is in Sheet1 column A
so your inventory list table is in Sheet2 columnA and Column B

copy this formula in sheet1 B1

=IF(ISNA(VLOOKUP(A1,
Sheet2!$A$1:$B$3,2,0)
),"",VLOOKUP(A1,She
et2!$A$1:$B$3,2,0))

-edit
i had to cut it up into 4 lines, but it should all be 1 formula. so put it all together in notepad or word, then paste.

also, if you dont mind a bunch of #N/A, then you can just use the vlookup by itself. but since you showed you wanted it clean with blanks, i went with the if statement.