I would submit that you don't want to continue the problem of having fields
that are related to a single record stored horizontally as multiple rows.
You need to get all of your data for 1 record into a single row that you can
treat like a database and parse it into as many constiuent parts as you
need, but all stored as different columns on 1 row.

You'll need to write some code that will traverse your 7 rows of data for
each record, and put it into columns on a single row in a 2nd worksheet.
You can do all of that under program control.

Here is an example of how to find the last row in a spreadsheet. You would
need something like this to get started being able to move through your 7
rows of data for each record. You can remove the ' in the '
CurrentCell.Activate statement to have the routine highlight each cell that
it works on as it moves through the data. Nice for debugging, but it slows
down the processing if you are crunching lots of rows.

This should help get you started.

sub TestLastRow()
dim mylastrow as integer
Range("D1").Select
myLastRow = FindLastRow()
end sub

Public Function FindLastRow()
Set currentcell = ActiveCell
Dim MyRow
MyRow = 0
' Look for 3 consecutive rows of empty cells
Do Until IsEmpty(currentcell) And IsEmpty(currentcell.Offset(1, 0)) And
IsEmpty(currentcell.Offset(2, 0))
Set currentcell = currentcell.Offset(1, 0)
MyRow = currentcell.Row
' CurrentCell.Activate
Loop
FindLastRow = MyRow - 1
End Function





"Rashid Khan" wrote in message
...
Hello All Experts,

I have several thousand rows of data in the following format in Column A
with sample data shown under each line. I am using Office XP.

1 Name MiddleName LastName withou commas
John Henry Joseph
2 Address line1 separated by commas
705, Heritage Avenue,
3 Address line2 separated by commas
Hill Street, Mount Area,
4 City Name with pincode separated by '-'
Delhi-12345
5 Telephone for Office and Residence separated by '-'
O-3456789, R-4567890
6 Mobile No and Fax No.
Mobile: 98200 12345 Fax: 09122 334567
6 Email Address
Email:
7 DOB and Blood Group
DOB: Jan 13 B.G: B+ve

I need to achieve the results on Sheet2 as follows:
as per the example data mentioned above.
Sheet2
A1: John
B1: Henry
C1: Joseph

D1: 705,
E1: Heritage Avenue,

F1: Hill Street,
G1: Mount Area,

H1: London
I1: 12345

J1: Mobile: 98200 12345
K1: Fax: 09122 334567

L1: Email:

M1: DOB: Jan 13
N1: B.G: B+ver

Pls note that the data is in standard group of Seven Rows each separated
by
a blank row .... running down to thousand of rows.

Can this be achieved.. At present I am doing manual copy/paste which is
very
very time consming.

Any help or better ideas, suggestion to achieve the desired results would
be
very much appreciated

Thanks to all in advance

Rashid Khan