GROUP NUMBERING

[Guest]

I need to create a number sequence file for data listed as shown.The groups
would be created from the first 12 digits. Example:

UPI SEQUENTIAL NUMBER
8DEB90020849A059 0001
8DEB90020849B059 0001
8DEB90022591A059 0002
8DEB90022591B059 0002
8DEB90022796A059 0003
8DEB90022796B059 0003
8DEB90022796C059 0003
8DEB90022796D059 0003

thanks in anticipation


Bill

 

[paul.robinson]

I need to create a number sequence file for data listed as shown.The groups
would be created from the first 12 digits. Example:

Hi
1. Sort the UPI column
2. If first data row is in row 2, column A then in cell B2 put a 1
3. Copy this formula to cell B3
=IF(LEFT(A3,12)=LEFT(A2,12),E2,E2+1)
4. Fill the formula down for other cells.

regards
Paul

 

[Roger Govier]

Hi

Format column B, Format>Cells>Number>Custom> 0000
in cell B2
=IF(LEFT(A2,12)=LEFT(A1,12),B1,N(B1)+1)
copy down as required

 

[JW]

This formula will check to see if the data is in the second row. If
it is, it places a 1 in the Seq Number field becuase that yould be the
first record (assuming headers are being used).
It then compares the left 12 characters of the current rows UPI number
to the left 12 characters of the UPI number above it. If they are the
same, it takes on the same number as the one above. If they are
different, it adds one to the seq number.
To acheive the leading zero format, you can apply a custom format to
the cells and enter 0000 ito the type field.
=IF(ROW()=2,1,IF(LEFT(A2,12)=LEFT(A1,12),B1,B1+1))

HTH
-Jeff-

 

[Guest]

With your data in column A, put 1 in B1 and put in B2:

=IF(LEFT(A2,12)=LEFT(A1,12),B1,B1+1)
and copy down

 

[Don Guillett]

If sorted then custom format col B as 0000 and use this

Sub assigngroupnumbers()
x = 1
For Each c In Range("a2:a" & Cells(Rows.Count, "a").End(xlUp).row)
If Left(c, 12) <> Left(c.Offset(-1), 12) Then
c.Offset(, 1) = x
Else
c.Offset(, 1) = c.Offset(-1, 1)
x = x + 1
End If
Next c
End Sub

 

[Guest]

Hi Roger,
thanks for the solution - elegant

Bill