Transpose multiple Columns to rows

Z

Zucco

Hey i need help transposing multiple sets of data to columns. I have
couple hundred and don't want to do each by hand any suggestions?


Home Slice Pizza
1415 SOUTH CONGRESS AVE · Austin, Texas · 78704-2434
Pizza Restaurant
2,572 were here · 139 likes


Double Dave's Pizzaworks
Austin, Texas
Pizza Restaurant
1,118 were here · 53 likes


Brooklyn Heights
14900 Avery Ranch Blvd · Austin, Texas · 78717
Pizza Restaurant
2,270 were here · 228 likes · Open until 12:00 am.


California Pizza Kitchen
3401 Esperanza Xing Ste 100 · Austin, Texas · 78758-8705
Pizza Restaurant
1,253 were here · 67 likes


Red House Pizzeria
1917 Manor Road · Austin, Texas · 78722
Pizza Restaurant
1,136 were here · 91 likes


Hoeks Death Metal Pizza
511 E. 6th Street · Austin, Texas · 78701
Pizza Restaurant
1,525 were here · 3,948 likes · Open until 3:00 am.


Double Dave's Pizzaworks
3000 Duval St · Austin, Texas · 78705-3823
Pizza Restaurant
375 were here · 28 likes


Salvation Pizza
624 W 34th St · Austin, Texas · 78705-1203
Pizza Restaurant
506 were here · 80 likes


Double Daves Pizza Works
11900 Metric Blvd Ste F · Austin, Texas · 78758-3121
Pizza Restaurant
437 were here · 23 likes


Brick Oven Restaurant
9911 Brodie Ln Ste 600 · Austin, Texas · 78748-5964
Pizza Restaurant · Italian Restaurant · Family Style Restaurant
465 were here · 24 like
 
V

Vacuum Sealed

Hey i need help transposing multiple sets of data to columns. I have a
couple hundred and don't want to do each by hand any suggestions?


Home Slice Pizza
1415 SOUTH CONGRESS AVE · Austin, Texas · 78704-2434
Pizza Restaurant
2,572 were here · 139 likes


Double Dave's Pizzaworks
Austin, Texas
Pizza Restaurant
1,118 were here · 53 likes


Brooklyn Heights
14900 Avery Ranch Blvd · Austin, Texas · 78717
Pizza Restaurant
2,270 were here · 228 likes · Open until 12:00 am.


California Pizza Kitchen
3401 Esperanza Xing Ste 100 · Austin, Texas · 78758-8705
Pizza Restaurant
1,253 were here · 67 likes


Red House Pizzeria
1917 Manor Road · Austin, Texas · 78722
Pizza Restaurant
1,136 were here · 91 likes


Hoeks Death Metal Pizza
511 E. 6th Street · Austin, Texas · 78701
Pizza Restaurant
1,525 were here · 3,948 likes · Open until 3:00 am.


Double Dave's Pizzaworks
3000 Duval St · Austin, Texas · 78705-3823
Pizza Restaurant
375 were here · 28 likes


Salvation Pizza
624 W 34th St · Austin, Texas · 78705-1203
Pizza Restaurant
506 were here · 80 likes


Double Daves Pizza Works
11900 Metric Blvd Ste F · Austin, Texas · 78758-3121
Pizza Restaurant
437 were here · 23 likes


Brick Oven Restaurant
9911 Brodie Ln Ste 600 · Austin, Texas · 78748-5964
Pizza Restaurant · Italian Restaurant · Family Style Restaurant
465 were here · 24 likes
Click to expand...
Would be easier if you showed what columns which was in as the
representation does not fit your request of multiple columns.

Also, did you want to add a % of the "Likes" to the total "Were Here"

A B C D E
1 NAME ADDRESS TYPE ATTENDEES LIKES
2

If it is laid out like you have presented above, it will require VB to
move each line up and over to the corresponding row for each.

HTH
Mick.
 
D

Don Guillett

Hey i need help transposing multiple sets of data to columns. I have a
couple hundred and don't want to do each by hand any suggestions?


Home Slice Pizza
1415 SOUTH CONGRESS AVE · Austin, Texas · 78704-2434
Pizza Restaurant
2,572 were here · 139 likes


Double Dave's Pizzaworks
Austin, Texas
Pizza Restaurant
1,118 were here · 53 likes


