#1
|
|||
|
|||
How to organize and insert blank cells between a huge number of data?
Hey guys, I am a MSc graduate, I am now working on a climate date from 1994 till 2010, they are on excel sheet with about 920,700 rows. I have the daily data for 26 weather station, each station measures about 10 parameters, but there are missing data in some days, and I need to add a blank in the missing day so we will estimate it later. Simply, the situation is like this example: You have the following data:
1 2 4 5 7 9 10 These numbers are in one column (A). I also created the standard column (B) as follow: 1 2 3 4 5 6 7 8 9 10 Now I need to put these "A" and "B" beside each other and edit column "A" according to column "B", so I will insert a blank to replace the missing data, so "B" will not change, while "A" will be like this 1 2 blank cell 4 5 blank cell 7 blank cell 9 10 Any way, attached is my example in an excel file, there 2 sheets in the file, the first sheet "Before" is the data I have plus the standard column I created, and the second sheet "After" contains the unchanged column "B" with the edited column "A" according to column "B" My question is there any function can do this automatically for the around 1 million cell I have??? If not, is there any add IN will help?? or can I even create add IN that will do that? If nothing works, is there any office application or any other software allows me to edit my data in the way I need?? I REALLY NEED YOUR HELP GUYS!! AND ASAP!!!! Please Note that I have at home Excel 2007 with OS windows 7 32bit and at the University I have Excel 2010 with OS windows vista 64bit, but the file attached is in ".xls" format since this forum dosn't support uploading ".xlsx" format. Last edited by tareq; 09-24-2010 at 12:17 PM. |
#2
|
|||
|
|||
Code:
Sub FixList() Range("A1").Select Do If (ActiveCell.Value > ActiveCell.Offset(0, 1)) Then Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Else: ActiveCell.Offset(1, 0).Range("A1").Select End If Loop Until IsEmpty(ActiveCell.Offset(0, 1)) End Sub The macro will quit when it encounters a row where col B is empty. |
#3
|
|||
|
|||
Thank you Kim for your input, please can you tell me how to add this to excel??
|
#4
|
|||
|
|||
Quote:
Mainly the code you posted didn't allow me to insert an entire row, and when I switch the columns the code didn't work. Please see the attached file and give me your feedback. P.S. I also updated the example attached in the thread subject. |
#5
|
|||
|
|||
The code does exactly what you asked for in your original post, and it does create output that matches the first example file you uploaded (before you changed it).
"Mainly the code you posted didn't allow me to insert an entire row" --Your original post specifically said that Column B should not change, and the original example file did not have inserted rows, just inserted cells in Col A. Also, the current file you uploaded doesn't show that entire rows have been inserted. "and when I switch the columns the code didn't work" --Of course it didn't. That is because what you asked for and what you wanted were entirely different. "I need more than that, attached is an example contains "Before" and "After" sheets that exactly describe the other problem I faced when applying your solution" --I don't know if you are saying that this file shows the issue after the code is run or after. And in this last sample file... On the After sheet, WHY aren't there inserted ranges in rows 28 thru 32? I have written another macro, but it doesn't stop at row 27, so I hesitate to post the code until I know what you expect it to do. |
#6
|
|||
|
|||
I have to go, so here is another shot at me figuring out what you want:
Code:
Sub Macro1() ' ' Macro1 Macro ' ' Range("B2").Select Do If (ActiveCell.Value > ActiveCell.Offset(0, -1)) Then ActiveCell.Range("A1:F1").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ActiveCell.Offset(1, 0).Range("A1").Select Else ActiveCell.Offset(1, 0).Range("A1").Select End If Loop Until IsEmpty(ActiveCell.Offset(0, -1)) Range("A1").Select End Sub |
#7
|
|||
|
|||
Quote:
Thanks again |
#8
|
|||
|
|||
Quote:
Love you love you love youuuuuuuuuu!!!!!!!!! :d :d :d :d :d thanksssssss!!! Blesssss youuuu!!! ))))))))))) |
#9
|
|||
|
|||
Thank you for letting me know!
|
#10
|
|||
|
|||
Welcome!! And thank you for letting ME KNOW!!!
|
#11
|
|||
|
|||
Another solution
Hi,
I saw your problem with weather data. It is really a nice macro you have got from another user. However, sometimes it could be really helpful also to know how to solve these issues the formula way I have attached an example with formulas. The formula I have used is extremely useful (I use it every week), so I really recommend you to learn it, since in other situations you might fall into, this might just be the solution for you. In the attached example you get an error message for lines that should be empty. You can delete all these error messages by selecting menu "Edit / Goto". the select "Special". After this click "Formulas" (and only mark "errors"). Klick OK. Now all cells with an error are marked. Press delete to make ALL these cells empty! I don't attach to many explanations to the formulas. Look and learn! Come back with questions if you like Before applying the formulas, I marked the columns B to G (in sheet "Before"), and named this area "data". After you have finished, you might like to convert the result of the formulas to values (copy... paste special..as values). Kind regards Bjorn |
#12
|
|||
|
|||
Quote:
Thank you Bjorn, it ia a pleasure knowing you |
#13
|
|||
|
|||
Hi, glad you liked it.
If you need help with the formula in your other sheet, just let me know. Kind regards Bjorn |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Bar chart help urgent ????? | aligahk06 | Excel | 1 | 07-08-2010 12:23 AM |
Bar chart help urgent ????? | aligahk06 | Project | 0 | 07-03-2010 06:58 AM |
Urgent help regarding automation. | aligahk06 | Excel | 1 | 01-14-2010 01:55 PM |
Urgent Help Required | id12345 | Word | 4 | 12-29-2009 08:22 AM |
Urgent help! | captainspi | Word | 2 | 04-19-2009 11:17 AM |