I would like to join 2 tables together and I don't know a good way to do it. Table A has all the real possible combinations of categories that I need, which is about 50,000 possibilities. Any "Total" in the data in Table A (highlighted in yellow) means any value will match.
Table B has actual demand and forecast data at the lower product # level with about 25,000 records that I will later use to run some forecast accuracy calculations.
I want to join the two tables together with the grey headed columns as key fields (right join) but using a sort of wildcard if there is "Total" in one of the fields of Table A.
For example, Records 1 and 2 from Table B should join with Category row 2 of Table A because any region matches "Total". However, only record 1 from Table B should join with Category row 1 of Table A because that category row needs region AIM specifically. Obviously, the table will expand into millions of records when this join is done.
I don't know if this can be done with some iterative macro or some other trick. Can someone help?
Solved! Go to Solution.
Joshua,
Thanks again for your help. I had not thought of an append then filter solution for a complicated join but you have taught me a new way of thinking. I found that the macro really didn't speed up my workflow compared to the append-filter you originally suggested. The append takes about 18 minutes but it is creates around 1.4 Billion records. The immediate filter removes all but around 1.5 Million which is much more manageable. I only need to run this work once a month anyway. It allows me to finally remove a slow and rigid excel macro based chain of files that I inherited.