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



## Maklil (Dec 28, 2022)

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.xlsmABCDE1DATECUSTOMERINV NOITEMQTY211/11/2022CSS-100INV-A123ITTT-100/AS-1200311/12/2022CSS-100INV-A123ITTT-100/AS-2300411/13/2022CSS-102INV-A125ITTT-100/AS-2400511/14/2022CSS-103INV-A126ITTT-100/AS-2500611/15/2022CSS-100INV-A124ITTT-100/AS-2600711/16/2022CSS-105INV-A128ITTT-100/AS-6700811/17/2022CSS-100INV-A129ITTT-100/AS-7800911/18/2022CSS-107INV-A130ITTT-100/AS-49001011/19/2022CSS-108INV-A131ITTT-100*12GF10001111/20/2022CSS-109INV-A132ITTT-100*12GF11001211/21/2022CSS-109INV-A132ITTT-100*12GF100012001311/22/2022CSS-111INV-A133IHGE200013001411/23/2022CSS-111INV-A134ITTT-100/AS-214001511/24/2022CSS-102INV-A135ITTT-100/AS-315001611/25/2022CSS-107INV-A137ITTT-100/AS-816001711/26/2022CSS-108INV-A138ITTT-100/AS-101601DATA


copy last row.xlsmABCDE1DATECUSTOMERINV NOITEMQTY211/11/2022CSS-100INV-A123ITTT-100/AS-1200312/27/2022CSS-100INV-A123ITTT-100/AS-1100412/27/2022CSS-100INV-A123ITTT-100/AS-15056DATECUSTOMERINV NOITEMQTY711/12/2022CSS-100INV-A123ITTT-100/AS-2300812/27/2022CSS-100INV-A123ITTT-100/AS-2-20910DATECUSTOMERINV NOITEMQTY1111/13/2022CSS-102INV-A125ITTT-100/AS-24001211/14/2022CSS-102INV-A125ITTT-100/AS-22001312/27/2022CSS-102INV-A125ITTT-100/AS-21001412/28/2022CSS-102INV-A125ITTT-100/AS-24001516DATECUSTOMERINV NOITEMQTY1711/19/2022CSS-108INV-A131ITTT-100*12GF10001812/27/2022CSS-108INV-A131ITTT-100*12GF9001912/28/2022CSS-108INV-A131ITTT-100*12GF0DETAILS

this  is  what I want
copy last row.xlsmABCDE1ITEMCUSTOMERINV NOITEMQTY21CSS-100INV-A123ITTT-100/AS-15032CSS-100INV-A123ITTT-100/AS-2-2043CSS-103INV-A126ITTT-100/AS-250054CSS-100INV-A124ITTT-100/AS-260065CSS-105INV-A128ITTT-100/AS-670076CSS-100INV-A129ITTT-100/AS-780087CSS-107INV-A130ITTT-100/AS-490098CSS-109INV-A132ITTT-100*12GF1100109CSS-109INV-A132ITTT-100*12GF100012001110CSS-111INV-A133IHGE200013001211CSS-111INV-A134ITTT-100/AS-214001312CSS-102INV-A135ITTT-100/AS-315001413CSS-107INV-A137ITTT-100/AS-816001514CSS-108INV-A138ITTT-100/AS-101601output


----------



## Alex Blakenburg (Dec 31, 2022)

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 (Dec 31, 2022)

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 (Dec 31, 2022)

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


----------



## Maklil (Dec 31, 2022)

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


----------



## Alex Blakenburg (Dec 31, 2022)

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 (Jan 1, 2023)

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


----------



## Alex Blakenburg (Jan 1, 2023)

You're welcome. Glad I could help.


----------

