Excel Move Original and Duplicate Rows to New Sheet 1

[Moebius01]

First, let me preface this by saying that I haven't done any VBA coding in more than a decade, so I'm pretty much flying blind (or working from some very faint memories). I've been searching around for ideas on this, but most of what I've found is either close (moves the duplicates but not the original), is looped in a fashion that can't handle 70k rows of data without freezing, or is producing inaccurate results.

Scenario: I have a file with approximately 70,000 rows of data. Column A contains a unique ID. I need to move all rows where an ID value occurs more than once to a separate sheet. So for example, if 48 rows of data contain the value "18659TI" in column A, I need to move all 48 rows to a new sheet, not just the 47 duplicates. If everything works, once the process finishes, the original sheet should only contain rows where the value in column A appeared 1 time, and the new sheet should contain all the rows where the value in column A appeared 2 or more times.

My attempts at piecing together something to achieve this have all failed. Any help would be ever so greatly appreciated.

 

[Andrzejek]

Column A contains a unique ID" and later "an ID value occurs more than once" - so the ID is not unique

But that put aside.
How about the solution without VBA?

If you order your data by Column A (ID) and introduce (insert) additional 'helper' column (let's say column B). Place this Formula in cell B2: [tt]
=IF(OR(A2=A1,A2 = A3),"THE SAME","DIFFERENT")[/tt]
Copy and paste it down the column B. You will detect/mark all rows with the same ID as "THE SAME" and unique IDs as "DIFFERENT".

Then it is just simple re-order (or filter) your data by column B and move (cut-paste) to another sheet.

Would that work for you?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Moebius01]

I'll give that approach try. I tried a similar approach using a conditional formatting formula to find duplicates (as well as the built in one) then sorting by the format, but it froze up trying to sort that way for some reason. Since this is (hopefully) a 1 time deal, I'm fine doing it sans VBA, I was just striking out there as well.

And yes, I should have said that column A "SHOULD" contain unique values.

 

[Moebius01]

Ok, worked like a charm. Not sure if Excel just takes longer trying to sort by highlighting or if something else was at play (had my formula bad or something).

Now, on the off chance that this needed to be repeated multiple times, do you think this would be possible in VBA? Some of the samples I found from searching took a similar approach (marking a column based on countif against the source column), but seemed to take forever (hour plus in some cases) when trying to run against 70k rows of data.

 

[SkipVought]

70k rows is NOT a big table. Sorting on VALUES should be quick. You must have some other stuff going on, maybe text in a numeric field???

I'd use COUNTIF().

Filter on 1 or not 1. Simple.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]