Applying formula to visible cells only

1. 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

2. Re: Applying formula to visible cells only

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

3. 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. Re: Applying formula to visible cells only

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

5. Re: Applying formula to visible cells only

Originally Posted by bebo021999
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

6. Re: Applying formula to visible cells only

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

7. 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. Re: Applying formula to visible cells only

Originally Posted by hrlngrv
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. Re: Applying formula to visible cells only

Originally Posted by Luden
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. Re: Applying formula to visible cells only

Originally Posted by Luden
. . . 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. Re: Applying formula to visible cells only

Originally Posted by Luden
. . . 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. Re: Applying formula to visible cells only

Originally Posted by hrlngrv
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!!!

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

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