Deleting Repeated data

Mar 6th, 2002, 07:48 PM

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?

Anne Troy · Mar 6th, 2002, 07:48 PM

Once and done or do you need a macro to do it every day/week...?

Mar 6th, 2002, 07:51 PM

I have to do three spreadsheets but only do them once
Can you help?
Thank you

Anne Troy · Mar 6th, 2002, 08:14 PM

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.

Mar 6th, 2002, 08:22 PM

sorry a stupid question
how do you do
Copy c3 down all the rest of the rows.

Anne Troy · Mar 6th, 2002, 08:26 PM

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.

Mar 6th, 2002, 08:39 PM

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

Anne Troy · Mar 6th, 2002, 08:42 PM

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.

Mar 6th, 2002, 08:57 PM

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

duddm00 · Apr 5th, 2002, 03:32 PM

Mar 6th, 2002, 07:48 PM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	Once and done or do you need a macro to do it every day/week...? __________________ ~Anne Troy

Mar 6th, 2002, 08:14 PM	#4
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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. __________________ ~Anne Troy

Mar 6th, 2002, 08:26 PM	#6
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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. __________________ ~Anne Troy

Mar 6th, 2002, 08:42 PM	#8
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	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. __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 6th, 2002, 07:48 PM	#1
Guest Posts: n/a	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?

Mar 6th, 2002, 07:51 PM	#3
Guest Posts: n/a	I have to do three spreadsheets but only do them once Can you help? Thank you

Mar 6th, 2002, 08:22 PM	#5
Guest Posts: n/a	sorry a stupid question how do you do Copy c3 down all the rest of the rows.

Mar 6th, 2002, 08:39 PM	#7
Guest Posts: n/a	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

Mar 6th, 2002, 08:57 PM	#9
Guest Posts: n/a	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

Apr 5th, 2002, 03:32 PM	#10
duddm00 New Member Join Date: Mar 2002 Posts: 5