Link to home
Start Free TrialLog in
Avatar of Shailesh Shinde
Shailesh ShindeFlag for India

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
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

It looks to me that you mean Columns B & D,but even then they don't match.
Avatar of Shailesh Shinde

ASKER

Hi
Sorry, I have mentioned Column B and C and not Column B and D

Thanks,
Shailesh
But I cannot see how the data matches
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
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
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?
Hi,

"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?
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
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Hi Shums,

The output of very first file does not contains the no match row. However, rest of the records comes as per requirement.
Please find attached screenshot of the first invoice data.
User generated image
Thanks,
Shailesh
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:
User generated imageI am attaching your initial Analysis file again, please test on these files
Analysis_1.xlsx
Analysis_2.xlsx
Thanks a lot!
I have re-test and got the required result.