Ask Your Question
1

HELP Extract records using ROWS arrays formulas

asked 2016-03-02 05:56:13 +0200

King_ZZ gravatar image

updated 2016-03-02 17:12:02 +0200

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; https://www.youtube.com/watch?v=NhRYt... 7.15 mins in.
Download tutorial doc here; https://people.highline.edu/mgirvin/Y...
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??

image description image description

edit retag flag offensive close merge delete

Comments

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.

Lupp gravatar imageLupp ( 2016-03-02 18:38:40 +0200 )edit

I am basically doing the same thing as being done in this tutorial. The sheet is available for download;
https://people.highline.edu/mgirvin/Y...
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.

King_ZZ gravatar imageKing_ZZ ( 2016-03-02 19:05:08 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2016-03-02 19:34:39 +0200

Lupp gravatar image

updated 2016-03-04 23:03:17 +0200

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

edit flag offensive delete link more

Comments

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

King_ZZ gravatar imageKing_ZZ ( 2016-03-04 19:34:13 +0200 )edit

"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 gravatar imageLupp ( 2016-03-04 22:56:58 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-03-02 05:56:13 +0200

Seen: 232 times

Last updated: Mar 04 '16