Formula for matching multi-value sets with database

[mike6732]

Hi all,

Long-time visitor, first-time poster.

I have a database of cases. Each case has been tested twice, and each test contains 5 scores (9-point scale).

I want to be able to manually enter scores for new cases (E:G 2:6 in attachment), and use a formula which feeds back which case/s - if any - have matching values (G8 in attachment).

I'm not sure if this is achievable, but any tips or suggestions on workarounds would be appreciated!

Thanks in advance

[XLent]

If you want to avoid code then given your version (Mac 2019) it would be simplest to create keys c/o TEXTJOIN etc... e.g.:

Please Login or Register  to view this content.

you can then, for ex., create an additional case with identical values to Case 2, and you should find G8 updates to pick up both Case 2 and the new Case.

[mike6732]

Wow. Thank you so much for this, this is exactly the kind of thing I wanted!! Blown away by the level of knowledge here!!

Thanks again

[mike6732]

One more quick question if you don't mind...

Would it be possible to have a range of values in one of the Score cells, and have a positive return if any of the values in the New Case cells match?

I've attached a new spreadsheet. The value in B9 should ideally be a range between 5-7.

Thanks in advance!

[forester_xb]

Sorry, I tried the second formula (for G8) and it doesn't work...

Should it be something wrong with my operation or is this formula missing something?..

Thanks in advance for answering my confusion!

[mike6732]

Thanks for taking a look! The second formula won't work as I have put a placeholder value in B9 ("5,6,7"). If you change this value to "7" it should work.

Hope this makes sense!

[forester_xb]

Sorry but it still doesn't work... and the value in B9 seems already 7..

Please see the file attached if you are willing to help me out here..

Thanks!

[mike6732]

Ah, I think we're looking at different spreadsheets. If you look at the spreadsheet attached below ('Case Database 3') you can see the formula working.

[XLent]

One more quick question if you don't mind...

Would it be possible to have a range of values in one of the Score cells, and have a positive return if any of the values in the New Case cells match?

you could try modifying as follows:

Please Login or Register  to view this content.

[XLent]

Sorry but it still doesn't work... and the value in B9 seems already 7..

Please see the file attached if you are willing to help me out here.

@forester_xb

FYI - in your sample attachment the formula in Col D is incorrect
1. should be in D1 rather than D2
2. reference to B2:C6 should be relative rather than fixed ($B$2:$C$6) - as a result the key value in D9 is not correct.

if you fix the above, and copy down the value will generate as expected in G8.

Note: TEXTJOIN is not available in XL2016 (per your profile).

[mike6732]

Incredible, works perfectly! Thank you so so much!

[forester_xb]

Hi,

thanks a lot for the reply! With the file you offered I found out that I wrongly put the first formula in D2, which is the main reason behind! And the rest is solved by Xlent's explanation!

Thanks!

[forester_xb]

Hi,

thanks a lot for the information!

It works now actually, and I'm using Excel 365 so I think it is updated to latest version maybe.
Just really curious about the usage of REPT() here, it outputs an array consisting of "" and "Case 2", which is not the normal case explained by the REPT function-Office Support website..

[XLent]

the REPT is being used as

REPT(range,boolean array)

the individual Booleans will only be True if the key has been found -- and in native XL True = 1 and False = 0

so, where the Boolean test returns FALSE we will get a null string as "apple" repeated 0 times = ""

we then use the outer TEXTJOIN to collate the individual results (the outer INDEX is used simply to negate requirement for Array Entry}.

the complex piece, in this particular thread, is establishing the boolean results, i.e.:

ISNUMBER(SEARCH(TEXTJOIN("^",,INDEX("R"&{1;2;3;4;5}&"C"&{1,2}&"@*,"&$F$2:$G$6&",*",0)),$D$1:$D$13))

the above basically creates an array of values, R1C1 to R5C2 {with each RxCy representing an element in the 5x2 matrix) to which we append item values

by prefixing & suffixing the values with wildcard & comma (*,x,*) we can then search for the current matrix in the others, and thus handle possibility of delimited ranges in prior cases
(e.g. 5,6,7 for one individual element)

HTH

[forester_xb]

Hi XLent,

BIG thanks for the greatly detailed explanation, with which I now eventually understand how this formula works, especially the REPT()!
One important takeaway is that, REPT(text, num_times) will repeat the text with specified times and finally output a text string, while REPT(range, num_times) will repeat each text in the range with corresponding number of times and finally output an array of strings.

Hope my understanding is correct and thank you very much again!

[AliGW]

It works now actually, and I'm using Excel 365 ...

Then please update your user profile!

In future, if you have queries pertaining to someone else's thread, you should start your own with your query and a link back to the thread in question. I hope this is clear.

[forester_xb]

Hi,
thanks for reminding.
Have updated my profile and will do so in future.

BR