Excel Help

[Brian Griffin]

Apologies for the daily Excel help thread.

I have a massive spreadsheet in front of me – it’s basically a record of all of our contacts, with varying levels of detail – Name, Job Role, Email Address etc.

I want to order this sheet by email address which is easy enough – but then I want to highlight all the duplicate email addresses, which again is fine…

Then I want to delete every duplicate – leaving only one instance of that address.

I can do this – but then Excel moves every email address up the column meaning the address is out of sync with the rest of the contact details…does anybody have a solution to this?

Cheers


BG

 

[Son of Southall]

Apologies for the daily Excel help thread.

I have a massive spreadsheet in front of me – it’s basically a record of all of our contacts, with varying levels of detail – Name, Job Role, Email Address etc.

I want to order this sheet by email address which is easy enough – but then I want to highlight all the duplicate email addresses, which again is fine…

Then I want to delete every duplicate – leaving only one instance of that address.

I can do this – but then Excel moves every email address up the column meaning the address is out of sync with the rest of the contact details…does anybody have a solution to this?

Cheers


BG

Filter all of the email addresses you do not need then highlight the said email addresses... [Ctrl + G], special, Visible cells only, right click and delete entire row

 

[Some Random Guy]

Data, remove duplicates, only have the column you want duplicates romoved in ticked.

Sounds like you have your e-mail column highlighted and aren't expanding the selection.

 

[Cowvahlo]

Data, remove duplicates, only have the column you want duplicates romoved in ticked.

Sounds like you have your e-mail column highlighted and aren't expanding the selection.

Ops an idiot

 

[Deleted member 1358]

Pivot table and count on addresses and look at those with a count greater than 1

 

[Brian Griffin]

Filter all of the email addresses you do not need then highlight the said email addresses... [Ctrl + G], special, Visible cells only, right click and delete entire row

I'll try this in a bit - thanks!

Data, remove duplicates, only have the column you want duplicates romoved in ticked.

Sounds like you have your e-mail column highlighted and aren't expanding the selection.

I think expanding the selection is what is causing me trouble

Ops an idiot

Yeah I'll take that

Pivot table and count on addresses and look at those with a count greater than 1

You what now?

 

[hypersonic]

A lovely database would sort that right out.

 

[Some Random Guy]

I think expanding the selection is what is causing me trouble

I'm sure you'll just have all of the columns ticked when you get the pop up.

Don't have a laptop in front of me to check like

 

[MackemBob]

A lovely database would sort that right out.

This. Get a professional to design a better system for you and you’ll avoid excel Hell.

 

[sedling]

A lovely database would sort that right out.

Or one of these imo. Much cheaper.

Logon or register to see this image

 

[hypersonic]

If your stuck with Excel, this'll work.

Add another column, enter the following (replacing the a1 bits with the first cell in the column you want to check):

=if(countif($a$1:a1)=1,a1,"")

Fill down then copy results and paste back special values over the original.

 

[Brian Griffin]

Data, remove duplicates, only have the column you want duplicates romoved in ticked.

Sounds like you have your e-mail column highlighted and aren't expanding the selection.

This was the solution!

Thank you!