how do i get excel to add 0s???

Hello everyone -

I am desperately in need of help:

I have a workbook with missing dates and would like excel to insert
any missing dates and add 0's into that missing dates if there is no
data..


For example:

Column A ------ Column B ------- Column C
1/6/2006   ------- CompanyA ------- 25
1/6/2006 ----------CompanyA ------- 50
1/7/2006 --------- CompanyB ---------100
1/8/2006 ----------CompanyB -----------125
1/9/2006 ----------CompnayA -----------52
1/9/2006 ----------CompanyA ----------50
1/10/2006 --------CompanyB ---------- 200

With the example above I would like excel to recognize that on
1/6/2006 companyB have no data and to insert a row with 1/6/2006 ---
CompanyB ---0

If both company A and B have no data then just insert 0's in... the
reason for this is when graph I would like to see the low day/week/
monthly average...

Can anyone help me with this?

I also posted this question on [http://www.mrexcel.com/board2/
viewtopic.php?t=287266]

0
thuyhong (1)
8/13/2007 2:29:55 PM
excel 39879 articles. 2 followers. Follow

1 Replies
503 Views

Similar Articles

[PageSpeed] 40

On Aug 13, 10:29 am, thuyh...@gmail.com wrote:
> Hello everyone -
>
> I am desperately in need of help:
>
> I have a workbook with missing dates and would like excel to insert
> any missing dates and add 0's into that missing dates if there is no
> data..
>
> For example:
>
> Column A ------ Column B ------- Column C
> 1/6/2006   ------- CompanyA ------- 25
> 1/6/2006 ----------CompanyA ------- 50
> 1/7/2006 --------- CompanyB ---------100
> 1/8/2006 ----------CompanyB -----------125
> 1/9/2006 ----------CompnayA -----------52
> 1/9/2006 ----------CompanyA ----------50
> 1/10/2006 --------CompanyB ---------- 200
>
> With the example above I would like excel to recognize that on
> 1/6/2006 companyB have no data and to insert a row with 1/6/2006 ---
> CompanyB ---0
>
> If both company A and B have no data then just insert 0's in... the
> reason for this is when graph I would like to see the low day/week/
> monthly average...
>
> Can anyone help me with this?
>
> I also posted this question on [http://www.mrexcel.com/board2/
> viewtopic.php?t=287266]

Here's a possibility for you.  You have to assign this macro to a
button click, and then click the button only after selecting the top
date in your ColumnA.  It will walk down the column filling in missing
zeroes for two companies, "CompA" and "CompB"  You'll want to change
these constants to match your situation.

Private Sub FillDate_Click()
    Dim MyDate As Date

    Dim MyRow As Integer
    Dim MyCol As Integer

    Const CompA = "CompA"
    Const CompB = "CompB"

    Dim CompAPresent As Boolean
    Dim CompBPresent As Boolean

    Dim ExpectedDate As Variant
    Dim LastDateSeen As Variant

    MyRow = Selection.Row
    MyCol = Selection.Column

    LastDateSeen = 0
    CompAPresent = False
    CompBPresent = False

    While IsDate(ActiveSheet.Cells(MyRow, MyCol).Value)

        ' detect if this is the same date we saw last time through
        If LastDateSeen <> 0 _
        And LastDateSeen <> ActiveSheet.Cells(MyRow, MyCol).Value Then

CompanyCheck:
            ' upon seeing a new date, check that we had found values
for
            ' each company on the prior date
            If Not CompAPresent Then
                ActiveSheet.Rows(MyRow).Insert
                ActiveSheet.Cells(MyRow, MyCol).Value = LastDateSeen
                ActiveSheet.Cells(MyRow, MyCol + 1).Value = CompA
                ActiveSheet.Cells(MyRow, MyCol + 2).Value = 0
                MyRow = MyRow + 1
            End If

            If Not CompBPresent Then
                ActiveSheet.Rows(MyRow).Insert
                ActiveSheet.Cells(MyRow, MyCol).Value = LastDateSeen
                ActiveSheet.Cells(MyRow, MyCol + 1).Value = CompB
                ActiveSheet.Cells(MyRow, MyCol + 2).Value = 0
                MyRow = MyRow + 1
            End If

            ' detect if this new date is the next chronological date
            ExpectedDate = DateSerial(Year(LastDateSeen),
Month(LastDateSeen), Day(LastDateSeen) + 1)
            If ActiveSheet.Cells(MyRow, MyCol).Value <> ExpectedDate
Then
                CompAPresent = False
                CompBPresent = False
                LastDateSeen = ExpectedDate
                GoTo CompanyCheck
            End If

            CompAPresent = False
            CompBPresent = False

        End If

        ' detect company on current record
        If ActiveSheet.Cells(MyRow, MyCol + 1).Value = CompA Then
