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?

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..

It works! Thank you very much for your time.

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

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.