Excel efficient cell comparison for large'ish data
Discussion
I'll try to explain clearly. Basically I have a large table of values generated by MS Forms which will grow to about 50,000 rows. One header is a Date. I want to remove all rows <> a target date.
I can do this quite easily by working down the column comparing the cell value and deleting rows where its <> target.
But, obviously this is slow and inefficient. Plus there also will be up to 20 tables.
So instead I used search and replace on the date column and replaced cells containing the target date with null ""
Then used Goto Special to select all the blanks and entirerow.select to select all the rows that did have the target date in them.
These are then copied and pasted skipping blanks to create a new table which then has a lot of other bits done to it.
Question is, is there a better way?
I also need to get the target date back into the rows cells, again could loop through them and that wold be okay, only ever likely to be a hundred or so rows but it would be far better (I think) to replace the date with something other than null originally then replace that back with the date.
But, can't see how I could use Goto Special to select a specific value?
Any clever suggestions welcome!
I can do this quite easily by working down the column comparing the cell value and deleting rows where its <> target.
But, obviously this is slow and inefficient. Plus there also will be up to 20 tables.
So instead I used search and replace on the date column and replaced cells containing the target date with null ""
Then used Goto Special to select all the blanks and entirerow.select to select all the rows that did have the target date in them.
These are then copied and pasted skipping blanks to create a new table which then has a lot of other bits done to it.
Question is, is there a better way?
I also need to get the target date back into the rows cells, again could loop through them and that wold be okay, only ever likely to be a hundred or so rows but it would be far better (I think) to replace the date with something other than null originally then replace that back with the date.
But, can't see how I could use Goto Special to select a specific value?
Any clever suggestions welcome!
Filters?
https://support.microsoft.com/en-us/office/filter-...
You can copy and paste the filtered data.
https://support.microsoft.com/en-us/office/filter-...
You can copy and paste the filtered data.
frisbee said:
Filters?
https://support.microsoft.com/en-us/office/filter-...
You can copy and paste the filtered data.
just worked that out https://support.microsoft.com/en-us/office/filter-...
You can copy and paste the filtered data.
Set TargetTable = Sheets("Sheet1").ListObjects("Table1")
TargetTable.Range.AutoFilter Field:=6, Criteria1:=DateValue(comdate)
TargetTable.Range.SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet2").Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = False
Oh well, only wasted half a day doing it the long way round because I hadn't worked out how to only copy the visible rows! arrgghh!
Edited by 21TonyK on Saturday 25th July 15:15
Meeten-5dulx said:
if or nested if satatement?
Looks like you have found a solution. If not, lmk and I can try and help.
Cheers, sorted (at least well enough for my needs)Looks like you have found a solution. If not, lmk and I can try and help.
Used a combination of filtering and sorting and just dummied up 200,000 records. Works in a few seconds which is more than good enough.
Key was finding our how to copy only the results of filtering.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff