VBA to find missing items between 4 sheets and name sheets that have and not

Hey guys, I have a big deal here...

I have 4 sheets (DIA,TOP,ONIX and JADE) with many data in it each. Sheets have data in comum but do have unique data as well. I already have a VBA code that generated another sheet (Master) with all data from every sheet (unique or not). But it's not enough for my work.

Book1 macro with all data.xlsm

data in excel is grouped by 4 equal columns in all sheets, check the worksheet as well:

A -> Item code (only number in the format 000-00-000)
B -> Revision code (A,B,C, D, E, F, J, H, I)
C -> Item description
D -> Supplier name

Check what I want VBA to do:

1 - With this "Master" sheet, I want the code not only to copy the data but show where it came from as well (sheet name);
2 - Compare column A of each sheet to the rest of sheets and filter the missing items, pasting the sheet name that have the item and pasting as well the sheets that might don't have. For exemple, compare one DIA item with All the other and then check what the different items, paste the item number and show each sheets contains this item and which sheet is missing this item.

For example:
850-00-404 (column a) revision(column b) descrpition(column c) supplier (column d) vessels that contain the item(column e) sheets that don't contain the item (column f)

Hey guys, this is very important for meeee!! I'm counting on you geniuses guys that we have here!!

I hope I've explained enough for u guys!! Follow the excel worksheet



Thankssss a lot!!

Hi, asafebass
If you could wirte some results you want in Master sheet, maybe someone could supplys the code you need.

Vba should seach items by item number and copy all the rows only from the first result (since the rest of the columns may vary, but item number is equal) but should also verify if the item number appears in the other sheets, returning where the item number appears and where not. I want something like that:

Item revision Description Supplier Item appers on Item is missing on
000-000-000 A Fire hose Jincle JADE, ONIX, DIA TOP

It seemed there are duplicate number in MASTER sheet. it's better to delete them.
try this

Hi, asafebass
Both of 223-01-404 and 223-01-403 are double in Master sheet, if you remove duplicate lines, the minor problems will disappear too, I believe.

Simcode, thanks a lot buddy!! It beautifully worked!!


But i'm still need your help! Since you could help me in all questions!

I'm facing problems with the items description... some of then don't match in relation to another sheet.

How can I create a VBA code to copy only the "C" column (description) data for a specif item number from all data sheets (dia,top,onix,jade - in sequence) to sheet1? the excel file is attached here Book1 macro with all data final.xlsm. Thanks a lot man, you're really helping me!

As your wisht

Hey man, thanks a lot, it worked perfectly!!! Sorry for the late answer, tou really helped me a lot man! Have a good week!