Multiplerows data into single cells based on row numbers

[qiyusi]

Hello,

I have Excel file in two column.
first column = Number
Second column = data

I need to combine multirows data into single one.
i have work vba, but this only work if i have blank row between each number.

Would be happy if anywant may check the attach file and give suggestion.

Thank you in advance.

[Akuini]

i have work vba, but this only work if i have blank row between each number.

You mean blank row or blank cell?
Your sample has blank cell between each number, is that the original condition?

[qiyusi]

You mean blank row or blank cell?
Your sample has blank cell between each number, is that the original condition?

Hello,

Thank you for your reply, yes it has blank cells between each number.

[MickG]

Try this for results starting "G1"

Please Login or Register  to view this content.

Regards Mick

[qiyusi]

Thanks MickG !!

It works perfectly.

i think it's solved.

i have only little known of vba, just curious do you have source where i can learn above? that explain about what is "temp", "ReDim", "Preserve".

[MickG]

You're welcome.
Nb:- You can Google "Redim Preserve" for a more formal answer, but basically
it Enables you to increase the size of an array when you don't know how big it needs to be.
In the code each time "c" increases (that's when the data found in column "A" is not an empty cell) then the size of the array increases by one.
NB:- The thing to remember is that you can only increase the last dimension of the array. In order to do that you have to transpose the array so the columns are the rows and the rows are the columns.
In the code the array looks like:- Redim Preserve ray(1 to2,1 to c) . So you now see the column (1 to 2) are now at the front and the rows (1 to c) are the last dimension.
The last thing the to remember is that when you place the array on the sheet you need to Transpose the array back to (application.transpose(ray)) to show the array correctly on the sheet.
Hope that helps