Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting new rows
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 " " 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting new rows
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: 0 7 1 8 3 5 6 7 7 6 8 6 9 5 Put in D2: =IF(ISNA(MATCH(A2,$B$2:$B$8,0)),"",INDEX($C$2:$C$8 ,MATCH(A2,$B$2:$B$8,0))) Copy D2 down to D25 D2:D25 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 paste special values ok Adapt to suit .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "max" wrote: 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 " " 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting new rows
Or if you prefer a non-VBA solution...
Name your data sets as shown: hr1 hr2 ws ws2 0 0 7 7 1 1 8 8 2 3 5 3 6 7 5 4 7 6 5 8 6 6 9 5 7 The formula for ws2 is =IF(COUNTIF(hr2,hr1),INDEX(ws,MATCH(hr1,hr2,0)),"" ) Copy Paste Special Value ws2 into ws, if desired. Also note that A2 becomes identical to A1. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting alternate blank rows in Excel | Excel Discussion (Misc queries) | |||
Using absolute cell refernce and inserting rows | Excel Worksheet Functions | |||
Inserting fillable Rows within a Protected Worksheet | Excel Worksheet Functions | |||
inserting columns within certain rows only | Excel Discussion (Misc queries) | |||
Inserting Rows w/formulas question | Excel Worksheet Functions |