Tuesday, September 1, 2015

Split list and append Data

Question:

I have a sheet called Master which has 3 columns: Name Home_Address, Work_Address. Note that a Name may have one or both associated addresses.

In a second sheet, Called Split. This sheet has 3 columns: Name, Address, Type I want to split each name and address to make 1 row for each Address, be it work or home, and add a last column, Type, with either Work or Home for values.  This will be based on which column the address comes from
How can I make this split list?

Solution:

Screenshot of Sheet "Master":

Screenshot of Sheet "Split":

I have the following formula in cell A2 of Sheet "Split":
={query(Master!A2:B,"select A,B,'Home' where B<>'' label 'Home' ''",0);query(Master!A2:C,"select A,C,'Work' where C<>'' label 'Work' ''",0)}


No comments:

Post a Comment