copy lastrow for 12000 rows based on first row for each range

[Maklil]

Hello
I have about 12000 rows for two sheets . sheet DATA contains data and sheet DETAILS contains the same structure but for each range is separated . so should match data between two sheets from columns B:D if the data are matched , then will copy the lastrow for each separated range from sheet DETAILS to sheet OUTPUT but based on condition , if the value in lastrow is the same value for first row(under header means second row for each range ) or the value in lastrow equal to zero should not copy at all, otherwise should copy the others , and if the existed data in sheet DATA but not existed as in separated range at all also should copy to sheet output .
with considering the data will change in sheets DATA,DETAILS ,then should clear data in sheet OUTPUT before brings data to updating any change for the others sheets .

copy last row.xlsm
	A	B	C	D	E
1	DATE	CUSTOMER	INV NO	ITEM	QTY
2	11/11/2022	CSS-100	INV-A123	ITTT-100/AS-1	200
3	11/12/2022	CSS-100	INV-A123	ITTT-100/AS-2	300
4	11/13/2022	CSS-102	INV-A125	ITTT-100/AS-2	400
5	11/14/2022	CSS-103	INV-A126	ITTT-100/AS-2	500
6	11/15/2022	CSS-100	INV-A124	ITTT-100/AS-2	600
7	11/16/2022	CSS-105	INV-A128	ITTT-100/AS-6	700
8	11/17/2022	CSS-100	INV-A129	ITTT-100/AS-7	800
9	11/18/2022	CSS-107	INV-A130	ITTT-100/AS-4	900
10	11/19/2022	CSS-108	INV-A131	ITTT-100*12GF	1000
11	11/20/2022	CSS-109	INV-A132	ITTT-100*12GF	1100
12	11/21/2022	CSS-109	INV-A132	ITTT-100*12GF1000	1200
13	11/22/2022	CSS-111	INV-A133	IHGE2000	1300
14	11/23/2022	CSS-111	INV-A134	ITTT-100/AS-2	1400
15	11/24/2022	CSS-102	INV-A135	ITTT-100/AS-3	1500
16	11/25/2022	CSS-107	INV-A137	ITTT-100/AS-8	1600
17	11/26/2022	CSS-108	INV-A138	ITTT-100/AS-10	1601
DATA

copy last row.xlsm
	A	B	C	D	E
1	DATE	CUSTOMER	INV NO	ITEM	QTY
2	11/11/2022	CSS-100	INV-A123	ITTT-100/AS-1	200
3	12/27/2022	CSS-100	INV-A123	ITTT-100/AS-1	100
4	12/27/2022	CSS-100	INV-A123	ITTT-100/AS-1	50
5
6	DATE	CUSTOMER	INV NO	ITEM	QTY
7	11/12/2022	CSS-100	INV-A123	ITTT-100/AS-2	300
8	12/27/2022	CSS-100	INV-A123	ITTT-100/AS-2	-20
9
10	DATE	CUSTOMER	INV NO	ITEM	QTY
11	11/13/2022	CSS-102	INV-A125	ITTT-100/AS-2	400
12	11/14/2022	CSS-102	INV-A125	ITTT-100/AS-2	200
13	12/27/2022	CSS-102	INV-A125	ITTT-100/AS-2	100
14	12/28/2022	CSS-102	INV-A125	ITTT-100/AS-2	400
15
16	DATE	CUSTOMER	INV NO	ITEM	QTY
17	11/19/2022	CSS-108	INV-A131	ITTT-100*12GF	1000
18	12/27/2022	CSS-108	INV-A131	ITTT-100*12GF	900
19	12/28/2022	CSS-108	INV-A131	ITTT-100*12GF	0
DETAILS

this is what I want

copy last row.xlsm
	A	B	C	D	E
1	ITEM	CUSTOMER	INV NO	ITEM	QTY
2	1	CSS-100	INV-A123	ITTT-100/AS-1	50
3	2	CSS-100	INV-A123	ITTT-100/AS-2	-20
4	3	CSS-103	INV-A126	ITTT-100/AS-2	500
5	4	CSS-100	INV-A124	ITTT-100/AS-2	600
6	5	CSS-105	INV-A128	ITTT-100/AS-6	700
7	6	CSS-100	INV-A129	ITTT-100/AS-7	800
8	7	CSS-107	INV-A130	ITTT-100/AS-4	900
9	8	CSS-109	INV-A132	ITTT-100*12GF	1100
10	9	CSS-109	INV-A132	ITTT-100*12GF1000	1200
11	10	CSS-111	INV-A133	IHGE2000	1300
12	11	CSS-111	INV-A134	ITTT-100/AS-2	1400
13	12	CSS-102	INV-A135	ITTT-100/AS-3	1500
14	13	CSS-107	INV-A137	ITTT-100/AS-8	1600
15	14	CSS-108	INV-A138	ITTT-100/AS-10	1601
output

 

