Turning data with dates into a chart

[Abdullah]

Hello,

I was hoping to get some help manipulating data. I have a BUNCH of data in the following format (a small snippit of an excel page there's A LOT of data)

2486690 3/9/1948 3/1/1949
694 4470


2487300 1/1/1950 4/13/1955
19000 13000


2487500 3/14/1905 3/15/1929
24800 8180
Basically I wanted to leave the first column as it is, the first column is just ID numbers...each ID number has 2 rows associated with it,

The top row has a date, anywhere from the year 1900 (apparently excel doesn't recognize anything previous to 1900 as a date) to the year 2008.
The bottom row is just some data about the ID number.

I wanted the top row to be the years, so it would end up as row of 109 cells (the first cell blank and then 1900 to 2008), and the ID numbers to run down the first column (as they do now). And the data contained under the date currently to be put into the chart in its correct place.

I assumed that putting the dates across the top wouldn't be difficult, I think I have to have a loop going through all the data looking at the 4 digits of the date to get year, then copy the data below the date, and paste it into column (year+1).

Does that sound like the approach I should be using? I assume that I can just take the 4 digits from the right of all the dates and have it be recognized as a number, am I right in that assumption?

Any help anyone can give me would be greatly appreciated

Thanks in advance,
Abdullah

[Abdullah]

Sorry, that data didn't end up looking like I assumed it would....it is

2486690 3/9/1948 3/1/1949
694 4470


2487300 1/1/1950 4/13/1955
19000 13000


2487500 3/14/1905 3/15/1929
24800 8180

[Abdullah]

690--3/9/1948--3/1/1949
-----------694------4470


300--1/1/1950--4/13/1955
--------19000------13000


500--3/14/1905--3/15/1929
----------24800-------8180

Sorry about all the dashes, it was the only was I could get that to line up

[MaximS]

Can you please attach sample file with source data and desired output?

[rbrhodes]

Hi Abdullah,

Don't know exactly how your data is layed out but here's an example of what I think you have

[Abdullah]

A sample file is attached, as you can see the data isn't consistent from ID number to ID number (ID numbers are down the first column).

Next to the ID number is a collection of dates and data for each date.

I'm hoping for a sheet with ID numbers down the first column (as they are now) but I would like the second column to represent 1900 the third 1901 the fourth 1902 etc till column 109 which would be 2008.

I would like the data at each ID number to match the ID number but be spaced into the correct year...lack of year, years with no data, or zeros for data I would like to just leave blank.

So at a glance you can see each ID numbers down the first column (y-axis) and go across to the correct year (x-axis) and see the data value for that year and that ID number.

I hope this helps explain what I'm trying to achieve...Thank you already for the help.

RBRHodes...that is very similar to what I have and what I am looking for as an output, but the output would not have the exact date, it would take the data under the exact date and put it into the correct year.

Please see the attached file with sample data

[mdmackillop]

Add the header data first

Sub Allocate()
Dim Rng As Range
Dim Ref As Range, Yrs As Range
Set Ref = Sheets(2).Columns(1)
Set Yrs = Sheets(2).Rows(1)
Set Rng = Intersect(Columns("B:IV"), ActiveSheet.UsedRange)
For Each cel In Rng.SpecialCells(xlCellTypeConstants)
If IsDate(cel.Text) = False Then
rw = Ref.Find(Cells(cel.Row - 1, 1)).Row
col = Yrs.Find(Split(cel.Offset(-1), "/")(2)).Column
Sheets(2).Cells(rw, col) = cel.Value
End If
Next
End Sub

[mdmackillop]

On checking, I noticed your data has more than one entry for some years. This totals relevant years, highlighted yellow.

Sub Allocate()
Dim Rng As Range
Dim Ref As Range, Yrs As Range
Set Ref = Sheets(2).Columns(1)
Set Yrs = Sheets(2).Rows(1)
Set Rng = Intersect(Sheets(1).Columns("B:IV"), Sheets(1).UsedRange)
For Each cel In Rng.SpecialCells(xlCellTypeConstants)
If IsDate(cel.Text) = False Then
rw = Ref.Find(Cells(cel.Row - 1, 1)).Row
col = Yrs.Find(Split(cel.Offset(-1), "/")(2)).Column
If Sheets(2).Cells(rw, col) <> "" Then
Sheets(2).Cells(rw, col).Interior.ColorIndex = 6
End If
Sheets(2).Cells(rw, col) = Sheets(2).Cells(rw, col) + cel.Value
End If
Next
End Sub

[Abdullah]

I only need one entry per year, so adding the values isn't necessary, I just assumed copying and pasting into the proper cell would work...

When I tried to run that code on the sheet with my data, I got a
run-time error '91'
Object variable or With block variable not set

And it complained about the line:

rw = Ref.Find(Cells(cel.Row - 1, 1)).Row

This may sound like a dumb question, but what should I set rw to?

[rbrhodes]

Hi Abdullah,

You don't set rw the code does.

With apologies to md, here's his code in my example.

1) Copy the Column numbers to Sheet2, Col A row 2 to ***

2) Fill Sheet2, Col B row 1 ---> with years

