Hello,

On worksheet6 I have 500 rows of data. In columnA it has data like H,
WH, O, B, AN. In columnB I would to add 1, 2, 3, 4, 5. I want

What my worksheet looks like now ColumnB is blank:

ColumnA
WH
O
O
B
AN
H
H
H

The way I would for my sheet to look like:

ColumnA ColumnB
WH 2
O 3
O 3
B 4
AN 5
H 1
H 1
H 1

Thank you for your help in advance,
jfcby

Set up a table in another range. Perhaps in Sheet2, if your main sheet is
Sheet1. The table (Sheet2!A1:A5) should look like this:

H
WH
O
B
AN

In Sheet1!B1 (next to WH in A1), enter this formula:

=MATCH(A1,Sheet2!$A1:$A5,0)

Fill this formula down as far as needed.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"jfcby" wrote in message
oups.com...
Hello,

On worksheet6 I have 500 rows of data. In columnA it has data like H,
WH, O, B, AN. In columnB I would to add 1, 2, 3, 4, 5. I want

What my worksheet looks like now ColumnB is blank:

ColumnA
WH
O
O
B
AN
H
H
H

The way I would for my sheet to look like:

ColumnA ColumnB
WH 2
O 3
O 3
B 4
AN 5
H 1
H 1
H 1

Thank you for your help in advance,
jfcby

jfcby wrote:
Hello,

On worksheet6 I have 500 rows of data. In columnA it has data like H,
WH, O, B, AN. In columnB I would to add 1, 2, 3, 4, 5. I want

What my worksheet looks like now ColumnB is blank:

ColumnA
WH
O
O
B
AN
H
H
H

The way I would for my sheet to look like:

ColumnA ColumnB
WH 2
O 3
O 3
B 4
AN 5
H 1
H 1
H 1

Thank you for your help in advance,
jfcby

Hi jfcby,

If your data starts in A1 then fill the following formula down column B

=MATCH(A1,{"H","WH","O","B","AN"},0)

Ken Johnson

Ken -

Like mine, only better.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Ken Johnson" wrote in message
ups.com...

Hi jfcby,

If your data starts in A1 then fill the following formula down column B

=MATCH(A1,{"H","WH","O","B","AN"},0)

Ken Johnson

Assume WH is in A1

in B1
=if(A1="H",1,if(A1="WH",2,if(A1="O",3,if(A1="B",4, if(A1="AN",5,"")))))

then drag fill down the column.

in code

Sub ABC()
Dim rng As Range
With Worksheets("Worksheet6")
Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
rng.Offset(0, 1).Formula = "=if(A1=""H"",1,if(A1=""WH"",2" & _
",if(A1=""O"",3,if(A1=""B"",4,if(A1=""AN"",5,"""") ))))"
' Option to replace formulas with values:
rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value
End Sub

--
Regards,
Tom Ogilvy



"jfcby" wrote in message
oups.com...
Hello,

On worksheet6 I have 500 rows of data. In columnA it has data like H,
WH, O, B, AN. In columnB I would to add 1, 2, 3, 4, 5. I want

What my worksheet looks like now ColumnB is blank:

ColumnA
WH
O
O
B
AN
H
H
H

The way I would for my sheet to look like:

ColumnA ColumnB
WH 2
O 3
O 3
B 4
AN 5
H 1
H 1
H 1

Thank you for your help in advance,
jfcby

Hello Tom,

Thank you for your response! I was wondering though when I insert the
formula in B1 and drag fill down the column it merges B1:B2, B3:B4,
B5:B6 and so forth down the column. How can I keep the cells from
merging when I drag fill down the column?

Thank you for your help,
jfcby

Tom Ogilvy wrote:
Assume WH is in A1

in B1
=if(A1="H",1,if(A1="WH",2,if(A1="O",3,if(A1="B",4, if(A1="AN",5,"")))))

then drag fill down the column.

in code

Sub ABC()
Dim rng As Range
With Worksheets("Worksheet6")
Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
rng.Offset(0, 1).Formula = "=if(A1=""H"",1,if(A1=""WH"",2" & _
",if(A1=""O"",3,if(A1=""B"",4,if(A1=""AN"",5,"""") ))))"
' Option to replace formulas with values:
rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value
End Sub

--
Regards,
Tom Ogilvy



"jfcby" wrote in message
oups.com...
Hello,

On worksheet6 I have 500 rows of data. In columnA it has data like H,
WH, O, B, AN. In columnB I would to add 1, 2, 3, 4, 5. I want

