Populate data according to specific count

[YasserKhalil]

Hello everyone
I have in column B some texts and in column C there are their count (numbers)
I need to populate the strings (texts in column B according to their count)

Here is the sample workbook that illustrate the desired results
Thanks advanced for great help

 

[shrivallabha]

You are more interested in macro solution but you can also try below approach.

In cell G2 write:
=B2
And cell G3 onwards (Copy down for more rows than you are anticipating)
=IFERROR(IF(COUNTIF($H$2:H2,H2)<INDEX($C$2:$C$6,MATCH(H2,$B$2:$B$6,0)),H2,INDEX($B$2:$B$6,MATCH(H2,$B$2:$B$6,0)+1)),"")

 

[YasserKhalil]

Thanks a lot Mr. Shrivallabha for reply
In fact as you siad I am interested in macro solution but i like also variant solutions
I tried your formula in G3 and it gives me empty result .. it didn't work for me or I am sure I can't apply it properly

 

[shrivallabha]

Sorry my bad Please update formula in cell G3 as shown below and then copy down:
=IFERROR(IF(COUNTIF($G$2:G2,G2)<INDEX($C$2:$C$6,MATCH(G2,$B$2:$B$6,0)),G2,INDEX($B$2:$B$6,MATCH(G2,$B$2:$B$6,0)+1)),"")

 

[Marc L]

Hi !

As we are in VBA forum :​

Sub Demo1()
            Dim C%, L&, R&, VA
    With Sheet1
        With .Cells(1).CurrentRegion
            VA = .Value
            ReDim VR$(1 To Application.Sum(.Columns(3)), 1 To 1)
        End With
        For R = 2 To UBound(VA)
            For C = 1 To VA(R, 3):  L = L + 1:  VR(L, 1) = VA(R, 2):  Next
        Next
            .[G1].CurrentRegion.Offset(1).Clear
            .[G2].Resize(UBound(VR)).Value = VR
    End With
End Sub

 

[shrivallabha]

VBA (Assuming you have less than 65536 rows of data all the time).

Public Sub RepeatItems()
Dim arResult() As String
ReDim arResult(Application.Sum(Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)) - 1)
Dim i As Long, j As Long, cnt As Long
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    For j = 0 To Range("C" & i).Value - 1
        arResult(cnt + j) = Range("B" & i).Value
    Next
    cnt = cnt + j
Next i
Range("G2").Resize(UBound(arResult) + 1, 1).Value = Application.Transpose(arResult)
End Sub

 

[Marc L]

Another way done by a very beginner :​

Sub Demo2()
        Dim L As Long, R As Long
        L = 2
        Application.ScreenUpdating = False
With Sheet1
        .Range("G1").CurrentRegion.Offset(1).Clear
    For R = 2 To .Range("A1").CurrentRegion.Rows.Count
        .Cells(R, 2).Copy .Cells(L, 7).Resize(.Cells(R, 3).Value)
        L = L + .Cells(R, 3).Value
    Next
End With
        Application.ScreenUpdating = True
End Sub

No rows limit for both Demo (> 65 536 is Ok) …
​

 

[YasserKhalil]

Thank you very very much for these great and wonderful solutions
Really you are GREAT people . I like this forum a lot..
I always receive great and awesome help ..

Another point if possible : I need to number the column F .. Which precedes Column G (that contains results now)
The expected results would be : 1,2,1,2,3,4,5,1,2,3,4,5,6,1,2,3,1,2,3,4,5,6,7,8
That's to number each item that was populated ..

 

[shrivallabha]

In cell F2 and copy down:
=COUNTIF($G$2:G2,G2)

 

[Marc L]

Another point if possible : I need to number the column F

As a beginner can mod :​

Sub Demo2a()
        Dim L As Long, N As Integer, R As Long
        L = 2
        Application.ScreenUpdating = False
With Sheet1
        .Range("G1").CurrentRegion.Offset(1).Clear
    For R = 2 To .Range("A1").CurrentRegion.Rows.Count
        N = .Cells(R, 3).Value
        .Cells(L, 6).Resize(N).Value = Evaluate("ROW(1:" & N & ")")
        .Cells(R, 2).Copy .Cells(L, 7).Resize(N)
        L = L + N
    Next
End With
        Application.ScreenUpdating = True
End Sub

 

[YasserKhalil]

Thank you very much for these wonderful solutions ..
Best Regards