Displaying unique data entries when comparing two lists in Excel!

[BCArbalest]

Morning guys, its just after 1am and after several days bashing my head on a keyboard and searching the net im appealing to a higher power for assistance! Ive devised a stockcount spreadsheet in excel that is setup as the following:

Tab 1: Scan

Column G is labelled Boxes, column H is labelled Stock

The idea is that portable scanners are used to scan in the barcodes of stock and then their boxes to give me a stock check list of everything that is present.

Tab 2: Data

I copy-paste an excel export of the section I am stockchecking, from A3 down it lists the barcodes for all of the items that should be present for me to check against. It includes numbers listed on stock on the system so i can compare them to the scans and see whats missing.

Tab 3: Results

Ive setup excel to check the barcodes from the two lists in Tab 1 vs the list on Tab 2 and return the total number of each type of item. IE

Stock ID Item Stock Found Boxes
1554256 Snooker cues 4 4 3

This means I can see if i need to order more boxes or if stock is missing.

This all works fine. However if I scan in an item that isnt on my list on page 2 then it will not show up. What I would like is on Tab 3 a separate column so any barcodes scanned in on tab 1 that are not found on the comparison list on tab 2 will show up so I can investigate. I have tried everything I know and exhausted everything I can find trying to make this work! Any help would be greatly appreciated!

[ThirtyTwo]

morning

I think we'd need to see an example of the file you're working with, edit your original post, go advanced and Manage Attachments

[FDibbins]

I would start with a coutif()=0 to ID the new scans. We cold then build a formula to pull out those records. Upload a sample, showing what you have and what you want

[Tony Valko]

Try something like this...

Data Range

	A	B	C	D
1	Tab1 Code	Tab2 Code	-----	Missing
2	1111	8888		3333
3	2222	1111		4444
4	3333	7777		6666
5	4444	2222		9999
6	5555	5555
7	6666
8	7777
9	8888
10	9999

This array formula** entered in D2 and copied down until you get blanks:

=IFERROR(INDEX(A:A,SMALL(IF(ISNA(MATCH(A$2:A$10,B$2:B$6,0)),ROW(A$2:A$10)),ROWS(D$2:D2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.