Very tricky Chart problem VBA

[Toufic Leen]

I need to create a chart with VBA, this Chart was very challenging with me, it is a comparison for two weeks statistics in so I have a column for the week, another one for the name of the location, another one for sales and the last is returns!

creating the cart is easy, what drives me crazy is two things I need to do:

1. how to change the labels? I need the lable to show bothe headers of column A & Column B:
   
   https://app.box.com/s/jlgueokq17sweedjlz84
2. I need to sort the Bars per the Site Name (Column A) and color it per the week (Column B): I though by sorting ill solve the problem but I failed!!!
   
   https://app.box.com/s/r9vq9oph68wxmtfkk3v4

the files is here
https://app.box.com/s/tl83vah9uzd5i7m7zo1v

Notice: number of rows is even (because each location repeats) but not limited to 24 as the example!

I think VBA is the only solution for this matter
Thanks a lot for any help

 

[SirJB7]

Hi, Toufic Leen!

The problem is that you can't sort date wise when the column contains text values. I'd suggest you to insert a new column B, format it as date dd/mm/yyyy and put there the starting day of the week, and in column C (actual B) place this formula:
=TEXTO(B2;"dd/mm/yyyy")&" to "&TEXTO(B2+6;"dd/mm/yyyy") -----> in english: =TEXT(B2,"dd/mm/yyyy")&" to "&TEXT(B2+6,"dd/mm/yyyy")

BTW you actually have dates in format MDY (rows 1:14) and DMY (rows 15:25): you should standardize them.

Regards!