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?

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

Then come back if more questions

best wishes

--

Bernard V Liengme

Microsoft Excel MVP

www.stfx.ca/people/bliengme

remove caps from email

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?

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?

> 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."

> > >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

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 -

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."

> > >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 -

1. "Matching" column A's values in column B

2. Counting A's in a table column 1, F's in column 2, etc...

3. Add up contents of column, if nothing in that column add up contents of other column

4. Add columns in Available columns in Add/remove Columns

5. add column between two existing columns with ALTER statement

6. Add one column based on the data in another column

7. Macro to rearrange/add columns based on column header?

8. Adding column data based on match in 2nd column - Help

9. Newbie: adding and averaging columns, based on feature in column 2 (awk?)

10. Adding data in columns based on criteria in more than one column..

11. Add data to a listview column based on column header, not index???

12. How to add entires in column based on another column...

13. How to add values in one column based on values in another column

14. SELECT errors out saying a column doesn't exist that SHOW COLUMNS thinks does exist

15. Add up a column based on values in other columns

5 post • Page:**1** of **1**