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 t...
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 |
- This is amazing!
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 SubYou 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.
- 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.