Forum Discussion
Splitting families into individuals excel formatting
Hi, I'm a newbie with excel. I am not having much luck finding a way to take an address list that is formatted with both spouses as a name, and change it to individuals. I Can split the names, text to columns no problem. However, I also have to duplicate it on the next row so that the list is formatted with individuals. here's the example. Im sure it's easy, im just a struggling newb.
| HAVE THIS | |||||||
| FAMILY NAME | MEMBER NAME | ADDRESS 1 | CITY | STATE | ZIP | PRIMARY/HOME PHONE | EMAIL ADDRESS |
| Doe | John and Jane | 2 Elm St | Lockwood | IN | 456789 | 555-555-5555 | email address removed for privacy reasons ; email address removed for privacy reasons |
| NEED THIS | |||||||
| FAMILY NAME | MEMBER NAME | ADDRESS 1 | CITY | STATE | ZIP | PRIMARY/HOME PHONE | EMAIL ADDRESS |
| Doe | John | 2 Elm St | Lockwood | IN | 456789 | 555-555-5555 | email address removed for privacy reasons |
| Doe | Jane | 2 Elm St | Lockwood | IN | 456789 | 555-555-5555 | email address removed for privacy reasons |
11 Replies
If you have 365 you might use a LAMBDA.
This LAMBDA simply pulls each row twice.
=LAMBDA(range,LET(data,range,r,ROWS(data),CHOOSEROWS(data,INT(SEQUENCE(r*2,,1,1/2)))))This formula uses SCAN on the Member Name column. With the aid of some extraction, it pulls the name before or after the "and" where needed:
=LAMBDA(range,SCAN("",CHOOSECOLS(PullRows(range),2),LAMBDA(a,v,IF(TEXTBEFORE(v," ")=a,TEXTAFTER(v," and "),TEXTBEFORE(v," ")))))HSTACK combines the arrays horizontally:
=LET(data,E2:L4,HSTACK(TAKE(PullRows(data),,1),MemberName(data),CHOOSECOLS(PullRows(data),SEQUENCE(,6,3,1))))can you elaborate? Appreciate the help.
If Power Query is available on your computer you can open the file that is attached to my previous post. Then you can enter data in the blue dynamic table. Then you can click in any cell of the green table, right-click with the mouse and refresh.
