Combine Multiple sections into single cells

Hi

I need some help with my spreadsheet I am working on.

I have multiple sections with blank cells in between that I need to combine with a | separator and I cannot figure out how I can do this.

Basically I need to combine all the colour variations in each section to show on the top line of their sections with a | in between each colour.

If anyone could help me do this that would be great as I am really stuck.

Thanks

COMBINE TO SINGLE CELLS.PNG

An image file is not much use to anyone, and many contributors (including me) are not able to read .PNG files on this forum.

Please attach a sample Excel workbook instead.

Pete

Hi

Thanks for letting me know, here is a sample workbook.

I have column A with the data as it is now and Column C with how I want the data to eventually look like.

The spreadhsheet I am working from has 140k rows in total so I need a solution to do this in bulk.

Thanks

Here's one way - put zero in B1 and this formula in B2:

=IF(A2="",MAX(B$1:B1)+1,IF(ISNUMBER(B1),A2,B1&"|"&A2))

Copy that formula down to the bottom of your data plus one row, so that it ends with a number.

Then you can put this formula in, say, D2:

=IFERROR(INDEX(B:B,MATCH(ROWS($1:1),B:B,0)-1),"")

When you copy this down it will bunch all the results onto consecutive rows, which I thought would be more useful than how you showed it in column C in your sample file. Consequently, you will only need to copy this formula down for as many sections as you have (i.e. the last number that you can see in column B). You can then fix the values in column D and delete the contents of column B.

Hope this helps.

Pete

Hi

Thanks for the reply, the formula you sent me almost worked but I need a few changes if thats possible?
I need the results to be displayed as in the sample file with the results in line with the first entry and not the last entry. The reason for this is because this is part of a massive spreadsheet with other data so needs to go in the same order.

I also need to delete the old data so that only the combined cell remain.

Here are the results of the formula you sent.

Thank you

In your second sample file the first data item is on row 3, so you should put zero in B2 and have this formula in B3:

=IF(A3="",MAX(B$2:B2)+1,IF(ISNUMBER(B2),A3,B2&"|"&A3))

Copy down, as before, to one row below your bottom data item. Then in D3 you can have this formula:

=IF(ISNUMBER(B4),B3,"")

which you should copy down to the last row of data (though it doesn't matter too much if you copy it further).

Then, to fix the values in column D you should first highlight column D by clicking on the column identifier at the top of the column, then click <copy>. Then right-click anywhere in that column and select Paste Special, then click on Values | OK then press the <Esc> key. Then you can delete column B, leaving you with the layout that you had in your first sample file.

Or, if you only want the combined data, you can delete columns A to C.

Hope this helps.

Pete

Hi

Yeah that works perfectly although the end result is displayed in the end cell of each section and I need it in the first cell of eachsection like below

Current formula

Black
Navy
Red Black|Navy|Red

Required format

Black Black|Navy|Red
Navy
Red