Updating Row Based on Value In Column

J

jurgensen

New Member
Joined
Jan 13, 2010
Messages
6
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
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]#
[/TD]
[TD]11-Oct
[/TD]
[TD]11-Nov
[/TD]
[TD]11-Dec
[/TD]
[TD]12-Jan
[/TD]
[TD]12-Feb
[/TD]
[TD]12-Mar
[/TD]
[TD]12-Apr
[/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD]200
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]50
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#
[/TD]
[TD]12-May
[/TD]
[TD]12-Jun
[/TD]
[TD]12-July
[/TD]
[TD]Total
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD][/TD]
[TD]1000
[/TD]
[TD]500
[/TD]
[TD]1500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD]60
[/TD]
[TD]70
[/TD]
[TD]80
[/TD]
[TD]210
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#
[/TD]
[TD]11-Dec
[/TD]
[TD]12-Jan
[/TD]
[TD]12-Feb
[/TD]
[TD]12-Mar
[/TD]
[TD]12-Apr
[/TD]
[TD]Total
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1000
[/TD]
[TD]1000
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]80
[/TD]
[TD]2500
[/TD]
[TD]2500
[/TD]
[TD]2500
[/TD]
[TD][/TD]
[TD][/TD]
[TD]7500
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


After Step 1
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]#
[/TD]
[TD]10/1/11
[/TD]
[TD]11/1/11
[/TD]
[TD]12/1/11
[/TD]
[TD]1/1/12
[/TD]
[TD]2/1/12
[/TD]
[TD]3/1/12
[/TD]
[TD]4/1/12
[/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD]200
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]50
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#
[/TD]
[TD]5/1/12
[/TD]
[TD]6/1/12
[/TD]
[TD]7/1/12
[/TD]
[TD]Total
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD][/TD]
[TD]1000
[/TD]
[TD]500
[/TD]
[TD]1500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD]60
[/TD]
[TD]70
[/TD]
[TD]80
[/TD]
[TD]210
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#
[/TD]
[TD]12/1/11
[/TD]
[TD]1/1/12
[/TD]
[TD]2/1/12
[/TD]
[TD]3/1/12
[/TD]
[TD]4/1/12
[/TD]
[TD]Total
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1000
[/TD]
[TD]1000
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]80
[/TD]
[TD]2500
[/TD]
[TD]2500
[/TD]
[TD]2500
[/TD]
[TD][/TD]
[TD][/TD]
[TD]7500
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


After Step 2
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]#
[/TD]
[TD]10/1/11
[/TD]
[TD]11/1/11
[/TD]
[TD]12/1/11
[/TD]
[TD]1/1/12
[/TD]
[TD]2/1/12
[/TD]
[TD]3/1/12
[/TD]
[TD]4/1/12
[/TD]
[TD]5/1/12
[/TD]
[TD]6/1/12
[/TD]
[TD]7/1/12
[/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD]200
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]50
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1000
[/TD]
[TD]500
[/TD]
[TD]1500
[/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]60
[/TD]
[TD]70
[/TD]
[TD]80
[/TD]
[TD]210
[/TD]
[/TR]
[TR]
[TD]32
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1000
[/TD]
[/TR]
[TR]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]80
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2500
[/TD]
[TD]2500
[/TD]
[TD]2500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7500
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
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!!
 
You must log in or register to reply here.

Similar threads

T
  • Question Question
Running Average of last n row if sum is between a certain value
Replies
1
Views
93
Peter_SSs
Peter_SSs
D
  • Question Question
How Do I Identify the cell with the right cost?
2
Replies
10
Views
156
Peter_SSs
Peter_SSs
J
  • Question Question
Sort X-Axis in PivotChart
Replies
2
Views
126
GraH
GraH
I
  • Question Question
Transfer Data based off a criteria to certain Column
2
Replies
11
Views
175
ItalianPlatinum
I
S
  • Question Question
VBA Separate Day and Time to Another Column if condition meet
2
Replies
10
Views
166
SamKhem
S

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top