| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| PO with sequential numbering with start / end numbering | Excel Discussion (Misc queries) | |||
| Group a range - closing the group does not hide the controls.... | Excel Programming | |||
| How can I convert a group of numbers to a group of letters? | Excel Worksheet Functions | |||
| Taking age group Ie ages 20-29 and picking out net sales for group | Excel Worksheet Functions | |||
| How do I group worksheets (Lotus 123 function is "Sheet>Group Shee | Excel Worksheet Functions | |||
Linear Mode