[Alex Blakenburg]

In your sample data the First Line in Details always matches the Line in Data ? Is that something that can be relied on ?
Also will there always only be one line in Data per combination ?

For the 1st example INV-A123 ITTT-100/AS-1 Details has 3 lines but only the last one is in your output. Are you expecting only the last line in output ?

Is it possible for a record to exist in Details and not in Data ? What rule applies when that happens ?

 

[Maklil]

Hi Alex

In your sample data the First Line in Details always matches the Line in Data ? Is that something that can be relied on ?

yes

Also will there always only be one line in Data per combination ?

I'm not sure which sheet you're talking about , if you mean sheet DATA yes . as to sheet DETAILS must be in first line Data per combination depends on sheet DATA until match the first line Data per combination with the last line and implement the condition as I said

if the value in lastrow is the same value for first row(under header means second row for each range ) or the value in lastrow equal to zero should not copy at all, otherwise should copy the others

For the 1st example INV-A123 ITTT-100/AS-1 Details has 3 lines but only the last one is in your output. Are you expecting only the last line in output ?

yes should be the last line in output

Is it possible for a record to exist in Details and not in Data ?

surely not becuase sheet DETAILS depends on sheet DATA. in others meaining sheet DATA is source .

 

[Alex Blakenburg]

I might not get a chance to do anything tonight being NY eve but will have a look tomorrow.

 

[Maklil]

no problem Alex .
I hope you can help me with this project as soon as possible .

 

[Alex Blakenburg]

Give this a try:

Sub GetLastTran()
    Dim dataSht As Worksheet, detailSht As Worksheet, outSht As Worksheet
    Dim dataRng As Range, detailRng As Range, outRng As Range
    Dim dataLRow As Long, detailLRow As Long, outLRow As Long
    Dim dataArr As Variant, detailArr As Variant, outArr() As Variant
    Dim dataAmt As Currency, detailAmt As Currency
    Dim i As Long, jCol As Long, detailRow As Long, outRow As Long
    
    Set dataSht = Worksheets("DATA")            ' <--- Change if not correct name
    Set detailSht = Worksheets("DETAILS")       ' <--- Change if not correct name
    Set outSht = Worksheets("output")           ' <--- Change if not correct name
    
    With dataSht
        dataLRow = .Range("A" & Rows.Count).End(xlUp).Row
        Set dataRng = .Range("A2:E" & dataLRow)
        dataArr = dataRng.Value2
    End With
    
    With detailSht
        detailLRow = .Range("A" & Rows.Count).End(xlUp).Row
        Set detailRng = .Range("A2:E" & detailLRow)
        detailArr = detailRng.Value2
    End With
    
    With outSht
        Set outRng = .Range("A2")
        outRng.CurrentRegion.Offset(1).ClearContents
        ReDim outArr(1 To UBound(dataArr, 1), 1 To UBound(dataArr, 2))
    End With
    
    Dim dictDetail As Object, dictKey As String

    Set dictDetail = CreateObject("Scripting.dictionary")
    ReDim Preserve detailArr(1 To UBound(detailArr), 1 To UBound(detailArr, 2) + 1)
    ' Load details range into Dictionary
    For i = 1 To UBound(detailArr)
        dictKey = detailArr(i, 2) & "|" & detailArr(i, 3) & "|" & detailArr(i, 4)
        ' Load latest row number
        If detailArr(i, 1) <> "" And UCase(detailArr(i, 1) <> "DATE") Then
            dictDetail(dictKey) = i
        End If
    Next i
    
    ' Load output array
    For i = 1 To UBound(dataArr)
        dataAmt = dataArr(i, 5)
        dictKey = dataArr(i, 2) & "|" & dataArr(i, 3) & "|" & dataArr(i, 4)
        If dictDetail.exists(dictKey) Then
            detailRow = dictDetail(dictKey)
            detailAmt = detailArr(detailRow, 5)
            If detailAmt <> 0 And detailAmt <> dataAmt Then
                ' Use Detail information
                outRow = outRow + 1
                For jCol = 1 To UBound(outArr, 2)
                    outArr(outRow, jCol) = detailArr(detailRow, jCol)
                Next jCol
            End If
        Else
            ' if not in Detail sheet use Data information
            outRow = outRow + 1
            For jCol = 1 To UBound(outArr, 2)
                outArr(outRow, jCol) = dataArr(i, jCol)
            Next jCol
        End If
    Next i
    
    ' Output results
    outRng.Resize(outRow, UBound(outArr, 2)).Value = outArr
    
    ' Recreate Index
    outRng.Value = 1
    Set outRng = outRng.Resize(outRow, 1)
    outRng.NumberFormat = "General"
    outRng.Cells(1).AutoFill Destination:=outRng, Type:=xlFillSeries

End Sub

 

[Maklil]

it's great !
works as I expected .
many thanks Alex .

 

[Alex Blakenburg]

You're welcome. Glad I could help.