Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 19th 06, 10:06 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5
Default Turn vertical data to horizontal data automaticaly

I have a big amount of records that looks like this:

Leo Sesa
10666 Wilkins Ave #306
310-494-5875

Paul Wakki
12510 Van Nuys Blvd
818-497-9552

Is it possible to make that info "horizontal", like this? :

Leo Sesa 10666 Wilkins Ave #306 310-494-5875
Paul Wakki 12510 Van Nuys Blvd 818-497-9552

I mean, I know how to drag and drop every cell one by one, but I'm looking
for something automatic since I have a lot of data.

Thanks a lot !




--
Leo

  #2   Report Post  
Old July 19th 06, 10:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,047
Default Turn vertical data to horizontal data automaticaly

Hi Lenadro

select B33 for eg

on B3 (with 3 cells selected) type =tranpose(a3:a5)
enter with Ctrl+Shift+Enter not just enter

copy it down jumping 3 rows and after exclude rows as you need

hth
regards from Brazil
Marcelo


"Leandro Sesarego" escreveu:

I have a big amount of records that looks like this:

Leo Sesa
10666 Wilkins Ave #306
310-494-5875

Paul Wakki
12510 Van Nuys Blvd
818-497-9552

Is it possible to make that info "horizontal", like this? :

Leo Sesa 10666 Wilkins Ave #306 310-494-5875
Paul Wakki 12510 Van Nuys Blvd 818-497-9552

I mean, I know how to drag and drop every cell one by one, but I'm looking
for something automatic since I have a lot of data.

Thanks a lot !




--
Leo

  #3   Report Post  
Old July 19th 06, 10:14 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1
Default Turn vertical data to horizontal data automaticaly


in a new cell use the concatenate function to simply string the data
together
=Concatenate(NameCell, AddressCell, CityStateZipCell)


--
dmb490
------------------------------------------------------------------------
dmb490's Profile: http://www.excelforum.com/member.php...o&userid=36556
View this thread: http://www.excelforum.com/showthread...hreadid=563062

  #4   Report Post  
Old July 20th 06, 12:46 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5
Default Turn vertical data to horizontal data automaticaly

Perfect. It worked! Thanks

Last question so I can solve the whole problem:

Now I have the info horizontally but with 3 blank rows between each record.
How can I delete the blank rows so I can have a standard list?

Thanks Marcelo, I'm in Los Angeles but I'm from Argentina

--
Leo


"Marcelo" wrote:

Hi Lenadro

select B33 for eg

on B3 (with 3 cells selected) type =tranpose(a3:a5)
enter with Ctrl+Shift+Enter not just enter

copy it down jumping 3 rows and after exclude rows as you need

hth
regards from Brazil
Marcelo


"Leandro Sesarego" escreveu:

I have a big amount of records that looks like this:

Leo Sesa
10666 Wilkins Ave #306
310-494-5875

Paul Wakki
12510 Van Nuys Blvd
818-497-9552

Is it possible to make that info "horizontal", like this? :

Leo Sesa 10666 Wilkins Ave #306 310-494-5875
Paul Wakki 12510 Van Nuys Blvd 818-497-9552

I mean, I know how to drag and drop every cell one by one, but I'm looking
for something automatic since I have a lot of data.

Thanks a lot !




--
Leo

  #5   Report Post  
Old July 20th 06, 01:06 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 79
Default Turn vertical data to horizontal data automaticaly

If you followed exactly what Marcelo suggested, then in cell E3 you can try:
=OFFSET(B$3,((ROW()-3)*4),0)
This is not an array so you can just hit Enter. Then copy and paste to the
two cells to the right. Lastly, copy the three cells (E3:G3) and paste down
as far as you need.

-Simon

"Leandro Sesarego" wrote:

Perfect. It worked! Thanks

Last question so I can solve the whole problem:

Now I have the info horizontally but with 3 blank rows between each record.
How can I delete the blank rows so I can have a standard list?

Thanks Marcelo, I'm in Los Angeles but I'm from Argentina

--
Leo


"Marcelo" wrote:

Hi Lenadro

select B33 for eg

on B3 (with 3 cells selected) type =tranpose(a3:a5)
enter with Ctrl+Shift+Enter not just enter

copy it down jumping 3 rows and after exclude rows as you need

hth
regards from Brazil
Marcelo


"Leandro Sesarego" escreveu:

I have a big amount of records that looks like this:

Leo Sesa
10666 Wilkins Ave #306
310-494-5875