What my worksheet looks like now ColumnB is blank:

ColumnA
WH
O
O
B
AN
H
H
H

The way I would for my sheet to look like:

ColumnA ColumnB
WH 2
O 3
O 3
B 4
AN 5
H 1
H 1
H 1

Thank you for your help in advance,
jfcby

That only happens for me if B1 and B2 are already merged. So unmerge them
before entering the formula. The formula by itself will merge no cells - it
sounds like you are coping existing formatting.

--
Regards,
Tom Ogilvy


"jfcby" wrote in message
ps.com...
Hello Tom,

Thank you for your response! I was wondering though when I insert the
formula in B1 and drag fill down the column it merges B1:B2, B3:B4,
B5:B6 and so forth down the column. How can I keep the cells from
merging when I drag fill down the column?

Thank you for your help,
jfcby

Tom Ogilvy wrote:
Assume WH is in A1

in B1
=if(A1="H",1,if(A1="WH",2,if(A1="O",3,if(A1="B",4, if(A1="AN",5,"")))))

then drag fill down the column.

in code

Sub ABC()
Dim rng As Range
With Worksheets("Worksheet6")
Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
rng.Offset(0, 1).Formula = "=if(A1=""H"",1,if(A1=""WH"",2" & _
",if(A1=""O"",3,if(A1=""B"",4,if(A1=""AN"",5,"""") ))))"
' Option to replace formulas with values:
rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value
End Sub

--
Regards,
Tom Ogilvy



"jfcby" wrote in message
oups.com...
Hello,

On worksheet6 I have 500 rows of data. In columnA it has data like H,
WH, O, B, AN. In columnB I would to add 1, 2, 3, 4, 5. I want

What my worksheet looks like now ColumnB is blank:

ColumnA
WH
O
O
B
AN
H
H
H

The way I would for my sheet to look like:

ColumnA ColumnB
WH 2
O 3
O 3
B 4
AN 5
H 1
H 1
H 1

Thank you for your help in advance,
jfcby

Hello Tom,

I opened a new worksheet in my current workbook, I started a new
workbook and I checked the cells format to make sure that the merge
cells box was unchecked when I copy the formula to B1 it automatically
selects merge and wrap text.

Does the worksheet or cell automatically change the cell format when
you copy a formula? If so how can that format setting be changed?

Thank you for your help,
jfcby

Tom Ogilvy wrote:
That only happens for me if B1 and B2 are already merged. So unmerge them
before entering the formula. The formula by itself will merge no cells - it
sounds like you are coping existing formatting.

--
Regards,
Tom Ogilvy


"jfcby" wrote in message
ps.com...
Hello Tom,

Thank you for your response! I was wondering though when I insert the
formula in B1 and drag fill down the column it merges B1:B2, B3:B4,
B5:B6 and so forth down the column. How can I keep the cells from
merging when I drag fill down the column?

Thank you for your help,
jfcby

Tom Ogilvy wrote:
Assume WH is in A1

in B1
=if(A1="H",1,if(A1="WH",2,if(A1="O",3,if(A1="B",4, if(A1="AN",5,"")))))

then drag fill down the column.

in code

Sub ABC()
Dim rng As Range
With Worksheets("Worksheet6")
Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
rng.Offset(0, 1).Formula = "=if(A1=""H"",1,if(A1=""WH"",2" & _
",if(A1=""O"",3,if(A1=""B"",4,if(A1=""AN"",5,"""") ))))"
' Option to replace formulas with values:
rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value
End Sub

--
Regards,
Tom Ogilvy



"jfcby" wrote in message
oups.com...
Hello,

On worksheet6 I have 500 rows of data. In columnA it has data like H,
WH, O, B, AN. In columnB I would to add 1, 2, 3, 4, 5. I want

What my worksheet looks like now ColumnB is blank:

ColumnA
WH
O
O
B
AN
H
H
H

The way I would for my sheet to look like:

ColumnA ColumnB
WH 2
O 3
O 3
B 4
AN 5
H 1
H 1
H 1

Thank you for your help in advance,
jfcby

Select cell B1:B500
enter the formula in the formula bar

hit Ctrl+Enter instead of just enter.


or

Enter the formula in B1

select the cell, do Edit = Copy

Select B2:B500 (or to the last cell)

Do edit=Paste Special and select only formulas.

--
Regards,
Tom Ogilvy


