Shailesh Shinde
asked on
Exttract required data from multiple excel files for invoice output
Hi All,
There is an urgent requirement for excel macro which will extract the required data from the multiple analysis report(more than 500 excel files) and generate one invoice excel file (Filename: Invoice).
input data to extract
**Fix rows and columns in each analysis file.**
Rows 23 to 34
and required columns are
Column B and C
Sample input data in input excel files
and required output
Column B data | Column C data | Discounted Rates (Need to add by code)| Amount in USD (Need to add by code)
Please find attached sample output file
Thanks,
Shailesh
Analysis_1.xlsx
Analysis_2.xlsx
Invoice.xlsx
There is an urgent requirement for excel macro which will extract the required data from the multiple analysis report(more than 500 excel files) and generate one invoice excel file (Filename: Invoice).
input data to extract
**Fix rows and columns in each analysis file.**
Rows 23 to 34
and required columns are
Column B and C
Sample input data in input excel files
and required output
Column B data | Column C data | Discounted Rates (Need to add by code)| Amount in USD (Need to add by code)
Please find attached sample output file
Thanks,
Shailesh
Analysis_1.xlsx
Analysis_2.xlsx
Invoice.xlsx
It looks to me that you mean Columns B & D,but even then they don't match.
ASKER
Hi
Sorry, I have mentioned Column B and C and not Column B and D
Thanks,
Shailesh
Sorry, I have mentioned Column B and C and not Column B and D
Thanks,
Shailesh
But I cannot see how the data matches
ASKER
Hi,
The output file contains static data from input file Column B and C and in output rates columns will be fixed values and based upon the rates and words column the amount column will have the calculated values.
Thanks,
Shailesh
The output file contains static data from input file Column B and C and in output rates columns will be fixed values and based upon the rates and words column the amount column will have the calculated values.
Thanks,
Shailesh
ASKER
Hi,
Please find the updated output file, in this file.
The rates are fixed throughout the analysis files and data from column B and C for this are fixed as well.
Thanks,
Shailesh
Invoice.xlsx
Please find the updated output file, in this file.
The rates are fixed throughout the analysis files and data from column B and C for this are fixed as well.
Thanks,
Shailesh
Invoice.xlsx
If you look at the example I have copied the invoice data to the analysis and the inputs do not match
Also, will you be using one invoice for each data workbook?
ASKER
Hi,
"Also, will you be using one invoice for each data workbook?"
Yes, only one invoice file for all input data.
Thanks,
Shailesh
"Also, will you be using one invoice for each data workbook?"
Yes, only one invoice file for all input data.
Thanks,
Shailesh
So you want 1000 blocks of data in 1 invoice?
ASKER
Hi Roy,
Extremely sorry for the incorrect output file.
Please find attached the updated input and output snippet in this attached file.
Thanks,
Shailesh
Analysis_1---Copy.xlsx
Extremely sorry for the incorrect output file.
Please find attached the updated input and output snippet in this attached file.
Thanks,
Shailesh
Analysis_1---Copy.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Shailesh, I don't think copying all those blocks of data into one file will be manageable. You will end up with an horrendous file to work with.
I would recommend re-thinking the design of what you want to achieve.
I would recommend re-thinking the design of what you want to achieve.
ASKER
Hi Shailesh,
I am not sure, which Analysis File you upload as I mentioned earlier, you need to select the initial file which you uploaded.
Please check below what I got in first file:
I am attaching your initial Analysis file again, please test on these files
Analysis_1.xlsx
Analysis_2.xlsx
I am not sure, which Analysis File you upload as I mentioned earlier, you need to select the initial file which you uploaded.
Please check below what I got in first file:
I am attaching your initial Analysis file again, please test on these files
Analysis_1.xlsx
Analysis_2.xlsx
ASKER
Thanks a lot!
I have re-test and got the required result.
I have re-test and got the required result.