dynamic lists for vocab spreadsheet

[graham1607]

Dear friends

I'm a Chinese language student who's new to this forum, so very nice to meet you! I am trying to make a multipurpose Excel program for me and my fellow students to test and consolidate our Chinese (no easy task!). My Excel knowledge is limited, and in particular, I'm not good with table manipulations.

Something that various parts of my nascent program are trying to do is interrogate a big data set of several thousand entries (my vocab entries, ordered in columns by 'topic', 'chinese', 'pronunciation', english' etc.) As the nature of Chinese is that many entries contain some of the same Chinese characters, I want to find a way to enter a character in a cell on a separate sheet, and dynamically list all the entries from my vocab list which contain that character. At the moment, the only way I can do this is by inserting another column in my list containing something like (L3)=L2+COUNTIF(K3,"*"&$M$1&"*"), such that for every new instance of an entry that meets the criteria, the value in the column at that point increases by one. I then use INDEX(MATCH(1,2,3,4,5,6 etc.)) on that column with the relevant data column I want to extract. This seems very inelegant, and is very processor intensive given the size of my data set. I would have though you could use some function involving SMALL (or something else I don't know) to do this much more efficiently. Would anyone kindly be able to help?

Many thanks!

Graham

[Bryan Hessey]

Dear friends

I'm a Chinese language student who's new to this forum, so very nice to meet you! I am trying to make a multipurpose Excel program for me and my fellow students to test and consolidate our Chinese (no easy task!). My Excel knowledge is limited, and in particular, I'm not good with table manipulations.

Something that various parts of my nascent program are trying to do is interrogate a big data set of several thousand entries (my vocab entries, ordered in columns by 'topic', 'chinese', 'pronunciation', english' etc.) As the nature of Chinese is that many entries contain some of the same Chinese characters, I want to find a way to enter a character in a cell on a separate sheet, and dynamically list all the entries from my vocab list which contain that character. At the moment, the only way I can do this is by inserting another column in my list containing something like (L3)=L2+COUNTIF(K3,"*"&$M$1&"*"), such that for every new instance of an entry that meets the criteria, the value in the column at that point increases by one. I then use INDEX(MATCH(1,2,3,4,5,6 etc.)) on that column with the relevant data column I want to extract. This seems very inelegant, and is very processor intensive given the size of my data set. I would have though you could use some function involving SMALL (or something else I don't know) to do this much more efficiently. Would anyone kindly be able to help?

Many thanks!

Graham

Hi,

that's a lot to read early in the morning, but if I have that correctly then the attached VLookup, an adaptation of a file from Carim, to return multiple lookup answers, can be modified to your needs.

Let me know how you go.
---

[graham1607]

Thank you Brian - that looks perfect. I'll give it a whirl and see how it goes.

Many thanks again

Graham

[graham1607]

Unfortunately, while this is very useful, I can't work out how to get it to find non-exact cells (i.e. "*"&criteria&"*"). That's the key issue, as I need to find and list all entries containing a character, not just those equalling the single character itself.

The array formula you kindly provided is:

{=IF(ROWS(B$2:B2)<=COUNTIF(Data!$A$2:$A$21,$A$2),INDEX(Data!$B$2:$B$21,SMALL(IF(Data!$A$2:$A$21=$A$2,ROW(Data!$A$2:$A$21)-ROW(Data!$A$2)+1),ROWS(B$2:B2))),"")}

where $A$2 is the cell containing the criteria. Is there any way of modifying this for e.g. "*"&$A$2&"*", as just substituting that into the formula doesn't work?

Many thanks again

Graham

[graham1607]

Could I perhaps simplify this question in case there is anyone out there who knows how to do this - I need to be able to select the n-th occurance of a value in a list that meets a wildcard criteria.

e.g. if the list is...

CANTERBURY
CAN
CANT
WONT
VULCAN
TRIED
FAILED

... then I want a way to list all those terms *CAN* in something like a Multiple Lookup table. If I could work out a way to e.g. pick the fourth instance (VULCAN), I could probably generalise this to work. But SMALL() won't work with wildcards. Any ideas?

Thanks so much...

[vane0326]

Could I perhaps simplify this question in case there is anyone out there who knows how to do this - I need to be able to select the n-th occurance of a value in a list that meets a wildcard criteria.

e.g. if the list is...

CANTERBURY
CAN
CANT
WONT
VULCAN
TRIED
FAILED

... then I want a way to list all those terms *CAN* in something like a Multiple Lookup table. If I could work out a way to e.g. pick the fourth instance (VULCAN), I could probably generalise this to work. But SMALL() won't work with wildcards. Any ideas?

Thanks so much...

Try...

=INDEX(Data!$B$2:$B$21,SMALL(IF(ISNUMBER(SEARCH($A$2,Data!$A$2:$A$21)),ROW(Data!$A$2:$A$21)-ROW(Data!$A$2)+1),ROWS(B$2:B2)))



Hope it helps!

[graham1607]

It works! Thanks so much!