+ Reply to Thread
Results 1 to 15 of 15

Dynamic array formula to indicate which rows are visible and which are not

  1. #1
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Dynamic array formula to indicate which rows are visible and which are not

    Hi,

    One of the few ways I know of which makes it possible to indicate whether rows are visible or not in Excel is the following:

    1) Fill a helper column (A) with 1's.
    2) Put the formula =SUBTOTAL(2,A1) in cell B1 and copy it down.
    3) When an auto filter is applied to content in the sheet, the formula in 2) will result in 1's for rows that are visible and 0's for rows that are hidden.
    4) Thus it is possible to incorporate this into the criteria for formulas that don't have a built-in possibility to ignore hidden rows (like the AGGREGATE function).

    I would like to see an alternative solution to this with a dynamic array formula with a spill range instead of having to copy the formula down, preferably also without using a helper column.

    Does anybody know how to do this, if it's possible?

    Best regards,
    Marbleking
    Last edited by Marbleking; 09-03-2022 at 07:18 AM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Dynamic array formula to indicate which rows are visible and which are not

    HOW TO ATTACH YOUR SAMPLE WORKBOOK:

    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.


    Then we also can test an offered solution, based on your criteria.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Dynamic array formula to indicate which rows are visible and which are not

    Hi,

    Please see attached workbook for example. Column B holds the results I am looking for.

    Regards,
    Marbleking
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Dynamic array formula to indicate which rows are visible and which are not

    a1=COUNTA(A4:A13)

    b1=SUBTOTAL(102,$B$4:$B$13)-A1


    b1 is filled with conditional format (green is no filter / red is filter is used).


    See the attached file.

  5. #5
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Dynamic array formula to indicate which rows are visible and which are not

    Thanks, oeldere

    What I am looking for is an alternative to the =SUBTOTAL(2,A1) copied down, that does the same, i.e. each copied-down formula gives 1 if the cell it resides in belongs to a visible row and 0 if the cell it resides in belongs to a hidden/filtered row. The formula alternative I am after should be a dynamic array formula that spills these results of 1's and 0's down the given range depending on whether rows are visible or not; and preferably without using a helper column.

    Regards,
    Marbleking

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Dynamic array formula to indicate which rows are visible and which are not

    I did not used a helpcolumn.

    Remove the filter and see what is happening.

    See column A and B for the result in the attached file.
    Last edited by oeldere; 09-03-2022 at 08:46 AM. Reason: edit: remove the filter

  7. #7
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Dynamic array formula to indicate which rows are visible and which are not

    Yes, but I am not looking for the single-cell results you have in A1 and B1, unfortunately. I am looking for an indicator in each row that switches to 1 for each of the rows that are visible and to 0 for each of the rows that are hidden:

    B1: Header
    B2: 1
    B3: 1
    B4: 0 (hidden row)
    B5: 1
    B6: 1
    Etc.

  8. #8
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,345

    Re: Dynamic array formula to indicate which rows are visible and which are not

    What is your goal at a slightly higher level?
    SUBTOTAL has 11 functions in the 100-level that automatically excludes hidden rows.
    Can they apply to you to achieve your ultimate goal

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,417

    Re: Dynamic array formula to indicate which rows are visible and which are not

    The OP is looking for a spill array that does the same job.

    @Marbleking

    If you make your array into a structured table, then the formula you have already will spill down automatically. Is this an option?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  10. #10
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,345

    Re: Dynamic array formula to indicate which rows are visible and which are not

    @Ali,

    Yes, I also read that The OP is looking for a spill array that does the same job?
    A spill-area function that indicates whether a row is hidden or not. That was also clear to me.

    But that can never be the ultimate goal.
    I expect that the OP wants to make other functions where this data is input again.

    Hence my question to the OP what the underlying purpose is.

  11. #11
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Dynamic array formula to indicate which rows are visible and which are not

    Hi AliGW,

    Yes, that's a good option.

    I have other, adjacent, columns with various filter criteria, though, and they are created with spilled arrays that cannot be used in a structured table. So for consistency, I'd like to see a spilled array solution for the visible/hidden row indicator too.

    My goal is to perform operations on various adjacent tables of data. Some of this data resides in structured tables, some in spilled arrays, and some in traditional formulas, but all will have the same number of rows when updated. I have MAXIFS, MINIFS, AVERAGEIFS etc. above the data, performing calculations on the various columns of data below. I want the addressed ranges in these formulas to update automatically when rows of data are added to the tables below (some automatically updated upon refresh of external data, and some manually). It's ok when I can accommodate the AGGREGATE function to perform the operations because it can ignore hidden rows, but it doesn't contain the ability to perform MAXIFS as far as I know and therefore I need a separate helper column I can use as input that indicates whether rows are filtered out or not. And I don't want this helper column to be something that needs manual updating or that extends any further down than the rest of the data tables. I can use the method I already have and use a structured column for this as AliGW suggested, but for consistency with the other filter criteria I have created, I'd like it to be a spilled column.

    Regards,
    Marbleking

  12. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Dynamic array formula to indicate which rows are visible and which are not

    Please try

    =SUBTOTAL(3,OFFSET(C1,SEQUENCE(ROWS(C2:C11)),))
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Dynamic array formula to indicate which rows are visible and which are not

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Dynamic array formula to indicate which rows are visible and which are not

    Cool, Bo_Ry and Fluff13! You posted almost the same solution at almost the same time! That seems to do the trick! Thanks a lot!

  15. #15
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: Dynamic array formula to indicate which rows are visible and which are not

    Glad to help & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Google Sheets: Merge dynamic ranges automatically (skipping rows without a visible data)?
    By rcurious in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 15
    Last Post: 06-21-2020, 09:27 AM
  2. How to merge dynamic ranges automatically (skipping rows without any visible data)GoogleD?
    By rcurious in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 06-21-2020, 09:19 AM
  3. Replies: 12
    Last Post: 06-21-2020, 09:12 AM
  4. Need some help on dynamic dropdow for filtered row or for visible rows only
    By dvpnp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-05-2013, 12:21 PM
  5. Use visible rows from autofilter to build Pivot table. Or use visible rows to Copy/Paste
    By mwhitedesigns in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-08-2012, 10:34 AM
  6. [SOLVED] Populate Listbox with all rows of a dynamic array where elements of a single array match.
    By Tayque_J_Holmes in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 08-07-2012, 04:54 AM
  7. Autofilter data and put the numbers of visible rows into an array
    By Duchesssix in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-18-2011, 09:04 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1