Copy and paste on a criteria VBA /& other

[jevi]

Hi All,

1. I have a big database and I need to copy from this huge database from column A to BZ, the rows differ everyday so now is 133.458. From this file "Database.xlsx", sheet will have an incremente number every day but is the first sheet always, I need to copy in another file "Working File.xlsx" sheet "Dati" the columns D, E, F,G and AM and the criteria is: exclude the value "0" (the column AM is with values and I need to exlude the "0").

2. Then when the data is copied to the "Working File.xlsx" sheet "Dati", I need to remove dublicates based on Column E,F,G,AM (which in the new file and sheet the columns will be A:E.

Then to make it perfect don't know if it is possibile with VBA (but important for me is 1 & 2):
- count the names that are in column D and the amount that is in the column E and give the result in a new sheet but same "Working File.xlsx" in cell B5;B6
- give the information from column A:E but for only the 10th clients with the highest value (that in is column E or old AM).

I try to record it but is not working as too many values when I uncheck the 0.

I hope I was clear but please let me know if you need more explanation
Thank you,

 

[DanteAmor]

2. Then when the data is copied to the "Working File.xlsx" sheet "Dati", I need to remove dublicates based on Column E,F,G,AM (which in the new file and sheet the columns will be A:E.

Before copying the data from "Database.xlsx" to "Working File.xlsx", do you have duplicate data on sheet "Working File.xlsx"?

If there are no duplicates, then, my logic says, if the value of sheet "Database.xlsx" already exists in "Working File.xlsx" then don't copy it.
Your process says, copy, check if it's duplicated then delete (3 steps).
My process: check, if it exists don't copy, if not then copy (2 steps).

What do you think?

 

[jevi]

Before copying the data from "Database.xlsx" to "Working File.xlsx", do you have duplicate data on sheet "Working File.xlsx"?

If there are no duplicates, then, my logic says, if the value of sheet "Database.xlsx" already exists in "Working File.xlsx" then don't copy it.
Your process says, copy, check if it's duplicated then delete (3 steps).
My process: check, if it exists don't copy, if not then copy (2 steps).

What do you think?

The database file has duplicate o triplicate values in it, and the data from "Database" is going to be copied in the "Working File".

Your logic is much better, one step less....as you can copy from "Database" all the values expect zeros but adding eliminating the duplicates that have the same values in column F, G and AM.

Let me know if you need to know more information about the file.

Thank you DanteAmor,

 

[DanteAmor]

In the "Working File", "Dati" sheet, which columns are the key to identify a duplicate: a,b,c,d and e?

 

[jevi]

In the "Working File", "Dati" sheet, which columns are the key to identify a duplicate: a,b,c,d and e?

it will be only C,D, E if this three columns have the same ID (C), Name ID (D), value (E), I need to remove them.

thnx

 

[DanteAmor]

it will be only C,D, E

Test the following macro on a portion of your data and see if the results are what you want.

Sub CopyAndPaste()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim dic As Object, ky As String
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, j As Long, lr As Long
  
  Set sh1 = Workbooks("Database").Sheets(1)
  Set sh2 = Workbooks("Working File").Sheets("Dati")
  Set dic = CreateObject("Scripting.Dictionary")
  
  a = sh1.Range("A2", sh1.Range("AM" & Rows.Count).End(3)).Value
  lr = sh2.Range("E" & Rows.Count).End(3).Row
  b = sh2.Range("A2:E" & lr).Value
  ReDim c(1 To UBound(a, 1), 1 To 5)
  
  'Stores the keys of the "dati" sheet in a dictionary
  For i = 1 To UBound(b, 1)
    'columns C, D and E
    ky = b(i, 3) & "|" & b(i, 4) & "|" & b(i, 5)
    dic(ky) = Empty
  Next
  
  'Review the records on sheet 1, if they meet the criteria then add them to the 'c' matrix
  For i = 1 To UBound(a, 1)
    'columns F, G and AM
    ky = a(i, 6) & "|" & a(i, 7) & "|" & a(i, 39)
    If a(i, 39) <> 0 Then
      If Not dic.exists(ky) Then
        j = j + 1
        c(j, 1) = a(i, 4)
        c(j, 2) = a(i, 5)
        c(j, 3) = a(i, 6)
        c(j, 4) = a(i, 7)
        c(j, 5) = a(i, 39)
      End If
    End If
  Next
  sh2.Range("A" & lr + 1).Resize(j, 5).Value = c
End Sub

 

[jevi]

Thank you..i tried it but it gives this error. I checked the columns and yes 6,7,39 which corrisponds to F, G, AM are the right columns to remove the duplicates.

Ky for what does it stands for? Key?

 

[DanteAmor]

The error is because you have an error result in the cell (e.g. #N/A, #VALUE, #DIV0, etc.) You should correct the errors in your cells.
If you can't correct them, then try the following:

Sub CopyAndPaste()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim dic As Object, ky As String
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, j As Long, lr As Long
  
  Set sh1 = Workbooks("Database").Sheets(1)
  Set sh2 = Workbooks("Working File").Sheets("Dati")
  Set dic = CreateObject("Scripting.Dictionary")
  
  a = sh1.Range("A2", sh1.Range("AM" & Rows.Count).End(3)).Value
  lr = sh2.Range("E" & Rows.Count).End(3).Row
  b = sh2.Range("A2:E" & lr).Value
  ReDim c(1 To UBound(a, 1), 1 To 5)
  
  'Stores the keys of the "dati" sheet in a dictionary
  For i = 1 To UBound(b, 1)
    'columns C, D and E
    ky = b(i, 3) & "|" & b(i, 4) & "|" & b(i, 5)
    dic(ky) = Empty
  Next
  
  'Review the records on sheet 1, if they meet the criteria then add them to the 'c' matrix
  For i = 1 To UBound(a, 1)
    'columns F, G and AM
    If Not IsError(a(i, 6)) And Not IsError(a(i, 7)) And Not IsError(a(i, 39)) Then
      ky = a(i, 6) & "|" & a(i, 7) & "|" & a(i, 39)
      If a(i, 39) <> 0 Then
        If Not dic.exists(ky) Then
          j = j + 1
          c(j, 1) = a(i, 4)
          c(j, 2) = a(i, 5)
          c(j, 3) = a(i, 6)
          c(j, 4) = a(i, 7)
          c(j, 5) = a(i, 39)
        End If
      End If
    End If
  Next
  If j > 0 Then sh2.Range("A" & lr + 1).Resize(j, 5).Value = c
End Sub

Ky for what does it stands for? Key?

The variable ky is only used by me to store the concatenated cells. It is not recommended to use variable names with reserved words. Key is already a VBA variable.

 

[jevi]

Hi there,

Sorry for the late reply but I have been sick....I will try the macro tomorrow and let you know if it works as today I don't have the new data. I will check it tomorrow if there is an error as it has 135.000 rows (e.g. #N/A, #VALUE, #DIV0, etc.)

Thank you so much also for the explanation of KY

 

[DanteAmor]

I hope it continues to improve your health.