Summarising Data - the best wayto proceed?

[andrewc]

Hi,

Rather than looking for an answer to a specific problem, I have please a general question about the best way to proceed:

As shown in the attached file I have source data for a list of unique product names in A1:O12 from which I want to extract summary data depending on whether the data is “Lagged”, “Non-Lagged” or to be “Excluded” (column N). The range that the source data covers will change from day to day (the number of rows will change) and some products may change from being defined as “Lagged”, “Non-Lagged” or to be “Excluded”. Also, the columns with “Rel” in the title are calculated results using the numbers from the source data.

Can someone please suggest the best way to extract dynamically summary data for any of the three flavours in column N, taking into account the fact that the size of the source data range changes from day to day, and also to include the “rel” formulae. I would like the summary table to be as dynamic as possible in response to new source data, without recourse to VBA. I’ve considered data tables, Pivot Tables and just a table of data that I manage manually each day but none seems to provide a great solution.

Thanks!

[Fotis1991]

1 way using Array formula.

Please Login or Register  to view this content.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[andrewc]

That's excellent, thank you!

May I please ask one more question:

If I were to sort this data, say in descending order of column M ("rel-ytd"), is there any way for the "n/a" results to appear at the bottom of the sorted table?

Thanks

[newdoverman]

I would make the source data a table and filter on column N for example "Non-Lagged", hold down the Alt key and press ; (Alt + to copy visible cells then place the cursor where the copied data was to go and Paste Special, ALL.

This method is fast, efficient, flexible and no formulae to mess up.

[Fotis1991]

You can sort the data in Sourse data sheet and then your results are as you need...

[andrewc]

Thanks very much

[newdoverman]

Glad you have a solution

Thank you for giving feedback.