Find all matching values in two tables and return identifier

**Nora Kro** · 08-16-2018, 02:47 PM

Hi All,

I have two tables, for both I have two options of presenting the data: (the data is just an example, the actual dataset is way bigger)

Table A1
Customer_ID | Purchased UPCs
43610023099 | 102
43610023099 | 5
43610023099 | 10
43610023090 | 102
43610023090 | 20
43610023090 | 15

Table A2
Customer_ID | Purchased UPCs
43610023099 | 102, 5, 10
43610023090 | 102, 20, 15

Table B1
Coupon_ID | Connected UPCs
Coupon_1 | 102
Coupon_1 | 3
Coupon_2 | 20
Coupon_2 | 11

Table B2
Coupon_ID | Connected UPCs
Coupon_1 | 102, 3
Coupon_2 | 20, 11

What I am trying to do is basically the following:
If any UPC of a customer matches any UPC of a Coupon, return that Coupon.

So I just want to show that they are connected through the UPC.
The result could be anything from:

Customer_1 | Coupon_1
Customer_1 | Coupon_3
Customer_2 | Coupon_2

or

Customer_1 | Coupon_1, Coupon_3
Customer_2 | Coupon_2

or

Coupon_1 | Customer_1
Coupon_2 | Customer_2
Coupon_3 | Customer_1

I am willing to do Macros in order to achieve this. And I thought about using a Match function with IF and eventually Arrays? But I am really struggeling to make something up that works.

PLEASE HELP!

**Xer2** · 08-16-2018, 03:40 PM

Take a look at the attached spreadsheet. I have named each table (both version '1') and done a 'vlookup' and 'concatenation' to create a conjoined field. The results are then displayed in a pivot table.

**brant.wheeler** · 08-16-2018, 04:30 PM

The file posted above doesn't seem correct, as it has lines for each instance, even when there is not a match.

My solution incorporates VBA. I think VBA is the best approach here since you said the data sets are large. Using formulas or pivots will bog down the workbook and can get very messy. I have attached a workbook with the VBA code in it along with your sample data. I think using Table A1 and Table B1 is probably the cleanest way to tackle this problem. You can take the full data sets, post them in the "Table A1" and "Table B1" tabs and then use the button in the "List" tab to run the macro and get your results.

Let me know if you have any questions.

Thanks!