CompAPresent = True
        If ActiveSheet.Cells(MyRow, MyCol + 1).Value = CompB Then
CompBPresent = True

NextLine:
        ' remember the date and move to next row
        LastDateSeen = ActiveSheet.Cells(MyRow, MyCol).Value
        MyRow = MyRow + 1

    Wend

    If LastDateSeen <> 0 Then
        If Not CompAPresent Then
            ActiveSheet.Rows(MyRow).Insert
            ActiveSheet.Cells(MyRow, MyCol).Value = LastDateSeen
            ActiveSheet.Cells(MyRow, MyCol + 1).Value = CompA
            ActiveSheet.Cells(MyRow, MyCol + 2).Value = 0
            MyRow = MyRow + 1
        ElseIf Not CompBPresent Then
            ActiveSheet.Rows(MyRow).Insert
            ActiveSheet.Cells(MyRow, MyCol).Value = LastDateSeen
            ActiveSheet.Cells(MyRow, MyCol + 1).Value = CompB
            ActiveSheet.Cells(MyRow, MyCol + 2).Value = 0
            MyRow = MyRow + 1
        End If
    End If

End Sub





HTH

Brian Herbert Withun

0
bhwithun (29)
8/14/2007 6:57:54 PM
Reply:

Similar Artilces:

how do I create qif files from Excel for import into Quicken?
Want to import a bunch of financial data that's in Excel into Quicken. Can I create a QIF file from Excel? You'll need a converter utility for this. Here's a google search that might help: http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD,GGLD:2004-31,GGLD:en&q=convert+excel+to+quicken tj "pb" wrote: > Want to import a bunch of financial data that's in Excel into Quicken. > Can I create a QIF file from Excel? Hi *pb*, See if http://xl2qif.chez.tiscali.fr/xl2qif_en.php suits your purpose. Don't forget to back up the folde...

Difficult Excel Question
I am doing a VLOOKUP, but when you select the sheet you wish to look at, I would like the name to chance depending on a name supplied by a cell. If anyone can solve this, please e-mail me rthakrar49@hotmail.com =VLOOKUP(A1,INDIRECT("'"&A2&"'!A1:H100"),3,FALSE) -- HTH RP (remove nothere from the email address if mailing direct) "rthakrar49@hotmail.com" <rthakrar49@hotmail.com@discussions.microsoft.com> wrote in message news:8BDA5D08-0AD1-43D2-A572-58900081D412@microsoft.com... > I am doing a VLOOKUP, but when you select the sheet ...

WIll M2004 still get bug fixes?
Hello, Now that M2005 is out, will Microsoft still provide bug fixes to M2004 or are we forced to upgrade? Thanks Frank likely no additional fixes to M2004 .. but why upgrade because of that fact? Vernon "Frank" <farocco@hotmail.com> wrote in message news:utd$JCT9EHA.960@TK2MSFTNGP11.phx.gbl... > Hello, > > Now that M2005 is out, will Microsoft still provide bug fixes to M2004 or > are we forced to upgrade? > > > Thanks > Frank > > M05 has more bugs than M04 needs fixes. Likely it still will when M06 comes out. M04 will never get anot...

WinDbg: Unable to get verifier list
I've been attempting to get to the bottom of a recurring BSOD crash happening on my system. I've already had 4 crashes so far over the past two weeks. So I've identified that NTOSKRNL.EXE is involved in all of them so far. It always somewhere in the stack. So I enabled Driver Verifier on NTOSKRNL, as well as HAL.DLL, NTFS.SYS, and FLTMGR.SYS which were also identified on the stack during various of the events. Okay so I had my latest crash yesterday, and it occurred on NTOSKRNL as well. The Verifier was already enabled on the system prior to this crash, and then wh...

Corrupted user profile.. how to get back to old name?
Running XP Pro at work. Login through a domain on Win2003SBS. I login with my name ssetnicky and password. We only use the server to store data really, it pushes out antivirus to all the machines, and we can access our accounting software's database which resides on the server. Each one of us has our own login and we're a member of the administrators group. My local computer's profile got corrupted around March 24th. When I logged on it looked as if I was viewing XP for the first time.. the little take a tour icon popped up, none of my quicklaunch icons were there, e...

My OL 2000 will not open-get error message
I have MS Office Premium-all of the other programs work. When I try to open OL2000 I get the the error msg. MS Outlook has encountered a problem and needs to close. I removed MS Office Premium and re-installed it-same problem. I also clicked repair from Control Panel-same problem. -- Roma Does it work correctly when trying to start Outlook in Safe Mode? Start-> Run; outlook.exe /safe For additional troubleshooting steps see; http://www.howto-outlook.com/faq/outlookdoesntstart.htm -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www...

