need help organizing random cells

Miguel1598

New Member
Joined
Nov 23, 2016
Messages
3
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 YATIMAge: 381840 VINSETTA BLVDROYAL OAKPh. 2486299304
ANDREW PRUSINOWSKIAge: 271878 VINSETTA BLVDROYAL OAKPh. 2483982504
MARVIN PRUSINOWSKIAge: 591878 VINSETTA BLVDROYAL OAKPh. 2483982504
BENJAMIN SHARKEYAge: 341840 VINSETTA BLVDROYAL OAKPh. 2486299304
SARAH PRUSINOWSKIAge: 301878 VINSETTA BLVDROYAL OAKPh. 2483982504
JENNIFER NELSON-MEFFORDAge: 441884 VINSETTA BLVDROYAL OAKPh. 2483187885
LANCE LANGEAge: 481820 VINSETTA BLVDROYAL OAKPh. 2483903767
ADELENE HOEYAge: 801860 VINSETTA BLVDROYAL OAKPh. 2485424605
BILL BOCKAge: 501830 VINSETTA BLVDROYAL OAKPh. 2485465121
DEBRA BOIKAge: 001820 VINSETTA BLVDROYAL OAKPh. 5868560541
PEGGY BURRYAge: 731870 VINSETTA BLVDROYAL OAKPh. 2485474293
BRYAN HALLAge: 481840 VINSETTA BLVDROYAL OAKPh. 2486299304
MICHELLE BOCKAge: 491830 VINSETTA BLVDROYAL OAKPh. 2485465121
DONALD BURRYAge: 741870 VINSETTA BLVDROYAL OAKPh. 2485474293
E REESE1203 MAYFIELD DRROYAL OAKPh. 2483986570
KATHLEEN REESE1203 MAYFIELD DRROYAL OAKPh. 2483986570
JAMES REESE III1203 MAYFIELD DRROYAL OAKPh. 2483986570
JAMES REESE IV1203 MAYFIELD DRROYAL OAKPh. 2483986570
JOYCE LUXON1207 MAYFIELD DRROYAL OAKPh. 2485410215
NICHOLAS LUXON1207 MAYFIELD DRROYAL OAKPh. 2485410215
SHANE LUXON1207 MAYFIELD DRROYAL OAKPh. 2485410215
KAREN MALERBA1217 MAYFIELD DRROYAL OAKPh. 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>
JUNE STEPHAN
Age: 831705 FORESTDALE CT
ROYAL OAK
Ph. 2485451177
JACK STEPHAN
Age: 001705 FORESTDALE CT
ROYAL OAK
Ph. 2485451177
BETTY SIER
Age: 911713 FORESTDALE CT
ROYAL OAK
Ph. 2485481307
JOHN SIER
Age: 521713 FORESTDALE CT
ROYAL OAK
Ph. 2485481307
MAUREEN SIER
Age: 521713 FORESTDALE CT
ROYAL OAK
Ph. 2485481307
SHANE MURPHY
Age: 721709 FORESTDALE CT
ROYAL OAK
Ph. 2485468052
GEORGE HOELAARS
Age: 791721 FORESTDALE CT
ROYAL OAK
Ph. 2485455568
JOAN HOELAARS
Age: 801721 FORESTDALE CT
ROYAL OAK
Ph. 2485455568
LENORE HOELAARS
Age: 491721 FORESTDALE CT
ROYAL OAK
Ph. 2485455568
BRENDA DOLBEE
Age: 661717 FORESTDALE CT
ROYAL OAK
Ph. 2485487533
DENNIS DOLBEE
Age: 631717 FORESTDALE CT
ROYAL OAK
Ph. 2485487533
BRENDA DOLBEE
Age: 661717 FORESTDALE CT
ROYAL OAK
Ph. 2485487533
DENNIS DOLBEE
Age: 631717 FORESTDALE CT
ROYAL OAK
Ph. 2485487533



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


Thanks in advance!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.

Code:
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
 
Upvote 0
one more quick question, what would i have to change for something like this
E REESE1203 MAYFIELD DR
ROYAL OAK
Ph. 2483986570
KATHLEEN REESE1203 MAYFIELD DR
ROYAL OAK
Ph. 2483986570
JAMES REESE III1203 MAYFIELD DR
ROYAL OAK
Ph. 2483986570
JAMES REESE IV1203 MAYFIELD DR
ROYAL OAK
Ph. 2483986570
JOYCE LUXON1207 MAYFIELD DR
ROYAL OAK
Ph. 2485410215
NICHOLAS LUXON1207 MAYFIELD DR
ROYAL OAK
Ph. 2485410215
SHANE LUXON1207 MAYFIELD DR
ROYAL OAK
Ph. 2485410215
KAREN MALERBA1217 MAYFIELD DR
ROYAL OAK
Ph. 2484390713

<colgroup><col span="3"></colgroup><tbody>
</tbody>
 
Upvote 0
let me explain whats happening so you can adapt it as you like...

Code:
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)
 
Last edited:
Upvote 0

Similar threads

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top