Horizontal filter for duplicate values, by the row??

K

krazykaj

Board Regular
Joined
Jul 20, 2005
Messages
143
Hi all,

I have a list of clients, and next to each client there are 4 entries that store variousemail addresses that each of the clients may have.
Some of the email addresses however are duplicates. Like their personal email might be the same as their work email, or their partners email they have listed is the same as their personal main email....
Is there a quick way to filter out the duplicate emails by the row/client???

Example: (Spans across 5 columns)

John - john@email.com - mary@email.com - johnwork@email.com - john@email.com
Bob - bob@email.com - jane@email.com - bob@email.com - bob@email.com
Sarah - sarahluke@email.com - luke@email.com - luke@email.com - sarahluke@email.com


I would like to quickly (like button click if possible) ;) be able to filter this list (keeping in mind there are 300 clients that i actually have to filter)

The result of the above should be:


John - john@email.com - mary@email.com - johnwork@email.com
Bob - bob@email.com - jane@email.com
Sarah - sarahluke@email.com - luke@email.com

I hope that makes sense ... and i hope it is possible to filter by the row.
I don't mind VBA if it is only possible via that...

:)

Thank you for your time and help,
Kind regards,
KJ
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Hello KJ

Copy this code to a new module in your workbook. You can also drop a commandbar from the Forms toolbar and assign the macro to it. You need to highlight the email adddress range first before you run the macro.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> remove_dupes()<br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> rCell <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rCell <SPAN style="color:#00007F">In</SPAN> Selection<br>    <SPAN style="color:#00007F">If</SPAN> WorksheetFunction.CountIf(Rows(rCell.Row), rCell) > 1 <SPAN style="color:#00007F">Then</SPAN><br>        rCell.Delete xlToLeft<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> rCell<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
That is brilliant! :)

I kinda get how it works (and kinda don't) ;) ... but it is great! I thought it would be this massive procedure ;)

Thank you again for you help,
Cheers
KJ
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Count number of rows where a dynamically growing set of columns has the specified value
Replies
0
Views
328
Bries
B
A
  • Question
Trying to combine data from two Excel sheets
Replies
0
Views
265
alex_345
A
S
  • Solved
VBA Script Help: Need to filter spreadsheet by Unique Vendor and Email to Corresponding Vendor then Loop
Replies
9
Views
499
strangejosh
S
A
  • Question
Email multiple addresses with multiple rows with VBA Macro
Replies
0
Views
399
atditiljazi
A
D
  • Question
Email looping a range of data
Replies
1
Views
526
Darkcloud617
D

Forum statistics

Threads
1,214,657
Messages
6,120,769
Members
448,991
Latest member
Hanakoro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top