Vlookup, removing duplicates

Thanks:  0
Likes:  0

1. ## 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. ## 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. ## 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. ## Re: Vlookup, removing duplicates

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

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

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

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

Thanks alot

5. ## 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.

## User Tag List

#### Posting Permissions

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