Brooklyn Heights
14900 Avery Ranch Blvd · Austin, Texas · 78717
Pizza Restaurant
2,270 were here · 228 likes · Open until 12:00 am.


California Pizza Kitchen
3401 Esperanza Xing Ste 100 · Austin, Texas · 78758-8705
Pizza Restaurant
1,253 were here · 67 likes


Red House Pizzeria
1917 Manor Road · Austin, Texas · 78722
Pizza Restaurant
1,136 were here · 91 likes


Hoeks Death Metal Pizza
511 E. 6th Street · Austin, Texas · 78701
Pizza Restaurant
1,525 were here · 3,948 likes · Open until 3:00 am.


Double Dave's Pizzaworks
3000 Duval St · Austin, Texas · 78705-3823
Pizza Restaurant
375 were here · 28 likes


Salvation Pizza
624 W 34th St · Austin, Texas · 78705-1203
Pizza Restaurant
506 were here · 80 likes


Double Daves Pizza Works
11900 Metric Blvd Ste F · Austin, Texas · 78758-3121
Pizza Restaurant
437 were here · 23 likes


Brick Oven Restaurant
9911 Brodie Ln Ste 600 · Austin, Texas · 78748-5964
Pizza Restaurant · Italian Restaurant · Family Style Restaurant
465 were here · 24 likes
Click to expand...

I am here in Austin and happy to help. When I copied your data I get 4 lines and 2 blank rows for a block of 6 rows. If only one blank or none change blank=
BTW. The best pizza is on RR 620 at Saconnes or Romes.

Sub transposeblocksSAS()
block = 6
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step block
Cells(i, 1).Resize(block).Copy
Cells(i, "b").End(xlUp)(2) _
..PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Next i
Columns.AutoFit
End Sub
 
C

Cimjet

Hi Don
Small typo, I think you meant this:
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row Step block
Regards
Cimjet

Hey i need help transposing multiple sets of data to columns. I have a
couple hundred and don't want to do each by hand any suggestions?


Home Slice Pizza
1415 SOUTH CONGRESS AVE · Austin, Texas · 78704-2434
Pizza Restaurant
2,572 were here · 139 likes


Double Dave's Pizzaworks
Austin, Texas
Pizza Restaurant
1,118 were here · 53 likes


Brooklyn Heights
14900 Avery Ranch Blvd · Austin, Texas · 78717
Pizza Restaurant
2,270 were here · 228 likes · Open until 12:00 am.


California Pizza Kitchen
3401 Esperanza Xing Ste 100 · Austin, Texas · 78758-8705
Pizza Restaurant
1,253 were here · 67 likes


Red House Pizzeria
1917 Manor Road · Austin, Texas · 78722
Pizza Restaurant
1,136 were here · 91 likes


Hoeks Death Metal Pizza
511 E. 6th Street · Austin, Texas · 78701
Pizza Restaurant
1,525 were here · 3,948 likes · Open until 3:00 am.


Double Dave's Pizzaworks
3000 Duval St · Austin, Texas · 78705-3823
Pizza Restaurant
375 were here · 28 likes


Salvation Pizza
624 W 34th St · Austin, Texas · 78705-1203
Pizza Restaurant
506 were here · 80 likes


Double Daves Pizza Works
11900 Metric Blvd Ste F · Austin, Texas · 78758-3121
Pizza Restaurant
437 were here · 23 likes


Brick Oven Restaurant
9911 Brodie Ln Ste 600 · Austin, Texas · 78748-5964
Pizza Restaurant · Italian Restaurant · Family Style Restaurant
465 were here · 24 likes
Click to expand...

I am here in Austin and happy to help. When I copied your data I get 4 lines and
2 blank rows for a block of 6 rows. If only one blank or none change blank=
BTW. The best pizza is on RR 620 at Saconnes or Romes.

Sub transposeblocksSAS()
block = 6
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step block
Cells(i, 1).Resize(block).Copy
Cells(i, "b").End(xlUp)(2) _
..PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Next i
Columns.AutoFit
End Sub
 
D

Don Guillett

Hi Don
Small typo, I think you meant this:
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row Step block
Regards
Cimjet



I am here in Austin and happy to help. When I copied your data I get 4 lines and
2 blank rows for a block of 6 rows. If only one blank or none change blank=
BTW. The best pizza is on RR 620 at Saconnes or Romes.

