Concatenate columns into 1 cell

[DTEInc]

I have a product list and it has 2 columns; pa_size and pa_volume. The next 2 columns are ma_size and ma_volume. In these columns, there will be a value on each sub product of the main product. The cell is blank where the main product located.
I need to concatenate the pa_size column into the blank cell in the row above at the ma_size cells that have the values in them and then go past the empty cells and find the next value and start over and concatenate the next set of cells.
I have attached an example of what I am trying to do. I filled in the top 3 examples and left the 2 bottom ones blank. I need to go through this workbook and have this formula be able to fill down since there is over 5000 products in the spreadsheet.
Can anyone help a fella out here?

[ChemistB]

I used a combination of VBA ( a UDF - User Defined Function) and an ARRAY Formula.

First I copied Tiger Avatar's CONCATALL(range, delimiter) code into a module

Please Login or Register  to view this content.

I copied your table to columns F,G,H, I to test this.

In G2 copied down is this arrayed function

=IF(AND(ISBLANK(F2),ISNUMBER(F3)),CONCATALL(F3:INDEX(F3:F$5000,MATCH(1,1/ISBLANK(F3:F$5000),0)),"\"),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

That can be copied and pasted over to I2 also and copied down

[6StringJazzer]

I don't know how to do this with formulas. Here is a VBA solution.

[excel_bat]

Hi! This worked for me. Hope it helps!

[DTEInc]

Thanks guys. Do you have any ideal how many nights I have been working and watching videos and reading about how to solve this issue? TOO many.
Thanks for your help.

[DTEInc]

I plugged in this formula and code and it didn't work. I did some investigating and I have some cells with numbers and letters and this appears to be why it will not work. What does the formula look like when this is the case?

[DTEInc]

Got it. Use ISREF instead of ISNUMBER.

[ChemistB]

I think you need to use something like F3<>"". IsRef will always return true when you have a reference to a cell inside the parentheses.