Return first value for each block in a range and count block size

[ekalavya]

Hello Excel experts,

I have a single yet very long column with several blocks. Each block has a header/category name and each block is separated from the next block with 1-3 new lines. My data is similar to:

Fruits

Apple

Banana

Orange

Grapes

Pineapple

Veggies

Celery

Carrots

Spinach

Nuts

Pecan

Walnut

<tbody>
</tbody>

I would like to know how many items are within each block, along with the block name. So, my desired output is:

<tbody>
</tbody>

Fruits	5
Veggies	3
Nuts	2

<tbody>
</tbody>

OR

Fruits - 5

Veggies - 3

Nuts - 2

<tbody>
</tbody>

I would prefer a formula, if possible. Thank you!

 

[Coding4Fun]

It appears you are working with Dynamic data, with your current setup I don't think this is possible, meaning you need more than one column as there is no way to identify what is a header vs data and no way to tie the header to the data block. At least not that I see.

If you were to use 2 columns, one for the Header or type and one for the values you could then use a COUNTIF formula but this would still require you to know how many headers there are and you would need to have them all listed somewhere.

Column A / Column B
Fruit / Apple
Fruit / Banana
Fruit / Orange
Veggie / Celery
Veggie / Carrots
Nuts / Pecan
Nuts / Walnut

 

[JoeMo]

While you are waiting for a formula solution, you can try this macro. Assumes The first block header is in cell A1.

Sub BlockHeadersAndSize()
Dim R As Range, Ar As Range, Output As Variant
On Error Resume Next
Set R = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeConstants)
If Not R Is Nothing Then
    ReDim Output(1 To R.Areas.Count, 1 To 2)
    For Each Ar In R.Areas
        i = i + 1
        Output(i, 1) = Ar(1)
        Output(i, 2) = Ar.Count - 1
    Next Ar
    Range("C1:D" & UBound(Output, 1)).Value = Output
Else
    MsgBox "no blank rows detected - exiting sub"
End If
End Sub

 

[Fluff]

Here's a VBA solution if it cannot be done by formulae

Sub GetGroupDetails()

   Dim Rng As Range
   Dim Rw As Long
   
   For Each Rng In Range("A:A").SpecialCells(xlConstants).Areas
      Rw = Rw + 1
      Range("C" & Rw).Resize(, 2) = Array(Rng.Resize(1, 1).Value, Rng.Count - 1)
   Next Rng
End Sub

 

[Coding4Fun]

Start your data on row 2 and you could try this, you would still need to copy the data somewhere and sort it to remove the space between the groups but essentially columns D & E will have what you are looking for by the end of this.

A	B	C	D	E
Fruits	=IF(AND(A1="",A2<>""),"Header",IF(A2<>"","Value",""))	=IF(B2="Header",A2,IF(B2<>"",C1,""))	=IF(AND(A1="",A2<>""),C2,"")	=IF(D2<>"",COUNTIF(C:C,D2),"")
Apple
Banana
Orange
Grapes
Pineapple
Veggies
Celery
Carrots
Spinich
Nuts
Pecan

<tbody>
</tbody>

 

[Coding4Fun]

Using the example in my last post you would end up with this.

I also noticed that the COUNTIF is off and should be adjusted to remove 1

This would be adjusted in column E

=IF(D2<>"",COUNTIF(C:C,D2)-1,"")

A	B	C	D	E
Fruits	Header	Fruits	Fruits	6
Apple	Value	Fruits
Banana	Value	Fruits
Orange	Value	Fruits
Grapes	Value	Fruits
Pineapple	Value	Fruits
Veggies	Header	Veggies	Veggies	4
Celery	Value	Veggies
Carrots	Value	Veggies
Spinish	Value	Veggies
Nuts	Header	Nuts	Nuts	2
Pecan	Value	Nuts

<tbody>
</tbody>

 

[bosco_yip]

Try,

1] Set-up as per post #.5, in range A1:A17, with A1, A8, A13 and A17 are blank

2] In C2, formula copied down until blank :

=IFERROR(INDEX(A$1:A$17,AGGREGATE(15,6,ROW(A$1:A$17)/(A$1:A$17=""),ROW(A1))+1),"")

3] In D2, formula copied down until blank :

=IF(C2="","",AGGREGATE(15,6,ROW(A$1:A$17)/(A$1:A$17=""),ROW(A2))-AGGREGATE(15,6,ROW(A$1:A$17)/(A$1:A$17=""),ROW(A1))-2)

Regards
Bosco

 

[VBA Geek]

Excel 2013/2016
	A	B	C	D	E	F
1			Number of Blocks	3
2	Fruits
3	Apple		Fruits	5
4	Banana		Veggies	3
5	Orange		Nuts	2
6	Grapes
7	Pineapple
8
9
10	Veggies
11	Celery
12	Carrots
13	Spinach
14
15
16	Nuts
17	Pecan
18	Walnut
19
Sheet1

Cell Formulas
Range	Formula
D1	=COUNTIFS(A1:A1000,"",A2:A1001,"?*")
D3	{=MATCH(TRUE,OFFSET($A$1,MATCH(C3,$A$1:$A$100,0)-1,0,1000)="",0)-2}
D4	{=MATCH(TRUE,OFFSET($A$1,MATCH(C4,$A$1:$A$100,0)-1,0,1000)="",0)-2}
D5	{=MATCH(TRUE,OFFSET($A$1,MATCH(C5,$A$1:$A$100,0)-1,0,1000)="",0)-2}
C3	=IF(ROWS(C$2:C2)>$D$1,"",INDEX(A$1:A$100,AGGREGATE(15,6,ROW(A$2:A$101)/((A$1:A$100="")*(A$2:A$101<>"")),ROWS(C$3:C3))))
C4	=IF(ROWS(C$2:C3)>$D$1,"",INDEX(A$1:A$100,AGGREGATE(15,6,ROW(A$2:A$101)/((A$1:A$100="")*(A$2:A$101<>"")),ROWS(C$3:C4))))
C5	=IF(ROWS(C$2:C4)>$D$1,"",INDEX(A$1:A$100,AGGREGATE(15,6,ROW(A$2:A$101)/((A$1:A$100="")*(A$2:A$101<>"")),ROWS(C$3:C5))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ekalavya]

Wow, thank you to each and every one of you fine souls! Truly humbled, as always. I have not tried the VBA yet but all formulae seem to work great. Depending on how I want to process the output, I will pick one solution and stick with it.

I really appreciate the help. Wish you all a great weekend!