Listing number of Occurrences in Excel

[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?

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

[chopin2256]

It works! Thank you very much for your time.

[cguan_77]

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

[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?