match the number from comma separated value and count

0
votes

I have a comma separated value in A2 and same numbers in different cells B1, C1, D1.... I want to match them from comma separated value and find out the count in B2, C2, D2. Please see the image attached you will get the context.

enter image description here

can anyone guide me if we can achieve this by formula or macro in excel.

Tried formula:

=LEN(TRIM($A$2))-LEN(SUBSTITUTE(TRIM($A$2),C1,","""))

any help will be highly appreciated.

Also, I have two data sets where I will be using this formula to find out the count of number from comma-separated value and based on count I want the repeated ones to come in a different cell please refer the image for better understanding.

enter image description here

4
excelvbaexcel-formula
image? and where is the code you have tried? – braX
i.stack.imgur.com/P7tsd.png – Sameer Farooqui
i tried to do it with excel formula but unable to achieve the desired result. =LEN(TRIM($A$2))-LEN(SUBSTITUTE(TRIM($A$2),C1,",""")) – Sameer Farooqui

4 Answers

2
votes

The following worked for me, give it a try:

enter image description here

Formula in B2:

=(LEN(","&SUBSTITUTE($A$2,",",",,")&",")-LEN(SUBSTITUTE(","&SUBSTITUTE($A$2,",",",,")&",",","&B$1&",","")))/LEN(","&B$1&",")

Drag right...

2
votes

Probably not the best solution but get the job done. Please note it is case-sensitive and please make sure to press Ctrl+Shift+Enter upon finishing this formula.

{=SUM(--(EXACT("!"&TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",100)),(ROW(INDIRECT("1:"&LEN($A2)))-1)*100+1,100)),"!"&B1)))}

You can replace ! in the above formula with a unique symbol that will never appear in the text string to be safer.

The logic is to SUBSTITUTE the comma , with and long string of blanks, then use MID to find each value in the text string and return the result as an array, then use EXACT to match each value in the array with the look up value and return a new array of TRUE and FALSE, then SUM up all TRUE which will give the count of the look up value.

Solution3

UPDATE #2

As requested by OP, here is one way of solving the second query which is to match the same value with the same occurrence from two text strings separated by comma ,.

Update2

The formula in Cell C2 is from the original solution which is used to find the occurrence of a given value in a text string;

The formula for Range C6:K6 is an array formula as shown below. I used a helper row to layout the matching values, and excluding the one that has 0 count for both data set;

{=IFERROR(INDEX($C$1:$K$1,,AGGREGATE(15,7,COLUMN(INDIRECT("1:"&COLUMNS($C$1:$K$1)))/($C$2:$K$2=$C$3:$K$3)/($C$2:$K$2>0),COLUMN()-2))&",","")}

The formula in Cell L8 is concatenating all values from Range C6:K6 and remove the last comma , from the final text string:

=LEFT(CONCATENATE(C6,D6,E6,F6,G6,H6,I6,J6,K6),LEN(CONCATENATE(C6,D6,E6,F6,G6,H6,I6,J6,K6))-1)
1
votes

A simpler way of doing it is to simply calculate the difference in the length of the string minus the length of the string when replacing the value searched by nothing and dividing by the length of the string searched The formula would be:

=(LEN($A$1)+1-LEN(SUBSTITUTE($A$1&",",B1&",","")))/LEN(B1&",")

enter image description here

0
votes

There is a much simpler solution:

=COUNTIF(SPLIT($A$2, ","), B1)

enter image description here