Hello all I hope someone may be able to help me. I have a large data set that I utilize index/match for to pull out keywords based off of a defined data set (actually 3 separate data sets that change frequently). I am wondering if there is opportunity's for the following. Speed up my index and match lookups (I cannot remove the formulas because as of right now the data changes as I am building my lists)
I have a couple of questions. is there a VBA code that can process this faster? Also this may be out of scope for this forum but is there a way to do this in SQL?
Currently I am doing an index match on 100,000 rows multiple times (I look in 3 separate fields 3 times for each for a total of at least 9 index and match lookups. The reason is when something is not found it may be found in another field, such as Summary, Description or a Notes field.
See formulas/table data below
Let me know if there is anything else I need to add, thank you in advance for any help.
My main Source Data
All of the "Keyword" Tables below contain 100s if not in to the 1000s of words or phrases.
Application table
Device Table
Issue Table
I have a couple of questions. is there a VBA code that can process this faster? Also this may be out of scope for this forum but is there a way to do this in SQL?
Currently I am doing an index match on 100,000 rows multiple times (I look in 3 separate fields 3 times for each for a total of at least 9 index and match lookups. The reason is when something is not found it may be found in another field, such as Summary, Description or a Notes field.
See formulas/table data below
Let me know if there is anything else I need to add, thank you in advance for any help.
My main Source Data
| Index and Lookup Sample.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | Summary | Description | Notes | Application on summary | Device on summary | Issue summary | Application on Description | Device on description | Issue on Description | Applicaton on Notes | Device on Notes | Issue on Notes | ||
| 2 | I want to put a lot of data here in regard to access restrictions | there is even more data here but this is just an example of excel access | Way more detail here but looking for specific phrases | Active Directory | Device Not Found | Access Request | Microsoft Excel | Device Not Found | Issue Not Found | Application Not Found | Device Not Found | Issue Not Found | ||
| 3 | One note is my problem | One note does gives me access denied | I cannot open one note, re-installed one note now I can access | Microsoft OneNote | Device Not Found | Issue Not Found | Microsoft OneNote | Device Not Found | Issue Not Found | Microsoft OneNote | Device Not Found | Issue Not Found | ||
| 4 | Outlook wont open password issue | Password is not working please reset my password | Reset user password now they can access | Microsoft Outlook | Device Not Found | Opening - Outlook | Application Not Found | Device Not Found | Issue Not Found | Application Not Found | Device Not Found | Issue Not Found | ||
| 5 | my laptop screen is broken | laptop screen broke | submitted request for new laptop | Hardware - Monitor/Screen | Laptop - Generic | Monitor/Screen - Broken | Hardware - Monitor/Screen | Laptop - Generic | Issue Not Found | Application Not Found | Laptop - Generic | Issue Not Found | ||
Problem issues reported | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2:D5 | D2 | =IFERROR(INDEX(Application[Translation],AGGREGATE(15,6,ROW($1:$6)*SIGN(MATCH("*"&Application[Application]&"*",$A2,0)),1)),"Application Not Found") |
| E2:E5 | E2 | =IFERROR(INDEX(Device[Translation],AGGREGATE(15,6,ROW($1:$6)*SIGN(MATCH("*"&Device[Device]&"*",$A2,0)),1)),"Device Not Found") |
| F2:F5 | F2 | =IFERROR(INDEX(Issue[Translation],AGGREGATE(15,6,ROW($1:$6)*SIGN(MATCH("*"&Issue[Issue]&"*",$A2,0)),1)),"Issue Not Found") |
| G2:G5 | G2 | =IFERROR(INDEX(Application[Translation],AGGREGATE(15,6,ROW($1:$6)*SIGN(MATCH("*"&Application[Application]&"*",$B2,0)),1)),"Application Not Found") |
| H2:H5 | H2 | =IFERROR(INDEX(Device[Translation],AGGREGATE(15,6,ROW($1:$6)*SIGN(MATCH("*"&Device[Device]&"*",$B2,0)),1)),"Device Not Found") |
| I2:I5 | I2 | =IFERROR(INDEX(Issue[Translation],AGGREGATE(15,6,ROW($1:$6)*SIGN(MATCH("*"&Issue[Issue]&"*",$B2,0)),1)),"Issue Not Found") |
| J2:J5 | J2 | =IFERROR(INDEX(Application[Translation],AGGREGATE(15,6,ROW($1:$6)*SIGN(MATCH("*"&Application[Application]&"*",$C2,0)),1)),"Application Not Found") |
| K2:K5 | K2 | =IFERROR(INDEX(Device[Translation],AGGREGATE(15,6,ROW($1:$6)*SIGN(MATCH("*"&Device[Device]&"*",$C2,0)),1)),"Device Not Found") |
| L2:L5 | L2 | =IFERROR(INDEX(Issue[Translation],AGGREGATE(15,6,ROW($1:$6)*SIGN(MATCH("*"&Issue[Issue]&"*",$C2,0)),1)),"Issue Not Found") |
All of the "Keyword" Tables below contain 100s if not in to the 1000s of words or phrases.
Application table
| Index and Lookup Sample.xlsx | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Application | Translation | sorting order | ||
| 2 | Outlook | Microsoft Outlook | 1 | ||
| 3 | Excel | Microsoft Excel | 1 | ||
| 4 | Screen | Hardware - Monitor/Screen | 1 | ||
| 5 | Password Reset | Active Directory | 1 | ||
| 6 | Access Restriction | Active Directory | 1 | ||
| 7 | One Note | Microsoft OneNote | 1 | ||
Application | |||||
Device Table
| Index and Lookup Sample.xlsx | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Device | Translation | sorting order | ||
| 2 | Laptop | Laptop - Generic | 1 | ||
| 3 | Desktop | Desktop - Generic | 1 | ||
| 4 | Firefly | HP Firefly | 1 | ||
Device | |||||
Issue Table
| Index and Lookup Sample.xlsx | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Issue | Translation | sorting order | ||
| 2 | Password Reset | Active Directory | 1 | ||
| 3 | Outlook wont open | Opening - Outlook | 1 | ||
| 4 | Screen is broken | Monitor/Screen - Broken | 1 | ||
| 5 | Access Restriction | Access Request | 1 | ||
Issue | |||||
