Comparing Model Numbers without a giant IF statement
 

Well this one is a doozy...

I have a column which is going to be fed model numbers of laptops from a
barcode reader. Each row is for a different barcode, and I have functions to
split it into its respective pieces already. What I've been having trouble on
is comparing the actual Model number (7 or 8 digit #) to a table I have which
lists all the known model numbers.

What I have now is a giant table which spans a few columns and takes the
Model number, compares it to the table on a separate sheet, and if it finds a
match, returns the model(T30,T41,T42... etc.) If it doesn't find a match it
fills in the cell with a non-printable character. Then I concatenate the
columns into one cell, and remove the non-printable characters, leaving the
laptop model.

Each comparison is done in its own cell, and basically what I'm wondering is
if there is a way for me to shrink this down. Eventually what I'll have is a
list of a couple hundred barcodes, and the giant "IF" table that I'm using
for comparisons seems unnecessary.

Here's an example of one of the rows that I use for figuring out the model:

S2=The model number
B#=The cell containing known model numbers
AA#=The cell containing model (T30,T41,T42 etc)
AA8=The cell containing the non-printable character

(Row 2)
=IF(S2='count of type'!B1,AA2,AA8)
=IF(S2='count of type'!B2,AA2,AA8)
=IF(S2='count of type'!B3,AA3,AA8)
=IF(S2='count of type'!B4,AA4,AA8)
=IF(S2='count of type'!B5,AA4,AA8)
=IF(S2='count of type'!B6,AA5,AA8)
=IF(S2='count of type'!B7,AA5,AA8)
=IF(S2='count of type'!B8,AA5,AA8)
=IF(S2='count of type'!B9,AA6,AA8)
=IF(S2='count of type'!B10,AA6,AA8)
=IF(S2='count of type'!B11,AA6,AA8)
=IF(S2='count of type'!B12,AA6,AA8)
=IF(S2='count of type'!B13,AA6,AA8)
=IF(S2='count of type'!B14,AA7,AA8)

There has to be a way for me to shrink this thing down to only a couple of
cells but I'm stumped, any takers? Thanks in advance!

Comparing Model Numbers without a giant IF statement
 

try using the vlookup function

=vlookup(s2,'count of type'!$b$1:$aa$14,26,0)

note the 26 refers to col AA (which is 26 columns to the right of
column B)



On Jun 11, 3:45 pm, Shawn Conn
wrote:
Well this one is a doozy...

I have a column which is going to be fed model numbers of laptops from a
barcode reader. Each row is for a different barcode, and I have functions to
split it into its respective pieces already. What I've been having trouble on
is comparing the actual Model number (7 or 8 digit #) to a table I have which
lists all the known model numbers.

What I have now is a giant table which spans a few columns and takes the
Model number, compares it to the table on a separate sheet, and if it finds a
match, returns the model(T30,T41,T42... etc.) If it doesn't find a match it
fills in the cell with a non-printable character. Then I concatenate the
columns into one cell, and remove the non-printable characters, leaving the
laptop model.

Each comparison is done in its own cell, and basically what I'm wondering is
if there is a way for me to shrink this down. Eventually what I'll have is a
list of a couple hundred barcodes, and the giant "IF" table that I'm using
for comparisons seems unnecessary.

Here's an example of one of the rows that I use for figuring out the model:

S2=The model number
B#=The cell containing known model numbers
AA#=The cell containing model (T30,T41,T42 etc)
AA8=The cell containing the non-printable character

(Row 2)
=IF(S2='count of type'!B1,AA2,AA8)
=IF(S2='count of type'!B2,AA2,AA8)
=IF(S2='count of type'!B3,AA3,AA8)
=IF(S2='count of type'!B4,AA4,AA8)
=IF(S2='count of type'!B5,AA4,AA8)
=IF(S2='count of type'!B6,AA5,AA8)
=IF(S2='count of type'!B7,AA5,AA8)
=IF(S2='count of type'!B8,AA5,AA8)
=IF(S2='count of type'!B9,AA6,AA8)
=IF(S2='count of type'!B10,AA6,AA8)
=IF(S2='count of type'!B11,AA6,AA8)
=IF(S2='count of type'!B12,AA6,AA8)
=IF(S2='count of type'!B13,AA6,AA8)
=IF(S2='count of type'!B14,AA7,AA8)

There has to be a way for me to shrink this thing down to only a couple of
cells but I'm stumped, any takers? Thanks in advance!

Comparing Model Numbers without a giant IF statement
 

Well, it sorta works...

"=VLOOKUP(S2,'count of type'!$B$1:$B$14,1,0)"

I had to change it as the table that I'm looking in is just one column. This
however just returns the model number (#######/#) rather the model
(T30,T41,T42 etc.) What I was thinking though, is that to the left of the
column that I'm searching in is another column which informs me what each
model number corresponds to. Any way to use the VLOOKUP function to return
the value to the left of the "found" cell?

"Tim879" wrote:

try using the vlookup function

=vlookup(s2,'count of type'!$b$1:$aa$14,26,0)

note the 26 refers to col AA (which is 26 columns to the right of
column B)



On Jun 11, 3:45 pm, Shawn Conn
wrote:
Well this one is a doozy...

I have a column which is going to be fed model numbers of laptops from a
barcode reader. Each row is for a different barcode, and I have functions to
split it into its respective pieces already. What I've been having trouble on
is comparing the actual Model number (7 or 8 digit #) to a table I have which
lists all the known model numbers.

What I have now is a giant table which spans a few columns and takes the
Model number, compares it to the table on a separate sheet, and if it finds a
match, returns the model(T30,T41,T42... etc.) If it doesn't find a match it
fills in the cell with a non-printable character. Then I concatenate the
columns into one cell, and remove the non-printable characters, leaving the
laptop model.

Each comparison is done in its own cell, and basically what I'm wondering is
if there is a way for me to shrink this down. Eventually what I'll have is a
list of a couple hundred barcodes, and the giant "IF" table that I'm using
for comparisons seems unnecessary.

Here's an example of one of the rows that I use for figuring out the model:

S2=The model number
B#=The cell containing known model numbers
AA#=The cell containing model (T30,T41,T42 etc)
AA8=The cell containing the non-printable character

(Row 2)
=IF(S2='count of type'!B1,AA2,AA8)
=IF(S2='count of type'!B2,AA2,AA8)
=IF(S2='count of type'!B3,AA3,AA8)
=IF(S2='count of type'!B4,AA4,AA8)
=IF(S2='count of type'!B5,AA4,AA8)
=IF(S2='count of type'!B6,AA5,AA8)
=IF(S2='count of type'!B7,AA5,AA8)
=IF(S2='count of type'!B8,AA5,AA8)
=IF(S2='count of type'!B9,AA6,AA8)
=IF(S2='count of type'!B10,AA6,AA8)
=IF(S2='count of type'!B11,AA6,AA8)
=IF(S2='count of type'!B12,AA6,AA8)
=IF(S2='count of type'!B13,AA6,AA8)
=IF(S2='count of type'!B14,AA7,AA8)

There has to be a way for me to shrink this thing down to only a couple of
cells but I'm stumped, any takers? Thanks in advance!