Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Turn vertical data to horizontal data automaticaly
Hi Lenadro
select B3:D3 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 B3:D3 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 B3:D3 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 B3:D3 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting vertical data list to horizontal data list | Excel Worksheet Functions | |||
How to make Horizontal data go Vertical | Excel Worksheet Functions | |||
Can you extend a formula horizontally while the data is vertical | Excel Discussion (Misc queries) | |||
Excel - turn a page number in a header from horizontal to vertical | Charts and Charting in Excel | |||
Flip the X and Y data on a chart so that the Y data is vertical. | Charts and Charting in Excel |