I have a problem which I'm hoping is a pretty straightforward one. I'm sure it's from high school computing or something, but I can't quite define the theory very well (possibly data redundancy/normalisation, from memory), and therefore can't get any good google results.

Essentially I have 4 forms which capture requests. Requests can be of 4 different types, and from 4 different locations. Unfortunately due to how the location data is captured, each record has 4 columns for the same piece of info. e.g.

Name Request Type Form1.Location Form2.Location Form3.Location Form4.location
John Unblock North
bob Recat East
Jane Transport West
Sarah Unblock South

What I'd like is just to have 1 column, something like Combined.Location for all records, eliminating the blank columns.

If anyone can help, it'd be greatly appreciated!


1 Answer 1


Use an { array expression }. Insert > Sheet and put this formula in cell A1 of the new sheet:

    Sheet1!A1:B1, "Location"; 
    Sheet1!A2:B, Sheet1!C2:C & Sheet1!D2:D & Sheet1!E2:E & Sheet1!F2:F 
  • This is amazing - thank you @doubleunart.
    – Mullin1864
    Mar 31, 2022 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.