Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Counting items in a range and outputting to a column in a pattern

I have a range like this, chunks of text items separated by blank spaced rows

Source

        A           B           C     

1       mercury     apple       SET A 
2       mars        mars              
3       jupiter     jupiter           
4       venus       haha              
5       saturn      saturn            
6                                     
7       jill        six         SET B 
8       earth       jill              
9       nine        earth             
10      ten         nine              
11                                    
12      thirteen    eleven      SET C 
13      fourteen    nepture           
14      sarah       thirteen          
15      sixteen     fourteen          
16      seventeen   sarah             
17                                    
18      nineteen    sixteen     SET D 
19      twenty      seventeen         
20                                    

I would like to add another column D which counts theh chunks of items and shows the count number in a repeated fashion, like so

Desired result, Col D

        A           B           C       D 

1       mercury     apple       SET A   1 
2       mars        mars                1 
3       jupiter     jupiter             1 
4       venus       haha                1 
5       saturn      saturn              1 
6                                         
7       jill        six         SET B   2 
8       earth       jill                2 
9       nine        earth               2 
10      ten         nine                2 
11                                        
12      thirteen    eleven      SET C   3 
13      fourteen    nepture             3 
14      sarah       thirteen            3 
15      sixteen     fourteen            3 
16      seventeen   sarah               3 
17                                        
18      nineteen    sixteen     SET D   4 
19      twenty      seventeen           4 

I’ve tried, but struggling, using various approaches via VLOOKUP, QUERY COUNTA etc and have explored FLATTEN but could really use some help here.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

I can already do that via Apps Script, but I’d really like a formula based approach.

>Solution :

try:

=INDEX(LAMBDA(c, IF(LEN(A:A&B:B), VLOOKUP(ROW(c), IF(c<>"", {ROW(c), 
 COUNTIFS(c, "<>", ROW(c), "<="&ROW(c))}), 2, 1), ))(C:C))

enter image description here

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading