Copy non adjacent rows

[atmbonda]

I have a large data set (90 000 rows) in Excel 2007 with duplicate rows.
I have filtered it down to 30 000 distinct rows.
When I tried to copy all those 30 000 distinct rows at once and paste it
into a different worksheet, I have an error message that reads "Microsoft
Excel cannot create or use the datarange reference because it's too
complex...."

If I copy 5-10 000 rowns at a time, it works but that will take me very long
because I have many similar files to handle.

Please can someone assist?

Thank you


PS: I want to go from this ....

1st Column 2nd Column 3rd Column
Number 1 A OK
Number 1 A Duplicate
Number 1 A Duplicate
Number 2 B OK
Number 2 B Duplicate
Number 3 C OK
Number 4 D OK
Number 4 D Duplicate
Number 4 D Duplicate


To this...

1st Column 2nd Column
Number 1 A
Number 2 B
Number 3 C
Number 4 D

 

[Dave Peterson]

Can you sort by that 3rd column (convert to values first if you have to). Then
there would only be one group/area to copy|paste.

 

[atmbonda]

Thank you Dave...

I tried and it worked. I had also filtered the data to only have the "OK"
then saved the file as .PRN so that only the active sheet and the data
displayed will be saved. Theerafter I opened the prn file in Excel. It was
working as well but just taking a little bit longer.

Thanks again foryour quicker way of doing it.

Al Mbonda

 

[Shane Devenshire]

Hi,

Instead of using AutoFilter why not use Advanced Filter with the copy to
another location option? You will need a criteria range and an output
range. Both must use titles that match those in the Data area. Take a look
at the help system. To keep it simple leave the copy to range on the same
sheet as the data.