Query

  • Thread starter Thread starter KiwiBrian
  • Start date Start date
K

KiwiBrian

I have a category column where each entry consists of one of 600 different
categories.
I want to be able to add an extra column containing one of about 30 more
generic higher level categories so that I can extract from the database by
selecting on the entries in this column.
I know how to do the selection process.
Where I would like a pointer is how to use the contents of a seperate two
column table which gives the higher level category for each of the 600 lower
level ones, to insert these new generic categories in the new column of the
database.
This would allow me to create a selection of different "selection criteria"
tables, and by placing the generic categories in different columns, allow
easy selection of the desired extraction process by the desired criteria by
selecting on the desired column.
I hope this is easier to understand than to describe.
Any help would be appreciated.
Brian Tozer.
 
VLOOKUP would be one way ..:

Assume the 2 col reference table is:

In Sheet1,
cols A and B, data from row2 down
-----------------
Cat HigherCat
100 ABC
101 DEF
102 GHI
etc

In Sheet2
--------------
you have "Cat" in col A, and would like to
extract the corresponding "HigherCat" in col B, viz.:

Cat HigherCat
102
101
100
etc

Put in B2: =VLOOKUP(A2,Sheet1!A:B,2,0)
Copy B2 down

Col B will return the matching "HigherCat"s
from the reference table in Sheet1, viz.:

Cat HigherCat
102 GHI
101 DEF
100 ABC
etc

Or, perhaps better with an error trap to return blanks ("")
instead of #NAs, for any non-matching items in "Cat"

Put instead in B1:
=IF(ISNA(MATCH(A2,Sheet1!A:A,0)),"",VLOOKUP(A2,Sheet1!A:B,2,0))
Copy down
 
Back
Top