Vlookup, duplicate and unique value help

[cgendron]

Hey all,
I have two columns, A and B. Column A has certain words pertaining to codes, column B has the codes.
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Change[/TD]
[TD="class: xl65, width: 64"]Code[/TD]
[/TR]
[TR]
[TD="class: xl67"]Deleted[/TD]
[TD="class: xl66, width: 64"]10021[/TD]
[/TR]
[TR]
[TD="class: xl67"]Added[/TD]
[TD="class: xl66, width: 64"]10022[/TD]
[/TR]
[TR]
[TD="class: xl67"] [/TD]
[TD="class: xl66, width: 64"]10060[/TD]
[/TR]
[TR]
[TD="class: xl67"]Deleted[/TD]
[TD="class: xl66, width: 64"]10061[/TD]
[/TR]
[TR]
[TD="class: xl67"] [/TD]
[TD="class: xl66, width: 64"]10120[/TD]
[/TR]
[TR]
[TD="class: xl67"] [/TD]
[TD="class: xl66, width: 64"]10140[/TD]
[/TR]
[TR]
[TD="class: xl67"]Deleted[/TD]
[TD="class: xl66, width: 64"]10180[/TD]
[/TR]
[TR]
[TD="class: xl67"] [/TD]
[TD="class: xl66, width: 64"]11055[/TD]
[/TR]
[TR]
[TD="class: xl67"] [/TD]
[TD="class: xl66, width: 64"]11100[/TD]
[/TR]
</tbody>[/TABLE]

I'm using a vlookup to find any code with the word "deleted" next to it. The obvious problem i have is it only bring in the first instance. How do i ignore the duplicates and only bring in the unique code instance? i have a summary column for all the deleted and added ones. I want all the deleted codes under the deleted column and added codes under the added column.

[TABLE="width: 157"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Deleted[/TD]
[TD]Added[/TD]
[/TR]
[TR]
[TD]J1725[/TD]
[TD]90651[/TD]
[/TR]
[TR]
[TD]J1725[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]J1725[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]J1725[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]J1725[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]J1725[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]J1725[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance!

 

[excelnoob123456]

Select both columns (the one containing the code and the one containing the status-deleted or added), go to the data tab then click remove duplicates and check that both columns selected then press OK.

Copy and paste the unique codes on the same row to the right of the ones you have just removed (so codes are in column a1 down, statuses in b1 down, copy the now unique codes from column a1 down to d1 down, then you can have e1 down as added column and f1 down as deleted) Then you could just use an if function in the deleted and added column which looks up to the current status column and codes column, as below:

added column -e:
=if(b1="added","added","")

deleted column -f:
=if(b1="deleted",deleted","")

Hope this helps

 

[cgendron]

Select both columns (the one containing the code and the one containing the status-deleted or added), go to the data tab then click remove duplicates and check that both columns selected then press OK.

Copy and paste the unique codes on the same row to the right of the ones you have just removed (so codes are in column a1 down, statuses in b1 down, copy the now unique codes from column a1 down to d1 down, then you can have e1 down as added column and f1 down as deleted) Then you could just use an if function in the deleted and added column which looks up to the current status column and codes column, as below:

added column -e:
=if(b1="added","added","")

deleted column -f:
=if(b1="deleted",deleted","")

Hope this helps

Thanks for the reply. I'm looking to do this automatically with a formula. I've done it in the past but can't remember the formula to ignore dups and only grab the unique values