Hi, I'm in a pickle and I can see a way out of it, but just not how to do it, arrghhhhhhhh!
We've got a [looong] list of more than 10,000 records which has on it:
Col A: old_product ID's
Col B: their customers.
(one old_product ID can have multiple customers)
And we need to work out how to integrate our new information which is even longer which has:
[this on a separate file...]
Col A: new_product ID
Col B: its associated products
(one new_product ID can have multiple associated products).
What we'd really like is a new listing which shows:
Col A: old_product ID
Col B: their_customers
Col C: new_product ID's
Col D: multiple_associated_products
The way I'd done this on a small scale was by duplicating rows and vlookups and attempted index matches, but frankly it's a mess and I wonder if somebody in the lovely world of "mr excel" may have some better ideas?
I'm open to using helper columns, UDF's, macro's and buying people a coffee to help get a method which will work!
Best
Neil
PS just to add a further fly in the ointment, rather than doing an exact match on the new and old product_ID's, they would like the list to take into account "partial matches" where the new_product_id *contains* the old_product_ID
We've got a [looong] list of more than 10,000 records which has on it:
Col A: old_product ID's
Col B: their customers.
(one old_product ID can have multiple customers)
And we need to work out how to integrate our new information which is even longer which has:
[this on a separate file...]
Col A: new_product ID
Col B: its associated products
(one new_product ID can have multiple associated products).
What we'd really like is a new listing which shows:
Col A: old_product ID
Col B: their_customers
Col C: new_product ID's
Col D: multiple_associated_products
The way I'd done this on a small scale was by duplicating rows and vlookups and attempted index matches, but frankly it's a mess and I wonder if somebody in the lovely world of "mr excel" may have some better ideas?
I'm open to using helper columns, UDF's, macro's and buying people a coffee to help get a method which will work!
Best
Neil
PS just to add a further fly in the ointment, rather than doing an exact match on the new and old product_ID's, they would like the list to take into account "partial matches" where the new_product_id *contains* the old_product_ID
