Copy non adjacent rows
 

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

Copy non adjacent rows
 

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 wrote:

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

Copy non adjacent rows
 

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

"Dave Peterson" wrote:

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 wrote:

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

Copy non adjacent rows
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"atmbonda" wrote in message
...

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