Paul Wakki
12510 Van Nuys Blvd
818-497-9552

Is it possible to make that info "horizontal", like this? :

Leo Sesa 10666 Wilkins Ave #306 310-494-5875
Paul Wakki 12510 Van Nuys Blvd 818-497-9552

I mean, I know how to drag and drop every cell one by one, but I'm looking
for something automatic since I have a lot of data.

Thanks a lot !




--
Leo



  #6   Report Post  
Old July 20th 06, 01:18 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,572
Default Turn vertical data to horizontal data automaticaly

Select the *entire* datalist and sort it.

The empty rows will be at the bottom.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Leandro Sesarego" wrote in
message ...
Perfect. It worked! Thanks

Last question so I can solve the whole problem:

Now I have the info horizontally but with 3 blank rows between each

record.
How can I delete the blank rows so I can have a standard list?

Thanks Marcelo, I'm in Los Angeles but I'm from Argentina

--
Leo


"Marcelo" wrote:

Hi Lenadro

select B33 for eg

on B3 (with 3 cells selected) type =tranpose(a3:a5)
enter with Ctrl+Shift+Enter not just enter

copy it down jumping 3 rows and after exclude rows as you need

hth
regards from Brazil
Marcelo


"Leandro Sesarego" escreveu:

I have a big amount of records that looks like this:

Leo Sesa
10666 Wilkins Ave #306
310-494-5875

Paul Wakki
12510 Van Nuys Blvd
818-497-9552

Is it possible to make that info "horizontal", like this? :

Leo Sesa 10666 Wilkins Ave #306

310-494-5875
Paul Wakki 12510 Van Nuys Blvd

818-497-9552

I mean, I know how to drag and drop every cell one by one, but I'm

looking
for something automatic since I have a lot of data.

Thanks a lot !




--
Leo


  #7   Report Post  
Old August 2nd 08, 02:17 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2008
Posts: 1
Default Turn vertical data to horizontal data automaticaly

Hi,
I have data(500 companies) that looks like this;
business name.
Address.
phone
business name.
Address.
phone
and I wat to convert it to this; (automatically)
business name,Address,phone
business name,Address,phone

can any one tell me how ?
thanks in advance
--
robw150956
God Bless


"Leandro Sesarego" wrote:

I have a big amount of records that looks like this:

Leo Sesa
10666 Wilkins Ave #306
310-494-5875

Paul Wakki
12510 Van Nuys Blvd
818-497-9552

Is it possible to make that info "horizontal", like this? :

Leo Sesa 10666 Wilkins Ave #306 310-494-5875
Paul Wakki 12510 Van Nuys Blvd 818-497-9552

I mean, I know how to drag and drop every cell one by one, but I'm looking
for something automatic since I have a lot of data.

Thanks a lot !




--
Leo

  #8   Report Post  
Old August 2nd 08, 04:31 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default Turn vertical data to horizontal data automaticaly

Sub ColtoRows()
Dim rng As Range
Dim I As Long
Dim J As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
J = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
For I = 1 To rng.Row Step nocols
Cells(J, "A").Resize(1, nocols).Value = _
Application.transpose(Cells(I, "A") _
.Resize(nocols, 1))
J = J + 1
Next
Range(Cells(J, "A"), Cells(rng.Row, "A")).ClearContents
Exit Sub
End Sub

If no blanks between sets.......enter 3 when the InputBox asks for number of
columns.

If a blank between sets......enter 4


Gord Dibben MS Excel MVP

On Fri, 1 Aug 2008 18:17:01 -0700, robw150956
wrote:

Hi,
I have data(500 companies) that looks like this;
business name.
Address.
phone
business name.
Address.
phone
and I wat to convert it to this; (automatically)
business name,Address,phone
business name,Address,phone

can any one tell me how ?
thanks in advance




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
converting vertical data list to horizontal data list tjb Excel Worksheet Functions 2 July 15th 06 02:17 AM
How to make Horizontal data go Vertical tx12345 Excel Worksheet Functions 10 December 24th 05 03:40 AM
Can you extend a formula horizontally while the data is vertical Shannon Excel Discussion (Misc queries) 3 December 20th 05 12:46 AM
Excel - turn a page number in a header from horizontal to vertical pbrookstx Charts and Charting in Excel 1 June 4th 05 01:01 AM
Flip the X and Y data on a chart so that the Y data is vertical. Haynesz Charts and Charting in Excel 1 December 3rd 04 01:14 PM


All times are GMT +1. The time now is 06:15 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017