Splitting families into individuals excel formatting

[jasonsheeley]

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

[#2]

@jasonsheeley 

A possible solution could be Power Query if you can use this feature.

[#3]

@OliverScheurich 

can you elaborate? Appreciate the help.

[#4]

@jasonsheeley 

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.

[#5]

Ok i gave it a shot. The problem is these lists are usually emailed to me with already of over 300 contacts as couples. I just need to split it up

[#6]

@jasonsheeley

Did you get the expected result? If so it should be possible to copy the 300+ contacts from the other file and paste them into the blue dynamic table. Then you can refresh the green table. In the attached file i've rearranged the layout of the tables. You can enter huge numbers of contacts in the blue table.

[#7]

@OliverScheurich 

i was sent with the name in one row. I split them up with text to columns. I should have included the specific layout in the beginning. I see what you are saying with copy and pasting to your data query file, but my info is going all over the place. this is an example of where I am now. Under family it would have said Accardo John & marlene. 

FAMILY

NAME

Primary Address

address 2

City

State

zip

Primary Phone

E-Mail Address

Accardo

John

&

Marlene

68 elm

Cody

WY

82414

307-586-0000

email address removed for privacy reasons

Adkins

Joanne

and

david

2202 main St

Cody

WY

82414

307-527-0000

email address removed for privacy reasons

Agudo

Freddy

&

mary

PO Box main

Cody

WY

83214

000-000-000

email address removed for privacy reasons

Alexander

Steven

&

dennis

1313 Red elm ave.

Cody

WY

82414

Alvarado

Nicole

&

jamie

29 house rd

Henderson

NV

89011

307-527-0000

[#8]

@jasonsheeley 

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))))

[#9]

@jasonsheeley 

The attached file transforms the data based on where you are now.

[#10]

Thanks for the help, im still trying to get it to work. Well, i am trying to use the tool properly, im sure it works. It's my lack of excel skill! i keep getting errors but im pretty sure it's user error.

[#11]

@jasonsheeley 

Sub names()

Dim i As Long
Dim j As Long
Dim k As Long

Range("P:AB").Clear

k = Range("A" & Rows.Count).End(xlUp).Row

j = 2
For i = 2 To k

Range(Cells(i, 1), Cells(i, 13)).Copy Destination:=Range(Cells(j, 16), Cells(j, 28))
Range(Cells(j, 18), Cells(j, 19)).Clear
j = j + 1

Range(Cells(i, 1), Cells(i, 13)).Copy Destination:=Range(Cells(j, 16), Cells(j, 28))
Cells(j, 17).Value = Cells(j, 19).Value
Range(Cells(j, 18), Cells(j, 19)).Clear
j = j + 1

Next i

End Sub

You can try these lines of code. In the attached file in sheet "Tabelle2" you can click the button in cell O2 to run the macro.

After copying your data select cell A2 in sheet "Tabelle2" and paste the data. The data should be entered in columns A to M. There can be hundreds of rows (actually tens of thousands, however that would affect the runtime of the macro) in columns A to M.

 

[#12]

This is amazing!