Summarize data within Spreadsheet

[bigroo1958]

Hello,

I am looking for a way to summarize the data presented on this spreadsheet, usually this spreadsheet has 90,000 rows, but I have trimmed it down.

For each serial number on the "Raw" sheet there is a meter read for each day of the month. What I wish to find do is pick only the first read of each month and then the last read of each month for each serial number and calculate the volume for columns D and E.

I have tried a pivot table but get so far and get stumped.

Any help or suggestions how to tackle this would be welcomed!!
I have found a work around but 2 hours later, this is not a practically solution month over month!!
Thanks

[FDibbins]

see if the attached will give you what you want? I created a small table to the right (you can move it if you want), with dates along the top and SN going down. you can extract unique SN's by copy/paste to the new column and then using Remove Duplicates (or I can give you a formula, but with 90000 rows, the arrays in that table may already slow things down)

[bigroo1958]

Thanks for the quick reply FDibbins!!

Yes, this appears to be what I want, I just need to digest the formula so I can understand what you have done.

How would you tackle the Column E volume calculations, would this be another table as per the first one or will you add the column D and E meter type into the table somehow?

Thanks

[FDibbins]

Yes, pretty much a 2nd table, with the references adjusted accordingly.

Note that I have used ARRAY formulas there, which need to be entered using CTRL SHIFT ENTER, not just enter (if you edit them in anyway)

We could do away with the array formula if it is slowing you down with those 90 000 rows, by changing the helper in F to..
=A2&INT(C2)
and then change the formulas in that table to...
=IFERROR(INDEX($D$2:$D$2000,MATCH($H2&I$1,$F$2:$F$2000,0),1)-INDEX($D$2:$D$2000,MATCH($H2&(J$1-1),$F$2:$F$2000,0),1),"")

(actually, I think this is a better way to do it anyway)

[bigroo1958]

Hi Fdibbins,

I have being doing some reading on Index and Match and also trying it out on my example.
I tried just pulling over the color read only from column E. I manage to get a return for June 2013, but when I grabbed the column M13:M34 and copy formula to the right or left the formula breaks and I receive #Ref! errors.

Can you look and advise what I am doing wrong? I am trying to return just the read at present.

[FDibbins]

OK 1st, I did say that those formulas were ARRAY formulas and needed to be entered using CTRL SHIFT enter, and not just enter - the formula will be surrounded by {} if done propertly

2nd, in post # 4, I suggested a simpler (regular) formula along with adjusting the helper column, you need to use that instead.

For Index/Match to work, all ranges have to match.

with that said, use the formula I suggested in F2, copied down...
=A2&INT(C2)
Then use this in I2, copied down and across...
=IFERROR(INDEX($D$2:$D$2000,MATCH($H2&I$1,$F$2:$F$2000,0),1)-INDEX($D$2:$D$2000,MATCH($H2&(J$1-1),$F$2:$F$2000,0),1),"")

Then for the 2nd table, use this in I13, copied down and across...
=IFERROR(INDEX($E$2:$E$2000,MATCH($H13&I$12,$F$2:$F$2000,0),1),"")

In the formula you used, you were only using the SN, you had not included the date

[bigroo1958]

Thanks FDibbins for your help, I am classing this thread as Solved!!

It has taken me a little well to digest the formula, but I beleive its under my belt and now in my toolbox of tricks!!