Results 1 to 5 of 5

Vlookup, removing duplicates

This is a discussion on Vlookup, removing duplicates within the Excel Questions forums, part of the Question Forums category; A.How to remove duplicate Invoices (I) rows? Like in Column B the type shows whether it is Invoice(I) or Order ...

  1. #1
    New Member
    Join Date
    Jul 2013
    Posts
    8

    Default Vlookup, removing duplicates

    A.How to remove duplicate Invoices (I) rows? Like in Column B the type shows whether it is Invoice(I) or Order (O).

    For mulitple invoices with the same number I have to keep only 1 and delete the rest.

    like for number 1061058, there are 8 invoices, I have to keep only 1.

    is there any formula instead of using remove duplicate function?

    NUMBER TYPE STORE CLONED_TO CLONED_FROM
    1061058 I 001
    1061058 I 001
    1061058 I 001
    1061058 I 001
    1061058 I 001
    1061058 I 001
    1061058 I 001
    1061058 I 001
    1061453 I 001
    1061453 I 001
    1062192 I 001
    1062192 I 001
    1062192 I 001
    1062192 I 001
    1062192 I 001
    1062192 I 001
    1062192 I 001
    1062192 I 001
    1062233 I 001
    1062252 I 001 3000629
    1062252 I 001 3000629
    1062252 I 001 3000629
    1062252 I 001 3000629
    1062252 I 001 3000629
    1062252 I 001 3000629
    1062252 I 001 3000629
    1062252 I 001 3000629
    1062252 I 001 3000629
    1062252 I 001 3000629
    1062252 I 001 3000629
    1062252 I 001 3000629
    1062252 I 001 3000629
    1062252 I 001 3000629


    B. I want to check which invoices numbers are matching with the number in Cloned_from column?

    NUMBER TYPE STORE CLONED_TO CLONED_FROM
    1061058 I 001
    1063077 I 001 1063080
    1063078 I 001 1063077
    1063079 I 001 1063077
    1063080 I 001 1063077

    Any formula?

    Plz reply for A & B.

  2. #2
    Board Regular
    Join Date
    Jun 2013
    Location
    UK
    Posts
    741

    Default Re: Vlookup, removing duplicates

    Hi

    Here's a VBA solution to part A:

    Code:
    Sub Remove_Duplicate_Invoices()
        n = Range("B" & Rows.Count).End(xlUp).Row
        For i = n To 2 Step -1
            If Cells(i, 2).Value = "I" Then
                Range("A2:A" & i).Name = "r_one"
                Range("B2:B" & i).Name = "r_two"
                Range("A" & i).Name = cell
                If i > [MIN(IF(r_two="I",IF(r_one=cell,ROW(r_one))))] Then
                    Cells(i, 1).EntireRow.Delete
                End If
            End If
        Next i
    End Sub
    Test in a practice workbook first.

    As for part B, I'm not quite sure what you are looking for. What do you want the end result to be?

    Hope this helps,

    Chris.

  3. #3
    Board Regular
    Join Date
    Jun 2013
    Location
    UK
    Posts
    741

    Default Re: Vlookup, removing duplicates

    EDIT: In my previous post, the line...

    Code:
    Range("A" & i).Name = cell
    ... should have been...

    Code:
    Range("A" & i).Name = "cell"
    Chris.

  4. #4
    New Member
    Join Date
    Jul 2013
    Posts
    8

    Default Re: Vlookup, removing duplicates

    Quote Originally Posted by Chris Mack View Post
    EDIT: In my previous post, the line...

    Code:
    Range("A" & i).Name = cell
    ... should have been...

    Code:
    Range("A" & i).Name = "cell"
    Chris.
    Hi,

    Thank you for your reply..

    I don't know about VBA. is there a way to use this formula in Excel?

    Thanks alot

  5. #5
    Board Regular
    Join Date
    Jun 2013
    Location
    UK
    Posts
    741

    Default Re: Vlookup, removing duplicates

    You could use the formula in an adjacent column.

    So it would be:

    Code:
    =IF(B2<>"I","",IF(MIN(IF($A$2:$A$55555=A2,IF($B$2:$B$55555=B2,ROW($A$2:$A$55555)))){less than symbol}ROW(A2),"DELETE",""))
    Entered with CTRL+SHIFT+ENTER.

    (You need to replace {less than symbol} with the "less than" symbol.)

    So you could use this formula in column F, and it would show which rows need to be deleted. You could then filter on column F for "DELETE", and delete those rows. Make sure to use "Select Only Visible Cells" when you do this, which will prevent any of the now hidden (by the filter) rows from being deleted.

    "Select Only Visible Cells" needs to be added to the Quick Access Toolbar in Options; a quick Google search will provide information on how to do this.

    Hope this helps,

    Chris.
    Last edited by Chris Mack; Oct 1st, 2013 at 01:23 PM.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com