| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding a Custom Column based on an existing Column (EXPERT)
I need to add a new column based on an existing column of data.
The existing data is in the format below: ID67 ID6802 ID6802 ID61 IA91 IA92 BMID75 BMID25 Each one of the data points will be grouped into different categories like below, ID67 = Red ID6802 = Orange ID6802 = Orange ID61 = Pink IA91 = Brown IA92 = Brown BMID75 = Black BMID25 = Black I would manually do it but there are a couple thousand rows. Also there are about 400 different types of data ("IA91") that have an associated group ("Brown"). Can someone give me a formula for this that can be applied to multiple spreadshirts once the association is established? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding a Custom Column based on an existing Column (EXPERT)
Have a look in Help at VLOOKUP
Then come back if more questions best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sean W." <Sean wrote in message ... I need to add a new column based on an existing column of data. The existing data is in the format below: ID67 ID6802 ID6802 ID61 IA91 IA92 BMID75 BMID25 Each one of the data points will be grouped into different categories like below, ID67 = Red ID6802 = Orange ID6802 = Orange ID61 = Pink IA91 = Brown IA92 = Brown BMID75 = Black BMID25 = Black I would manually do it but there are a couple thousand rows. Also there are about 400 different types of data ("IA91") that have an associated group ("Brown"). Can someone give me a formula for this that can be applied to multiple spreadshirts once the association is established? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding a Custom Column based on an existing Column (EXPERT)
I am recieving an error when I use the VLOOKUP Function. Below is the actual
formula I am using. =VLOOKUP(O3,Sheet2!A3:B6:$B$8,2,TRUE) With O3 Cell "ID65" and the reference table on sheet2 being: ID61 3 ID65 2 ID67 4 ID68 1 The error states a VALUE# error. Anyone know how to fix this problem? "Bernard Liengme" wrote: Have a look in Help at VLOOKUP Then come back if more questions best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sean W." <Sean wrote in message ... I need to add a new column based on an existing column of data. The existing data is in the format below: ID67 ID6802 ID6802 ID61 IA91 IA92 BMID75 BMID25 Each one of the data points will be grouped into different categories like below, ID67 = Red ID6802 = Orange ID6802 = Orange ID61 = Pink IA91 = Brown IA92 = Brown BMID75 = Black BMID25 = Black I would manually do it but there are a couple thousand rows. Also there are about 400 different types of data ("IA91") that have an associated group ("Brown"). Can someone give me a formula for this that can be applied to multiple spreadshirts once the association is established? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding a Custom Column based on an existing Column (EXPERT)
On Jan 16, 9:37*am, Sean W. wrote:
I am recieving an error when I use the VLOOKUP Function. *Below is the actual formula I am using. =VLOOKUP(O3,Sheet2!A3:B6:$B$8,2,TRUE) With O3 Cell "ID65" and the reference table on sheet2 being: ID61 * *3 ID65 * *2 ID67 * *4 ID68 * *1 The error states a VALUE# error. *Anyone know how to fix this problem? "Bernard Liengme" wrote: Have a look in Help at VLOOKUP Then come back if more questions best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sean W." <Sean wrote in message ... I need to add a new column based on an existing column of data. The existing data is in the format below: ID67 ID6802 ID6802 ID61 IA91 IA92 BMID75 BMID25 Each one of the data points will be grouped into different categories like below, ID67 * = *Red ID6802 *= Orange ID6802 *= Orange ID61 *= Pink IA91 *= Brown IA92 *= Brown BMID75 = Black BMID25 = Black I would manually do it but there are a couple thousand rows. *Also there are about 400 different types of data ("IA91") that have an associated group ("Brown"). Can someone give me a formula for this that can be applied to multiple spreadshirts once the association is established?- Hide quoted text - - Show quoted text - Sean, a couple of thoughts: Make sure there are no extraneous characters in your data; either in the 1st data column, or in your value to be looked up column. The TRIM function will remove unwanted spaces that can hose your lookup; if they exist. You might also try using the =TEXT(A1,0) etc.formula to make sure your data is on the same playing field. Use another column to clean up the data with that formula, and paste them back to their original positions. See if those might do the trick. Pierre |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding a Custom Column based on an existing Column (EXPERT)
The table ranges are incorrect in your formula - I think you mean
this: =VLOOKUP(O3,Sheet2!A3:B6,2,FALSE) Hope this helps. Pete On Jan 16, 3:37*pm, Sean W. wrote: I am recieving an error when I use the VLOOKUP Function. *Below is the actual formula I am using. =VLOOKUP(O3,Sheet2!A3:B6:$B$8,2,TRUE) With O3 Cell "ID65" and the reference table on sheet2 being: ID61 * *3 ID65 * *2 ID67 * *4 ID68 * *1 The error states a VALUE# error. *Anyone know how to fix this problem? "Bernard Liengme" wrote: Have a look in Help at VLOOKUP Then come back if more questions best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sean W." <Sean wrote in message ... I need to add a new column based on an existing column of data. The existing data is in the format below: ID67 ID6802 ID6802 ID61 IA91 IA92 BMID75 BMID25 Each one of the data points will be grouped into different categories like below, ID67 * = *Red ID6802 *= Orange ID6802 *= Orange ID61 *= Pink IA91 *= Brown IA92 *= Brown BMID75 = Black BMID25 = Black I would manually do it but there are a couple thousand rows. *Also there are about 400 different types of data ("IA91") that have an associated group ("Brown"). Can someone give me a formula for this that can be applied to multiple spreadshirts once the association is established?- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| making a new column based on data in an existing column | Excel Discussion (Misc queries) | |||
| Adding a Baseline Value into my existing column graph. | Excel Worksheet Functions | |||
| Adding one column based on results from another column | Excel Discussion (Misc queries) | |||
| adding extra text into an existing column | Excel Worksheet Functions | |||
| Adding Values Based on a Separate Column | Excel Discussion (Misc queries) | |||
Hybrid Mode
