Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-23-2010, 12:32 PM
tareq tareq is offline How to organize and insert blank cells between a huge number of data? Windows 7 How to organize and insert blank cells between a huge number of data? Office 2007
Novice
How to organize and insert blank cells between a huge number of data?
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default 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.
Attached Files
File Type: xls Example.xls (22.5 KB, 7 views)

Last edited by tareq; 09-24-2010 at 12:17 PM.
Reply With Quote
  #2  
Old 09-24-2010, 09:30 AM
Kimberly Kimberly is offline How to organize and insert blank cells between a huge number of data? Windows 7 How to organize and insert blank cells between a huge number of data? Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

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
Be sure to do this on a copy of your worksheet, not the original.
The macro will quit when it encounters a row where col B is empty.
Reply With Quote
  #3  
Old 09-24-2010, 09:36 AM
tareq tareq is offline How to organize and insert blank cells between a huge number of data? Windows 7 How to organize and insert blank cells between a huge number of data? Office 2007
Novice
How to organize and insert blank cells between a huge number of data?
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default

Thank you Kim for your input, please can you tell me how to add this to excel??
Reply With Quote
  #4  
Old 09-24-2010, 12:15 PM
tareq tareq is offline How to organize and insert blank cells between a huge number of data? Windows 7 How to organize and insert blank cells between a huge number of data? Office 2007
Novice
How to organize and insert blank cells between a huge number of data?
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default

Quote:
Originally Posted by Kimberly View Post
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
Be sure to do this on a copy of your worksheet, not the original.
The macro will quit when it encounters a row where col B is empty.
Well, thanks again, you did solve the problem, but 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.
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.
Attached Files
File Type: xls Example.xls (22.5 KB, 4 views)
Reply With Quote
  #5  
Old 09-24-2010, 01:20 PM
Kimberly Kimberly is offline How to organize and insert blank cells between a huge number of data? Windows 7 How to organize and insert blank cells between a huge number of data? Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 09-24-2010, 01:30 PM
Kimberly Kimberly is offline How to organize and insert blank cells between a huge number of data? Windows 7 How to organize and insert blank cells between a huge number of data? Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 09-24-2010, 01:44 PM
tareq tareq is offline How to organize and insert blank cells between a huge number of data? Windows 7 How to organize and insert blank cells between a huge number of data? Office 2007
Novice
How to organize and insert blank cells between a huge number of data?
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default

Quote:
Originally Posted by Kimberly View Post
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.
I do completely understand your points, but since I have a very limited time to finish the data, I started working on them manually yesterday, so I faced many other problems more than those found on my original post :S like I have to shift the entire row, and not only one cell!! Any way, the situation is exactly as found in my attached file, and the "Before" and "After" sheets are a very small sample on the data I have to deal with, the "Before" sheet contains the data I received in addition to the full data column I created, and the "After" sheet contains the data after being modified according to the full data column I created, and I didn't applied your code on them. In the Attached example the column I created is column "A" and it is named "Date". But in any ways, I deeply appreciate your effort and your great help, and now I am hoping you will give another code that will help me. I really did my best!! And after this issue I am more determined to take an extensive Visual Basic courses or any other programming language courses!! These advance courses are really a must these days!! :S
Thanks again
Reply With Quote
  #8  
Old 09-24-2010, 01:47 PM
tareq tareq is offline How to organize and insert blank cells between a huge number of data? Windows 7 How to organize and insert blank cells between a huge number of data? Office 2007
Novice
How to organize and insert blank cells between a huge number of data?
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default

Quote:
Originally Posted by kimberly View Post
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
yessss!! It worked!!!! You did it kim!!! Omg!!! I don't know how to thank you my dearest!!! ))))))))) you solved my problemmm!!!!!
Love you love you love youuuuuuuuuu!!!!!!!!! :d :d :d :d :d
thanksssssss!!! Blesssss youuuu!!! )))))))))))
Reply With Quote
  #9  
Old 09-24-2010, 05:29 PM
Kimberly Kimberly is offline How to organize and insert blank cells between a huge number of data? Windows 7 How to organize and insert blank cells between a huge number of data? Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Thank you for letting me know!
Reply With Quote
  #10  
Old 09-25-2010, 03:17 PM
tareq tareq is offline How to organize and insert blank cells between a huge number of data? Windows 7 How to organize and insert blank cells between a huge number of data? Office 2007
Novice
How to organize and insert blank cells between a huge number of data?
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default

Quote:
Originally Posted by Kimberly View Post
Thank you for letting me know!
Welcome!! And thank you for letting ME KNOW!!!
Reply With Quote
  #11  
Old 09-28-2010, 12:18 PM
BjornS BjornS is offline How to organize and insert blank cells between a huge number of data? Windows Vista How to organize and insert blank cells between a huge number of data? Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default 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
Attached Files
File Type: xls Example_Before_After.xls (40.5 KB, 16 views)
Reply With Quote
  #12  
Old 09-29-2010, 11:44 AM
tareq tareq is offline How to organize and insert blank cells between a huge number of data? Windows 7 How to organize and insert blank cells between a huge number of data? Office 2007
Novice
How to organize and insert blank cells between a huge number of data?
 
Join Date: Sep 2010
Posts: 15
tareq is on a distinguished road
Default

Quote:
Originally Posted by BjornS View Post
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
Great formula!!! I tried it in a separate sheet though but it didn't work But it worked perfectly in your example! Actually this formula will help me much more, cause I can control many parameters taken in different intervals!! I will work on it. Excel is full of surprises!!
Thank you Bjorn, it ia a pleasure knowing you
Reply With Quote
  #13  
Old 09-29-2010, 02:12 PM
BjornS BjornS is offline How to organize and insert blank cells between a huge number of data? Windows Vista How to organize and insert blank cells between a huge number of data? Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

Hi, glad you liked it.
If you need help with the formula in your other sheet, just let me know.

Kind regards
Bjorn
Reply With Quote
Reply

Thread Tools
Display Modes


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
How to organize and insert blank cells between a huge number of data? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:48 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft