GROUP NUMBERING
 

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

GROUP NUMBERING
 

On May 15, 1:38 pm, Polarbilly
wrote:
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


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

thanks in anticipation

Bill

GROUP NUMBERING
 

Hi

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

--
Regards

Roger Govier


"Polarbilly" wrote in message
...
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

GROUP NUMBERING
 

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-

Polarbilly wrote:
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

GROUP NUMBERING
 

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
--
Gary''s Student - gsnu200721


"Polarbilly" wrote:

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

GROUP NUMBERING
 

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

--
Don Guillett
SalesAid Software

"Polarbilly" wrote in message
...
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

GROUP NUMBERING
 

Hi Roger,
thanks for the solution - elegant

Bill

"Roger Govier" wrote:

Hi

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

--
Regards

Roger Govier


"Polarbilly" wrote in message
...
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