Excel Tool Bars
Lately, when I open Excel, the top Excel name bar and the bottom tool bar are not on the screen. It appears that the worksheet is too large to be contained on the screen. Hi Bailey, Can't tell from your description, so just trying to grasp at straws.. -- are the toolbars floating, if so drag to correct position so the become docked Applies to toolbars but not the name box and address bar. -- do you have problems in other applications, i.e. Display settings, screen resolutions -- is the zoom control at 100% -- What happens in Full Screen mode and in restore (buttons upper right...

using excel from dotnet
situation: I'm working on a new app in vbexpress 2008 using acad com interop Also need to write extracted data to excel. problem: At home(laptop) i have excel 10. At the office excel 12. (both seem to have excel5 listed in refs.) I need to work at both locations. can i reference both 10 and 12 in the same vbproj (i think not) can i just reference 5 and is that just an older version with common (but not the latest) interface? i guess i can just have 2 vbproj files (one on laptop one at work) and just copy the .vb class files that are being edited back and forth, not cop...

Remove a document from a workspace in Excel 2007
I have a user who created a workspace and included a file that he now wants to remove from the workspace. Is there a way to remove one file from a workspace? Thank you. ...

Can I use Free Excel Viewer 97/2000 in Windows XP?
Microsoft downloads doesn't list the free excel viewer compatible with Windows XP. Is there a free download to view and print excel spreadsheets, that's compatible with Windows XP? Linda The Viewer for Office 97/2000 works fine under Windows XP. Gord Dibben Excel MVP On Fri, 22 Oct 2004 15:11:01 -0700, "Linda" <Linda@discussions.microsoft.com> wrote: >Microsoft downloads doesn't list the free excel viewer compatible with >Windows XP. Is there a free download to view and print excel spreadsheets, >that's compatible with Windows XP? Linda An...

Excel 2000/2003 conflict
Whenever I try to open excel files created using 2000, 2003 gives me errors on simple formulas. For example, a SUM formula is marked as an error and displays a wrong number, when I go up to the formula bar and hit enter without changing the conents it recalculates to the right answer. There is many more errors such as "inconsistent formula" or "lost visual basic project" which doesn't allow a 2000 file open without repair. Is there anything I can download to fix all of this together or will I have to fix them all by hand? Does this have to do anything with the differen...

Unable to run excel macro after changing file name
After changing the filename in an excel macro, receiving Error number 5 - Invalid procedure call or argument when users with excel 2000 run the macro. Users with excel 2003 are not having this issue Post the line of code that is causing the error. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "merf" <merf@discussions.microsoft.com> wrote in message news:F68DF323-214D-4F01-8D8E-654F9500242E@microsoft.com... > After changing the filename in an excel macro, receiving Error > number 5 - > Invalid procedure cal...

compare information from two spreadsheets or excel sheets
Don't know if any one can help, I am trying to find an easy way to compare data from one spread sheet to another on Excel 2000. Every week I create a spreadsheet and need to find out if any further items added on it. example old spreadsheet would be called Andrews 19.04.2010 with 3 sheets 4 columns on each. The new one would be Andrews 26.04.2010 same set up again but possibley new items on the Andrews 26.04.2010 Add a fifth corresponding column on the NEW sheet and insert the following formula in the first cell: =SUMPRODUCT((OldSheet!A:A=A1)*(OldSheet!B:B=B1)*(OldShee...

Seam-Excel error when exporting file
When I export an excel file from a website the file exports with the file name of 'seam-excel' and I can't format the cells or create a pivot table without saving and re-naming the file. How can I get my file to not export with that file name? Is it a security setting? Have never heard of this problem being generated through Excel alone. I would suggest the source would be at the website from which you are exporting. Can you post a URL? I'd like to have a look just out of curiosity. Gord Dibben MS Excel MVP On Fri, 12 Feb 2010 12:41:01 -0800, fallou...

Installing components for Microsoft Excel XP
I Need Help. I recently demoted my PDC to a standard server and joined another domain. Now when I attempt to access Excel I receive the following: =========================================== "Installing components for Microsoft Excel" Installing: "Microsoft Excel Feature" =========================================== It runs and then goes away but it does this every time I open Excel. How do I get rid of this message. I really don't want to re-install Excel. Things I have done. 1. Copied the old user's profile to the new one. 2. Move the Application Data...

