Counts Expanding

**sathishkm** · 04-13-2021, 01:08 AM

Hi All,

I have company names in column A and there counts in column B. I need to expand the companies according to the count mentions in columns B. Please find the attachment.

**jindon** · 04-13-2021, 01:58 AM

Try

Sub test()
    Dim a, b, i As Long, ii As Long, n As Long
    With Cells(1).CurrentRegion
        a = .Value
        ReDim b(1 To Application.Sum(.Columns(2)), 1 To 1)
    End With
    For i = 2 To UBound(a, 1)
        For ii = 1 To a(i, 2)
            n = n + 1: b(n, 1) = a(i, 1)
    Next ii, i
    [d2].Resize(n) = b
End Sub

**sathishkm** · 04-13-2021, 03:32 PM

Thanks. I got what I was expecting it

**Bo_Ry** · 04-13-2021, 02:42 AM

Please try

Sub ex()
Dim a
With Range("A2", Cells(Rows.Count, 1).End(xlUp))
    a = Split(Join(Application.Transpose(Evaluate("Rept(" & .Address & "&""|""," & .Offset(, 1).Address & ")")), ""), "|")
    [d2].Resize(UBound(a)) = Application.Transpose(a)
End With
End Sub

**kvsrinivasamurthy** · 04-13-2021, 03:09 AM

If you are interested in FORMULA solution.
ARRAY formula in F2 then copied down till blank cells are seen.

=IFERROR(INDEX($A$2:$A$5,SMALL(IF(SUMIF(OFFSET($B$2,0,0,ROW($B$2:$B$5)-ROW($B$1)),">0")>=ROWS($F$2:$F2),ROW($B$2:$B$5),""),1)-ROW($B$1)),"")

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

Counts Expanding

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Counts Expanding

Re: Counts Expanding

Re: Counts Expanding

Re: Counts Expanding

Re: Counts Expanding

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Subtracting one sum of counts from another sum of counts in a pivot table

Expanding values/counts chart

Expanding summary cells into individual counts together with associated values

[SOLVED] Expanding an IF formula

[SOLVED] Expanding a summary cell into individual counts...opposite of consolidate?

Expanding Group using VBA

expanding frequencies/counts

Bookmarks

Bookmarks

Posting Permissions