Updating Row Based on Value In Column

[jurgensen]

Hello All,

We use an old web-based tool at my office that can't export Excel reports. So I'm devising a way to copy an entire page, straight from the browser, and clean it up so that the data can quickly be utilized for other purposes.

The data that I'm using is financial information broken down by month in multiple sections. When I copy and paste from the web portal, the headers of the data (month and year) show up in such a way that the day of the month is being used as an indicator for the year. For example, 4/13/12 would be represented as "13-Apr" (indicating April 2013.) [I guess our developers are banking on a new system by the year 2029!]

Step 1
What I would like to do is have a macro that will go through all the rows with these dates and apply the following formula to them: =DATEVALUE(CONCATENATE((MID(TEXT(A1,"yyyy-mm-dd"),6,2)),"-01-",(MID(TEXT(A1,"yyyy-mm-dd"),9,2)))). [In this case A1 would contain a date that needs to be converted.] This will convert them into fields that will be recognizeable as correct years.

Step 2
My next step would be to organize all the columns of data by month and year under one master header.

I created three tables showing where I'm starting, then, what I would expect data to look like after step 1 and step 2. If anyone can help me with either, I would appreciate it.


Original Data

#	11-Oct	11-Nov	11-Dec	12-Jan	12-Feb	12-Mar	12-Apr	Total
1						100	200	300
5		50						50
10		100						100
#	12-May	12-Jun	12-July	Total
25		1000	500	1500
27	60	70	80	210
#	11-Dec	12-Jan	12-Feb	12-Mar	12-Apr	Total
32					1000	1000
100
80	2500	2500	2500			7500

<TBODY>
</TBODY>

After Step 1

#	10/1/11	11/1/11	12/1/11	1/1/12	2/1/12	3/1/12	4/1/12	Total
1						100	200	300
5		50						50
10		100						100
#	5/1/12	6/1/12	7/1/12	Total
25		1000	500	1500
27	60	70	80	210
#	12/1/11	1/1/12	2/1/12	3/1/12	4/1/12	Total
32					1000	1000
100
80	2500	2500	2500			7500

<TBODY>
</TBODY>

After Step 2

#	10/1/11	11/1/11	12/1/11	1/1/12	2/1/12	3/1/12	4/1/12	5/1/12	6/1/12	7/1/12	Total
1						100	200				300
5		50									50
10		100									100
25									1000	500	1500
27								60	70	80	210
32							1000				1000
100
80			2500	2500	2500						7500

<TBODY>
</TBODY>

A few notes:
1. All the rows with dates to be reformatted contain a "#" symbol. This could be used as an indicator for where to apply the formula.
2. Column A sometimes contains other text in between "#" and the various numerical data, I deleted it in my example for the sake of neatness.
3. The spacing between various sections and the numbers of rows in each section of data are not always the same. They are not fixed and are subject to change.


Thanks very much!!