Search code examples
concatenationlibreoffice-calc

How Can I Concatenate Many Sets of Columns Into Several New Columns?

I have a spreadsheet. 60 rows, ~100 columns.

I wish to concatenate the data from each set of 3 columns, to produce ~30 columns of 'merged' data.

e.g. currently columns include "Fighter 1", "Fighter 2", Fighter 3", "Wizard 1", "Wizard 2", Wizard 3" etc.

i wish to put the data from each set into I column each, labelled "Fighter", "Wizard" etc. with the data from the three columns 'merged', with a delimiter (:). I need Empty Cells included.

I know I can use CONCATENATE(B2, C2, D2) to combine the data from one set of cells.

I can Drag this formula down, to fill the column.

But if I drag the Formula sidewards (to the right), it increments each value by 1, rather than the 3 that I need.

e.g. I want the columns to become (B2, C2, D2), (E2, F2, G2), (H2, I2, J2) etc, but I get (B2, C2, D2), (C2, D2, E2), (D2, E2, F2) ...

Solution

  • You could make use of the OFFSET function

    We'll start with the premise that your data spans Column A to Column CU (1-99) and your "merged" data spans column CW to Column EC (101-133).

    Starting with Cell CW2 (assuming your headers are on row 1), write this formula (I've added line breaks to make it easier to read):

    =CONCATENATE(
    OFFSET($A2, 0, 3 * (COLUMN() - 101)), 
    OFFSET($B2, 0, 3 * (COLUMN() - 101)), 
    OFFSET($C2, 0, 3 * (COLUMN() - 101))
)

    This will concatenate together the three adjacent columns of the original dataset, and when you drag-fill the formula to each of the next 32 columns, each merged column will have been offset by exactly three cells each, lining them up to concatenate all your data.

    The important parts that you might need to change in that function for your use:

    • $A2 It's important to start with whichever column is the start of your entire dataset, not just the columns relative to each merged set. Other than that though, if your data starts at Column C or E or Z or wherever, this will still work so long as this references that first column.
    • 3 Is a reference to how many columns you're grouping by. If you had a series like "Fighter 1" "Fighter 2" "Fighter 3" "Fighter 4" "Wizard 1" "Wizard 2" "Wizard 3" "Wizard 4"...., then you'd use 4 instead of 3.
    • 101 is the 1-indexed column of the start of your "Merged" data. That way, COLUMN()-101 is 0 for the first column, 1 for the second, 2 for the third, and so on. This then gets multiplied by 3 above to yield 0, 3, 6, 9, ...