How? Deleting Non Recurring Data

[dzdncfsd]

I have a very large spreadsheet and I'm trying to cleanup.

Let me give you a brief description of the spreadsheet.

I have 3 columns of importance

Column 1 = Item #
Column 2 = Date
Column 3 = Location

What I'm looking to do is erase any data where there are less then 2 occurances of the same item # for each location.

1st option = I subtotalled based on date/item/location however I'm not sure how to sort based on the subtotal. If there is a way to do this that would resolve my issue.

2nd option = Would it be possible to create a macro to say if there are less then 2 occurances of the same item # for a given location delete the row.


I would do this manually however we're talking about 50,000 lines of data.


Thank You
DZDNCFSD

 

[pgc01]

Hi dzdncfsd
Welcome to the board

You don't need any macro. Just filter the combinations and delete the lines.

To delete all combinations of occurances of only one item # for each location:

Assuming row 1 has the headers, and data is in A3:C50000:

D1="Test"

In D2:

=SUMPRODUCT(--($A$2:$A$50000=A2),--($C$2:$C$50000=C2))=1

Copy down to D50000

Select column D.
Data>Filter>Autofilter

Select in the autofilter dropdown list: True
Delete the visible lines

Disable the autofilter.
Delete column D.

Hope this helps
PGC

 

[dzdncfsd]

Thanks for the help... This did exactly what I needed it to.


Now the bad part is running this against 46,000 lines basically shut my computer down for 4 hours.

 

[pgc01]

Hi dzdncfsd

Now the bad part is running this against 46,000 lines basically shut my computer down for 4 hours.

I'm afraid this may be my fault.

I forgot a very important step. After copying the formula down to D50000 one should select column D, Copy, Paste Special Values.

This way the sheet would only have constants and I believe this would have speeded up the process a lot.

If this is a process you have to execute more times try it next time.

Cheers
PGC