[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

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

James F Cooper

12/17/2006 6:40:00 PM

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

Jon Peltier

12/17/2006 7:57:00 PM

0

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://Pelti...
_______


"jfcby" <jamesfc30@earthlink.net> wrote in message
news:1166380815.858681.141950@n67g2000cwd.googlegroups.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
>


Ken Johnson

12/17/2006 8:05:00 PM

0


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

Tom Ogilvy

12/17/2006 8:20:00 PM

0

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" <jamesfc30@earthlink.net> wrote in message
news:1166380815.858681.141950@n67g2000cwd.googlegroups.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
>


James F Cooper

12/17/2006 10:43:00 PM

0

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" <jamesfc30@earthlink.net> wrote in message
> news:1166380815.858681.141950@n67g2000cwd.googlegroups.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
> >

Tom Ogilvy

12/17/2006 11:15:00 PM

0

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" <jamesfc30@earthlink.net> wrote in message
news:1166395382.859242.31130@73g2000cwn.googlegroups.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" <jamesfc30@earthlink.net> wrote in message
>> news:1166380815.858681.141950@n67g2000cwd.googlegroups.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
>> >
>


James F Cooper

12/17/2006 11:52:00 PM

0

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" <jamesfc30@earthlink.net> wrote in message
> news:1166395382.859242.31130@73g2000cwn.googlegroups.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" <jamesfc30@earthlink.net> wrote in message
> >> news:1166380815.858681.141950@n67g2000cwd.googlegroups.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
> >> >
> >

Tom Ogilvy

12/18/2006 12:15:00 AM

0

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" <jamesfc30@earthlink.net> wrote in message
news:1166399546.702344.101820@t46g2000cwa.googlegroups.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" <jamesfc30@earthlink.net> wrote in message
>> news:1166395382.859242.31130@73g2000cwn.googlegroups.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" <jamesfc30@earthlink.net> wrote in message
>> >> news:1166380815.858681.141950@n67g2000cwd.googlegroups.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
>> >> >
>> >
>


James F Cooper

12/18/2006 12:19:00 AM

0

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" <jamesfc30@earthlink.net> wrote in message
> > news:1166395382.859242.31130@73g2000cwn.googlegroups.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" <jamesfc30@earthlink.net> wrote in message
> > >> news:1166380815.858681.141950@n67g2000cwd.googlegroups.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
> > >> >
> > >

James F Cooper

12/18/2006 1:05:00 AM

0

Hello Tom,

Thank you for your help everthing works great!

jfcby

Tom Ogilvy wrote:
> 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" <jamesfc30@earthlink.net> wrote in message
> news:1166399546.702344.101820@t46g2000cwa.googlegroups.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" <jamesfc30@earthlink.net> wrote in message
> >> news:1166395382.859242.31130@73g2000cwn.googlegroups.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" <jamesfc30@earthlink.net> wrote in message
> >> >> news:1166380815.858681.141950@n67g2000cwd.googlegroups.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
> >> >> >
> >> >
> >

Jon Peltier

12/18/2006 3:09:00 AM

0

Ken -

Like mine, only better.

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


"Ken Johnson" <KenCJohnson@gmail.com> wrote in message
news:1166385881.486376.166810@16g2000cwy.googlegroups.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
>