Vlookup, removing duplicates

[great123]

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

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Any formula?

Plz reply for A & B.

 

[Chris Mack]

Hi

Here's a VBA solution to part A:

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.

 

[Chris Mack]

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

Range("A" & i).Name = cell

... should have been...

Range("A" & i).Name = "cell"

Chris.

 

[great123]

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

Range("A" & i).Name = cell

... should have been...

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

 

[Chris Mack]

You could use the formula in an adjacent column.

So it would be:

=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",""))

<row(a2),"delete",""))"
<row(a2),"delete",""))[ code]
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.</row(a2),"delete",""))[></row(a2),"delete",""))"