Hi All,
I asked this question yesterday and after JohnTopley masterfully explained and gave the answer, I forgot to add an important part to making this formula work. I tried sending JohnTopley a PM, but the system is acting weird. The original post was below:
http://www.excelforum.com/excel-form...ml#post4257647
The part that I did not fully explain is follows:
Data Set 1
Store #- Loc #- SKU
1A-------- 1------- W1
1A-------- 1------- W2
1A-------- 1------- W3
1B-------- 1------- W3
2A-------- 2------- W3
2A-------- 2------- W4
Data Set 2
Loc #- SKU
1------ W1
1------ W2
1------ W3
1------ W4
2------ W3
2------ W4
What I’m trying to accomplish is for the rule to first scan Store # for all matching then isolate the data into that group. This is where my original idea of, =IF(B2=B1,A1,IF(A1=1,0,1)) came from. So with in this case the logic will only first pick up on the 1A because they all share the same store #:
Data Set 1
Store #- Loc #- SKU
1A-------- 1------- W1
1A-------- 1------- W2
1A-------- 1------- W3
The only thing that matches both Data Sets 1 and 2 is the Loc #. After focusing on the three listed stores that share the same store #, then it will check with a vlookup or a match if the location from Data Set 2 has the SKU from Data Set 1
Data Set 2
Loc #- SKU
1------ W1 Caught
1------ W2 Caught
1------ W3 Caught
1------ W4 Missing
2------ W3
2------ W4
Data Set 1
Store #- Loc #- SKU
1A-------- 1------- W1
1A-------- 1------- W2
1A-------- 1------- W3
This process repeats for every unique Store # that shares value. Sorry for the lack of information and I apologize to JohnTopley for not explaining the problem fully and wasting his time on the first help. Thank you all in advance.
Bookmarks