-2

So I have two sets of three columns that I need to chart. I attached the sheet as reference, but here is a picture: https://i.stack.imgur.com/IZwEo.png

These two sets of three columns are from two separate reports and I want to combine them together to make a chart.

My goal with the chart is to input which "SKU" I am charting and it gives me a chart of "Sales" for that SKU sorted by "Date".

So, for example, if I inputted "1" for the SKU, it would output a chart showing that "SKU '1' sold 18 units on 7/13/17 and sold 16 units on 7/20/17".

I'm fairly new to excel, so I think I'm probably not understanding the formats for charts which may be causing my difficulty.

Thanks for the help! Let me know if you need more information.

Link to the spreadsheet: https://www.dropbox.com/s/hcd7l2n8gsdjibj/Workbook.xlsx?dl=0

ChillAnon
  • 11
  • 4

2 Answers2

1

One way to do solve your problem is to rearrange your data like this:

SKU 7/13/2017   7/20/2017
1      18         16
2      20          2
3      54          4
4      75         15
5      55         22

Then select it and make a column chart. The initial plot includes the SKU values, so I select that data and removed it using the DELETE key. The result gives what you requested. See the animated gif -- note that in the final graph, I added titles and reformatted.

enter image description here

Tony M
  • 1,694
  • 2
  • 17
  • 33
  • Is there an easy way to automatically sort my columns like that? Maybe a VBA script or something you'd recommend? – ChillAnon Jul 20 '17 at 18:47
  • Yes, you could definitely automate rearranging the columns. In the example you provided, it's really just shifting some columns, except for the dates need to get into a row; paste with transform could be helpful for that. I'm not clear how extensive your data is, so it's hard to get more specific. Yes, a VBA script could take care of really difficult or large situations -- if that's the case, maybe you could post another question for that and if I see it, I'd be happy to help. – Tony M Jul 20 '17 at 20:28
  • Okay. I'm going to look at all my options for this sheet and I will ask another question if need be. Thanks for the assistance! – ChillAnon Jul 24 '17 at 17:34
  • Posted another thread. https://stackoverflow.com/questions/45288516/transposing-sets-of-columns-on-top-of-each-other-in-excel – ChillAnon Jul 24 '17 at 19:27
0

There are many ways to do this. A quick way is to set up a small table that uses the SKU number inputted (in yellow below), and a VLOOKUP() formula to get the info you need.

If your data looks like this, you can set it up like this:

enter image description here In column B, do:

=VLOOKUP($A9,$A$2:$F$6,2,FALSE)

and in C, D, E, change the 2 to 3, 5, and 6 respectively.

Then create a bar chart. The data selection is a little tricky, but logical.

Add a Legend Entry Series where you choose the TITLE as the Date (in C9 and E9 above), and the data to be the sales B9 and D9):

enter image description here

Then do the same for the other sales info.

Then, for the "Horizontal (Category) Axis Labels" just click a cell that says "SKU".

Add the legend and any labels you want, and you should get:

enter image description here

Then, change the SKU number (in Yellow) to any of the available options and the chart will update!

enter image description here

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • Thanks! This is a good solution for my issue. My only conundrum is how I'm going to design this work work as more and more sets of columns are added. – ChillAnon Jul 20 '17 at 19:15
  • @Cylias - Ah, I didn't save it. Sorry :( With regard to your question on more info, how is the data added? In groups going off to the right? – BruceWayne Jul 20 '17 at 20:17
  • Sorry for the late reply. Through a VBA script, the columns "SKU". "Sales" and "Date" are added in that order to the first blank column on the left. – ChillAnon Jul 24 '17 at 15:44