"jfcby" wrote in message
oups.com...
Hello Tom,

I opened a new worksheet in my current workbook, I started a new
workbook and I checked the cells format to make sure that the merge
cells box was unchecked when I copy the formula to B1 it automatically
selects merge and wrap text.

Does the worksheet or cell automatically change the cell format when
you copy a formula? If so how can that format setting be changed?

Thank you for your help,
jfcby

Tom Ogilvy wrote:
That only happens for me if B1 and B2 are already merged. So unmerge
them
before entering the formula. The formula by itself will merge no cells -
it
sounds like you are coping existing formatting.

--
Regards,
Tom Ogilvy


"jfcby" wrote in message
ps.com...
Hello Tom,

Thank you for your response! I was wondering though when I insert the
formula in B1 and drag fill down the column it merges B1:B2, B3:B4,
B5:B6 and so forth down the column. How can I keep the cells from
merging when I drag fill down the column?

Thank you for your help,
jfcby

Tom Ogilvy wrote:
Assume WH is in A1

in B1
=if(A1="H",1,if(A1="WH",2,if(A1="O",3,if(A1="B",4, if(A1="AN",5,"")))))

then drag fill down the column.

in code

Sub ABC()
Dim rng As Range
With Worksheets("Worksheet6")
Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
rng.Offset(0, 1).Formula = "=if(A1=""H"",1,if(A1=""WH"",2" & _
",if(A1=""O"",3,if(A1=""B"",4,if(A1=""AN"",5,"""") ))))"
' Option to replace formulas with values:
rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value
End Sub

--
Regards,
Tom Ogilvy



"jfcby" wrote in message
oups.com...
Hello,

On worksheet6 I have 500 rows of data. In columnA it has data like
H,
WH, O, B, AN. In columnB I would to add 1, 2, 3, 4, 5. I want

What my worksheet looks like now ColumnB is blank:

ColumnA
WH
O
O
B
AN
H
H
H

The way I would for my sheet to look like:

ColumnA ColumnB
WH 2
O 3
O 3
B 4
AN 5
H 1
H 1
H 1

Thank you for your help in advance,
jfcby

Hello Tom,

I continued to work with the problem and I copied the formula directly
into the cell it merged and wraped text was automatically selected as
format. But, if I selected B1 then copied the formula directly in the
formula bar it works right.

Thank you for your help,
jfcby

jfcby wrote:
Hello Tom,

I opened a new worksheet in my current workbook, I started a new
workbook and I checked the cells format to make sure that the merge
cells box was unchecked when I copy the formula to B1 it automatically
selects merge and wrap text.

Does the worksheet or cell automatically change the cell format when
you copy a formula? If so how can that format setting be changed?

Thank you for your help,
jfcby

Tom Ogilvy wrote:
That only happens for me if B1 and B2 are already merged. So unmerge them
before entering the formula. The formula by itself will merge no cells - it
sounds like you are coping existing formatting.

--
Regards,
Tom Ogilvy


"jfcby" wrote in message
ps.com...
Hello Tom,

Thank you for your response! I was wondering though when I insert the
formula in B1 and drag fill down the column it merges B1:B2, B3:B4,
B5:B6 and so forth down the column. How can I keep the cells from
merging when I drag fill down the column?

Thank you for your help,
jfcby

Tom Ogilvy wrote:
Assume WH is in A1

in B1
=if(A1="H",1,if(A1="WH",2,if(A1="O",3,if(A1="B",4, if(A1="AN",5,"")))))

then drag fill down the column.

in code

Sub ABC()
Dim rng As Range
With Worksheets("Worksheet6")
Set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
rng.Offset(0, 1).Formula = "=if(A1=""H"",1,if(A1=""WH"",2" & _
",if(A1=""O"",3,if(A1=""B"",4,if(A1=""AN"",5,"""") ))))"
' Option to replace formulas with values:
rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value
End Sub

--
Regards,
Tom Ogilvy



"jfcby" wrote in message
oups.com...
Hello,

On worksheet6 I have 500 rows of data. In columnA it has data like H,
WH, O, B, AN. In columnB I would to add 1, 2, 3, 4, 5. I want

What my worksheet looks like now ColumnB is blank:

ColumnA
WH
O
O
B
AN
H
H
H

The way I would for my sheet to look like:

ColumnA ColumnB
WH 2
O 3
O 3
B 4
AN 5
H 1
H 1
H 1

Thank you for your help in advance,
jfcby