Inserting Values In Existing Formula

CARBOB

Well-known Member
I need to replace the values in the braces with the values in Column A. Is there an easy way to do this or am I stuck with doing each one manually? There are 92 lines in Column A. Thanks for any suggestions.
FL LOTTO 6-53.xlsx
ABCD
1
2
37,13,38,17,29,30,440
447,8,33,41,24,23,28
537,13,5,32,26,6,8
622,23,33,34,39,42,16
736,25,37,20,43,34,31
Sheet3

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hello,

I think this is correct, however you need to add a , at the beginning and at the end of the numbers in COl A

=SUMPRODUCT(--ISNUMBER(FIND(","&TEXT(\$B5:\$G5,"@@")&",",A3:A7)))

change A3:A7 as required

Building on onlyadrafter's formula, I think:

=SUMPRODUCT(--ISNUMBER(FIND(","&\$B5:\$G5&",",","&A3&",")))

will do the job.

Building on onlyadrafter's formula, I think:

=SUMPRODUCT(--ISNUMBER(FIND(","&\$B5:\$G5&",",","&A3&",")))

will do the job.

Thanks to both of you for responding. I might be misunderstanding, here are the results
FL LOTTO 6-53.xlsx
ABCDEFGH
35692539417,13,38,17,29,30,440
Sheet3

Change this

SUMPRODUCT(--ISNUMBER(MATCH(\$B5:\$G5,{25,50,22,48,21,49,24,53,29,5,35,12,43,23,4,38,18,3,40,27,11},0)))

To this
SUMPRODUCT(--ISNUMBER(MATCH(\$B5:\$G5,{7,13,38,17,29,30,44},0)))

Do you have a problem with the result? None of the numbers in A3:F3 appear in G3, so the result of zero is correct isn't it?

Do you have a problem with the result? None of the numbers in A3:F3 appear in G3, so the result of zero is correct isn't it?

Sorry Mr Poulson, misunderstood the results. Thank you!!!

The formula in SW5 counts the number of matches found in Col's ST:SV. The formula counts duplicate digits as 2. I need a formula that counts duplicate(0,0,7) or triple(5,5,5)digits as 1.
CASH3 MID 100GROUP COMBO SUMS WITH HORS.xlsx
SSSTSUSVSW
50,1,2,5,8
603/17/092183
703/16/090072
803/15/094552
Sheet1

Try:

=SUMPRODUCT(--ISNUMBER(FIND(","&\$ST6:\$SV6&",",","&SW\$5&",")),1/COUNTIF(\$ST6:\$SV6,\$ST6:\$SV6))

Fantastic Mr Poulson, Thank You

Try:

=SUMPRODUCT(--ISNUMBER(FIND(","&\$ST6:\$SV6&",",","&SW\$5&",")),1/COUNTIF(\$ST6:\$SV6,\$ST6:\$SV6))

=SUMPRODUCT(--ISNUMBER(FIND(","&DRAWS!\$B\$3:\$G\$3&",",","&\$L6505&",")))
in column M6505, it's returning a value of 4 when comparing this string 34,45,08,37,31,07,48 to these values 8 16 31 37 45 48 and it should return 5.
FL LOTTO 6-53.xlsx
LMNO
650534,45,08,37,31,07,48411
Sheet3

Replies
4
Views
267
Replies
3
Views
131
Replies
14
Views
1K
Replies
0
Views
282
Replies
0
Views
168

1,207,091
Messages
6,076,522
Members
446,211
Latest member
b306750

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back