If A1=H Then B1=1, Excel 2000 & 2003

[jfcby]

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

 

[Jon Peltier]

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

 

[Ken Johnson]

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

 

[Tom Ogilvy]

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

 

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

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.

 

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

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.

 

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

Hello Tom,

Thank you for your help everthing works great!

jfcby

 

[Jon Peltier]

Ken -

Like mine, only better.

- Jon