Macro to compare and process lines in fixed file vs daily incoming file

[sncb]

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:

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.

 

[sncb]

Ok, please give me sometime and I will get you the files. thanks again.

 

[Fuji]

I will go off line soon, so take your time...

 

[sncb]

Hi Fuji, this is a replica of what my actual data looks in both files. Let me know please if this is ok or you need more information.

Code	Name	S Date	C date	SP1	SP2	SP3	TID	FC	TMS
123	123-US-STATE-SUBSTATE	1-Jan-24	5-Jan-24	123456	789456	121212121	11111111	ZZ	OCEAN
123	123-US-STATE-SUBSTATE	1-Jan-24	5-Jan-24	123456	789456	232323232	11111111	ZZ	OCEAN
123	123-US-STATE-SUBSTATE	1-Jan-24	5-Jan-24	123456	789456	999999999	11111111	ZZ	OCEAN
123	123-US-STATE-SUBSTATE	1-Jan-24	5-Jan-24	123456	789456	141414141	11111111	ZZ	OCEAN
456	456-ME-STATE-SUBSTATE	10-Jan-24	15-Feb-24	456789	123321	212121212	22222222	ZZ	OCEAN
456	456-ME-STATE-SUBSTATE	10-Jan-24	15-Feb-24	456789	123321	858585858	22222222	ZZ	OCEAN

 

[Fuji]

Just tested with EXACTLY the same data in both file and obviously nothing will happen.
And just changed one of the cells and worked as it should

Sub test()
    Dim wb As Workbook, a, b, i&, ii&, s(1), ub&
    For Each wb In Workbooks
        If wb.Name = ThisWorkbook.Name Then
            a = wb.Sheets(1).[a1].CurrentRegion.Value
        ElseIf UCase$(wb.Name) = "OPENORDERS.XLSX" Then
            b = wb.Sheets(1).[a1].CurrentRegion.Value
        End If
    Next
    If Not IsArray(b) Then MsgBox "OpenOrders.xlsx is not open": Exit Sub
    ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(a, 2) + 1), b(1 To UBound(b, 1), 1 To UBound(b, 2) + 1)
    For i = 2 To Application.Max(UBound(a, 1), UBound(b, 1))
        If i <= UBound(a, 1) Then a(i, UBound(a, 2)) = Join(Application.Index(a, i, 0), Chr(2))
        If i <= UBound(b, 1) Then b(i, UBound(b, 2)) = Join(Application.Index(b, i, 0), Chr(2))
    Next
    For i = 2 To UBound(a, 1)
        If a(i, UBound(a, 2)) <> "" Then
            For ii = 2 To UBound(b, 1)
                If b(ii, UBound(b, 2)) <> "" Then
                    If a(i, UBound(a, 2)) = b(ii, UBound(b, 2)) Then
                        a(i, UBound(a, 2)) = "": b(i, UBound(b, 2)) = ""
                    End If
                End If
            Next
        End If
    Next
    With ThisWorkbook.Sheets(1)
        With .[a1].Resize(UBound(a, 1), UBound(a, 2))
            .Value = a: ub = UBound(a, 2)
            .AutoFilter UBound(a, 2), "<>"
            If .Columns(1).SpecialCells(12).Count > 1 Then
                .Offset(1).SpecialCells(12).Copy Sheets("sheet2").Range("a" & Rows.Count).End(xlUp)(2)
                .Offset(1).EntireRow.Delete
            End If
            .AutoFilter
        End With
        .Range("a" & Rows.Count).End(xlUp)(2).Resize(UBound(b, 1), UBound(b, 2)).Value = b
        a = Application.Transpose(Evaluate("row(1:" & UBound(a, 2) - 1 & ")"))
        .Columns(ub).Clear
        ReDim Preserve a(0 To UBound(a) - 1)
        With .[a1].CurrentRegion
            .Rows(2).Copy
            .Cells.PasteSpecial xlPasteFormats
            .RemoveDuplicates (a), xlNo
        End With
        Application.Goto .Cells(1)
    End With
    Sheets("sheet2").Columns(UBound(b, 2)).Clear
End Sub

 

[sncb]

Then I am totally confused myself

I tried various alternatives. Thinking it could be formatting I did a copy-paste values to brand new sheets and yet the same problem. With the dummy data it seems to work perfectly so I'm confounded.

What exactly is the error saying? Maybe I could understand from the error msg and try to pinpoint the probable cause.

 

[sncb]

Good news!!!! It worked now...dont know what it was but its ok. I just started from scratch and it worked as expected...

Great...thanks a lot for your help Fuji...Appreciate the effort and the goodwill. Wish you a good weekend.

 

[Fuji]

That's good to hear.

It sometime happens...

It worked now...dont know what it was but its ok. I just started from scratch and it worked as expected...

 

[sncb]

Hi Fuji,

Sorry to come back on this one. When I changed the filename to the one I need, the whole Ln4 errored out - Error 424 - Object required in yellow. Im guessing its referring to the fact that the file with the name is not open but it actually is. I tried with various names but getting the same error. Any ideas why?

Ln4:
If wb.Name = Tracking.Name Then

and the file name is Tracking.xlsm

 

[Fuji]

Can you show me the line(s) that you changed?

 

[sncb]

In my real data, I took the file with like 20 lines in the Tracking file and deleted the bottom 10 lines and saved it as the OPENORDERS file and tested and then got this error. I also got this error sometimes with test data too so its random.