How to optimize Excel formula automatically when drag and drop?

1
votes

I have an excel formula that is looking for some courses in an array from a cell. In the picture below the column A2 is populated automatically from Wix with an array of courses that can contain 1 to 10 courses. And in regard to this array, the A:K columns are populated with 1 if the course exist in the array and 0 if not.

The formula is: =--ISNUMBER(FIND(""""&B2:K2&"""",A2))

The problem is when I fill a row and drag and drop to populate the formula to a great number of rows, the formula is changing. I need to keep B2:K2 the same, and only the A2 should change . If the row is A3, in the formula I should have =--ISNUMBER(FIND(""""&B2:K2&"""",A3)).

How can I automatically do this, because I have a large numbers if rows, and cannot right the formula to all rows?

enter image description here

1
arraysexcelexcel-formula
Change the formula to: =--ISNUMBER(FIND(""""&$B$2:$K$2&"""",A2)), or if you copy then to the next row maybe =--ISNUMBER(FIND(""""&$B2:$K2&"""",A2)) The $ sign indicates that that part of the cell reference should not change when copying the formula. ( BTW: I Hate drag and drop... 😉) – Luuk
Your result will be always shown in B2:K2? It seems you are using Excel365 then you can use dymanic formulas. – Harun24HR
@byteis8bits Could you please try my answer. – Harun24HR
Note: You don't need to drag anything. All you need is a single formula where you refer to all cells in the A column. See the comment by @T.M. under the answer here. I have edited the answer to reflect this. – JvdV
@JvdV nice solution! Post it here! – byte is 8bits

1 Answers

2
votes

If you want to keep B2:K2 constant then lock them by $ means absolute position like-

=--ISNUMBER(FIND(""""&$B$2:$K$2&"""",A2))

You can try below formula at once for whole range of data.

=IF(ISERROR(FILTERXML("<t><s>"&SUBSTITUTE(A2:A4,",","</s><s>")&"</s></t>","//s[contains(., '" & $B$1:$K$1 & "')]")),0,1)

enter image description here