need help organizing random cells

[Miguel1598]

ok so I'm trying to organize some data but when i paste it from elsewhere i can post it but I can't seem to find a way to organize it. I ave to do hundreds of these and I'd prefer to not do it all by hand so if anyone knows a way of organizing these with a formula that would be marvelous!

this is how i need it to be organized
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

ZAKARIA YATIM	Age: 38	1840 VINSETTA BLVD	ROYAL OAK	Ph. 2486299304
ANDREW PRUSINOWSKI	Age: 27	1878 VINSETTA BLVD	ROYAL OAK	Ph. 2483982504
MARVIN PRUSINOWSKI	Age: 59	1878 VINSETTA BLVD	ROYAL OAK	Ph. 2483982504
BENJAMIN SHARKEY	Age: 34	1840 VINSETTA BLVD	ROYAL OAK	Ph. 2486299304
SARAH PRUSINOWSKI	Age: 30	1878 VINSETTA BLVD	ROYAL OAK	Ph. 2483982504
JENNIFER NELSON-MEFFORD	Age: 44	1884 VINSETTA BLVD	ROYAL OAK	Ph. 2483187885
LANCE LANGE	Age: 48	1820 VINSETTA BLVD	ROYAL OAK	Ph. 2483903767
ADELENE HOEY	Age: 80	1860 VINSETTA BLVD	ROYAL OAK	Ph. 2485424605
BILL BOCK	Age: 50	1830 VINSETTA BLVD	ROYAL OAK	Ph. 2485465121
DEBRA BOIK	Age: 00	1820 VINSETTA BLVD	ROYAL OAK	Ph. 5868560541
PEGGY BURRY	Age: 73	1870 VINSETTA BLVD	ROYAL OAK	Ph. 2485474293
BRYAN HALL	Age: 48	1840 VINSETTA BLVD	ROYAL OAK	Ph. 2486299304
MICHELLE BOCK	Age: 49	1830 VINSETTA BLVD	ROYAL OAK	Ph. 2485465121
DONALD BURRY	Age: 74	1870 VINSETTA BLVD	ROYAL OAK	Ph. 2485474293
E REESE		1203 MAYFIELD DR	ROYAL OAK	Ph. 2483986570
KATHLEEN REESE		1203 MAYFIELD DR	ROYAL OAK	Ph. 2483986570
JAMES REESE III		1203 MAYFIELD DR	ROYAL OAK	Ph. 2483986570
JAMES REESE IV		1203 MAYFIELD DR	ROYAL OAK	Ph. 2483986570
JOYCE LUXON		1207 MAYFIELD DR	ROYAL OAK	Ph. 2485410215
NICHOLAS LUXON		1207 MAYFIELD DR	ROYAL OAK	Ph. 2485410215
SHANE LUXON		1207 MAYFIELD DR	ROYAL OAK	Ph. 2485410215
KAREN MALERBA		1217 MAYFIELD DR	ROYAL OAK	Ph. 2484390713

<colgroup><col style="width: 110px"><col width="98"><col width="104"><col width="93"><col width="129"></colgroup><tbody>
</tbody>

this is how it looks when I just paste it in

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Age: 83	1705 FORESTDALE CT
ROYAL OAK
Ph. 2485451177
JACK STEPHAN
Age: 00	1705 FORESTDALE CT
ROYAL OAK
Ph. 2485451177
BETTY SIER
Age: 91	1713 FORESTDALE CT
ROYAL OAK
Ph. 2485481307
JOHN SIER
Age: 52	1713 FORESTDALE CT
ROYAL OAK
Ph. 2485481307
MAUREEN SIER
Age: 52	1713 FORESTDALE CT
ROYAL OAK
Ph. 2485481307
SHANE MURPHY
Age: 72	1709 FORESTDALE CT
ROYAL OAK
Ph. 2485468052
GEORGE HOELAARS
Age: 79	1721 FORESTDALE CT
ROYAL OAK
Ph. 2485455568
JOAN HOELAARS
Age: 80	1721 FORESTDALE CT
ROYAL OAK
Ph. 2485455568
LENORE HOELAARS
Age: 49	1721 FORESTDALE CT
ROYAL OAK
Ph. 2485455568
BRENDA DOLBEE
Age: 66	1717 FORESTDALE CT
ROYAL OAK
Ph. 2485487533
DENNIS DOLBEE
Age: 63	1717 FORESTDALE CT
ROYAL OAK
Ph. 2485487533
BRENDA DOLBEE
Age: 66	1717 FORESTDALE CT
ROYAL OAK
Ph. 2485487533
DENNIS DOLBEE
Age: 63	1717 FORESTDALE CT
ROYAL OAK
Ph. 2485487533

<colgroup><col style="width: 110px"><col width="98"><col width="104"></colgroup><tbody>
[TD="bgcolor: #b7e1cd"]JUNE STEPHAN[/TD]

</tbody>

Thanks in advance!

 

[tygrrboi]

IF you paste that into A...
(So all of the data except the street name are in A... street name is in B next to the age for each person...)

This will output it starting in column E in the format that you like...

