Count data upwards

[bdtran]

Hello All,

I am trying to get the result in column-A and I really need help with a formula to automatically count upwards as I insert data into column-B.

In the example below I just wanted to count data "X" in column-B upwards as in our situation, the data has been inserted in the top row as needed with a new row added.

Thanks much for your help.
*BDT

	A	B
1
2	000006-X	X
3		Y
4	000005-X	X
5	000004-X	X
6		Z
7	000003-X	X
8	000002-X	X
9	000001-X	X

<tbody>
</tbody>

 

[James006]

Hello,

You could test following event macro

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
If Target = "" Then Target.Offset(0, -1).ClearContents: Exit Sub
Dim x As Long
x = Application.WorksheetFunction.CountIf(Range("B3:B100"), Target.Value)
Target.Offset(0, -1) = Format(x + 1, "000000" & "-" & Target.Value)
End Sub

Hope this will help

 

[bdtran]

James,

Thanks much for your quick response. I realize that I also need to count upwards data "Z" and Data "Y" as well as they are inserted in the top row.

Really appreciate for your help.
Thanks,
*BDT

 

[James006]

Hello,

Have you already tested the macro ...???

Since it does perform what you are asking for ...

HTH

 

[bdtran]

James,

I tested it and it worked with the result I am looking for.

Is there any way that can be applied the same count for data "Z" and "Y"?

Also with your code, I have to insert the data "X" in the B2 cell in order to get the result in A2. Is there a way that I can insert data "X" in any cell within the defined range for example B1:B100? because my production data comprise 30 rows for each record in column-A. Such as in the above table "000006-X" indicates a record number comprises of 30 rows data following.

Thank you again for your help.
*BDT