Sub transposeblocksSAS()
block = 6
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step block
Cells(i, 1).Resize(block).Copy
Cells(i, "b").End(xlUp)(2) _
.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Next i
Columns.AutoFit
End Sub
Click to expand...

When I copied the msg data to a file it was on row 1 not 2. TESTED!
 
C

Cimjet

Hi Don
My apologies, I always start at row 2 because of heading and I didn't think
for minute.
I guess it's too early in the morning...
Best regards
Cimjet
Hi Don
Small typo, I think you meant this:
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row Step block
Regards
Cimjet



I am here in Austin and happy to help. When I copied your data I get 4 lines
and
2 blank rows for a block of 6 rows. If only one blank or none change blank=
BTW. The best pizza is on RR 620 at Saconnes or Romes.

Sub transposeblocksSAS()
block = 6
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Step block
Cells(i, 1).Resize(block).Copy
Cells(i, "b").End(xlUp)(2) _
.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Next i
Columns.AutoFit
End Sub
Click to expand...

When I copied the msg data to a file it was on row 1 not 2. TESTED!
 
D

Don Guillett

Hi Don
My apologies, I always start at row 2 because of heading and I didn't think
for minute.
I guess it's too early in the morning...
Best regards
Cimjet


When I copied the msg data to a file it was on row 1 not 2. TESTED!
Click to expand...

Sounds just like something I do frequently.
 
W

W.Shelton

Hey i need help transposing multiple sets of data to columns. I have a
couple hundred and don't want to do each by hand any suggestions?

Home Slice Pizza
1415 SOUTH CONGRESS AVE · Austin, Texas · 78704-2434
Pizza Restaurant
2,572 were here · 139 likes

Double Dave's Pizzaworks
Austin, Texas
Pizza Restaurant
1,118 were here · 53 likes

Brooklyn Heights
14900 Avery Ranch Blvd · Austin, Texas · 78717
Pizza Restaurant
2,270 were here · 228 likes · Open until 12:00 am.

California Pizza Kitchen
3401 Esperanza Xing Ste 100 · Austin, Texas · 78758-8705
Pizza Restaurant
1,253 were here · 67 likes

Red House Pizzeria
1917 Manor Road · Austin, Texas · 78722
Pizza Restaurant
1,136 were here · 91 likes

Hoeks Death Metal Pizza
511 E. 6th Street · Austin, Texas · 78701
Pizza Restaurant
1,525 were here · 3,948 likes · Open until 3:00 am.

Double Dave's Pizzaworks
3000 Duval St · Austin, Texas · 78705-3823
Pizza Restaurant
375 were here · 28 likes

Salvation Pizza
624 W 34th St · Austin, Texas · 78705-1203
Pizza Restaurant
506 were here · 80 likes

Double Daves Pizza Works
11900 Metric Blvd Ste F · Austin, Texas · 78758-3121
Pizza Restaurant
437 were here · 23 likes

Brick Oven Restaurant
9911 Brodie Ln Ste 600 · Austin, Texas · 78748-5964
Pizza Restaurant · Italian Restaurant · Family Style Restaurant
465 were here · 24 likes
Click to expand...

Hopefully, I'm not too late with this post.

Here is a non-VBA approach to this problem.

It looks like your data is consistent with the format so consider
using a helper column and the OFFSET function to transpose your data.

I placed your example data in column A starting in cell A2. Then in
cells B2 through B11, I entered 0,5,10,15,20,25...and so on for the
helper cells. Then in cells C2, D2, E2 and F2 I use the following
OFFSET function.
ColumnC
ColumnD
ColumnE ColumnF
ROW 2 =OFFSET($A$2,$B3+1,0,1,1) =OFFSET($A$2,$B3+2,0,1,1) =OFFSET($A
$2,$B3+3,0,1,1) =OFFSET($A$2,$B3+4,0,1,1)

I just copied the formula down till no more record information was
returned.

This seemed to work for me.

Regards,

W.Shelton
 
M

Max

Assuming your data as posted runs in A2 down (groups of 4 populated rows, interspersed with 2 blank rows, ie a total of 6 rows between groups)
Put in B2: =OFFSET($A$2,ROWS($1:1)*6-6+COLUMNS($A:A)-1,)
Copy B2 across by 4 cols to E2, fill down as far as required to exhaust thesource data
 
Top