I am trying to build a macro based excel tracker for my orders. I have an excel file called 'Tracker' that I maintain on my laptop and another file that i receive daily called 'OpenOrders'. The structure of the lines on both files are exactly the same.
In the 'Tracker 'file I have lines that show existing open orders. Then during the day yesterday some of the orders in the system would have closed and today I would have received the updated 'OpenOrders' file. Now in the newly arrived file today, orders that were closed yesterday would therefore no longer appear in the today's file while orders that are still open in the system will of course show up.
I would like to use a macro in the 'Tracker' file to look at the newly arrived file called 'OpenOrders' and
1. Compare the rows in the 'Tracker' file lines Sheet1 with the rows in the newly arrived OpenOrders file Sheet1
2. If the order lines in the Tracker file Sheet1 do not exist in the newly arrived 'OpenOrders' file Sheet1 (meaning they have been closed in the system) then those rows in the Tracker file Sheet1 should be moved to Sheet2
3. If order lines in the Tracker file Sheet1 exist in the newly arrived 'OpenOrders' file Sheet1 (meaning they are still open in the system) then the existing rows in 'Tracker' Sheet1 should remain as they are and new lines from 'OpenOrders' should be added to the 'Tracker' file Sheet1
4. The next day when the macro is run, same action as #2 and #3 but newly closed orders should be added to the last row on Tracker file Sheet2 so that i can maintain the list of all Closed orders.
Data in both files start from A1 as shown in the screenshots.
'Tracker' file as of yesterday evening:
New arrived 'OpenOrders' file this morning: (Let's say ord2 and ord3 were closed yesterday)
After running the Macro, the expected output in the 'Tracker' file Sheet 1:
and the expected output in the 'Tracker' file Sheet 2:
Thanks in advance for any input that could be provided. Also thanks to advise if I've missed out on anything.
In the 'Tracker 'file I have lines that show existing open orders. Then during the day yesterday some of the orders in the system would have closed and today I would have received the updated 'OpenOrders' file. Now in the newly arrived file today, orders that were closed yesterday would therefore no longer appear in the today's file while orders that are still open in the system will of course show up.
I would like to use a macro in the 'Tracker' file to look at the newly arrived file called 'OpenOrders' and
1. Compare the rows in the 'Tracker' file lines Sheet1 with the rows in the newly arrived OpenOrders file Sheet1
2. If the order lines in the Tracker file Sheet1 do not exist in the newly arrived 'OpenOrders' file Sheet1 (meaning they have been closed in the system) then those rows in the Tracker file Sheet1 should be moved to Sheet2
3. If order lines in the Tracker file Sheet1 exist in the newly arrived 'OpenOrders' file Sheet1 (meaning they are still open in the system) then the existing rows in 'Tracker' Sheet1 should remain as they are and new lines from 'OpenOrders' should be added to the 'Tracker' file Sheet1
4. The next day when the macro is run, same action as #2 and #3 but newly closed orders should be added to the last row on Tracker file Sheet2 so that i can maintain the list of all Closed orders.
Data in both files start from A1 as shown in the screenshots.
'Tracker' file as of yesterday evening:
Tracker.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | ColA | ColB | ColC | CustOrdNo | OrderNo | ColF | LnNo | ColH | Product | ColJ | ||
2 | Info | Info | Info | Ord1 | IntOrd1 | Info | 1 | Info | ProductA | Info | ||
3 | Info | Info | Info | Ord2 | IntOrd2 | Info | 1 | Info | ProductB | Info | ||
4 | Info | Info | Info | Ord3 | IntOrd3 | Info | 1 | Info | ProductC | Info | ||
5 | Info | Info | Info | Ord4 | IntOrd4 | Info | 1 | Info | ProductD | Info | ||
6 | Info | Info | Info | Ord5 | IntOrd5 | Info | 1 | Info | ProductE | Info | ||
Sheet1 |
New arrived 'OpenOrders' file this morning: (Let's say ord2 and ord3 were closed yesterday)
OpenOrders.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | ColA | ColB | ColC | CustOrdNo | OrderNo | ColF | LnNo | ColH | Product | ColJ | ||
2 | Info | Info | Info | Ord1 | IntOrd1 | Info | 1 | Info | ProductA | Info | ||
3 | Info | Info | Info | Ord4 | IntOrd4 | Info | 1 | Info | ProductD | Info | ||
4 | Info | Info | Info | Ord5 | IntOrd5 | Info | 1 | Info | ProductE | Info | ||
5 | Info | Info | Info | Ord6 | IntOrd6 | Info | 1 | Info | ProductM | Info | ||
6 | Info | Info | Info | Ord7 | IntOrd7 | Info | 1 | Info | ProductD | Info | ||
Sheet1 |
After running the Macro, the expected output in the 'Tracker' file Sheet 1:
Tracker.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | ColA | ColB | ColC | CustOrdNo | OrderNo | ColF | LnNo | ColH | Product | ColJ | ||
2 | Info | Info | Info | Ord1 | IntOrd1 | Info | 1 | Info | ProductA | Info | ||
3 | Info | Info | Info | Ord4 | IntOrd4 | Info | 1 | Info | ProductD | Info | ||
4 | Info | Info | Info | Ord5 | IntOrd5 | Info | 1 | Info | ProductE | Info | ||
5 | Info | Info | Info | Ord6 | IntOrd6 | Info | 1 | Info | ProductM | Info | ||
6 | Info | Info | Info | Ord7 | IntOrd7 | Info | 1 | Info | ProductD | Info | ||
Sheet1 |
and the expected output in the 'Tracker' file Sheet 2:
Tracker.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | ColA | ColB | ColC | CustOrdNo | OrderNo | ColF | LnNo | ColH | Product | ColJ | ||
2 | Info | Info | Info | Ord2 | IntOrd2 | Info | 1 | Info | ProductB | Info | ||
3 | Info | Info | Info | Ord3 | IntOrd3 | Info | 1 | Info | ProductC | Info | ||
Sheet2 |
Thanks in advance for any input that could be provided. Also thanks to advise if I've missed out on anything.