Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programitically Transpose Data and Parse it
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programitically Transpose Data and Parse it
Hello George,
Thanks for your reply but pardon me for my ignorance. I could not get what u r trying to suggest. My knowledge about VBA for that matter is very minimal.. The NG is a huge dome under which many experts like u are there to lend out a helping hand. Uptil now I have been posting on the NG with my problems and someone or other came up with a solution which I could just copy/paste it as a code and run it. That is as far as my knowledge goes. Yes u r right that I do not want to my fields into row wise and not column wise. But thats how I dont know what to do.. I visited Greg Ritchie's Website but could not find anything to suit my problem in a single go. The document I have is from an OCR software and thats why I am having double data in single Column eg DOB and BG on a single line and Mobile and Fax on a single line.. I hope u can suggest something to me now. Rashid Khan "George Stevenson" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programitically Transpose Data and Parse it
What you are asking for is that have someone write the code for you. That
is consulting which you should be paying for. If you are going to write it yourself, then you need help to get over the hurdles, but you need to expend a fair amount of research effort yourself. That is the only way to get better at programming in Excel. Try a few things. What I posted was an example of how you put a program together that can traverse rows of data and know when to stop. Sorry I can't be of more help beyond this. "Rashid Khan" wrote in message ... Hello George, Thanks for your reply but pardon me for my ignorance. I could not get what u r trying to suggest. My knowledge about VBA for that matter is very minimal.. The NG is a huge dome under which many experts like u are there to lend out a helping hand. Uptil now I have been posting on the NG with my problems and someone or other came up with a solution which I could just copy/paste it as a code and run it. That is as far as my knowledge goes. Yes u r right that I do not want to my fields into row wise and not column wise. But thats how I dont know what to do.. I visited Greg Ritchie's Website but could not find anything to suit my problem in a single go. The document I have is from an OCR software and thats why I am having double data in single Column eg DOB and BG on a single line and Mobile and Fax on a single line.. I hope u can suggest something to me now. Rashid Khan "George Stevenson" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programitically Transpose Data and Parse it
Ok. Thanks for the reply
Rashid Khan "George Stevenson" wrote in message ... What you are asking for is that have someone write the code for you. That is consulting which you should be paying for. If you are going to write it yourself, then you need help to get over the hurdles, but you need to expend a fair amount of research effort yourself. That is the only way to get better at programming in Excel. Try a few things. What I posted was an example of how you put a program together that can traverse rows of data and know when to stop. Sorry I can't be of more help beyond this. "Rashid Khan" wrote in message ... Hello George, Thanks for your reply but pardon me for my ignorance. I could not get what u r trying to suggest. My knowledge about VBA for that matter is very minimal.. The NG is a huge dome under which many experts like u are there to lend out a helping hand. Uptil now I have been posting on the NG with my problems and someone or other came up with a solution which I could just copy/paste it as a code and run it. That is as far as my knowledge goes. Yes u r right that I do not want to my fields into row wise and not column wise. But thats how I dont know what to do.. I visited Greg Ritchie's Website but could not find anything to suit my problem in a single go. The document I have is from an OCR software and thats why I am having double data in single Column eg DOB and BG on a single line and Mobile and Fax on a single line.. I hope u can suggest something to me now. Rashid Khan "George Stevenson" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i parse every nth data point from a large data set in exce | Excel Worksheet Functions | |||
How to parse data | Excel Discussion (Misc queries) | |||
How to parse data | Excel Discussion (Misc queries) | |||
Parse Data to Various Columns | Excel Discussion (Misc queries) | |||
Parse raw data | Excel Programming |