HELP Extract records using ROWS arrays formulas

[King_ZZ]

What I am trying to do is from one document to another but I will show you on the same sheet.
I am using ROWS and then $ fixing the number not the letter, but I double checked my formula using an online tutorial


found here; Excel Magic Trick 616: LARGE & SMALL Functions (8 Examples) - YouTube 7.15 mins in.

Download tutorial doc here; https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT615-622.xlsm

you need tab (616) it is completed on tab (616an)

=IF(COLUMNS($G15:G15)>$F15,"",INDEX($B$2:$B$12,SMALL(IF($B$2:$B$12=$E15,ROW($B$2:$B$12)-ROW($B$2)+1),COLUMNS($G15:G15))))

Then when I press ctrl+shift+enter to make it an array ( or use the array tick box when using ctrl+f2)
then I copy across the formula (or down using ROWS)

The COLUMNS count doesn’t move along and it shows up Err:508

Im not sure where the page is that tells you what that error means but in openoffice it means;

508 Pair missing Missing bracket or parenthesis; for example, closing brackets but no opening brackets.

Could someone have a look into this for me am I doing something wrong??

[Lupp]

To “have a look into this” is very much aggravated if “this”, the real spreadsheet, is not available. To speak for myself expressly: I cannot find out much based on images in such a case. I also do never watch videos concerning spreadsheets. A questioner giving me a sheet and telling me what he wants to achieve will often get a useful answer, however, I think. Caution! Karma on the way. Please upload your example now.

[King_ZZ]

I am basically doing the same thing as being done in this tutorial. The sheet is available for download;

https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT615-622.xlsm

On tab 616 and the formula I want to do is on tab 616an in cell g15 through to i15.



I will be using ROWS in my formula but its the same as COLUMNS except u lock the letter not the number.



If that is not what you mean I will do something different to help you answer my query.

[Lupp]

Filling an array formula into adjacent cells dragging the fill handle will define the filled range as the output range of one singel array formula. To get the array formula filled for independent output to each of the cells concerned you have to use either Ctrl+DragMouse or the ‘Edit’ > ‘Fill’ tool (starting with V 5.1 moved to the new ‘Sheet’ menu item) or a Copy/paste proceeding.
Err:508 is ‘Missing Pair’ as you mentioned. The error message is misleading in this case, however.
The inner IF IF($B$2:$B$12=$E15,ROW($B$2:$B$12)-ROW($B$2)) on the first parameter position of SMALL is incomplete. It will return FALSE, numerically equivalent to 0 (zero) for every element of $B$2:$B$12 noit equal to $E15. Clearer: Explicitly put 0 on the third parameter position.

Editing:
As things still not seem to be clear to the detail, I want to supply an example demonstrating the contrast. The formulae in the helper columns doing the work here also are not too easy to understand, if not explained thoroughly. They can be analysed and understood step by step from left to right, however. No sorcery! See attached.
ask65461EMT615-622Reworked001.ods

[King_ZZ]

Thank you for your answer



I did not know about the Ctrl+DragMouse as I believe it is just DragMouse in MSoffice



However adding the ,0 to the inner IF statement is incorrect as this gives a result of;

{“Sue”;“Sioux”;“Christina”…



Which is an array result and then lists this downwards until it lists them all.



The Err:508 was due to not using the Ctrl+DragMouse

[Lupp]

“adding the ,0 to the inner IF statement is incorrect” Did you analyse my statement? The IF call actually is incomplete and has to retiurn FALSE if the condition does not come out TRUE. If the completion causes a different result, the result before or the result after (at least) do not comply with the specification. Using formulae depending on such imponderabilities is not a good idea (IMO).

[Lupp]

Filling an array formula into adjacent cells dragging the fill handle will define the filled range as the output range of one singel array formula. To get the array formula filled for independent output to each of the cells concerned you have to use either Ctrl+DragMouse or the ‘Edit’ > ‘Fill’ tool (starting with V 5.1 moved to the new ‘Sheet’ menu item) or a Copy/paste proceeding.
Err:508 is ‘Missing Pair’ as you mentioned. The error message is misleading in this case, however.
The inner IF IF($B$2:$B$12=$E15,ROW($B$2:$B$12)-ROW($B$2)) on the first parameter position of SMALL is incomplete. It will return FALSE, numerically equivalent to 0 (zero) for every element of $B$2:$B$12 noit equal to $E15. Clearer: Explicitly put 0 on the third parameter position.

Editing:
As things still not seem to be clear to the detail, I want to supply an example demonstrating the contrast. The formulae in the helper columns doing the work here also are not too easy to understand, if not explained thoroughly. They can be analysed and understood step by step from left to right, however. No sorcery! See attached.
ask65461EMT615-622Reworked001.ods