Dear All,
I have created excel file contains 3 worksheets namely, Sheet1, Sheet2 and Sheet 3
Sheet 1 is the row data which I have taken from the website through pdf file. (PDF attached separately) . While doing copy+paste, it has truncated into unorganized way.
password of PDF is 02122005
I have extracted the Headings and variable Data through different formulas by spreading into separate columns (because of various criteria, it cannot be clubbed)
Since row data is not in uniform way, result of using "Data - Text to columns" is not accurate.
Sheet 2 is the sample format in which it data has to be arranged properly. Data of Sheet 1 and Sheet 2 has been given identical colouring for matching.
In Sheet 2 I tried to extract data of a customer say "Name of Deductor = Powergrid Corporation of India Ltd" which has 20 entries, with linking of Sheet 1
There are many customers in the database and their individual entries is not uniform may be 1 or more than 1. If Sheet 2 works fine, then the Columns from B to AE can be avoided and data can be taken directly with function. However, linking of sheet 1 with sheet 2 with each and every entry is creating issues. Forget about the merging in the row header of each group. (Ref Col B to E of Sheet 2)
Sheet 3 is the another way I tried to extract data through indexing. This also works far better than Sheet 2. But identification of Row Nos and Column Nos matching with corresponding cells becomes difficult. Anyway, I have completed with the formula. But I face the following queries
1. What will be the best way to arrange data as per sample format either in Sheet 2 or Sheet 3.
2. In sheet 3 Indexing can be used. However, if the variable data of Sheet 1 is changed, Data of sheet 3 is affected (due to incorrect indexing). Pls see the coloured portion in Sheet 1 (Olive Green). How to correct this ?
2. I Have used "Find" so many number of times to extract data of W10 to AE29 of Sheet 1. Here, the formula is too lengthy. Is this can be shortened ?
Kindly avoid macro
In fact, this procedure is being done each month for preparation of TDS Data. Once it is streamlined, would be of great help to simplify process.
VDS
I have created excel file contains 3 worksheets namely, Sheet1, Sheet2 and Sheet 3
Sheet 1 is the row data which I have taken from the website through pdf file. (PDF attached separately) . While doing copy+paste, it has truncated into unorganized way.
password of PDF is 02122005
I have extracted the Headings and variable Data through different formulas by spreading into separate columns (because of various criteria, it cannot be clubbed)
Since row data is not in uniform way, result of using "Data - Text to columns" is not accurate.
Sheet 2 is the sample format in which it data has to be arranged properly. Data of Sheet 1 and Sheet 2 has been given identical colouring for matching.
In Sheet 2 I tried to extract data of a customer say "Name of Deductor = Powergrid Corporation of India Ltd" which has 20 entries, with linking of Sheet 1
There are many customers in the database and their individual entries is not uniform may be 1 or more than 1. If Sheet 2 works fine, then the Columns from B to AE can be avoided and data can be taken directly with function. However, linking of sheet 1 with sheet 2 with each and every entry is creating issues. Forget about the merging in the row header of each group. (Ref Col B to E of Sheet 2)
Sheet 3 is the another way I tried to extract data through indexing. This also works far better than Sheet 2. But identification of Row Nos and Column Nos matching with corresponding cells becomes difficult. Anyway, I have completed with the formula. But I face the following queries
1. What will be the best way to arrange data as per sample format either in Sheet 2 or Sheet 3.
2. In sheet 3 Indexing can be used. However, if the variable data of Sheet 1 is changed, Data of sheet 3 is affected (due to incorrect indexing). Pls see the coloured portion in Sheet 1 (Olive Green). How to correct this ?
2. I Have used "Find" so many number of times to extract data of W10 to AE29 of Sheet 1. Here, the formula is too lengthy. Is this can be shortened ?
Kindly avoid macro
In fact, this procedure is being done each month for preparation of TDS Data. Once it is streamlined, would be of great help to simplify process.
VDS
