leeksleeks
Board Regular
- Joined
- Oct 31, 2013
- Messages
- 96
Hi,
I have a large excel file of golf results (150,000 rows) and I have a vlookup formula that will look though the results and tell me what position the golfer came in previous years when playing on that course. However the vlookup formula I use takes forever to calculate and seem to be calculating any chance it gets (calculating 4 processors). I have read on other posts that people with many more rows of information have found very quick ways of calculating their results, so I was wondering if anyone can help me out.
I have two main worksheets. 1 called Results and 1 called Summary.
On the worksheet called RESULTS I enter the results from each tournament. Below is how this looks starting from Cell A1. The concatenation I use is for the lookup on the next worksheet called SUMMARY. Below is the information I add to the RESULTS worksheet:
<tbody>
</tbody>
The below table is what the Summary worksheet looks like. I have hidden a few columns but the column cell references are correct. The information I am wanting from this worksheet is The Golfer (A2), The Course (A1) and The Year (M1). Below is an example of what the SUMMARY worksheet looks like:
.....A.......M......N..................AB....................................AC
<tbody>
</tbody>
So in order for me to find out how golfer A did in 2012 at Pebble Beach I am looking up the concatenation from both worksheets using the following code: (The results of 3 would appear in cell M2 above.
=IF(ISNA(VLOOKUP(AB2,Results!$A:$B,2,FALSE)),"-",VLOOKUP(AB2,Results!$A:$B,2,FALSE))
However when I am trying to lookup 500 golfers at once it takes an absolute age to produce their results. Is there a better way I could do this so that it speeds up the lookup?
I look forward to hearing if anyone can speed up this process.
I have a large excel file of golf results (150,000 rows) and I have a vlookup formula that will look though the results and tell me what position the golfer came in previous years when playing on that course. However the vlookup formula I use takes forever to calculate and seem to be calculating any chance it gets (calculating 4 processors). I have read on other posts that people with many more rows of information have found very quick ways of calculating their results, so I was wondering if anyone can help me out.
I have two main worksheets. 1 called Results and 1 called Summary.
On the worksheet called RESULTS I enter the results from each tournament. Below is how this looks starting from Cell A1. The concatenation I use is for the lookup on the next worksheet called SUMMARY. Below is the information I add to the RESULTS worksheet:
=CONCATENATE(C2&J2&K2) | POS | PLAYER | TO PAR | R1 | R2 | R3 | R4 | TOT | COURSE | YEAR | TOURNAMENT | TOUR |
GolferAPebbleBeach2013 | 1 | Golfer A | -12 | 69 | 69 | 69 | 69 | 276 | Pebble Beach | 2013 | US Open | PGA |
<tbody>
</tbody>
The below table is what the Summary worksheet looks like. I have hidden a few columns but the column cell references are correct. The information I am wanting from this worksheet is The Golfer (A2), The Course (A1) and The Year (M1). Below is an example of what the SUMMARY worksheet looks like:
.....A.......M......N..................AB....................................AC
Pebble Beach | 2012 | 2013 | |||
Golfer A | 3 | 70 | =CONCATENATE(A2,A1,M1 | =CONCATENATE(A2,A1,N1) | |
Golfer B | 5 | - | =CONCATENATE(A3,A1,M1 | =CONCATENATE(A3,A1,N1) | |
Golfer C | 6 | 1 | =CONCATENATE(A4,A1,M1 | =CONCATENATE(A3,A1,N1) | |
Golfer D | 2 | 2 | =CONCATENATE(A5,A1,M1 | =CONCATENATE(A3,A1,N1) |
<tbody>
</tbody>
So in order for me to find out how golfer A did in 2012 at Pebble Beach I am looking up the concatenation from both worksheets using the following code: (The results of 3 would appear in cell M2 above.
=IF(ISNA(VLOOKUP(AB2,Results!$A:$B,2,FALSE)),"-",VLOOKUP(AB2,Results!$A:$B,2,FALSE))
However when I am trying to lookup 500 golfers at once it takes an absolute age to produce their results. Is there a better way I could do this so that it speeds up the lookup?
I look forward to hearing if anyone can speed up this process.
Last edited: