Generate Report Per Plot Based on Number of Times a Specific Value Appears on a Given Day

Hi all,

This is a hard one for me to explain, so bear with me...

I'm hoping to generate a report based on the number of times each plot (column F) appears within the range. I知 also hoping to sum the total of each plot (column D) based on the number of times it appears.

However, I知 faced with a couple of problems:

1. Some rows contain more than one plot (separated by a comma). In this instance the time spent, and therefore cost, would be divided by the number of plots/values within the same cell to fairly reflect the time/cost spent. This is reflected in columns G and H.


2. I知 also hoping to return the results based on a few separate conditions:
1. Based on a yes/no value in column E. Again, I want to see these results returned as separate columns on a new report.
2. Based on the particular site shown in column C.

In sheet 2 you will see the results I'm trying to output to give you a better idea.

I知 currently trying to achieve this for a larger subset of data and would therefore prefer if I could have a report auto-generate for me, if possible.

Thank you in advance. Let me know if none of the above is clear!

Luke

Glenn this is awesome, thank you.

Just a couple of things with this:

1. Would you be able to adjust the 'number of days' columns to reflect the half and part days where multiple plots exist for the same record as you have with the cost? At the moment I think these are being overcounted.
2. Would it be possible to auto-generate plots based on their values in sheet 1? I've included these plots manually for illustrative purposes. No worries if not, as I can add these in myself in anticipation for later records being added.

Many thanks again

As long as the site name and corresponding plot numbers are auto-generated individually (similar to how I've presented) in sheet 2 then there won't be much else required aside from the totals for cost/time which I've included. Dates I don't think will be necessary as these will be available in sheet 1 if needed.
Also, if possible, I'd like to count the total number of times the same plot numbers appear within the first sheet, as a reference.

Oh nuts. Knew it would never be that simple haha.

Duplicates plot numbers will most certainly occur across multiple sites. This is very common!

Hi guys,

Little bit late returning to the party...

Thank you both so much for your combined efforts here.

I'm gonna incorporate this into my larger subset of data now and see how it works for me.

Last thing (I know, I know), would it be possible to include a count of the total number of times each plot number (with corresponding site) appears, and if possible incorporate this into sheet 2?

Hi Guys,

Had a chance to test these formulas, but came across a couple of snags:

1. Totals not adding up to the actual cost (in sheet 3) when using a larger data set (i assume the way the plot numbers are sequenced/formatted in each cell could be a problem?)
2. When I sort the table It changes the total figure in the second report. This happens when I sort by date in the original data set. When I sort by 'site' the total amount is closer to the actual amount.

I've created two new sheets (sheet 3 and 4) which shows what I mean when using each formula, including their totals. As mentioned, they all sum to the same amount, but will all change when I filter in sheet 3.

I'm sure there's an easy explanation for this (I'm probably being a donkey). Let me know if there's an easy fix!

Glenn this is awesome. I owe you multiple beer when/if I'm ever in Ireland!

I noticed a couple of duplicate plot numbers in the same cell skewing the results, but everything adds up perfectly now!

Here's that one last thing (promise)... would you be able to revisit the number of plots (highlighted in orange) in sheet 3. I noticed that I'm counting all plots regardless of the site, so am undercounting the total number of plots and corresponding site (if that makes sense?). I believe this corresponds with the defined name you created for me previously.

Oops - my bad.

Should be there now.

As is, it obviously works for me, but only when filtering to individual site names.

Plot numbers occurring in both chargeable and non-chargeable for the same site isn't a problem. It's literally just the duplicate plots in the presence of multiple sites that is the issue since it's undercounting the total. This is obviously skewing my averages (to the left of the 'number of plots') as a result.

Not a major issue if this can't be rectified. Will just have to make do with the number of plots and averages on the individual site level instead of an overall / client level!

Can't believe I missed this...

Everything is there in sheet 4 in order for me to apply averages as is - what a muppet!

Thanks again. You've truly gone above and beyond. This is awesome!

Hi Glenn,

Sorry to be bugging you with this again. Firstly, my spreadsheet has been working fantastically and have had no issues up to this point. However...

Since inputting the last batch of data, the FILTERXML formula (in sheet 2) is returning #VALUE error. I noticed when removing last months data that I'm no longer met with this error. The same is true when I remove 80 or so rows regardless of the date or their contents.

I assume this formula has a limit on how much data it can parse based on this? Is there any way to rectify this?

Hope you're well by the way!!

See attached. I'm not overly worried about the columns after column D, just the formula in C4 that I'm struggling with.

This is great Glenn, thank you.

Ideally wanted this as a continuous sheet, but is not the end of the world.

In future, I will have to retire the current workbook and create a new one. Should be easy enough to bring two sheets together at a later stage!