Add serial alphabet suffix based on a constant number

[Anees Ahmed]

Hi,

I have a data as name in 1st column and quantity in 2nd column like

Sam 2
Ram 3
Madan 2
Reddy 5

I want the data to be sorted to create unique bar code values as

Sam_1
Sam_2
Ram_1
Ram_2
Ram_3
Madan_1
Madan_2
Reddy_1
Reddy_2
Reddy_3
Reddy_4
Reddy_5

Kindly help. Thanks in advance

 

[L. Howard]

Hi,

I have a data as name in 1st column and quantity in 2nd column like

Sam 2
Ram 3
Madan 2
Reddy 5

I want the data to be sorted to create unique bar code values as

Sam_1
Sam_2
Ram_1
Ram_2
Ram_3
Madan_1
Madan_2
Reddy_1
Reddy_2
Reddy_3
Reddy_4
Reddy_5

Kindly help. Thanks in advance

I found this little diddy.

Option Explicit

Sub CopyData()
Dim lRow As Long
Dim RepeatFactor As Variant

    lRow = 1
    Do While (Cells(lRow, "A") <> "")
           
        RepeatFactor = Cells(lRow, "B")
        If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then
                   
           Range(Cells(lRow, "A"), Cells(lRow, "B")).Copy
           Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "B")).Select
           Selection.Insert Shift:=xlDown
              
           lRow = lRow + RepeatFactor - 1
        End If
       
        lRow = lRow + 1
    Loop
End Sub

Howard

 

[XOR LX]

Hi.

Assuming that your table is in A1:D4:

In E1:

=IF(ROWS($1:1)>SUM($B$1:$B$4),"",INDEX($A$1:$A$4,MATCH(TRUE,MMULT(N(ROW(INDEX(A:A,1):INDEX(A:A,4))>=COLUMN(INDEX(1:1,1):INDEX(1:1,4))),$B$1:$B$4)>=ROWS($1:1),0)))

In F1:

=IF(E1="","",COUNTIF(E$1:E1,E1))

In G1:

=IF(F1="","",E1&"_"&F1)

Copy all down until you start to get blanks for the results.

Note that the 4 in the parts:

ROW(INDEX(A:A,1):INDEX(A:A,4))

and:

COLUMN(INDEX(1:1,1):INDEX(1:1,4))

represents the fact that your table contains 4 rows' worth of data, and should be amended where necessary.

Note also that there is a theoretical limit of 16,384 rows' worth of data with this set-up. This can quite easily be modified to allow for more if necessary, though at the cost of an extra function call or two (and possibly array-entry).

Regards

 

[XOR LX]

In fact, on reflection it would be better to change that static 4 to the dynamic:

COUNTA($A$1:$A$4)

so that you do not have to worry about redetermining and entering this value every time your data range changes.

Regards

 

[L. Howard]

Sorry, I do believe I misread your post.

The code is of no use to you for your out come.

Howard

 

[bosco_yip]

No helper columns required.

1] Assume row 1 housed the heading, your data in A2:B5,

2] D2, enter formula and copy down :

=IF(ROW(A1)<=SUM(B$2:B$5),LOOKUP(ROW(A1),SUMIF(OFFSET(B$1,,,ROW($1:$99),),"<>")+1,A$2:A$4)&"_"&ROW(A1)-LOOKUP(ROW(A1),SUMIF(OFFSET(B$1,,,ROW($1:$99),),"<>")+1)+1,"")

Regards

 

[Anees Ahmed]

I found this little diddy.

Option Explicit

Sub CopyData()
Dim lRow As Long
Dim RepeatFactor As Variant

    lRow = 1
    Do While (Cells(lRow, "A") <> "")
           
        RepeatFactor = Cells(lRow, "B")
        If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then
                   
           Range(Cells(lRow, "A"), Cells(lRow, "B")).Copy
           Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "B")).Select
           Selection.Insert Shift:=xlDown
              
           lRow = lRow + RepeatFactor - 1
        End If
       
        lRow = lRow + 1
    Loop
End Sub

Howard

Can you upload a sample excel with my data.

Sam 2
Ram 3
Madan 2
Reddy 5

 

[Anees Ahmed]

I found this little diddy.

Option Explicit

Sub CopyData()
Dim lRow As Long
Dim RepeatFactor As Variant

    lRow = 1
    Do While (Cells(lRow, "A") <> "")
           
        RepeatFactor = Cells(lRow, "B")
        If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then
                   
           Range(Cells(lRow, "A"), Cells(lRow, "B")).Copy
           Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "B")).Select
           Selection.Insert Shift:=xlDown
              
           lRow = lRow + RepeatFactor - 1
        End If
       
        lRow = lRow + 1
    Loop
End Sub

Howard

Hi,
Thanks a lot for the code. I got the items split but the suffix _1, _2 is not added to the A row. Please advise

 

[L. Howard]

Hi,
Thanks a lot for the code. I got the items split but the suffix _1, _2 is not added to the A row. Please advise

Please see post #5.

Howard

 

[Anees Ahmed]

Hi,

Thanks a lot for your code. I have figured it out.

My actual data

Name Quantity

Sam 2
Ram 3
Madan 2
Reddy 5

Step1
I have used your code and run the excel macro.

Sub CopyData()
Dim lRow As Long
Dim RepeatFactor As Variant

lRow = 1
Do While (Cells(lRow, "A") <> "")

RepeatFactor = Cells(lRow, "B")
If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then

Range(Cells(lRow, "A"), Cells(lRow, "B")).Copy
Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "B")).Select
Selection.Insert Shift:=xlDown

lRow = lRow + RepeatFactor - 1
End If

lRow = lRow + 1
Loop
End Sub


I got this result
Sam
Sam
Ram
Ram
Ram
Madan
Madan
Reddy
Reddy
Reddy
Reddy
Reddy


Step 2
And i used the following formula in b2 =A1&"_"&(COUNTIF($A$1:A1,A1)) I got the result

Sam_1

Sam_2

Ram_1

Ram_2

Ram_3

Madan_1

Madan_2

Reddy_1

Reddy_2

Reddy_3

Reddy_4

Reddy_5