software

HI
I am relatively new user of excel and hopefully there is a fairly easy
soultion for my problem. I am currently working on a large data set
containing hourly wind speed over a large period of time.
My problem is explained below.

For example:
col A1 contains hours 0-23. The data in Col A2 should contain the same
hours. Col A3 shows hourly wind speed data. As you can see from Ex 1 the
hours of A1 and A2 do not match. What I am trying to do is to find a way that
allows me to compare the hours of A1 and A2 and if they are not equal, col A2
and A3 should move down so that A1 and A2 are equal, also leaving a blank
space in the A3 col. Ex 1 shows my current problems where A1 does not equal
A2. Ex 2 shows the way I want it to be
Ex 1 Ex2
A1 A2 A3 A1 A2 A3
0 0 7 0 0 7
1 1 8 1 1 8
2 3 5 2 2
3 6 7 3 3 5
4 7 6 4 4
5 8 6 5 5
6 9 5 6 6
7 7 7 6
quot;
quot;
23

What I have done so far is to to create a IF funtion that allows me to
compare A1 and A2 so they return a true value or false. Then I have to do the
rest manually, by inserting new rows. And that is very time demanding...

Hope I made myself clear. I appreciate all the help I can get it.
Thanks
Regards
/ max

Max,

If a Macro will do try:

Sub InsertIt()
Dim Endrow As Long
Dim x As Long

Application.ScreenUpdating = False
Endrow = Cells(Rows.Count, 1).End(xlUp).Row

For x = 1 To Endrow
If Cells(x, 1).Value lt;gt; Cells(x, 2).Value Then
Range(Cells(x, 2), Cells(x, 3)).Insert Shift:=xlDown
End If
Next x

Application.ScreenUpdating = True
End Sub

If you are new to Macros you will find excellent help he

www.mvps.org/dmcritchie/excel/getstarted.htm

--
HTH

Sandy
In Perth, the ancient capital of Scotland

with @tiscali.co.ukquot;maxquot; gt; wrote in message
...
gt; HI
gt; I am relatively new user of excel and hopefully there is a fairly easy
gt; soultion for my problem. I am currently working on a large data set
gt; containing hourly wind speed over a large period of time.
gt; My problem is explained below.
gt;
gt; For example:
gt; col A1 contains hours 0-23. The data in Col A2 should contain the same
gt; hours. Col A3 shows hourly wind speed data. As you can see from Ex 1 the
gt; hours of A1 and A2 do not match. What I am trying to do is to find a way
gt; that
gt; allows me to compare the hours of A1 and A2 and if they are not equal, col
gt; A2
gt; and A3 should move down so that A1 and A2 are equal, also leaving a blank
gt; space in the A3 col. Ex 1 shows my current problems where A1 does not
gt; equal
gt; A2. Ex 2 shows the way I want it to be
gt; Ex 1 Ex2
gt; A1 A2 A3 A1 A2 A3
gt; 0 0 7 0 0 7
gt; 1 1 8 1 1 8
gt; 2 3 5 2 2
gt; 3 6 7 3 3 5
gt; 4 7 6 4 4
gt; 5 8 6 5 5
gt; 6 9 5 6 6
gt; 7 7 7 6
gt; quot;
gt; quot;
gt; 23
gt;
gt; What I have done so far is to to create a IF funtion that allows me to
gt; compare A1 and A2 so they return a true value or false. Then I have to do
gt; the
gt; rest manually, by inserting new rows. And that is very time demanding...
gt;
gt; Hope I made myself clear. I appreciate all the help I can get it.
gt; Thanks
gt; Regards
gt; / max
Here's a play using formulas ..

Assuming that the data in your Ex 1
i.e. the numbers 0 - 23 are within A2:A25
and B2:C8 is your source data, viz:

07
18
35
67
76
86
95

Put in D2:
=IF(ISNA(MATCH(A2,$B$2:$B$8,0)),quot;quot;,INDEX($C$2:$C$8 ,MATCH(A2,$B$2:$B$8,0)))
Copy D2 down to D25

D225 will return the values within C2:C8
aligned with the numbers in A2:A25 as required

If required, freeze the values in col D with an in-place:
Copy gt; paste special gt; values gt; ok

Adapt to suit ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;maxquot; wrote:

gt; HI
gt; I am relatively new user of excel and hopefully there is a fairly easy
gt; soultion for my problem. I am currently working on a large data set
gt; containing hourly wind speed over a large period of time.
gt; My problem is explained below.
gt;
gt; For example:
gt; col A1 contains hours 0-23. The data in Col A2 should contain the same
gt; hours. Col A3 shows hourly wind speed data. As you can see from Ex 1 the
gt; hours of A1 and A2 do not match. What I am trying to do is to find a way that
gt; allows me to compare the hours of A1 and A2 and if they are not equal, col A2
gt; and A3 should move down so that A1 and A2 are equal, also leaving a blank
gt; space in the A3 col. Ex 1 shows my current problems where A1 does not equal
gt; A2. Ex 2 shows the way I want it to be
gt; Ex 1 Ex2
gt; A1 A2 A3 A1 A2 A3
gt; 0 0 7 0 0 7
gt; 1 1 8 1 1 8
gt; 2 3 5 2 2
gt; 3 6 7 3 3 5
gt; 4 7 6 4 4
gt; 5 8 6 5 5
gt; 6 9 5 6 6
gt; 7 7 7 6
gt; quot;
gt; quot;
gt; 23
gt;
gt; What I have done so far is to to create a IF funtion that allows me to
gt; compare A1 and A2 so they return a true value or false. Then I have to do the
gt; rest manually, by inserting new rows. And that is very time demanding...
gt;
gt; Hope I made myself clear. I appreciate all the help I can get it.
gt; Thanks
gt; Regards
gt; / max

Or if you prefer a non-VBA solution...
Name your data sets as shown:
hr1hr2wsws2
0077
1188
235
3675
476
586
6957

The formula for ws2 is
=IF(COUNTIF(hr2,hr1),INDEX(ws,MATCH(hr1,hr2,0)),quot;quot; )
Copy gt; Paste Special gt; Value ws2 into ws, if desired.
Also note that A2 becomes identical to A1.