Number Cells As Cell Value Changes, Excel 2000 & 2003

[jfcby]

Hello,

My worksheet is setup with 3 columns multiple rows varying in length.
Example:

Col-1 Col-2 Col-3
AIR COMP AIR COMPRESSOR
CONVEYORS CONVEYORS
DKFTN DRINKING FOUNTAIN
DKFTN DRINKING FOUNTAIN
DOM TK-EL HOT WATER TANK-ELEC
EMER LIGHT EMERGENCY LIGHT
EMER LIGHT EMERGENCY LIGHT
EMER LIGHT EMERGENCY LIGHT

I would like number Col-1 in this format:

Col-1 Col-2 Col-3
001 AIR COMP AIR COMPRESSOR
001 CONVEYORS CONVEYORS
001 DKFTN DRINKING FOUNTAIN
002 DKFTN DRINKING FOUNTAIN
001 DOM TK-EL HOT WATER TANK-ELEC
001 EMER LIGHT EMERGENCY LIGHT
002 EMER LIGHT EMERGENCY LIGHT
003 EMER LIGHT EMERGENCY LIGHT

Some of my worksheets have a lot of data and i would like to use a
macro to insert the number for each item as example describes above.

Thank you for your help,
jfcby

 

[Dave Peterson]

I put the data in B1:Cxxx.
Then formatted column A with a custom format of 000
(format|cells|number tab|custom category|000)

Then I put 1 in A1
and this in A2:
=IF(B2=B1,A1+1,1)
and dragged down column A.

As a macro:

Option Explicit
sub testme
dim LastRow as long
with worksheets("sheet9999") 'change the name here
lastrow = .cells(.rows.count,"B").end(xlup).row
.range("a1").value = 1
with .range("a2:a" & lastrow)
.formulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C+1,1)"
.value = .value
end with
.range("a:a").numberformat = "000"
end with
end sub

 

[jfcby]

I put the data in B1:Cxxx.
Then formatted column A with a custom format of 000
(format|cells|number tab|custom category|000)

Then I put 1 in A1
and this in A2:
=IF(B2=B1,A1+1,1)
and dragged down column A.

As a macro:

Option Explicit
sub testme
dim LastRow as long
with worksheets("sheet9999") 'change the name here
lastrow = .cells(.rows.count,"B").end(xlup).row
.range("a1").value = 1
with .range("a2:a" & lastrow)
.formulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C+1,1)"
.value = .value
end with
.range("a:a").numberformat = "000"
end with
end sub

Hello,

My worksheet is setup with 3 columns multiple rows varying in length.
Example:

Col-1 Col-2 Col-3
AIR COMP AIR COMPRESSOR
CONVEYORS CONVEYORS
DKFTN DRINKING FOUNTAIN
DKFTN DRINKING FOUNTAIN
DOM TK-EL HOT WATER TANK-ELEC
EMER LIGHT EMERGENCY LIGHT
EMER LIGHT EMERGENCY LIGHT
EMER LIGHT EMERGENCY LIGHT

I would like number Col-1 in this format:

Col-1 Col-2 Col-3
001 AIR COMP AIR COMPRESSOR
001 CONVEYORS CONVEYORS
001 DKFTN DRINKING FOUNTAIN
002 DKFTN DRINKING FOUNTAIN
001 DOM TK-EL HOT WATER TANK-ELEC
001 EMER LIGHT EMERGENCY LIGHT
002 EMER LIGHT EMERGENCY LIGHT
003 EMER LIGHT EMERGENCY LIGHT

Some of my worksheets have a lot of data and i would like to use a
macro to insert the number for each item as example describes above.

Thank you for your help,
jfcby

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Hello Dave,

Thank you for your help the code works great!

jfcby