Dictionary request

[emukiss10]

Hello Friends!

One of our Moderators - Fluff - is providing Dictinaries which speeds up everything beyond expectations.
Can You make for me a macro that will compare colums A,E,G and remove duplicates in this fashion?

ex.

IF A5, E5, G5 has the same values as A54321, E54321, G54321 and A99, E99, G99 Then leave only one instance of that record. (all in one sheet!)


A5 = 10, E5 = 100, G5 = PP1 / A54321 = 10, E54321 = 100, G54321 = PP1 / 99 = 10, E99 = 100, G99 = PP1


(fashion)

   Dim Cl As Range
   Dim v1 As String
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets(1)
   Set Ws2 = Sheets(2)
   With CreateObject("Scripting.dictionary")
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.count).End(xlUp))
         v1 = Cl.Value & Cl.Offset(, 3).Value
         If Not .Exists(v1) Then .Add v1, Cl.Offset(, 5).Offset(, 2).Value
      Next Cl
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.count).End(xlUp))
         v1 = Cl.Value & Cl.Offset(, 4).Value
         If .Exists(v1) Then Cl.Offset(, 10).Offset(, 3).Value = .Item(v1)
      Next Cl
   End With

I have working code made by myself (myself = slow) but I was wondering if it can be speedup by dictionaries.
Usually Im importing 28-31 text files to seperate sheets (for every day of month) and work on them. Each has between 500K-1.5M records.

Best regards
W.

 

[Rick Rothstein]

Yes, and its quite fast but I need to test it on monday at work. Ive just tested it at home with heavy eq.

If the code runs slow with your heavy equation ladened worksheet, then try this modification for my code and see if that improves things...

Sub RemoveDuplicateTripletsAEG()
  With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .DisplayAlerts = False
  End With
  Range("A1").CurrentRegion.RemoveDuplicates Array(1, 5, 7), xlNo
  With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    .DisplayAlerts = True
  End With
End Sub

 

[emukiss10]

Hello. It works nice. It is fast.

Can we remove rows based on values in column G using Arrays?

I need to remove every row that has

xxxx|80|1|xxxx, xxxx|80|01|xxxx, xxxx|80|03|xxxx, xxxx|80|10|xxxx in col G.\

("xxxx" can by any string from 1 to 4 char).


-------------------------
Eric W
Your method works fine but its slower than concatenate (A1&E1&G1) and remove duplicates AND Arrays are 15 x faster. Tested on 1.5 M records.

 

[Rick Rothstein]

Can we remove rows based on values in column G using Arrays?

I need to remove every row that has

xxxx|80|1|xxxx, xxxx|80|01|xxxx, xxxx|80|03|xxxx, xxxx|80|10|xxxx in col G.\

("xxxx" can by any string from 1 to 4 char).

I am not entirely sure what you mean by the above. What is in Column G that you want removed from the example you showed?

 

[emukiss10]

Hello Rick.

In column G i have values like: 12OL|80|01|PsAA (<- this is one value). Another example 2000|80|11|9100 .

I would like to remove entire row if value in column G contains 80|01 or 80|1 or 80|03 or 80|10.

At the moment my macro is doing:

1. Text to Columns seperated by pipe "|"
2. AutoFilter columns to leave only values mentioned above
3. Remove rows that are visible with autofilter.

I was wondering if You could write more elegant code which will be faster.

 

[Rick Rothstein]

Hello Rick.

In column G i have values like: 12OL|80|01|PsAA (<- this is one value). Another example 2000|80|11|9100 .

I would like to remove entire row if value in column G contains 80|01 or 80|1 or 80|03 or 80|10.

Is the 80|1 because of the 80|01 where the leading zero is dropped or because there is another entry in Column G with 80|1 in it?

I presume the 80|03 and 80|10 are there because they appear in separate cells in Column G, correct?

For, say, 12OL|80|01|PsAA why are we only looking at the 80|01 and not the 12OL or the PsAA?

 

[emukiss10]

Is the 80|1 because of the 80|01 where the leading zero is dropped or because there is another entry in Column G with 80|1 in it?

I presume the 80|03 and 80|10 are there because they appear in separate cells in Column G, correct?

For, say, 12OL|80|01|PsAA why are we only looking at the 80|01 and not the 12OL or the PsAA?

Middle numbers seperated by pipe "|" are kind of markers. I need to remove rows that have those two numbers in the middle. AAAA|10|01|AAAA is different than AAAA|10|1|AAAA and both need to be removed with whole row.

 

[Rick Rothstein]

Hello Rick.

In column G i have values like: 12OL|80|01|PsAA (<- this is one value). Another example 2000|80|11|9100 .

I would like to remove entire row if value in column G contains 80|01 or 80|1 or 80|03 or 80|10.

Okay, now I understand (from our last set of exchanges) what you want to do here, but I have one final question. Is this request to be coupled with your original question (that is, is this functionality to be combined with the code I posted earlier that removes duplicates) or is this a separate request requiring a separate macro?

 

[emukiss10]

Sorry for the delay. I would like it to be separate macro so I could use it on other projects to come, but for now it will be used in the same macro (with AEG)