VBA, VLookUp, or something else?

Hello!

I am struggling with what exactly I need to do with the data I am gathering, and I'm REALLY hoping I'm not being complicated, of course that would explain the headaches if it is, ha!

So in my department, we virtually consume materials to create new ones in our ERP, then we move them to the warehouse. I need to see what everyone is consuming to see if they are taking as much as they are suppose to or if they aren't taking enough. Our ERP system is QAD, and I've been working with it for 4 years now. In February I decided I was going to try to monitor everyone's accuracies on my own, so I run a history report for specified dates, and export that to an excel file. I have created a tab for every week from Jan 1 - current. In the beginning it wasn't terrible to go in and format things by hand, then use formulas manually pulling them down the column to do multiple rows. Then I learned how to create macros to do what I need. At least I thought I did. It formats everything for me, and puts the formula in the rows of the column I specified in the macro. The formula does what it's suppose to, however it's not enough. I think I need it to be more complex and I don't know which way I need to go. Below is a sample of my formula, what it does and doesn't do. I've since been promoted, and am now required to run these reports, however I do not have enough time to do my base job, plus the hours worth of combing through to see if supplies were consumed correctly.
Okay, so once I run my specific search criteria and export the file from QAD into Excel. I take that week and add it to my yearly workbook and run the macro. The primary function of the macro was to style it and help me sort through the work orders that were not accurate. Which was working well in the beginning when I didn't have other responsibilities to do. I had the time to sort through them manually, even if I did get headaches from the eye strain. The more I need to do though, the less time I have to filter through the right and wrongs, so, all it's really doing right now is formatting my cells, because the formula is simple. I think I need to somehow make a more complex formula, utilize VBA or create some sort of database. Because what I have to compare one line with another isn't as complex as I need it to be. This is the formula that the macro inserts in every cell in column H to reference G.

"=IF(OR(G2=G3, G2=G4, G2=G1), "", "x")"

Which is fine if I want the all or nothing approach. Some of our products require multiple supplies to be consumed and sometimes controllers don't always consume every item. Even though the X's are highlighting "Hey! I don't have anymore ID's to socialize with!" the ones that do have multiple ID's may not be correct either, because they could consume 2 or 3 supplies, but the order calls for 4. Which is why, I've had to go behind to see which ones that were "right" were actually wrong every week, got frustrated and stopped doing it. I have every week of this year exported, and half are formatted correctly, the other half are waiting to be formatted. I've attached to spreadsheets, one with a week that has been formatted that I combed through after running the macro, the other is before the macro has touched it.

What I would like is to be able to run it, so that it will style, search for the word "recover" or "regrind" in remarks and if true then the line is correct, flag the ones that aren't correct and count the the number of X's for each ID off to the side.
I don't know how to get that to work without creating some sort of database with a listing of our products and the supplies that makes our products, and somehow the macro references that file and marks them correct or not based on that.

Have I created confusion? Is what I need doable?

cross posted without links:
https://www.excelforum.com/excel-programming-vba-macros/1284537-macro-vba-or-database.html
Stargem83, for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184

Stargem83 said:

sort through the work orders that were not accurate.

Click to expand...

It's very difficult to help you since I don't know how to determine if a work order is 'accurate' (work order is column I 'Order' in the raw data?)

Stargem83 said:

Some of our products require multiple supplies to be consumed and sometimes controllers don't always consume every item.

Click to expand...

How do I know if every item is 'consumed'?

Stargem83 said:

Even though the X's are highlighting "Hey! I don't have anymore ID's to socialize with!" the ones that do have multiple ID's may not be correct either, because they could consume 2 or 3 supplies, but the order calls for 4.

Click to expand...

This will need some explaining!

Stargem83 said:

Which is why, I've had to go behind to see which ones that were "right" were actually wrong every week

Click to expand...

What makes it right? …or wrong!?

Stargem83 said:

What I would like is to be able to run it, so that it will style, search for the word "recover" or "regrind" in remarks and if true then the line is correct, flag the ones that aren't correct and count the the number of X's for each ID off to the side.

Click to expand...

This sort of thing might be really easy if you use a pivot table, but still I need to know the logic behind what makes

Stargem83 said:

I don't know how to get that to work without creating some sort of database

Click to expand...

Well, the raw data you're downloading into Excel is already almost a perfect database (some headers are the same). What's more, you'd be better putting all the year's (even multiple years' data) into a single worksheet. 1000 records per week is trivial even if there are several years of data.

So this almost perfect database stays much as it is (perhaps adding a few columns) (hidden away from humans preferably!) and you create reports which interrogate this data on another sheet or sheets.
You could start with a pivot table or two. Play with the one in Sheet 1 of the attached.

I know nothing of QAD; perhaps you can make a connection from Excel direct to it so that you don't need to do any exporting/updating of data.

I agree with Pascal on 2 points, first that I would do away with the multi-weeks sheets and amalgamate them into a single sheet and do your weekly analysis off of there; and secondly that you need a database, but you already have a perfectly good one, your ERP system.

You might be able to query that database directly, using Power Query and PowerPivot or maybe just simple ADO, but even if you can't you can continue to create an Excel export report.

But seriously, I think you need to look at this in a much broader context. You have an ERP which is probably very good for managing the business processes, but add some extra logic and you can get so much more, business information. I recently worked on a system where the client made security doors, similar setup in that the doors were built from numerous materials, they had an ERP for recording all of the business data, and we built a front-end in Excel for sales, invoice, BoMs and so on. You could get similar sorts of information.

To do so, I would suggest you commission a capable consultant to work with you, identify the data you have, the data you don't (yet) have, what you want to do. You and your team would need to invest the time and the money required to support the consultant, and test the system, but the results could be eye-opening for you. You seem to understand that you can get more, but I wonder if you understand how much more. It doesn't have to all be built in one go, it can be done incrementally, but knowing your overall gaol(s) is a first step.