Reformatting Data for a line graph

I have a log of information that's sorted first by division, then by date. it's essentially made so that the first of the month, it will grab the data i need and make a monthly snapshot using the copy row feature. it looks basically like this:

I want to make a line graph that will be one line for each division to show the monthly change per division. to look like this:

From what i understand, to do that, the data will need to look something like this instead:

Is there a way that i could transpose this information? I believe that if i i had the dates already filled in to the 2nd sheet, i could use INDEX(MATCH) to look up and fill it in, but I'm not sure how I would fill all the unique dates into the DATE1 column or is there a better way to do it that I'm not considering.

Thanks in advance!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

You are going to need to manually prefill the dates (workaround for keeping the graph clean below).

From there you would use an INDEX/COLLECT to bring the appropriate numbers in.

=INDEX(COLLECT({Source Sheet Number Column}, {Source Sheet Date Column}, @cell = [Date Column]@row, {Source Sheet Division Column}, @cell = "DIV1"), 1)

Then you can create a report that is looking at the reformatted sheet and pulling in all columns. Filtering it to only show rows where the Date column is in the past should give you a dynamically growing chart as time progresses.

Z.Win

@Paul Newcome

Thanks for that. It's a bit disappointing though. I was hoping there a way to set it up once and just let it run on it's own.

Paul Newcome

That's pretty much how it works. Since you are only doing monthly breakdowns, you could set up the sheet so that it has room for many many years worth of dates. Enough dates so that if the same system is in place by the time you run out, it may be time for an upgrade anyway.

Z.Win

Yeah. It's a great tool for certain things, but some other things, that seem like should be so simple, it can't do.

Thanks

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.