2 column lookup formula to reference new data

[rbrodarick]

Hello community,

I need help with a report I'm running at my work.
See the attached for an example of what I'm up against.
Bear in mind my actual working list is many pages long,
so the more systematic approach the better.

My new job as scheduler includes running a list of open orders and updating my spreadsheet daily.
Each morning I need to add orders and mark old orders as complete.
Populating data in Table 3 will help me reorganize my Table 1 (Master Work Log).

Note Table 2 was copy/pasted on the sheet from running a daily report
(I could also import to another sheet if someone needs it for a simpler formula)
The tricky part has been cross referencing order numbers:
1. that have been fulfilled (in which case I would hide data in row 6 on Table 1 as it's Complete)
2. that may be duplicated in Table 2 and already in Table 1 (see Column A "Existing Orders")

The biggest problem I'm facing is making a formula that will run Table 2 data against Table 1 order numbers
and fill in Table 3 (without any closed orders or currently existing order numbers from Table 1.)

I need a way to easily extrapolate all the unlike data to create a copy/paste friendly list of order numbers I can add to my running tally on Table 1.

There has to be a much simpler way to approach this than what I've been trying.

Using Microsoft 365.

Thanks in advance,
RB

Note: For order numbers with more than one line item, we use a series format (i.e. ######-123, etc.)
If it is necessary I can group these under one master 6 digit order number, so then the XLOOKUP formula runs and finds like data.

 

[awoohaw]

Have you looked into using Power Query. It looks like you are building a small custom application.
Here is a playlist from MyOnlineTrainingHub that has many tutorials on how to extract transform and load your daily data.

If you want the forum to help you with worksheet solutions, would be kind enough to help the forum help you? Please use the xl2bb add in to upload mini portions of each of your workbooks so the forum doesn't have to create your entire application.

Thanks in advance!