Excel - Graph two sets of account balance histories together (dates may vary slightly)

I've got the account transaction data for two different accounts and I'd like to graph them together. Unfortunately, the data isn't daily, it's updated whenever there's a transaction, so while they "share" the key of date, they don't share all the same values for that key. However, there's obviously a lot of commonality

How can I graph these both as separate datasets on the same chart since their key isn't a perfect match. but it similar?
LVL 28
Ryan McCauleyData and Analytics ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I would combine them onto a third spreadsheet which does list them by day, and then use SUMIF to copy them into this third sheet, and then do the graph from that.

So you would have columns:
Date
Account 1 Value
Account 2 Value
0
Glenn RayExcel VBA DeveloperCommented:
If your x-axis is represented by dates, you can easily plot two separate ranges of data across a date range, regardless of the where/when the data points occur.

If you use a scatter chart (X-Y chart), you can choose each accounts dates and transaction amounts for the X and Y values, respectively and combine the plots with no additional steps.  This example has data from two separate sheets:
XY plot
If, however, you want to plot with a bar/column chart, you'll have to create a third table as Phillip Burton suggested.  One column would have a continuous range of dates and each of the other two columns would have a formula to return that accounts transaction amount for that date.  For example:
combine account informationIf the data is discrete (i.e., one transaction/balance total per day) then you can use VLOOKUP to return the amount, otherwise you'd use SUMIF.  If the source worksheet is titled "Account A" and has dates in column A and the amounts in column B, then the formulas in the combined sheet would be:
B2:  =VLOOKUP(A2,'Account A'!A:B,2,FALSE)   or
B2:  =SUMIF('Account A'!A:A,A2,'Account A'!B:B)

and similarly for Account B
C2:  =VLOOKUP(A2,'Account B'!A:B,2,FALSE)   or
C2:  =SUMIF('Account B'!A:A,A2,'Account B'!B:B)

And a combined bar chart would then look like this:
combined bar chart
Note that these two charts plot the EXACT same data points, so now you have to consider how readable or useful these are to decide what kind to proceed with.  I've attached an example workbook with all this for you to test.

Regards,
-Glenn
EE-CombinedAccounts.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.