3) Run the macro

Note:

- The macro clears any existing data from sheet 2
- The macro does not copy zeros to sheet 2
- It will run from either sheet

You could have the sub populate the header row and column too...

[mdmackillop]

No apologies necessary.
BTW Both items here should be qualified
Set Rng = Intersect(Sheets(1).Columns("B:IV"), Sheets(1).UsedRange)

[Abdullah]

I'm sorry to sound like such a newbie...but I put that code into an excel sheet and couldn't get it to run...I kept getting that run time '91' error about setting RW.

So then I just copied and pasted new data into sheet 1 of your example rbrhodes, and that's when I got an error complaining about an unqualified variable and complaints about line
Set Rng = Intersect(Sheets(1).Columns("B:IV"), Sheets(1).UsedRange)

I'm not really sure how to qualify a variable, or how to get this code to work with new data...as I've said I used the code in other excel files and I copied and pasted new data into RB's example.

Sorry to be such a bother...but any help would be appreciated.

[mdmackillop]

Can you post your new data so I can test it?

[Abdullah]

I finally have all my data together. Shown below is a very small sample. Some more info that might help: I have many worksheets with large (and varying) amounts of data. For example, one worksheet has 1130 rows, another has 1680. In regards to having multiple values for one year, the code should look at the day and month to determine what year to assign. This is streamflow data and is sorted by water year. A water year run October through Septmeber. For example, the code should read 9/30/92 and assign year=1992; 11/15/92 should assign year=1993.

Thanks very much for your help.

10100004/11/19514/27/19524/1/19534/23/19544/20/19555/15/19564/22/19574/25/1958 1900019200210002780025400146001600034200 10100705/1/19844/27/19854/2/19864/1/19874/10/19884/7/19894/28/19904/10/1991 46503420492086801760261037403640 10105005/9/19475/9/19484/17/19494/23/19504/12/19514/30/19524/1/19534/24/1954 6870036600292003600045100382005110058600 10110009/18/19325/5/19334/26/19345/2/19355/3/19364/30/19374/21/19385/11/1939 1650023400183001080018100126001110021500 10135005/7/19045/7/19055/11/19065/21/19075/9/19085/8/19304/24/19314/23/1932 84203170856078109010938051107530 10158004/25/19584/27/19595/16/19605/16/19617/17/19625/6/196311/10/19635/2/1965 21500734013100206001020012800158004370 10165004/27/19524/1/19536/29/19545/5/19555/15/19564/24/19574/25/19584/27/1959 5600118001660057103570373081803190 10170004/13/19314/13/19325/4/19334/21/19345/1/19353/22/19364/30/19374/22/1938 1350020900240003620023300378001550017500

[Abdullah]

See the attached worksheet for properly formatted data.

[rbrhodes]

Hi Abdullah,

I think this will do it...

One note tho: As you are splitting the year into Oct last year to Sept this year you need to have 1899 in the list. I've added it here. I think the comments in the code will expalin everything else. Let me know if not.

The button on sheet 1 is from the forms toolbar, just right click and 'Cut' to get rid of it.

[Abdullah]

Problem solved! With some minor tweaks, I was able to apply your code to all of my workheets. Thank you VERY much for your help!!

Abdullah