Excel 2007 : Cross Referencing tables

**TheAesthete** · 03-04-2011, 09:09 AM

Ok, I've been asked to do a horrible task by my manager and I'm hoping you guys can help me out here.

I have two tables; Table 1 is of "Packages" and "Products" So down the side it has the title of our packages, and across the top it has the list of all our products, and then the cells are maked with a "1" if the package contains that product.

Table 2 is a list of our customers and the packages they have. So it's a very similar set up where the customer is down the side and the packages are across the top and a "1" is marked where the customers have bought that package.

(I didn't set things up like this...)

So now my boss has decided he'd like to know what products our customers have, So I need to somehow have a list of our customers down the side and a list of our products along the top and look these two tables up, cross referencing that if the customer is maked as having a package, all the products get marked for that customer. (Some customers will have multiples of one product because of the packages they have but i don't need to know this, as long as i know they have one)
I experimented breifly with "If" formula's but i think there are two many packages and products for this to be a time saver.

I have this horrible feeling I will have to go through this manually unless you guys know of a solution.

Thanks for your time and I hope that all makes sense.

**scottylad2** · 03-04-2011, 09:13 AM

Sounds doable. Can you upload a sample of what you have and how you would like it to look after, much easier using your layout than us guessing

**TheAesthete** · 03-04-2011, 11:58 AM

Thanks for your quick response, unfortunately my works Internet has gone down and can't upload attachments via my phone :S weirdly I can still email things, just not use IE.

I'll upload the test spreadsheet I've been fiddling around with asap.

**TheAesthete** · 03-07-2011, 06:52 AM

Hi Guys, internet is finally back up and running.

I've attached a mock up of what I have at the moment. Sheet1 is where I want the final data to appear. As you can see I've attempted a rather clumsy IF formula which works fine on this smaller collection of data, but would take me a long time to do with the amount of customers/packages/products we have and i'm sure there has to be a simplier and more transparent way of doing it.

Hope you can help.

Gareth