Member Avatar for chopin2256

In excel, what I am trying to do is list the number of occurrences of column B, based on the number from column A. Here is sample data:

A..........B
3..........40
5..........56
2..........23
..........
..........
..........
..........

Example output to column C would be:

C
40, 40, 40
56, 56, 56, 56, 56
23, 23

Can a simple excel function accomplish this, or does this need to be a VBA macro? Can someone help me?

  • 3 Contributors
  • 4 Replies
  • 112 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by JacoMuller

Dani AI

Generated

Two simple approaches already shown in this thread are both valid: supplied a working macro, and gave a compact single-cell formula using repetition and trimming. Both solve the immediate need, but choice depends on Excel version and constraints: a formula keeps the workbook macro-free; a macro (or a small UDF) is cleaner if many rows or very large repeat counts are required.

For Excel with dynamic arrays and TEXTJOIN (Excel for Microsoft 365 / Excel 2019+), the repeated-value string can be built in one cell without helper columns. For example, to repeat B1 A1 times and join with a comma and space:

=IF(A1<=0,"",TEXTJOIN(", ",TRUE,IF(SEQUENCE(A1),B1)))

This uses SEQUENCE to build an array of the requested length and TEXTJOIN to concatenate. If the environment lacks TEXTJOIN or SEQUENCE, a helper-column approach, Power Query, or the REPT/LEFT trick (already posted by ) are the alternatives.

A small VBA UDF is a tidy cross-version solution if macros are acceptable. Place this in a standard module and call as =RepeatValue(A1,B1):

Function RepeatValue(times As Long, val As Variant) As String
    Dim arr() As String
    Dim i As Long
    If times <= 0 Then RepeatValue = "" : Exit Function
    ReDim arr(1 To times)
    For i = 1 To times
        arr(i) = CStr(val)
    Next i
    RepeatValue = Join(arr, ", ")
End Function

Notes and cautions: non-integer or negative counts should be normalized (use INT or CLNG), empty B values produce repeated blanks, and Excel cells have a 32,767-character limit so very large repeats will be truncated or slow. For workflows that need repeated rows rather than a single concatenated cell, use Power Query to expand rows based on the count and then Group/Combine as needed.

Recommended Answers

All 4 Replies

Member Avatar for cguan_77

hi chopin2245, try this its working...

Dim i, i1, i2 As Integer
Dim ix, itotal, itotal1  As String
Dim lastcell As Integer



lastcell = 3 'assumed that there are 3 cells only


For i2 = 1 To lastcell ' loop until last value is done

Range("A" & i2).Select 'activate the cell to loop how many times

ix = Range("A" & i2).Value 'pass the value to another variable
MsgBox ix     'just to show the value of the next cell

i = CInt(ix)  ' convert the value of the string to integer
i1 = 0        'reset the counter to zero

While i1 <> i   ' procedure to display the value

itotal = CStr(Range("b" & i2).Value)
    
 Range("c" & i2).Value = CStr(itotal) + "," + " " + CStr(itotal1)
    
itotal1 = CStr(itotal) + "," + " " + CStr(itotal1)
 
i1 = i1 + 1
Wend

itotal1 = ""  'reset the value to nothing
itotal = ""

Next i2

you need to create a macro for this one.. go to tools..macro..key in the macro name that you want..then click "Create Macro" then on the sub proc do like this >>>

Sub proc

paste the code here

end sub

hope it helps..

Member Avatar for chopin2256

It works! Thank you very much for your time.

Member Avatar for cguan_77

hi chopin2256, welcome, just don't forget to mark the thread as solve... :)

Member Avatar for JacoMuller

Try this:
=LEFT(REPT(B1 & ", ",A1),LEN(REPT(B1 & ", ",A1))-2)

Jaco

In excel, what I am trying to do is list the number of occurrences of column B, based on the number from column A. Here is sample data:

A..........B
3..........40
5..........56
2..........23
..........
..........
..........
..........

Example output to column C would be:

C
40, 40, 40
56, 56, 56, 56, 56
23, 23

Can a simple excel function accomplish this, or does this need to be a VBA macro? Can someone help me?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.