Should I use Array Match/Index for my Application?

[ozziewendy]

Hello good folks,

I am thinking about creating a spreadsheet to keep track of items (lets call them recipes) by keywords (lets call them key ingredients). Before I actually start to type in huge wodges of data I need to ensure I understand how to format it. So what I thought was:

Data Sheet
Recipe Name Recipe Book Page Keywords
Cooked Chook Chook Book 111 tarragon, mustard
Chook Salad Chook Book 222 radish, cress, cherry tomatoes

Search Sheet
Cell A1: Drop down list of all possible keywords
Cells B1 to B200: Array function match/index using partial search of keywords

I know I have to do a bit of work to get the array function working but have been having a look at various sites and it doesn't look too difficult (famous last words??).

So my questions are:

1. I haven't done any real analysis, but envisage a couple of thousand recipes (I have several shelves of cook books), each with an average of 3 to 5 keywords, with up to a couple of hundred matches for each keyword. Will this blow the array function out of the water - I don't want to have to wait 10 minutes for a search.

2. Is there a better way to do this? I'm not looking for detailed formulae, just a hint about the general approach to take.

I'm not an Excel expert by any means, but am pretty good at working stuff out once I am set on the right track.

Thanks in anticipation ........

Wendy

 

[Hui]

I would start with maybe 5 or 10 recipes
Set them up and see how you go
I assume each keyword will be in a different column/cell
make sure you are happy with the formulas and reports before you enter too much data

There are some very advanced techniques for dealing with large amounts of data which use Conjunctive Truth Tables (its a system for simplifying searches to one composite field using simple binary math)
If the system become too slow you could implement that, it handles 50-100,000 records of 40-50 fields with no problems

 

[ozziewendy]

Hui,

Thanks for the prompt reply.

I actually thought that I would have to put all the keywords into a single cell, because looking at the index/match examples I have found that seems to be how it works.....

Wendy

 

[Hui]

Thats why you should start with a small sample and setup the reports first

It is easy enough to join or split them either way

You may actually find using both techniques may be required

Have you thought about adding fields like Course (Breakfast, Lunch, Dinner, Desert, Snack, School Lunch) etc and Main Protein, Main Spice etc

 

[ozziewendy]

I don't want to make it too complicated. I am actually doing it so that when I have something in the fridge/cupboard that is in danger of being chucked out (such as the dag end of a bunch of tarragon) I can find what recipes in my collection use it. So I'm not going to enter all the ingredients, just the ones that tend to get left over from something else.

I'll have a play around over the next week or so and come back with any questions, thanks for responding.

 

[ozziewendy]

Well I've got the bones of this and it seems to work ok. The only issue I had was that the data validation on the Book and Keyword fields didn't work when I had the 'Ignore Blank' box ticked - in this case it allowed any old rubbish to be entered. It's no biggie, but a bit odd.

Now I just have to enter all my books!