Complex Count Unique, Filter and Pivot Macro

Hello,

I'm new to this forum so please excuse any faux pas I might make!

I have been away from VBA for some time but now find myself having to do something quite complex and feel like a complete dummy. I can get parts of this to work but can't pull it all together in one macro!

I have a spreadsheet with some columns (attached) and I need to be able to run a macro that:

1. Scans column B for unique IDs (the whole column as rows will be added and subtracted constantly)
2. Looks to corresponding cells in Column J of unique IDs from Column A and counts the row if J <26 and also <9 (with <26 inclusive of <9) (obviously each unique ID row counted only once - do not want each row for every ID)
3. Excludes any rows where the unique ID in Column B has only one corresponding result of "7-PTEN LOH" in Column O
4. Then, pivots results by Column E. However, Column E needs to be calculated items before pivot, due to old data entry and new data entry being different (so will need to add results for "3-Lung" and "Lung" together, etc.). Pivot table should be a count of unique IDs with results of Column J going down and Column E going across.

All IDs, etc. from this data have been removed and replaced with made-up combinations but the data types are the same.

Does this make sense?

Thanks for any help you can offer.

data example.xls