Match to master

[blanchard306]

Ok, I have an issue and just can't think of the best way to figure it out.

I have a list with about 40K line items and I need to find what is missing for each Number group. In the attached sample of my data you can see that the first number combo on the Master tab is missing one from the Key tab. I need to know for each number combo which if any are missing from the key tab.

So logic is-

If Type, Prod & G/NG on Master Match on key there should be all the codes from the KEY Tab on the Master Tab. So below is a quick look at the data from the attached file. As you can see one is missing from number group 440. I need a fast way to run through all 40K line items on the Master tab and have it tell me what is missing from each "number" group. On the attached file you will see number group 41MM isn't missing any. Thanks for any help.17791



Master Tab

Key Tab


Type
Number
Code
Prod
G/NG

Type
Prod
Code
Date
G/NG


AF
440
A66AN
S7
G

AF
S7
A66AN
6/21/2010
G


AF
440
A66EN
S7
G

AF
S7
A66EN
2/1/2011
G


AF
440
S70AAN
S7
G

AF
S7
S70AAN
1/1/2011
G


AF
440
S70ACN
S7
G

AF
S7
S70ACN
2/13/2012
G


AF
440
S70AEL
S7
G

AF
S7
S70AEL
2/25/2013
G


AF
440
S70AEN
S7
G

AF
S7
S70AEN
2/25/2013
G


AF
440
S70AFL
S7
G

AF
S7
S70AFL
2/25/2013
G








AF
S7
S70AGN
9/23/2013
G

[onlyadrafter]

Hello,

not sure it this is exactly what you require, but it may be a start (not sure how slow it will be either).

On the Key tab (have assumed your data starts in cell A1) in F2 enter this formula:


=MATCH(A2&B2&C2&E2,Master!A:A&Master!D:D&Master!C:C&Master!E:E,0)

when you have entered it don't just press enter, press CTRL and SHIFT and ENTER (array formula), this will either give numbers (where a match is) or #N/A (no match).

Can also provide a VBA solution if you require

[blanchard306]

Thanks not quite what I need as this gives me a number of non matching I need something that I can either a formula I can use or vba the will automate the process that runs in the master tab to tell me which number groups are missing what from the key tab.

[SamT]

How does the code "know" that Prod S7, Code S70AGN belongs with Number Group 440?

All we can say from the data presented is that Master Type AF doesn't have that Product line.

[blanchard306]

At this point I don't have any code. I am just starting with this data and trying to figure out the fastest and best way to do this compare. I need a way to say if type, prod and N/NG match does the number group have all the "codes" from the key in it. I was thinking I might need to rearrange the data in the master to be horizontal and then do a match and see what is missing... but I don't know if that will work either....

[SamT]

I was thinking I might need to rearrange the data in the master to be horizontal and then do a match and see what is missing... but I don't know if that will work either....
The Worksheet Structure is fine as is.

Just one question:
How do WE "know" that Prod S7, Code S70AGN belongs with Number Group 440?

The best we cold do right now is to go thru the Key sheet and tell you that Prod S7, Code S70AGN is not anywhere in the Master sheet.