Deleting Repeated data

G

Guest

Guest
Hello people
I have two columns of repeated data like the following
1 aa
1 aa
2 bb
2 bb
2 bb
3 cc
3 cc
and I am trying to delete the repeated ones and leave only just one data like
1 aa
2 bb
3 cc
So is there any quick way to do that?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Once and done or do you need a macro to do it every day/week...?
 
Upvote 0
I have to do three spreadsheets but only do them once
Can you help?
Thank you
 
Upvote 0
Assuming you really only have 2 columns:

In c1, type: Dupes
In c2, type: =a2&b2
In c3, type: =if(a3&b3=c2,"dupe","")

Copy c3 down all the rest of the rows.

Select only the populated cells in column 3. Copy them. Don't move cursor. Hit Edit-Paste special, Values, Ok.
Click on C2 and hit the ZA (descending) sort button. Delete all rows that say *dupe*. Re-sort how you like.
 
Upvote 0
After entering the formula in C3, hit Enter. Then click on C3. You'll see a little black blob in the bottom, right-hand corner of the black border that tells you the cell is selected.

Double-click that black blob. If it doesn't do anything, click on that bottom, right-hand corner and drag down until you're at the end of the cells in column B and let go.

That little black blob (or square) is called the fill handle.

You could also select C3 and then all the cells below it. Then hit Edit-Fill, Down.
 
Upvote 0
I see how it works
but for some reason, my if formula doesn't work after the second row
it gives me "" instead of dupe which is what it suppose to be
 
Upvote 0
It's ONLY supposed to be dupe if it's a duplicate. If you didn't already do so (Doh! I forgot!), sort your data first by column A then B. Click in like A2, and hit Data-Sort. Choose Column A (or its heading) and *then by* Column B (or its heading). Forgot to tell you to sort first! Doh!

Look, I'm going to bed. If you can't work it out, email it to Dreamboat@TheWordExpert.com and I can send it back to ya first thing in the morning.
 
Upvote 0
yeah I got it
I need to put the if statement in column D to get the proper dupe/" "
and =A2&B2 in column C
Thanks a lot DreamBoat
Goodnight
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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