Adding a Custom Column based on an existing Column (EXPERT)

Adding a Custom Column based on an existing Column (EXPERT)

Post by U2VhbiBXL » Thu, 17 Jan 2008 23:06:02


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?
 
 
 

Adding a Custom Column based on an existing Column (EXPERT)

Post by Bernard Li » Thu, 17 Jan 2008 23:27:13

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

 
 
 

Adding a Custom Column based on an existing Column (EXPERT)

Post by U2VhbiBXL » Fri, 18 Jan 2008 00:37:01

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?
 
 
 

Adding a Custom Column based on an existing Column (EXPERT)

Post by Pierr » Fri, 18 Jan 2008 00:49:20


> ID61 ??> > ID65 ?>2
> ID67 > ?
> ID6> ?gt;?
>
> The error states a VALUE# error. 燗nyone know how to fix this pr>bl>m?> >> >
>
> "Bernard Liengme" >r>te:
> > Have a look in Help at V>O>KUP
> > Then come back if more que>t>ons
> > best >i>hes
> > Bernard V L>e>gme
> > Microsoft Exc>l>MVP
> >www.stfx.ca/people/bl>e>gme
> > remove caps from>em>i>
>
> > "Sea< W." wrote in m>s>age

> > >I need to add a new column based on an existing column of>da>a> >>
> > > The existing data is in the format >el>w> >>
> > > >D>7
> > > >D>8>2
> > > >D>8>2
> > > >D>1
> > > >A>1
> > > >A>2
> > > >M>D>5
> > > >MI>2> >>
> > > Each one of the data points will be grouped into different categorie> >i>e
> > > >el>w> >>
> > > ID67 ?gt;=>?gt;ed
> > > ID6802 ?gt; >r>nge
> > > ID6802 >=>O>ange
> > > ID>1>?gt; Pink
> > > IA>1>?gt; Brown
> > > I>9> >= Brown
> > > BM>D>5>= Black
> > > BM>D2> > >lack
>
> > > I would manually do it but there are a couple thousand rows.>?gt;l>o ther> >>>> > are
> > > about 400 different types of data ("IA91") that have an asso>i>t>d group
> > > (>B>o>n").
>
> > > Can someone give me a formula for this that can be applied>t> >ultiple
> > > spreadshirts once the association is established?- Hide q>ot>d 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
 
 
 

Adding a Custom Column based on an existing Column (EXPERT)

Post by Pete_U » Fri, 18 Jan 2008 01:24:42

The table ranges are incorrect in your formula - I think you mean
this:

=VLOOKUP(O3,Sheet2!A3:B6,2,FALSE)

Hope this helps.

Pete


> ID61 ??> > ID65 ?>2
> ID67 > ?
> ID6> ?gt;?
>
> The error states a VALUE# error. 燗nyone know how to fix this pr>bl>m?> >> >
>
> "Bernard Liengme" >r>te:
> > Have a look in Help at V>O>KUP
> > Then come back if more que>t>ons
> > best >i>hes
> > Bernard V L>e>gme
> > Microsoft Exc>l>MVP
> >www.stfx.ca/people/bl>e>gme
> > remove caps from>em>i>
>
> > "Sea< W." wrote in m>s>age

> > >I need to add a new column based on an existing column of>da>a> >>
> > > The existing data is in the format >el>w> >>
> > > >D>7
> > > >D>8>2
> > > >D>8>2
> > > >D>1
> > > >A>1
> > > >A>2
> > > >M>D>5
> > > >MI>2> >>
> > > Each one of the data points will be grouped into different categorie> >i>e
> > > >el>w> >>
> > > ID67 ?gt;=>?gt;ed
> > > ID6802 ?gt; >r>nge
> > > ID6802 >=>O>ange
> > > ID>1>?gt; Pink
> > > IA>1>?gt; Brown
> > > I>9> >= Brown
> > > BM>D>5>= Black
> > > BM>D2> > >lack
>
> > > I would manually do it but there are a couple thousand rows.>?gt;l>o ther> >>>> > are
> > > about 400 different types of data ("IA91") that have an asso>i>t>d group
> > > (>B>o>n").
>
> > > Can someone give me a formula for this that can be applied>t> >ultiple
> > > spreadshirts once the association is established?- Hide q>ot>d text -
>
> - Show quoted text -