Chart Data Macro

[unwantedoz]

Hello, I need help making a macro. I spent an hour or two couple of hours googling how to make macros but I still have no idea how to do what I want to.

Anyway I want display a chart on a website. I will be using fusioncharts.js to make it as trying it I have found it really easy to use.

My problem is the data is not in the format it needs. It needs it in either JSON or XML as a single string format.

So the data I record is a csv which looks like this in excel:

Date Cost $
23/08/2015 78
24/08/2015 58
25/08/2015 112
26/08/2015 97

So I need a macro that will convert that simple 2 column data into either of the following:

JSON in the form:

{
"label": "23/08/2015",
"value": "78"
},
{
"label": "24/08/2015",
"value": "59"
},
{
"label": "25/08/2015",
"value": "112"
},
{
"label": "26/08/2015",
"value": "97"
}

or single string XML in the form:

<set label='23/08/2015' value='78' /><set label='24/08/2015' value='59' /><set label='25/08/2015' value='112' /><set label='26/08/2015' value='97' />

Any help would be appreciated.

[blzbub]

Hi, not sure about the macro version (although someone will) but a quick fix is to let Excel do it as follows, hope it helps :-

=LEFT(A2,10) =RIGHT(A2,LEN(A2)-11)
23/08/2015 78 23/08/2015 78
24/08/2015 58 24/08/2015 58
25/08/2015 112 25/08/2015 112
26/08/2015 97 26/08/2015 97
24/08/2015 58 24/08/2015 58

NOTE - it would fail if your date for the 1st of a month shows as 1/10/2015 but would work with 01/10/2015

(sorry the table formatting in the data above went crazy, basically if you put your data in Column A then the 2 formulas follows in Columns B then C)

[unwantedoz]

Thanks for trying, i'm not sure what those two formula are meant to be doing.

I need the data which is just the date and a number in the next cell changed to include all the additional information.

So if the data was

Column 1_______Column 2
25/08/2015_____112

I need the two pieces of data in that entry (the 25/08/2015 and the 112) converted to (doesn't matter where on the page it is or if a new sheet etc) an entry which takes up 1 Column & 4 rows being

Column X
{
"label": "23/08/2015",
"value": "78"
},

or into a single cell (including the < and > at the start and end)
<set label='23/08/2015' value='78' />

Here is an example workbook with the data manually change to how it needs to look bellow it.

For the XML type it doesn't matter if each entry is in a separate cell (next to each other, they can not be under each other) or if they are all added together and put into the one cell.

[blzbub]

Think i have solved it but can't seem to attach the updated spreadsheet.

However here is the code which you can add to a macro enabled command button - or add the code into a module and run as a macro.

Please Login or Register  to view this content.

Cheers

[unwantedoz]

Thank you blzbub, that works getting the data into the xml format except it places all the entries under each other and they need to be next to each other.

This cross post macro does the job http://forum.chandoo.org/threads/mac...rt-data.26066/

Thank you for your time