Macro to find last entry in a series and count number

Hi,

I have uploaded a sample spreadsheet with what I am trying to do. I want to get a list of the last chapter(f) of each book (a) I have highlighted the ones that I am interested in in yellow. I want to get a list of them with the number of times each appears. This is made difficult for me as the number of chapters in each book varies so the position of the last chapter for each book varies in the worksheet each time. I have conditional formatting on to make the worksheet look as it does now and don't know if this might help?

Basically I would like to see

Birch 2
Oak 1
Beech 1
Pine 1

but obviously my document is much larger at around 30,000 rows so it's far to large to carry this out manually.

Very grateful for any help

Hi,

One approach is as in the attached.

Create two helper columns AC & AD on sheet2. On sheet 3 use standard data filtering to extract a unique list of chapters in column B and use the formula in column C to get the counts.

I'd also advise you to avoid merged cells as you have on sheet1. These are more trouble than they're worth and cause many problems. Also where you have a de facto database as you have here which you want to analyse then always have column labels at the top of the data.