Help needed on a Combo COUNT Multiple Text Items with CONTAINS

[GTdiscus]

All,

Thank you in advance for your help. I am trying to do what I believe is a combo of a COUNT and a CONTAINS function. I would like to compare just one cell to another set of cells and count how many items within the cell being checked are contained in the other cells. An example will help make this more clear.

Cell to check (we shall call it 1) against the references has: A, B, C

Reference cell 2 has: A, D, E
Reference cell 3 has: A, B, C, D
Reference cell 4 has: X, Y, Z

The equation would check to see how many of 1's items (3 total in the example that are text) are in cells 2, 3 and 4. The correct response would be for each as follows:
Cell 2: 1 (A is present here...so it just has one matching...so just 1 as the count)
Cell 3: 3 (All of them from 1 are here...D doesn't matter, as it isn't one of the ones I'm looking for)
Cell 4: 0 (nothing from 1 is present)

Hope this makes sense!

Best wishes,
Scott

 

[DRSteele]

I think this does what you describe. Copy B4 and C4 downwards as far as necessary.

	A	B	C	D
1	Test criteria:	a	b	c
2
3	Data	by reference	hard-coded
4	A, D, E	1	1
5	A, B, C, D	3	3
6	X, Y, Z	0	0

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet61​

Array Formulas Cell Formula B4 {=SUM(--(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)=$B$1:$D$1))} C4 {=SUM(--(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)={"a","b","c"}))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

 

[GTdiscus]

I think this does what you describe. Copy B4 and C4 downwards as far as necessary.

	A	B	C	D
1	Test criteria:	a	b	c
2
3	Data	by reference	hard-coded
4	A, D, E	1	1
5	A, B, C, D	3	3
6	X, Y, Z	0	0

<tbody>
</tbody>

Sheet61​

Array Formulas Cell Formula B4 {=SUM(--(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)=$B$1:$D$1))} C4 {=SUM(--(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)={"a","b","c"}))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

Thank you for the response. I tried to copy/paste as you mentioned and it doesn't do anything (ctrl+shift+enter to get curly brackets). Is this specific to certain versions of Excel? I pasted it without them (using ctrl-V) and that of course doesn't work (shows 1 for the top two rows and 0 for the last one). So a couple of questions:
1) is there another way around the curly brackets?
2) why does the formula need to be different for the two rows you have (B4 and C4)? I expected to have one equation to drag all the way down...and also don't want to modify anything in the equation as the number of items being looked for changes

not sure if emails can be sent directly...but mine is: scottreida@alumni.virginia.edu

Thanks for the help, Scott

 

[DRSteele]

Copy all of this =SUM(--(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1)=$B$1:$D$1)) Go to B4. Paste into the edit line; hold down Ctrl and Shift together and then press enter. That will put the curly brackets around the invoked formula. Copy B4 downwards as far as necessary.

The curly brackets are necessary because the formula conducts a function argument array operation.

I included the hardcoded portion of $B$1:$D$1 simply for your convenience.

You can watch this and other videos on the terrific YouTube channel to get a feel for how array operations work in Excel. https://www.youtube.com/watch?v=DSivs93UDgc

And you can review the Forum Rules (especially #4) here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[GTdiscus]

Thanks again for the help! It is working now. I'm not sure what I wasn't doing properly before. Great set of video suggestions too. Also on rules...noted :D
Best wishes, Scott

 

[DRSteele]

Splendid. I'm glad your project is moving forward.

Watching those ExcelIsFun videos and ones from Bill Jelen on a regular basis will markedly increase your knowledge of Excel. Have fun with excel. Best of luck.

 

[GTdiscus]

I just applied the formula today to my actual data and it isn't returning any values other than 1. I have a feeling that it is due to the values being one character long letters. I would like to be checking strings of x length to similar strings of x length (x can be any number of characters). It would also be helpful if it doesn't necessarily have to be verbatim. In other words...I'm hoping that "regression" would be a successful match with "regression equations". I just want part of the term to be included for it to add a 1 to the sum.

any ideas on how to modify the equation mentioned already in this thread?

 

[DRSteele]

That formula checks for the existence of any of the approved characters within a text string and their count, which is what you sought.

Please provide some sample data together with desired result for the actual situation.

 

[GTdiscus]

Food for thought...
I am fairly certain the fix is in the MID function part of this (but can't get it to work yet). I found a link that gives some helpful ideas with name splits using MID. An excerpt:

1. For Extracting First Name of a Person: I have used the formula <code>=MID(A1,1,SEARCH(" ",A1,1))</code> . In this example MID function searches the string at A1 position and starts the substring operation from 1^st character of the input string. For the length of extracted string I have used Search Function which scans the string at A1 cell for the space (“ ”) character and returns its position as an integer value. And thus the resultant of both these functions fetches the person’s name.
2. For Extracting Last Name of a Person: In the second part to extract the last name of a person I have used the formula <code>=MID(A1,SEARCH(" ",A1),100)</code> . In this formula the MID function extracts from the cell no. A1. The start position is decided by the Search formula, here Search formula gives the position of space character (“ ”) and hence extraction starts from there and continues till the end of string.
The above function also includes a space (“ ”) character at the start of the Last name substring so a better option would be to use <code>=MID(A2,SEARCH(" ",A1)+1,100)</code>.

LINK:
Excel MID Function – How to Use

Thanks, Scott

 

[GTdiscus]

Skill 1	Skill 2	Skill 3	Skill 4	Skill 5	Skill 6	Skill 7	Skill 8	Skill 9	Skill 10
root cause analysis	pareto chart	regression	process mapping	simulation

<colgroup><col width="114" style="width:86pt" span="10"> <tbody>
</tbody>

I have a list of skills (one given as a string in each cell). I am trying to bounce these off another list that has skills as well. This is basically what was done previously with letters. I would like the sample table below to be checked for that array of skills (the blue section above) and just give a count of how many of them appeared. In this example, the only one there is "simulation"- so I'd like it to return a 1. If another was present of course...we'd want a 2. I am not sure if there is a way to NOT make it case sensitive (in this case it'd be an issue).

Simulation, Design of Experiments, Statistics, Kitten Walking

0

<tbody>
</tbody><colgroup><col></colgroup>