How to enter multiple array formula

0
votes

Is there any way to do "Ctrl+Shift+Enter" on multiple cells containing formula? I want to turn them all into array.

I googled for the solution, and it tells me to select them all, hit F2 to go into edit mode, then Ctrl+Shift+Enter, but that converts all other array formulas into the first one!

Example: I have cells going down vertically with the following formula in them:

=INDEX(Data!V2:V64, SMALL(IF(W2=Data!B2:B64, ROW(Data!B2:B64)-MIN(ROW(Data!B2:B64))+1, ""), ROW(A1)))
=INDEX(Data!V2:V64, SMALL(IF(W2=Data!B2:B64, ROW(Data!B2:B64)-MIN(ROW(Data!B2:B64))+1, ""), ROW(A2)))
=INDEX(Data!V2:V64, SMALL(IF(W2=Data!B2:B64, ROW(Data!B2:B64)-MIN(ROW(Data!B2:B64))+1, ""), ROW(A3)))
=INDEX(Data!V2:V64, SMALL(IF(W2=Data!B2:B64, ROW(Data!B2:B64)-MIN(ROW(Data!B2:B64))+1, ""), ROW(A4)))
=INDEX(Data!V2:V64, SMALL(IF(W2=Data!B2:B64, ROW(Data!B2:B64)-MIN(ROW(Data!B2:B64))+1, ""), ROW(A5)))
=INDEX(Data!V2:V64, SMALL(IF(W2=Data!B2:B64, ROW(Data!B2:B64)-MIN(ROW(Data!B2:B64))+1, ""), ROW(A6)))
...

What is the fastest way without editing each cell individually then hitting Ctrl+Shift+Enter?

Appreciate any help

1
arraysexcelexcel-formula
Go to the first formula - lock all your multi-cell ranges with F4(so that $ appears before the columns AND row numbers), use Ctrl-Shift-Enter, then copy down... - MacroMarc
Perfect MacroMarc, appreciate the tip - Dergyll

1 Answers

0
votes

Try one of these:

  1. =INDEX(Data!V2:V64, SMALL(IF(W2=Data!B2:B64, ROW(Data!B2:B64)-MIN(ROW(Data!B2:B64))+1, ""), ROW(A:A)))

  2. If first formula is in the first row, you can use this:

    =INDEX(Data!V2:V64, SMALL(IF(W2=Data!B2:B64, ROW(Data!B2:B64)-MIN(ROW(Data!B2:B64))+1, ""), ROW()))

So you Row as reference to current row.