google-apps-scriptgoogle-sheets

Append cell range to end of different row based on column 1 values

I have a student contact spreadsheet as shown below, where each students has 2, sometimes 3 parental contacts. As can be seen on Sheet1, each student's contact is on a separate row in the spreadsheet.

Sheet1

However, I need it to get it into the format on Sheet2, ie. where Column A values match, those rows are joined together.

Sheet2

The actual sheet has about 2000 records so manually going through and cutting and pasting isn;t really an option.

Any help would be much appreciated.

Dave.

Solution

  • I think this needs to be a two-step process - see Sheet3 in this sheet: https://docs.google.com/spreadsheets/d/1kY8SgKKJhstlIghop4cdRfH3YTuHviwhFZISB9ADJ-4/edit?usp=sharing

    The first formula in cell A2 creates a list of the IDs:

    =UNIQUE(Sheet1!A2:A)

    The second formula in cell B2 (and copied down the column) transfers the contact details from columns to rows:

    =SPLIT(CONCATENATE(FILTER(ARRAYFORMULA(IF(ISBLANK(Sheet1!A:D),"n/a",Sheet1!A:D)&"|"),Sheet1!A:A=A2)),"|")

    You can also hide column A if you prefer