How to rearrange data vertical to horizontal

ThirdGenAggie06

New Member
Joined
Mar 25, 2010
Messages
4
How do I turn the top data set into one that looks like the lower data set? I've tried the paste special method, but that's going to put all the information in its own cells like I need. PLEASE HELP!!

What I have:

SMITH, MARK JOSEPH
SMITHTECH
124 6th St.
MADEUP, TX 77777
(555)123-4567
LICENSE: 1234
CONTINUING EDUCATION HOURS: 1.23

What I need: All in one line, with each entry in its own cell. With other entries to follow below.

Last First Company Address City, State Zip ........etc.
Smith Mark SmithTech 124 6th St. MadeUp, TX 77777
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
It works, but I have a list of 2,000 people and if I highlight all 2,000 and do a paste transpose, it puts them all in one row together. I can highlight each person's information individually and paste transpose it, but I'm trying to find a way to do it without having to copy-paste-transpose 2,000 times...
 
Upvote 0
I see, - I think it will be a VBA job. Is the data like

SMITH, MARK JOSEPH
SMITHTECH
124 6th St.
MADEUP, TX 77777
(555)123-4567
LICENSE: 1234
CONTINUING EDUCATION HOURS: 1.23
SMITH, MARK JOSEPH
SMITHTECH
124 6th St.
MADEUP, TX 77777
(555)123-4567
LICENSE: 1234
CONTINUING EDUCATION HOURS: 1.23

or

SMITH, MARK JOSEPH SMITH, MARK JOSEPH
SMITHTECH SMITHTECH
124 6th St. 124 6th St.
MADEUP, TX 77777 MADEUP, TX 77777
(555)123-4567 (555)123-4567
LICENSE: 1234 LICENSE: 1234
CONTINUING EDUCATION HOURS: 1.23 CONTINUING EDUCATION
 
Upvote 0
The data reads just like the second one you have there... there are 2,000 entries like that listed vertically down the spreadsheet with blank cells separating them from each other.
 
Upvote 0
No, scratch that... it is listed like the first one, but it has a blank cell between the two entries...

Joe Shmo
XXX
XXX
XXX

Jane Shmo
XXX
XXX
XXX
 
Upvote 0
Are there always 7 records for each person or can they differ?

i.e. always
Code:
1  SMITH, MARK JOSEPH
2  SMITHTECH
3  124 6th St
4  MADEUP, TX 77777
5  (555)123-4567
6  LICENSE: 1234
7  CONTINUING EDUCATION HOURS: 1.23
 
Upvote 0
Please bear in mind - I've only started to teach myself VBA in the last year so a proper VBA expert would probably despair - but this is how I would do it

1. Insert a column before your info
2. Number you 1st set of data 1-7 (I am now assuming numbers colmn A, info col B & all the data is on Sheet(1) )

3. On Sheet(2) enter headers B2 to H2

4. Run below macro

Code:
Sheets(1).Select
    Range("A3:A9").Select
    Selection.Copy
    
    Do While Selection.End(xlDown).Offset(2, 1) <> ""
    Selection.End(xlDown).Select
    Selection.Offset(2, 0).Select
    ActiveSheet.Paste
    Loop
    
    Columns("A:A").Select
    Selection.Replace What:="", Replacement:="blnk", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    
    Dim rng As Range
    Set rng = Sheets("sheet1").Range("B3:B20000").SpecialCells(xlCellTypeVisible)
    
    Range("A2").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="1"
    rng.Select
    Selection.Copy
    Sheets(2).Select
    Range("B3").Select
    ActiveSheet.Paste
    
    Sheets(1).Select
    Range("A2").Select
    Selection.AutoFilter Field:=1, Criteria1:="2"
    rng.Select
    Selection.Copy
    Sheets(2).Select
    Range("C3").Select
    ActiveSheet.Paste
    
    Sheets(1).Select
    Range("A2").Select
    Selection.AutoFilter Field:=1, Criteria1:="3"
    rng.Select
    Selection.Copy
    Sheets(2).Select
    Range("D3").Select
    ActiveSheet.Paste
    
    Sheets(1).Select
    Range("A2").Select
    Selection.AutoFilter Field:=1, Criteria1:="4"
    rng.Select
    Selection.Copy
    Sheets(2).Select
    Range("E3").Select
    ActiveSheet.Paste
    
    Sheets(1).Select
    Range("A2").Select
    Selection.AutoFilter Field:=1, Criteria1:="5"
    rng.Select
    Selection.Copy
    Sheets(2).Select
    Range("F3").Select
    ActiveSheet.Paste
    
    Sheets(1).Select
    Range("A2").Select
    Selection.AutoFilter Field:=1, Criteria1:="6"
    rng.Select
    Selection.Copy
    Sheets(2).Select
    Range("G3").Select
    ActiveSheet.Paste
    
    
    Sheets(1).Select
    Range("A2").Select
    Selection.AutoFilter Field:=1, Criteria1:="7"
    rng.Select
    Selection.Copy
    Sheets(2).Select
    Range("H3").Select
    ActiveSheet.Paste
 
Upvote 0
How do I turn the top data set into one that looks like the lower data set? I've tried the paste special method, but that's going to put all the information in its own cells like I need. PLEASE HELP!!

What I have:


01
02
03
04
05
06
07


WHAT I NEED

01 02 03
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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