Group numbers

[Anar]

Hi, everyone

I need help. The issue is described below.


Below is a list of invoice groups. Group number of the first group of invoices is 1 and it is written in front of the first invoice. I need to write a formula which will show me group number of each invoice group. The group number should be written in front of the first invoice number in each group. For example, there should be 2 in front of E3478, 3 in front of E621 and etc.


Column A Column B

Invoice Invoices

groups

1 E126854

E126856

E126858

E126860


E3478

E3483

E3485

E3487

E3489

E3491


E621

E628

E630

E632

E634

E636


E9874567

E9874573

E9874575

E9874577

E9874579

E9874581


E74582


E74579

E74581

E74582


BR,

Anar

 

[Hui]

Anar


Assuming your data is in A2:A... and that A1 is blank


Put this in B2

=IF(A1="",COUNTIF($A$1:A2,"")&A2,IF(A2="","",A2))

Copy down


Copy column B and paste as values over itself

Delete Column A if required

 

[Anar]

Hui,


For better understanding what I need I have uploaded the file here, it is a result of what I want:


https://docs.google.com/spreadsheet/ccc?key=0AvS6fHQ9kTRHdEtkcnhpYThsOE94dlFkRnJhaDg3enc&hl=en_US

 

[vijaySharma]

Anar,


Try the code below...

Function LastRowInOneColumn(col)

'Find the last used row in a Column: column A in this example

'http://www.rondebruin.nl/last.htm

Dim lastRow As Long

With ActiveSheet

lastRow = .Cells(.Rows.Count, col).End(xlUp).Row

End With

LastRowInOneColumn = lastRow

End Function


Sub updateNumbers()

Dim cntr As Long

Dim lastRow As Long


    lastRow = LastRowInOneColumn("B")

cntr = 2

Range("A2").Select

Do While ActiveCell.Row <= lastRow


        Do While ActiveCell.Offset(0, 1) <> ""

ActiveCell.Offset(1, 0).Select

Loop


        ActiveCell.Offset(1, 0).Select

If ActiveCell.Offset(0, 1) <> "" Then ActiveCell.Value = cntr

cntr = cntr + 1

Loop

End Sub


HTH


~VijaySharma

 

[Anar]

Thank you for the code.


The solution is already found:

In cell A3: =IF(AND(B2="",B3<>""),MAX($A$2:A2)+1,"")