Create/Update Excel Objects in Power Point
I developed an add-in in Power Point 97, 2000, XP and 2003 for creating Excel charts/tables and being able to update them. The code I used is below: Dim PPT_CHRT As OLEFormat Dim PPT_SLD As Slide 'ADD a chart/table PPT_SLD.Shapes.AddOLEObject Left:=var_chrt_L, Top:=var_chrt_T, Width:=var_chrt_W, Height:=var_chrt_H, ClassName:="Excel.Chart.8", Link:=msoFalse Set PPT_CHRT = PPT_SLD.Shapes(var_SHP_count_no).OLEFormat PPT_SLD.Shapes(var_SHP_count_no).Select 'EDIT chart/table Set PPT_SLD = ActivePresentation.Slides(var_SHP_SLD_no) Set PPT_CHRT...

getting the output of a transform as DOM
Hello, I am doing an XSL transformation using the XslTransform object and I would like to get the output as a DOM (XmlDocument or XmlElement) Do I have to get it serialized and then parse it again, or is there anyway to do it more directly? TIA Andy Andy Fish wrote: > I am doing an XSL transformation using the XslTransform object and I would > like to get the output as a DOM (XmlDocument or XmlElement) > > Do I have to get it serialized and then parse it again, or is there anyway > to do it more directly? Sure there is direct way: XmlDocument res = new XmlDocument(); ...

Comparing two excel files
I work with large data in excel. Most of the time I need to match two different worksheets. Both worksheets always have two columns that contains same data (name & ID). What would be the best way to match names and ID to obtain a new file with data that couldn't match? if Excel good to do that or should I use other software like Access? please advise. Look at Chip Pearson's page on working with Duplicates and Uniques http://www.cpearson.com/excel/duplicat.htm -- Regards, Tom Ogilvy Alexandra <anonymous@discussions.microsoft.com> wrote in message news:1091601c3f335$a...

Change listbox text size in Excel 2007
Using data validation list box function to display a named range, How can the text size be increased when displayed in the list box, How can the number of records displayed be increased from the default This can not be done. See link for further detail and possible workarounds: http://www.contextures.com/xlDataVal08.html#Font -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Maxwell Crompton" wrote: > Using data validation list box function to display a named range, > How can the text size be increased when displayed in ...

How do I create a hyperlink to a web page in Excel?
How do I create a hyperlink to a web page in Excel? I click on Insert/Hyperlink. Click the on the "Browse the Web" button and slect a web site. After creating the hyperlink, I am unable to open the hyperlink. I get a error message box saying, "Unable to open. Unable to locate Internet server or Proxy server". Try copying the webpage address and pasting it into a cell. With the cell selected, press F2 and Enter and it will create the hyperlink for you. HTH -- Sincerely, Michael Colvin "Olowalu" wrote: > How do I create a hyperlink to a web page i...

my first excel experience (share portfolio)
hi guys although i have had excel on my laptop for years this is the first time i have ventured to use it. i am trying to do a stock /share portfolio, so i can tell at a glance my positions, so far the layout as follows:- a6 buy/sell, input b6 amount buy,input c6 amount sell,input d6 name stock/share,input e6 epic code,input f6 buy date,input g6 sell date,input h6 purchase price per share,(bid)input i6 sell price per share,input j6 sub total [=h6*b6],calc k6 buy commission �,input l6 sell commission �,input m6 stamp duty tax,=((h6/100)*b6)*/0.5 ,calc n6 total co...

copy scanned documents to excel
Hello, please help, I recieve a lot of documents in paper and I need to input them to excel, i scann them with imaging but it comes out single columned and that doesnt work for me, what can i do? Have tried the Text to Column command, under Data? It's function is to split up data in such situations. If you're not familiar with it, try following the Text to Column wizard (the screen that appears when you use Text to Column). "Malu" <Malu@discussions.microsoft.com> wrote in message news:42B2C174-B169-4BC1-9522-8EA78E0AE9BD@microsoft.com... > Hello, please hel...

Toolbars in Excel 2003
I have a custom Toolbar which is now not required. Despite deleting it using View Toolbars Customize Delete and then saving the file when I close Excel and re-open the file it was located in the Toolbar reappears. What am I maissing You may want to check to see if there's an addin that adds the toolbar. Tools|Addins keep track of the ones you toggle off/on when you're testing. Or maybe you have an addin in your XLStart folder that's doing the work. Close excel and windows start button|search (for XLStart) Move files out of that folder and test to see if you can find the probl...

How can I convert an Excel file to an address book?
How can I convert an Excel file to an address book to import into email? ...

How to Get Numerical Data
I am converting a VB6/ADO/Access application to .NET and I want to convert the data received to XML. The data acts as the database source to some Crystal Reports 10 reports. In the old system, the value fields were recognized as decimal values by CR and they were formatted as xx.xx for output. With XML, everything is string data. (I have an XSD file that sets criteria such as decimal or integer but that doesn't change what CR sees when it reads the data. Is there a way to handle this? Wayne ...