You can easily output it to a different sheet in column A or wherever you like but this should get you started.

Sub SortIt()
Dim i As Integer, lastrow As Integer, j As Integer


With ActiveSheet
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
    For i = 0 To lastrow / 4
        j = 1 + (4 * i)
        .Range("E" & i + 1).Value = .Range("A" & j).Value
        .Range("F" & i + 1).Value = .Range("A" & j + 1).Value
        .Range("G" & i + 1).Value = .Range("B" & j + 1).Value
        .Range("H" & i + 1).Value = .Range("A" & j + 2).Value
        .Range("I" & i + 1).Value = .Range("A" & j + 3).Value
    Next i


End With


End Sub

 

[Miguel1598]

You're a life saver thank you!

 

[Miguel1598]

one more quick question, what would i have to change for something like this
[TABLE="width: 192"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]E REESE[/TD]
[TD="colspan: 2"]1203 MAYFIELD DR[/TD]
[/TR]
[TR]
[TD="colspan: 2"]ROYAL OAK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Ph. 2483986570[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KATHLEEN REESE[/TD]
[TD="colspan: 2"]1203 MAYFIELD DR[/TD]
[/TR]
[TR]
[TD="colspan: 2"]ROYAL OAK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Ph. 2483986570[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JAMES REESE III[/TD]
[TD="colspan: 2"]1203 MAYFIELD DR[/TD]
[/TR]
[TR]
[TD="colspan: 2"]ROYAL OAK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Ph. 2483986570[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JAMES REESE IV[/TD]
[TD="colspan: 2"]1203 MAYFIELD DR[/TD]
[/TR]
[TR]
[TD="colspan: 2"]ROYAL OAK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Ph. 2483986570[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOYCE LUXON[/TD]
[TD="colspan: 2"]1207 MAYFIELD DR[/TD]
[/TR]
[TR]
[TD="colspan: 2"]ROYAL OAK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Ph. 2485410215[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NICHOLAS LUXON[/TD]
[TD="colspan: 2"]1207 MAYFIELD DR[/TD]
[/TR]
[TR]
[TD="colspan: 2"]ROYAL OAK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Ph. 2485410215[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SHANE LUXON[/TD]
[TD="colspan: 2"]1207 MAYFIELD DR[/TD]
[/TR]
[TR]
[TD="colspan: 2"]ROYAL OAK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Ph. 2485410215[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KAREN MALERBA[/TD]
[TD="colspan: 2"]1217 MAYFIELD DR[/TD]
[/TR]
[TR]
[TD="colspan: 2"]ROYAL OAK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Ph. 2484390713[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[tygrrboi]

let me explain whats happening so you can adapt it as you like...

Sub SortIt()
Dim i As Integer, lastrow As Integer, j As Integer


With ActiveSheet
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
    For i = 0 To lastrow / 4
        j = 1 + (4 * i)
        .Range("E" & i + 1).Value = .Range("A" & j).Value
        .Range("F" & i + 1).Value = .Range("A" & j + 1).Value
        .Range("G" & i + 1).Value = .Range("B" & j + 1).Value
        .Range("H" & i + 1).Value = .Range("A" & j + 2).Value
        .Range("I" & i + 1).Value = .Range("A" & j + 3).Value
    Next i


End With


End Sub

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
gets the location of the last row that has your original data in it. That way the code does not have to do any more work than necessary.

For i = 0 To lastrow / 4 .... next i
this is looping through however many people you have... the total number of rows of data divided by 4 rows per person.

j = 1 + (4 * i)
this will give you the location of each person as you loop. When i is 0 (the first time through) it is looking at row 1 + 0*4 or row 1. The second time when i = 1 its looking at 1 + (4*1) or row 5... etc.


.Range("E" & i + 1).Value = .Range("A" & j).Value
.Range("F" & i + 1).Value = .Range("A" & j + 1).Value
.Range("G" & i + 1).Value = .Range("B" & j + 1).Value
.Range("H" & i + 1).Value = .Range("A" & j + 2).Value
.Range("I" & i + 1).Value = .Range("A" & j + 3).Value

Lets look at this part from just the first time through, when i = 0. We also know that j is 1 because of the arithmetic done just before.

Range E & i + 1 is cell E1. In cell E1, put the value of Range A & J The contents of A1 go into E1
Range F & 1 gets A & 1 + 1 or A2.
Range G & 1 gets B & 1 + 1 or B2.
Range H gets A3, Range I gets A4...


Thats all there is to it.

If you lose the age and put the address up by the name you would just adjust the second part of what gets assigned to your new range. So for your example,

.Range("E" & i + 1).Value = .Range("A" & j).Value 'name
.Range("F" & i + 1).Value = .Range("B" & j).Value 'street
.Range("G" & i + 1).Value = .Range("B" & j + 1).Value 'address
.Range("H" & i + 1).Value = .Range("A" & j + 2).Value 'phone number

you will also need to adjust all the places that say 4 to 3 since there are only 3 rows per person.for the loop...
For i = 0 To lastrow / 3
and for j... j = 1 + (3 * i)