J
Jay
I've been doing some ad-hoc analysis at work today and as a quick fix (and
because I don't have the Access query skills) I ended up with 2 sheets:
Sheet 1 - Contains 21,000 rows of data, 10 columns with the first column
being a concatenation of 4 cells from each row
Sheets 2 - Contains 10,000 rows with 7 VLOOKUPs in each row, but each
vlookup is like the following:
=VLOOKUP(A1&$B$1&$C$1&D1,data_range,9,false)
Where the data_range is all 21,000 rows x 10 columnsrange in sheet2
Anyway, the file is simply too unwieldy to use. It takes an age to calculate
the cells, apply autofilter options etc.
So, my question is - how can I make it more useable. I've hard-coded (paste
special-values) most of the rows in sheet1 and applied the lookups about
1000 at a time before hard coding the looked up data and doing the next
batch.
However, I want to change the values in B1 and C1 and run it to capture a
different data set.
Is there any way I can do this without it taking an inordinate length of
time. It kept ending up in a 'Not Responding' state on my work PC.
Any help appreciated.
Jay
because I don't have the Access query skills) I ended up with 2 sheets:
Sheet 1 - Contains 21,000 rows of data, 10 columns with the first column
being a concatenation of 4 cells from each row
Sheets 2 - Contains 10,000 rows with 7 VLOOKUPs in each row, but each
vlookup is like the following:
=VLOOKUP(A1&$B$1&$C$1&D1,data_range,9,false)
Where the data_range is all 21,000 rows x 10 columnsrange in sheet2
Anyway, the file is simply too unwieldy to use. It takes an age to calculate
the cells, apply autofilter options etc.
So, my question is - how can I make it more useable. I've hard-coded (paste
special-values) most of the rows in sheet1 and applied the lookups about
1000 at a time before hard coding the looked up data and doing the next
batch.
However, I want to change the values in B1 and C1 and run it to capture a
different data set.
Is there any way I can do this without it taking an inordinate length of
time. It kept ending up in a 'Not Responding' state on my work PC.
Any help appreciated.
Jay