Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relocate Data
I pasted html address data onto a new sheet, which worked perfectly. I wish to relocate 60,000 records of data in columns to rows, IE: From this format: A 1 Name 2 Street 3 City 4 State 5 Zip To this format: A B C D E F 1 Name Street City State Zip Telephone I have tried a Macro, but can't get it to replicate, and I have tried using Move/Special Paste with Covert to Value, but it won't work on text. If I try to move all this data by hand, I could be at this for months... Help, please!!! Thank you! -- jawdawson ------------------------------------------------------------------------ jawdawson's Profile: http://www.excelforum.com/member.php...o&userid=30648 View this thread: http://www.excelforum.com/showthread...hreadid=503059 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relocate Data
HI jawdawson,
If you have a list with repeating items like this (assume the list starts in cell A1) Name Street City State Zip next entry ... And you wish to convert it to an Excel list appear as: Name Street City State Zip etc... First enter a new heading 'Sort' at the end of the new list. Then enter formulas to fill in the first row. So you have; Name Street City State Zip Sort =A1 = A2 =A3 =A4 =A5 Then enter the following formula under Sort; =MOD(ROW(A1),5)=1 Where 7 is the number of items that repeat in your original list. This formula returns TRUE every 5th row. Copy the formulas down until you capture all records in the original list. Now, select all of the new list (all the formulas), copy and paste special as values. Then sort the new list by 'Sort' in descending order. All the correct entries will be at the top (with TRUE in the sort column). below this there will be a bunch of rubbish entries (with FALSE in the sort column) - you can delete these. Ed Ferrero Microsoft Excel MVP http://www.edferrero.com I pasted html address data onto a new sheet, which worked perfectly. I wish to relocate 60,000 records of data in columns to rows, IE: From this format: A 1 Name 2 Street 3 City 4 State 5 Zip To this format: A B C D E F 1 Name Street City State Zip Telephone I have tried a Macro, but can't get it to replicate, and I have tried using Move/Special Paste with Covert to Value, but it won't work on text. If I try to move all this data by hand, I could be at this for months... Help, please!!! Thank you! -- jawdawson ------------------------------------------------------------------------ jawdawson's Profile: http://www.excelforum.com/member.php...o&userid=30648 View this thread: http://www.excelforum.com/showthread...hreadid=503059 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relocate Data
If your data is consistent.
i.e. always 6 rows You can use a macro on a copy of your worksheet. Sub ColtoRows() Dim rng As Range Dim i As Long Dim j As Long Dim nocols 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 End Sub Gord Dibben Excel MVP On Thu, 19 Jan 2006 14:20:38 -0600, jawdawson wrote: I pasted html address data onto a new sheet, which worked perfectly. I wish to relocate 60,000 records of data in columns to rows, IE: From this format: A 1 Name 2 Street 3 City 4 State 5 Zip To this format: A B C D E F 1 Name Street City State Zip Telephone I have tried a Macro, but can't get it to replicate, and I have tried using Move/Special Paste with Covert to Value, but it won't work on text. If I try to move all this data by hand, I could be at this for months... Help, please!!! Thank you! Gord Dibben MS Excel MVP |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relocate Data
Thank you, gents. -- jawdawson ------------------------------------------------------------------------ jawdawson's Profile: http://www.excelforum.com/member.php...o&userid=30648 View this thread: http://www.excelforum.com/showthread...hreadid=503059 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Saving data in a worksheet within a workbook | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |