Removing data from a list.

  • Thread starter Thread starter david987
  • Start date Start date
D

david987

I was just wondering how could I remove duplicates from a long list, I
also want to eliminate the one I searched for initially:
Lets say I have:
Hello
Hello
Hello1
Hello2

I'd like to seach for Hello, and have both of them removed.
Any ideas on how I can do this ?
 
Hi David,

Here is a general macro I've used in the past for removing
duplicate rows based on the data in a particular column.
I'm sure you can adjust for the appropriate ranges in your
data file.

' This macro expects the column B contains data until the
last cell
' in the data range - be sure you sort your data first

Sub RemoveDups()
Range("B9").Select
' start in beginning of range and go through each row
until a blank
' is found
mcellValue1 = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select

Do Until ActiveCell.Value = ""
If ActiveCell.Value = mcellValue1 Then
Selection.EntireRow.Delete
End If
mcellValue1 = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select
Loop

End Sub

HTH
Beth
Excel MVP
 
You can use an AutoFilter to remove the items that are duplicates:

1. Assuming your list is in cells A2:100, add a heading in cell A1,
e.g. Items
2. In cell B1, add a heading, e.g. Dups
3. In cell B2, enter a formula that counts each item in column A:
=IF(COUNTIF($A$2:$A$100,A2)>1,"X","")
4. Copy the formula down to row 100
5. Select a cell in the table, and choose Data>Filter>AutoFilter
6. From the dropdown in cell B1, choose X
7. Select and delete the visible rows
8. To remove the AutoFilter, choose Data>Filter>AutoFilter
 
I don't know how to do what Debra just told me, it seems too
complicated!
Anyone suggests an easier way ?
 
Actually, that way is one of the easier ways.

Print it off and try it against a copy of your workbook--just in case you screw
it up.

If you still can't do it, post back with a little more specific info--no
workbooks, though.
 
4. Copy the formula down to row 100
5. Select a cell in the table, and choose Data>Filter>AutoFilter
6. From the dropdown in cell B1, choose X
7. Select and delete the visible rows

I don't undersand those steps
 
You can use an AutoFilter to remove the items that are duplicates:

1. Assuming your list is in cells A2:100, add a heading in cell A1,
e.g. Items
2. In cell B1, add a heading, e.g. Dups
3. In cell B2, enter a formula that counts each item in column A:
=IF(COUNTIF($A$2:$A$100,A2)>1,"X","")
4. Copy the formula down to row 100
5. Select a cell in the table, and choose Data>Filter>AutoFilter
6. From the dropdown in cell B1, choose X
7. Select and delete the visible rows
8. To remove the AutoFilter, choose Data>Filter>AutoFilter

#4. The formula needs to be in every row.
Select B2:B100 using your mouse.
type that formula in: =IF(COUNTIF($A$2:$A$100,A2)>1,"X","")
(Adjust your selection (B2:Bxxxx) to match the number of rows you have
and adjust that formula to match that number)

1000 rows, select B2:b1000 and your formula will be:
=IF(COUNTIF($A$2:$A$1000,A2)>1,"X","")

#5. Select all of column B (slightly different from Deb's but equivalent)
then on the menubar, hit Data, then Filter, then Autofilter.

#6 You'll see a little dropdown arrow in B1. Click on it. Select X.
You'll see a change to your worksheet--only the duplicates will be
showing.

#7 Use your mouse to select the first visible cell under B1 through the
last visible cell in column B
rightclick on any of those selected cells.
Click Delete.
Click entirerow

#8 Back to the menubar and hit Data, then filter, then autofilter
to remove the filtered view.


This is a pretty useful technique for getting rid of lots of stuff (autofilter,
that is). It'll be good to learn and use a few times.
 
Oops. I left out an important step:
You can use an AutoFilter to remove the items that are duplicates:

1. Assuming your list is in cells A2:100, add a heading in cell A1,
e.g. Items
2. In cell B1, add a heading, e.g. Dups
3. In cell B2, enter a formula that counts each item in column A:
=IF(COUNTIF($A$2:$A$100,A2)>1,"X","")
4. Copy the formula down to row 100
5. Select a cell in the table, and choose Data>Filter>AutoFilter
6. From the dropdown in cell B1, choose X
7. Select and delete the visible rows
8. To remove the AutoFilter, choose Data>Filter>AutoFilter

#4. The formula needs to be in every row.
Select B2:B100 using your mouse.
type that formula in: =IF(COUNTIF($A$2:$A$100,A2)>1,"X","")
(Adjust your selection (B2:Bxxxx) to match the number of rows you have
and adjust that formula to match that number)

1000 rows, select B2:b1000 and your formula will be:
=IF(COUNTIF($A$2:$A$1000,A2)>1,"X","")

****added in this post****
Type that formula in B2 (still with B2:Bxxxx selected)
Hit ctrl-Enter (not just enter)
The formula will fill in all those cells.

#5. Select all of column B (slightly different from Deb's but equivalent)
then on the menubar, hit Data, then Filter, then Autofilter.

#6 You'll see a little dropdown arrow in B1. Click on it. Select X.
You'll see a change to your worksheet--only the duplicates will be
showing.

#7 Use your mouse to select the first visible cell under B1 through the
last visible cell in column B
rightclick on any of those selected cells.
Click Delete.
Click entirerow

#8 Back to the menubar and hit Data, then filter, then autofilter
to remove the filtered view.
 
I can't see any "X" from the dropdown in Cell B1....
What did I do wrong ?
Do i need to replace X with something ?
 
You filled in the range with that formula:
=IF(COUNTIF($A$2:$A$100,A2)>1,"X","")

And your data was in column A?

If your data that you want to check is in a different column, you'll have to
adjust the addresses in that formula.

If that isn't it, post the formula you used--not the workbook.
 
Ok here's exactly what I want to do:
I have one big list of 2997 emails, all in one column ( A1:A2997)
I then have another email list of people I want removed from list A.
306 E-mails ( B1:B306 ).
Those in B exist in A and I want them removed from A.
 
First, move that stuff in B1:B306 to another worksheet so that they're out of
the way and safe.

Then put this formula in B1:
=IF(ISNUMBER(MATCH(A1,sheet2!A:A,0)),"deleteme","keepme")
Copy it down thru B2997.

Now insert a new row 1. Put some nice headers in there.

Select column B and do
Data|filter|Autofilter

Use the dropdown to show all the "deleteme"

select those visible cells and right click on the selection and click Delete.
Then answer yes to the "entire row" prompt.

(that's why you moved the stuff in column B.)

If you did it wrong, close without saving and try again.
 
Back
Top