+ Reply to Thread
Results 1 to 12 of 12

Applying formula to visible cells only

  1. #1
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    office 365
    Posts
    26

    Applying formula to visible cells only

    Hey guys,

    After filtering my list, I wasn't able to apply my formula to visible cells only.
    A B C
    Duplicate Order
    1 0
    1 0
    1 0
    1 0
    2
    2
    3 0
    3 0
    3 0
    4
    4
    5 0
    5 0
    6
    6
    6
    6

    I need to sort out zeros. For the rest, I need to hard code 1 in A6---right beside the first 2 and write the formula =IF(A7=A6, C6, C6+1)
    When I tried to drag down, the formula always include the hidden cells. How can I solve it.Capture.PNG
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Applying formula to visible cells only

    Try in C2:
    Please Login or Register  to view this content.
    Drag down
    Quang PT

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Applying formula to visible cells only

    If you'd be willing to use 2 cells per result, you could dispense with needing to make entries in col C.

    D2: =SUBTOTAL(2,A2)

    Fill D2 down into D3:D18.

    C2: =D2
    C3: =IF(D3,IF(COUNTIF(C$2:C2,1),C2+(A3<>INDEX(A$2:A2,MATCH(1,INDEX(0/D$2:D2,0)))),D3),C2)

    Fill C3 down into C4:C18. Filter on col B. These col C formulas produce

    C
    6 1
    7 1
    11 2
    12 2
    15 3
    16 3
    17 3
    18 3

  4. #4
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    office 365
    Posts
    26

    Re: Applying formula to visible cells only

    It worked. Thank you so much for your help!!!

  5. #5
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    office 365
    Posts
    26

    Re: Applying formula to visible cells only

    Quote Originally Posted by bebo021999 View Post
    Try in C2:
    Please Login or Register  to view this content.
    Drag down
    Hi there. A new problem occurred. I look at my data and there are data like this:
    A B C
    Duplicate Order
    1 0
    1 0
    1 0
    1 0
    2
    2
    2 0
    3 0
    3 0
    3 0
    4
    4
    5 0
    5 0
    6 0
    6
    6 0
    6 0
    Then there is a problem of not numbering that 6.
    Capture2.PNG
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    office 365
    Posts
    26

    Re: Applying formula to visible cells only

    A new problem occurred. I really appreciate it if yall can help. Thank you!!!!

  7. #7
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Applying formula to visible cells only

    FWIW, my approach using 2 columns works.

    The other approach fails with this formula in C17, =IF(B17="",IF(A17=A16,C16,MAX(C$1:C16)+1),"") (I dropped the column-absolute $ from the col C reference). Why? B17="", and A17=A16, but B16=0, so C16="".

    I've already provided a working solution, as seen here. I'll let bebo021999 come up with a fix for his formula.

  8. #8
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    office 365
    Posts
    26

    Re: Applying formula to visible cells only

    Quote Originally Posted by hrlngrv View Post
    FWIW, my approach using 2 columns works.

    The other approach fails with this formula in C17, =IF(B17="",IF(A17=A16,C16,MAX(C$1:C16)+1),"") (I dropped the column-absolute $ from the col C reference). Why? B17="", and A17=A16, but B16=0, so C16="".

    I've already provided a working solution, as seen here. I'll let bebo021999 come up with a fix for his formula.
    Hi there. I have tried your method. It works fine when data set is small. However, I have a large data set like 150k rows. When I tried to sort out the zeroes and get the result. Excel is just not responding. Do you have any solutions for that? That's the reason I used bebo021999's answer.

  9. #9
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    office 365
    Posts
    26

    Re: Applying formula to visible cells only

    Quote Originally Posted by Luden View Post
    Hi there. I have tried your method. It works fine when data set is small. However, I have a large data set like 150k rows. When I tried to sort out the zeroes and get the result. Excel is just not responding. Do you have any solutions for that? That's the reason I used bebo021999's answer.
    Also I just want the value. So when I tried to copy and paste value only, the result is changed because I can't paste only the value to the visible cells only.

  10. #10
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Applying formula to visible cells only

    Quote Originally Posted by Luden View Post
    . . . I have a large data set like 150k rows. When I tried to sort out the zeroes and get the result. Excel is just not responding. . . .
    The problem is that if you want formulas which adapt to filtering, it may have to be slow.

    Or maybe not. I can't find any robust way to handle this with 1 formula per result. With only 2 formulas per result, it is indeed quite slow due to needing to find the nearest previous visible row. Looks like efficiency requires 3 formulas per result: one for the SUBTOTAL call to tell whether the row is hidden or not, another to hold the last visible col A value, and the col C values.

    C2: =D2
    C3: =C2+D3*(A3<>E2)

    Fill C3 down as far as needed.

    D2: =SUBTOTAL(3,A2)
    E2: =IF(D2,A2,E1)

    Select D2:E2 and fill down as far as needed. Filter cols A:B, and col C values should be correct.

    I've updated my copy of your sample workbook here on OneDrive. I added a worksheet named lots of data, which has 100,000 rows of made-up data and categories 1-4 in col B on which to filter. There's still some lag, but I figure it's unavoidable for as many calculations as are performed.

  11. #11
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Applying formula to visible cells only

    Quote Originally Posted by Luden View Post
    . . . because I can't paste only the value to the visible cells only.
    If you mean copying filtered values and you want to paste only the visible values elsewhere, copy, then paste special as Values and check the box labeled Skip blanks, and that should paste only the visible filtered cells as values.

  12. #12
    Registered User
    Join Date
    09-13-2019
    Location
    US
    MS-Off Ver
    office 365
    Posts
    26

    Re: Applying formula to visible cells only

    Quote Originally Posted by hrlngrv View Post
    The problem is that if you want formulas which adapt to filtering, it may have to be slow.

    Or maybe not. I can't find any robust way to handle this with 1 formula per result. With only 2 formulas per result, it is indeed quite slow due to needing to find the nearest previous visible row. Looks like efficiency requires 3 formulas per result: one for the SUBTOTAL call to tell whether the row is hidden or not, another to hold the last visible col A value, and the col C values.

    C2: =D2
    C3: =C2+D3*(A3<>E2)

    Fill C3 down as far as needed.

    D2: =SUBTOTAL(3,A2)
    E2: =IF(D2,A2,E1)

    Select D2:E2 and fill down as far as needed. Filter cols A:B, and col C values should be correct.

    I've updated my copy of your sample workbook here on OneDrive. I added a worksheet named lots of data, which has 100,000 rows of made-up data and categories 1-4 in col B on which to filter. There's still some lag, but I figure it's unavoidable for as many calculations as are performed.
    Yes, it's unavoidable. It works if you wait patiently lol. Thanks for all your work!!!

+ 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. [SOLVED] problem applying formula to countifs only visible cells after filtering
    By Tbone1234 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-08-2019, 01:27 PM
  2. [SOLVED] Applying formula to visible rows only
    By denisirio in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-13-2015, 01:45 PM
  3. Delete visible rows after applying autofilter - not working
    By Jo2710 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-03-2014, 01:23 AM
  4. [SOLVED] Applying formula only to visible rows.
    By Folshot in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-22-2014, 12:01 PM
  5. [SOLVED] Applying code only to visible cells
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-08-2012, 11:07 AM
  6. Replies: 8
    Last Post: 07-14-2012, 10:22 AM
  7. Applying Formulas to Visible Cells Only
    By SteveC in forum Excel General
    Replies: 7
    Last Post: 06-26-2006, 06:50 PM

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