Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Model & Serial Numbers | Excel Worksheet Functions | |||
Giant font sizes in chart text boxes | Charts and Charting in Excel | |||
IF statement comparing month and year separately | Excel Worksheet Functions | |||
Print Preview Mode - without giant Page 1 | Excel Discussion (Misc queries) | |||
Matching parts to model numbers | Excel Discussion (Misc queries) |