| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy Multiple non adjacent columns from one Worksheet to another | Excel Discussion (Misc queries) | |||
| Compare adjacent fields in adjacent rows | New Users to Excel | |||
| When data match, copy adjacent value to adjacent column | Excel Worksheet Functions | |||
| How do I copy every fifth column in one worksheet to adjacent col. | Excel Discussion (Misc queries) | |||
| copy and paste non-adjacent rows | Excel Discussion (Misc queries) | |||
Linear Mode
