| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Transpose multiple Columns to rows
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose multiple Columns to rows
On 16/03/2012 12:33 PM, Zucco wrote:
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 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose multiple Columns to rows
On Thursday, March 15, 2012 8:33:19 PM UTC-5, Zucco wrote:
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 -- Zucco 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose multiple Columns to rows
Hi Don
Small typo, I think you meant this: For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row Step block Regards Cimjet "Don Guillett" wrote in message news:29919190.1039.1331901997170.JavaMail.geo-discussion-forums@ynmb12... On Thursday, March 15, 2012 8:33:19 PM UTC-5, Zucco wrote: 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 -- Zucco 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose multiple Columns to rows
On Friday, March 16, 2012 8:59:46 AM UTC-5, Cimjet wrote:
Hi Don Small typo, I think you meant this: For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row Step block Regards Cimjet "Don Guillett" wrote in message news:29919190.1039.1331901997170.JavaMail.geo-discussion-forums@ynmb12... On Thursday, March 15, 2012 8:33:19 PM UTC-5, Zucco wrote: 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 -- Zucco 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 When I copied the msg data to a file it was on row 1 not 2. TESTED! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose multiple Columns to rows
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 "Don Guillett" wrote in message news:16091498.1305.1331908780315.JavaMail.geo-discussion-forums@ynll40... On Friday, March 16, 2012 8:59:46 AM UTC-5, Cimjet wrote: Hi Don Small typo, I think you meant this: For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row Step block Regards Cimjet "Don Guillett" wrote in message news:29919190.1039.1331901997170.JavaMail.geo-discussion-forums@ynmb12... On Thursday, March 15, 2012 8:33:19 PM UTC-5, Zucco wrote: 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 -- Zucco 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 When I copied the msg data to a file it was on row 1 not 2. TESTED! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose multiple Columns to rows
On Friday, March 16, 2012 9:57:29 AM UTC-5, Cimjet wrote:
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 "Don Guillett" wrote in message news:16091498.1305.1331908780315.JavaMail.geo-discussion-forums@ynll40... On Friday, March 16, 2012 8:59:46 AM UTC-5, Cimjet wrote: Hi Don Small typo, I think you meant this: For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row Step block Regards Cimjet "Don Guillett" wrote in message news:29919190.1039.1331901997170.JavaMail.geo-discussion-forums@ynmb12.... On Thursday, March 15, 2012 8:33:19 PM UTC-5, Zucco wrote: 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 -- Zucco 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 When I copied the msg data to a file it was on row 1 not 2. TESTED! Sounds just like something I do frequently. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose multiple Columns to rows
On Mar 15, 9:33*pm, Zucco wrote:
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 -- Zucco 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose multiple Columns to rows
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 the source data On Friday, March 16, 2012 9:33:19 AM UTC+8, Zucco wrote: 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 -- Zucco |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Transpose multiple groups of columns to multiple rowsrow | Excel Worksheet Functions | |||
| Transpose multiple Columns to rows | Excel Worksheet Functions | |||
| TRANSPOSE ROWS TO COLUMNS | Excel Discussion (Misc queries) | |||
| Transpose columns to rows using first columns repeated. | Excel Worksheet Functions | |||
| Transpose Columns to Rows | Excel Programming | |||
Linear Mode
