Relocate Data

[jawdawson]

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!

 

[Ed Ferrero]

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

 

[Gord Dibben]

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

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

 

[jawdawson]

Thank you, gents.