Need to convert data from a row format to column in Excel

[scottd431]

I have approximately 2000 records that were given to me in row format. Basically it is name, address, city and phone but they are in the row and I would like to move them to column so that I will have a column for each field. I have used the cut, copy, paste, I have also used the copy, and Paste Special with Transpose and that works great but one record at a time. Is there a short cut or add-in or anything someone could recommend so that I might do all of them at one time or multiple because I have over 2000 records and currently that euquals to approx 9000+ rows, there is a blank row separating each record. Thank for any help you might can provide.
Scott

 

[PCmad]

You should be able to do this by recording a macro/VB code, then editing the macro to select just the rows that need to be converted for example a1:a2000 giving you rows from 1 to 2000.

 

[Zathras]

Since this isn't the VBA forum, here is a non-VBA way:

If indeed the addresses all line up in 5-row groups, you can use formulas, paste special and sort as follows: (If the addresses don't line up in 5-row groups, you will need to fix that first.)

1. Assume the workbook is set up like this:

A1: 'raw
B1: 'name
C1: 'address
D1: 'city
E1: 'phone
F1: 'spacer
A2: 'smith
A3: '123 main
A4: 'hometown
A5: '555 123-4567
A6:
A7: 'brown
A8: '456 first
A9: 'nexttown
A10: '555 321-9871
A11:
A12: 'green
A13: '789 second
A14: 'newtown
A15: '555 555-5555

2. Then enter the following formulas in B2 thru F2:

B2: =A2
C2: =A3
D2: =A4
E2: =A5
F2: =A6

3. Copy the formulas in B2 thru F2 down as far as you need (to the row that contains the name in the last address)

4. Select columns B thru F, copy and paste/special values.

5. Sort the sheet on column F

6. Delete all of the rows that don't have zero in column F

7. Delete column F

8. Delete column A