Best approach for multiple look ups?

[CazzCampbell]

So, I want create a form that will utilise multiple datasets and am looking for some advice on the best way to do this.

In as basic an explanation as possible…

Step 1: I enter a postcode
Step 2: It automatically looks up the LSOA from a very large dataset (too large for one sheet, spreads across 4)
Step 3: This finds a correlating score through looking up the LSOA on a second data set - this gives a numerical response
Step 4: The postcode is looked for on a third dataset, to see if they have accessed a particular scheme - this gives a response of yes or no
Step 5: The postcode is looked for in a fourth dataset to see if they have accessed a further particular scheme - this gives a response of yes or no

Is this too much data processing for Excel to handle in an efficient, speedy way? Would it be better suited through a different tool? Any and all advice welcomed!

Thanks.

Just to add, I have spreadsheets with XLOOKUP formulas that do all of this separately to some extent so I know it’s possible with excel, I just don’t know if it’s too much data that it will become too bulky. The LSOA lookup dataset is 4.5m rows of data alone.

 

[Micron]

Based on my interpretation and you asking if there is a better tool I'd say you need a real relational database. Not sure what your terminology represents, but if you have e.g. level 1 of something (LSOA?) and possibly 2 or more other levels that are related but not exactly the same, the primary key of LSOA records will be in the other table(s) as a foreign key. If your queries don't return data from those other tables, then there was no 'access of another scheme' by any specified user. I doubt you'd have to hold your records over 4 workbooks if that's what you are saying. Even an Access database table could contain millions of records if they're not too complicated. Add to that, you can have concurrent users, which unless things have changed, you can't do with Excel.

 

[CazzCampbell]

Based on my interpretation and you asking if there is a better tool I'd say you need a real relational database. Not sure what your terminology represents, but if you have e.g. level 1 of something (LSOA?) and possibly 2 or more other levels that are related but not exactly the same, the primary key of LSOA records will be in the other table(s) as a foreign key. If your queries don't return data from those other tables, then there was no 'access of another scheme' by any specified user. I doubt you'd have to hold your records over 4 workbooks if that's what you are saying. Even an Access database table could contain millions of records if they're not too complicated. Add to that, you can have concurrent users, which unless things have changed, you can't do with Excel.

Thanks for taking the time to respond. I'll try and clarify a little more!

I currently have an Excel spreadsheet that utilises multiple sets of data, but it's bulky and slow to open so adding in another function like I need to, I just don't think will work.

These are the sheets currently in use:

View attachment 104212

Aut 23 Analysis is my sheet that I do my analysis on, the rest of the sheets are the data sets pulled from various places.

So my blank sheet looks like this:



When I enter the postcode, it:

1. Pulls the locality, LSOA and TES from 'national_tes'
2. Then uses an IF formula to calculate the TES priority
3. Then uses XLOOKUP to see if that postcode is in 'schoolsaward_lookup'
4. Then uses XLOOKUP to see if that postcode is in 'treepack_lookup' and if it is, adds the total trees for that record

View attachment 104211

Currently, the schoolsaward_lookup sheet is empty and I think if I add that in it will become unmanageable.

This is the postcode lookup data: National Statistics Postcode Lookup (May 2022)

The rest is internal data.

So basically, I want/need to know if there's a better way to do this as it's a huge excel file, takes a long time to open and I think could become unmanageable.

I've little experience of Access, but that might be a better option potentially?

 

[Micron]

Your links don't work for me.

I've little experience of Access, but that might be a better option potentially?

I would say so. It's quite powerful and it's primary purpose is to store, relate and retrieve data. It can do some calculating, although not nearly as much as Excel. Coupled with vba, there is probably no limit to what you can use it for. Seems like in your situation, the main focus is to lookup information, which is what Access excels at as long as you design and relate tables properly. You would have to abandon your spreadsheet design thinking. Sheets are wide and things are related across columns (wide). Relational tables are tall and each table is about one entity only and the fields (columns) are for the attributes of that entity. The hardest part is sometimes defining which is which and it depends on the purpose of the db. So there is a fairly steep learning curve if you go that way, but that's what forums are for. Here's a set of links that I have collected. Concentrate on normalization and see what you think. The rest are for the most common pitfalls that people make when they start out. Maybe bookmark your thread and come back to those links should you decide to proceed.

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

How do I Create an Application in Microsoft Access?

How do I Create an Application in Microsoft Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

Naming conventions
- General: Commonly used naming conventions
- MS Access Naming Conventions

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - Multivalued Fields

Last/First/DLast/DFirst Explained
- ORDER BY - First, Last and always!