Find duplicate and compare which one of them is the highest
hi,
I have a file with more than 90,000 rows in which I need to do the following,
Find duplicate reference in Column C
Check the maximum values in Column G for that duplicate reference and enter the value in Column Q as "Fees".
Check the minimum values in Column G for that duplicate reference and enter the value in Column Q as "VAT" and replace part of the string "-M] - Fees" in Column M with "-M] - VAT".
As a beginner starter according to your attachment & explanation :
PHP Code:
Sub Demo1() Dim V, W, Rg As Range Application.ScreenUpdating = False With [A7].CurrentRegion.Columns .Sort .Cells(3), xlAscending, Header:=xlYes .Item(3).AdvancedFilter xlFilterCopy, , [U1], True V = .Item(3).Address End With With [U1].CurrentRegion.Resize(, 2).Columns .Item(2).Value2 = Evaluate("IF({1},COUNTIF(" & V & "," & .Item(1).Address & ")=1)") .Sort .Cells(2), xlAscending, Header:=xlNo W = Application.Match(True, .Item(2), 0) If W > 1 Then V = .Item(1).Resize(W - 1).Value2 .Clear End With If W > 1 Then If Not IsArray(V) Then V = Array(V) With [A7].CurrentRegion.Columns(3) For Each W In V Set Rg = .Find(W, , xlValues, xlWhole) With Range(Rg, .FindPrevious(Rg)).Offset(, 4) With .Cells(Application.Match(Application.Max(.Cells), .Cells, 0)) .Copy .Offset(, 10) End With With .Cells(Application.Match(Application.Min(.Cells), .Cells, 0)) .Offset(, 6).Value2 = Replace(.Offset(, 6).Value2, "Fees", "VAT") .Copy .Offset(, 10) End With End With Next End With Set Rg = Nothing End If Application.ScreenUpdating = True End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 09-27-2019 at 09:09 PM.
Reason: optimization …
Thanks for the code, as I have mentioned my original data is very huge and your code is running very slow and could not get the result. I have tested the code in the attached file. It is working but the data changed it original order which I do not prefer.
As for the replacement of string, it is replacing for the maximum value as VAT, I need for the minimum value.
As you must have seen before writing anything my code already takes the minimum value fot the VAT ‼
So you are confusing minimum & maximum of negative values LoL !
But as a beginner starter, you can amend this code …
Re: Find duplicate and compare which one of them is the highest
hi Akuini,
Can you amend this code, below is your code.
Find duplicate reference in Column C
Check the minimum values in Column G for that duplicate reference and enter the value in Column Q as "Fees".
Check the maximum values in Column G for that duplicate reference and enter the value in Column Q as "VAT" and replace part of the string "-M] - Fees" in Column M with "-M] - VAT".
Re: Find duplicate and compare which one of them is the highest
hi marks,
I am getting the error in the below line and also Check the minimum values in Column G for that duplicate reference and enter the value in Column Q as "Fees".
Check the maximum values in Column G for that duplicate reference and enter the value in Column Q as "VAT" and replace part of the string "-M] - Fees" in Column M with "-M] - VAT".
Re: Find duplicate and compare which one of them is the highest
Barieq, that class is installed by default for most Windows machines as part of the standard vb.net framework files.
It should be installed automatically if your windows is 2003 or later.
Check if you have c:\Windows\Microsoft.Net\Framework, otherwise, you can install from MS.
Look: https://www.ozgrid.com/forum/forum/h...tomation-error
Above is calculating min/max within dups.
So if the stored value is greater then the value, it replaces it with the smaller value and store its row index with an array.
And the other way for the max.
If you describe what is Min/Max, it would be easier to understand.
Re: Find duplicate and compare which one of them is the highest
Dear Jindon,
I negative values
-1 is maxi
-2 is mini
In positive values
1 is mini
2 is max
The amount in Column will always be negative. In simple language larger value will be always Fees and smaller values will be VAT for those duplicate entries.
Re: Find duplicate and compare which one of them is the highest
hi,
Now u came to the point. It is not my result, I have clearly mentioned as Original Text. The result should be (33.44) as Fees and (1.66) as VAT. In original text all the duplicates for both the row will be Fees. Just I need to separate Fees and VAT. The only criteria is to lookup at Column G for the smaller and larger value.
Re: Find duplicate and compare which one of them is the highest
Hi Jindon,
Excellent, this is what I need. But still it is not yet solved. As I told you it is replacing text for other Account Name, example for Master, Visa which is not a duplicate. You can check my file with yellow interior color.
In Simple, just replace only for duplicate entries or Text starts with "Mada"
Bookmarks