Formula for classification system

  • Thread starter Thread starter Dean
  • Start date Start date
D

Dean

Hi all,

Essentially what I am after is a formula, which I am assuming will
have to include VLOOKUP at some point, that will allow me to classify
data into a five character classification system.

A spreadsheet with the data can be found here:
http://spreadsheets.google.com/ccc?key=pIZmEkZZWJ0g3zkJbqI-EPA

The first worksheet 'Class test' has my sample data, and the second
worksheet 'Class VLOOKUP' is the actual data definitions. In column F-
H on worksheet 2 are example codes derived from that actual data
definitions. By using the data located in columns A-G on worksheet 1 I
would like to end up with that 5 character classification in column H.

As before, I would be very grateful if you could offer any assistance
regarding this. If you have further questions. please let me know.

Thanks in advance,
Dean
 
Hi Dean
What you want can be done but with some modification to your form and
descriptions.
=VLOOKUP('Class test'!A3,C2:D8,2,FALSE) will give you the first letter "T"
=VLOOKUP('Class test'!B3,C11:D17,2,FALSE) will give you your second letter
"W" and you can see how that works.
Problems: What lookup is reading on one page, it's got to be the same
wording on the second page eg: Roughcst,etc "A4" on first page lookup on
second page is what ?
maybe C4 but the software can't guess that. You need to put the same wording
on both forms.
I changed column B information to column D. that's for VLOOKUP to work ok
I found lots of empty space at the beginning and after your descriptions,
are you importing this information or are you typing it. You will get #NA
error if you leave those empty space .
Last you can CONCATENATE all letters this way =I3&J3&K3&L3&M3. Each letter &
number is your VLOOKUP cell, you would type this in "Class test H Column.
HTH
John
 
Hi Dean
What you want can be done but with some modification to your form and
descriptions.
=VLOOKUP('Class test'!A3,C2:D8,2,FALSE) will give you the first letter "T"
=VLOOKUP('Class test'!B3,C11:D17,2,FALSE) will give you your second letter
"W" and you can see how that works.
Problems: What lookup is reading on one page, it's got to be the same
wording on the second page eg: Roughcst,etc "A4" on first page lookup on
second page is what ?
maybe C4 but the software can't guess that. You need to put the same wording
on both forms.
I changed column B information to column D. that's for VLOOKUP to work ok
I found lots of empty space at the beginning and after your descriptions,
are you importing this information or are you typing it. You will get #NA
error if you leave those empty space .
Last you can CONCATENATE all letters this way =I3&J3&K3&L3&M3. Each letter &
number is your VLOOKUP cell, you would type this in "Class test H Column.
HTH
John
Click to expand...

Thanks for your help John. As you suggested, once the spaces were
removed all of the formuals retuned the correct data. Well done with
this, I guess I was over-thinking it; your solutions are quite simple
(in hindsight).

Thanks again,
Dean
 
You're Welcome
Hi Dean
What you want can be done but with some modification to your form and
descriptions.
=VLOOKUP('Class test'!A3,C2:D8,2,FALSE) will give you the first letter "T"
=VLOOKUP('Class test'!B3,C11:D17,2,FALSE) will give you your second letter
"W" and you can see how that works.
Problems: What lookup is reading on one page, it's got to be the same
wording on the second page eg: Roughcst,etc "A4" on first page lookup on
second page is what ?
maybe C4 but the software can't guess that. You need to put the same
wording
on both forms.
I changed column B information to column D. that's for VLOOKUP to work ok
I found lots of empty space at the beginning and after your descriptions,
are you importing this information or are you typing it. You will get #NA
error if you leave those empty space .
Last you can CONCATENATE all letters this way =I3&J3&K3&L3&M3. Each letter
&
number is your VLOOKUP cell, you would type this in "Class test H Column.
HTH
John
Click to expand...

Thanks for your help John. As you suggested, once the spaces were
removed all of the formuals retuned the correct data. Well done with
this, I guess I was over-thinking it; your solutions are quite simple
(in hindsight).

Thanks again,
Dean
 
Back
Top