Hi Guys/Gals,
I have the following problem:
On Sheet1("Calculation Sheet") I have a Cell("A3") with a dropdown list of species names and a Cell("B3") which returns the occurence code(QDS) of the selected species in A3. On Sheet3("Records") I have occurence records of the species in the following format with a few examples:
Column A: Species
Naja mossambicca
Naja mossambicca
Naja mossambicca
Column B: QDS
3222AB
3116CA
2328DD
Now, if I choose a name in the dropdown list(Sheet1:A3), I need B3 and downwards(as multiple occurences are common) to return the QDS codes.
At the moment I am using the following in B3:
=IF(ISERROR(INDEX(Records!$A$2:$B$33202,SMALL(IF(Records!$A$2:$B$33202=$A$3,ROW(Records!$A$2:$B$33202)),ROW(1:1)),2)),"",INDEX(Records!$A$2:$B$33202,SMALL(IF(Records!$A$2:$B$33202=$A$3,ROW(Records!$A$2:$B$33202)),ROW(1:1)),2)
Firstly, the formula above seems to always omit one search result. Secondly, it takes quite a while(about 30 sec) to display the results, as there are about 33,000 records to process.
I would like to write a piece of code that could loop through Records:Column A and match all rows with Calculation Sheet:A3, then copy the values in Records:Column B, which corresponds to the matches, to Calculation Sheet:B3 and downwards.
Some help would be greatly appreciated.
Cheers for now...
MarVil
Bookmarks