PDA

View Full Version : Return Missing Lookups



Sir Babydum GBE
11-27-2007, 06:29 AM
Hi

Please have a look at the attached file.

The master list would be a weekly file I'd receive. My Lookup tabel would really be on a separate sheet - I'd have a new sheet for each "shop" with the same lookups on each.

My problem is that in real life, there may my many hundreds of different products, but I want the individual shop sheets to have tables that display only the products they sell.

However, there may be times where a shop starts a new product - and so I want to be alerted if there is a product on the master sheet in a given shop that is not one of the criteria on that shop's lookup table.

So in I3 and I4 formulas would return "Saw" and "Screws" respectively because the products are present on the master list for shop 1, but missing on shop 1's lookup table.

Can this be done?

Thanks.

Bob Phillips
11-27-2007, 07:47 AM
How about this approach

Sir Babydum GBE
11-27-2007, 08:07 AM
How about this approach

Thanks a lot.

It's good - because it highlights the missing tools - but here the problem is that if I'm to have an exhaustive list of tools on each sheet, then I may as well run an advanced filter for unique records on the master sheet and just have a very long lookup table for each shop anyway.

So I was hoping for a list of, say, 10 cells that would return only the missing tools for that shop - then I would add them to the lookup table manually.

I know it's not an efficient way of working with spreadsheets - but I'm bound to stick with the spreadsheets I'm given from others.

I defer to your vastly superior knowledge though - so I'll assume my preferred method doesn't exist!

Bob Phillips
11-27-2007, 10:20 AM
Oh I didn't say that.

How about this

Sir Babydum GBE
11-27-2007, 12:07 PM
Oh I didn't say that.

How about this

It is sheer brilliance. :bow: I haven't got a clue what's going on in that formula though!

Thanks a million (I must owe you about that by now???) :beerchug:

Aussiebear
11-27-2007, 12:20 PM
I haven't got a clue what's going on in that formula though!

Its pretty simple Sir GBE, On one hand it takes the relative price of hard boiled eggs on every thrid Tuesday and compares them against the flight habits of the Australian emu...and what ever is missing logically from there is in the other list

That Bob is good, isn't he!