Copy non adjacent rows

  • Thread starter Thread starter atmbonda
  • Start date Start date
A

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
 
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.
 
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
 
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.
 
Back
Top