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:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Fruits[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Veggies[/TD]
[/TR]
[TR]
[TD]Celery[/TD]
[/TR]
[TR]
[TD]Carrots[/TD]
[/TR]
[TR]
[TD]Spinach[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Nuts[/TD]
[/TR]
[TR]
[TD]Pecan[/TD]
[/TR]
[TR]
[TD]Walnut[/TD]
[/TR]
</tbody>[/TABLE]


I would like to know how many items are within each block, along with the block name. So, my desired output is:
[TABLE="width: 141"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 160"]
<tbody>[TR]
[TD]Fruits[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Veggies[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Nuts[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fruits[/TD]
[TD]=IF(AND(A1="",A2<>""),"Header",IF(A2<>"","Value",""))[/TD]
[TD]=IF(B2="Header",A2,IF(B2<>"",C1,""))[/TD]
[TD]=IF(AND(A1="",A2<>""),C2,"")[/TD]
[TD]=IF(D2<>"",COUNTIF(C:C,D2),"")[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Veggies[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Celery[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carrots[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Spinich[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nuts[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pecan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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,"")

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fruits[/TD]
[TD]Header[/TD]
[TD]Fruits[/TD]
[TD]Fruits[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Value[/TD]
[TD]Fruits[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Value[/TD]
[TD]Fruits[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Value[/TD]
[TD]Fruits[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[TD]Value[/TD]
[TD]Fruits[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[TD]Value[/TD]
[TD]Fruits[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Veggies[/TD]
[TD]Header[/TD]
[TD]Veggies[/TD]
[TD]Veggies[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Celery[/TD]
[TD]Value[/TD]
[TD]Veggies[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carrots[/TD]
[TD]Value[/TD]
[TD]Veggies[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Spinish[/TD]
[TD]Value[/TD]
[TD]Veggies[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nuts[/TD]
[TD]Header[/TD]
[TD]Nuts[/TD]
[TD]Nuts[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Pecan[/TD]
[TD]Value[/TD]
[TD]Nuts[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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!