+ Reply to Thread
Results 1 to 26 of 26

Find duplicate and compare which one of them is the highest

  1. #1
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    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".

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi ! Try this …


    As a beginner starter according to your attachment & explanation :

    PHP Code: 
    Sub Demo1()
            
    Dim VWRg As Range
            Application
    .ScreenUpdating False
        With 
    [A7].CurrentRegion.Columns
            
    .Sort .Cells(3), xlAscendingHeader:=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(" "," & .Item(1).Address ")=1)")
            .
    Sort .Cells(2), xlAscendingHeader:=xlNo
             W 
    Application.Match(True, .Item(2), 0)
             If 
    1 Then V = .Item(1).Resize(1).Value2
            
    .Clear
        End With
        
    If 1 Then
                
    If Not IsArray(VThen V = Array(V)
            
    With [A7].CurrentRegion.Columns(3)
                For 
    Each W In V
                           Set Rg 
    = .Find(W, , xlValuesxlWhole)
                    
    With Range(Rg, .FindPrevious(Rg)).Offset(, 4)
                        
    With .Cells(Application.Match(Application.Max(.Cells), .Cells0))
                             .
    Copy .Offset(, 10)
                        
    End With
                        With 
    .Cells(Application.Match(Application.Min(.Cells), .Cells0))
                             .
    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 …

  3. #3
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Re: Hi ! Try this …

    hi Marc,

    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.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    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 …

  5. #5
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: Find duplicate and compare which one of them is the highest

    Hi, Barieq
    I need to be clear, for example -1 & -2
    Which one do you consider as maximum value? -1 or -2?

  6. #6
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Re: Find duplicate and compare which one of them is the highest

    hi Mark,

    Sorry, you are right. it is taking the minimum value. I have confused with positive and negative value in Post # 1.

  7. #7
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    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".

    Please Login or Register  to view this content.

  8. #8
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: Find duplicate and compare which one of them is the highest

    Check my code.
    Does that give you the results you expect?
    HTML Code: 
    Attached Files Attached Files
    Best Regards,
    Maras.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find duplicate and compare which one of them is the highest

    Try
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    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".

    here minimum value is "-2" and max value is (-1)

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Re: Find duplicate and compare which one of them is the highest

    hi jindon,

    Same confusion in negative max and min

    here minimum value is "-2" and max value is (-1). I think we need to change the below part

    Please Login or Register  to view this content.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find duplicate and compare which one of them is the highest

    Then try just swap Vat and Fees and see if the results are correct...

  13. #13
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    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

  14. #14
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Re: Find duplicate and compare which one of them is the highest

    Hi Jindon,

    It it replacing all the text in that column, I need only to those rows which are duplicates and min value. I have attached the workbook with remarks.
    Attached Files Attached Files

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find duplicate and compare which one of them is the highest

    I don't think I understand about what you mean by Minimum and Maximum.
    You said
    here minimum value is "-2" and max value is (-1). I think we need to change the below part
    Please Login or Register  to view this content.
    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.

  16. #16
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    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.

    Hope you understand.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find duplicate and compare which one of them is the highest

    I think the current calculation should be correct then...
    Last edited by jindon; 09-28-2019 at 06:56 AM.

  18. #18
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Re: Find duplicate and compare which one of them is the highest

    But why the code changes the text for all the rows which are not duplicates? Did you check the file what I have attached with remarks?

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find duplicate and compare which one of them is the highest

    Take "REFERENCE : 78861090-63101443"
    It appears in row 43 & 78 and Debit amount are (33.44) & (1.66) respectively.

    My result gave (33.44) a Vat and (1,66) a Fees, but your result are both Fees why?

  20. #20
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Re: Find duplicate and compare which one of them is the highest

    hi jindon

    Please see below pictureAttachment 643240

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find duplicate and compare which one of them is the highest

    Your link is not working.

  22. #22
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    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.

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find duplicate and compare which one of them is the highest

    Still not sure...
    Try change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  24. #24
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    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"

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find duplicate and compare which one of them is the highest

    Try
    Please Login or Register  to view this content.

  26. #26
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Re: Find duplicate and compare which one of them is the highest

    Hi Jindon,

    Sorry for the late reply, I have tested your code and it is working perfectly as desired. Thank you for solving this thread.

    And thanks for all those who have helped me specially Mark & Maras.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find Duplicate in one column then compare to values in another
    By cooper.1651 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-08-2015, 12:10 PM
  2. Replies: 7
    Last Post: 05-28-2014, 03:19 AM
  3. Replies: 2
    Last Post: 03-20-2012, 06:30 PM
  4. Replies: 11
    Last Post: 11-03-2011, 09:18 PM
  5. VBA code to find highest and 2nd highest number based in criteria
    By Michael007 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-25-2011, 08:38 AM
  6. compare/find a duplicate telephone number within a row
    By Grav11 in forum Outlook Formatting & Functions
    Replies: 2
    Last Post: 11-04-2009, 03:22 PM
  7. Replies: 2
    Last Post: 08-16-2005, 10:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1