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
LVL 3
Shailesh ShindeLocalization Engineering & AutomationAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
It looks to me that you mean Columns B & D,but even then they don't match.
Shailesh ShindeLocalization Engineering & AutomationAuthor Commented:
Hi
Sorry, I have mentioned Column B and C and not Column B and D

Thanks,
Shailesh
Roy CoxGroup Finance ManagerCommented:
But I cannot see how the data matches
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Shailesh ShindeLocalization Engineering & AutomationAuthor Commented:
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
Shailesh ShindeLocalization Engineering & AutomationAuthor Commented:
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
Roy CoxGroup Finance ManagerCommented:
If you look at the example I have copied the invoice data to the analysis and the inputs do not match
Roy CoxGroup Finance ManagerCommented:
Also, will you be using one invoice for each data workbook?
Shailesh ShindeLocalization Engineering & AutomationAuthor Commented:
Hi,

"Also, will you be using one invoice for each data workbook?"
Yes, only one invoice file for all input data.

Thanks,
Shailesh
Roy CoxGroup Finance ManagerCommented:
So you want 1000 blocks of data in 1 invoice?
Shailesh ShindeLocalization Engineering & AutomationAuthor Commented:
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
ShumsExcel & VBA ExpertCommented:
Hi Shailesh,

I am not sure, which format is correct and what to follow after reading all your thread, anyway, I already created a macro which does as per your initial requirement to consolidate all the Analysis files into Invoice Workbook.

Please follow below steps:
  1. First you need to create Discount Rate Sheets in Invoice Workbook.
  2. Then Create a New Folder anywhere on your desktop.
  3. Copy all the Analysis files in Newly created folder
  4. Download attached file and paste outside newly created folder.
  5. Open attached Invoice_v1 workbook.
  6. Click on Upload Invoices, which will prompt you to select the folder for Analysis files
  7. Select Newly created Folder
  8. That's it.
I have already amended required changes in your Invoice Workbook for eg, Context Match in Analysis Files and Context matches in Invoice Workbook.

Kindly note, my code wont work if you have different Analysis File Format. Try on files which you initially submitted.
Invoice_v1.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
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.
Shailesh ShindeLocalization Engineering & AutomationAuthor Commented:
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.
Test_1.jpg
Thanks,
Shailesh
ShumsExcel & VBA ExpertCommented:
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:
ResultI am attaching your initial Analysis file again, please test on these files
Analysis_1.xlsx
Analysis_2.xlsx
Shailesh ShindeLocalization Engineering & AutomationAuthor Commented:
Thanks a lot!
I have re-test and got the required result.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.