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


[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]000006-X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]000005-X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]000004-X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]000003-X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]000002-X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]000001-X[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

 

[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