In the attached sheet, data in Column A (serial) and B (Name) is common for set of data in Column C (city1) and Column D (Town1), Column E(city2) and Column F(Town2), Column G(City3) and Column H (Town3).
- I need to copy this data into a new sheet where I only have Column
A(serial), Column B(Name), Column C(City) and column D(Town).
All city values in rows under City and all town values in rows under Town and serial and Name to repeat for each city and column for corresponding rows.
I tried below formula for each column:
- Serial:
=ARRAYFORMULA(FLATTEN(SPLIT(REPT(currentdata!A2&"^",counta(currentdata!C2,currentdata!E2,currentdata!G2)),"^"))) - Name:
=ARRAYFORMULA(FLATTEN(SPLIT(REPT(currentdata!B2&"^",counta(currentdata!C2,currentdata!E2,currentdata!G2)),"^"))) - City:
=ARRAYFORMULA(FLATTEN(FILTER({currentdata!C2,currentdata!E2,currentdata!G2},LEN({currentdata!C2,currentdata!E2,currentdata!G2})))) - Town:
=ARRAYFORMULA(FLATTEN(FILTER({currentdata!D2,currentdata!F2,currentdata!H2},LEN({currentdata!D2,currentdata!F2,currentdata!H2}))))
It works for 1 row only. However, when I try to expand to all rows, it doesn’t work.
Below is the link to sample file where first sheet has current data and second sheet has the format of what it needs to be. And third sheet has formula for 1 row.
https://docs.google.com/spreadsheets/d/1K-whYc5boxi6qpqR3V-kVBNo6SYNxYoC9nQDJSzS_Yo/edit?usp=drivesdk
Suraj Maharjan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.