How to rearrange data vertical to horizontal

[ThirdGenAggie06]

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

 

[Dannyh1]

Hi - paste special transpose should do this?

 

[ThirdGenAggie06]

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

 

[Dannyh1]

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

 

[ThirdGenAggie06]

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.

 

[ThirdGenAggie06]

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

 

[Dannyh1]

Are there always 7 records for each person or can they differ?

i.e. always

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

 

[Dannyh1]

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

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

 

[REHANSRSPL]

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