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

[CThai]

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

Book1
	A	B	C	D
1	Date	Company Name	Total
2	1/2/2006	A	25
3	1/2/2006	A	35
4	1/3/2006	B	125
5	1/5/2006	B	20
6	1/5/2006	B	185
7	1/7/2006	A	200
8	1/8/2006	B	250
9	1/10/2006	B	250
10	1/10/2006	A	350
Sheet1

 

[CThai]

I created the graph but the 0 (zeros) still not showing up... the data is blank --- I'm never use autofilter before so maybe i'm doing something wrong --

thank you for your patience and help.

 

[tightwad]

what about days where both companys have no data?

 

[CThai]

with days that both company have no data excel will also insert 0 (zeros) in it - the reason for this is - when graph i need to see the low day/week/monthly so the line graph will show 0's data for say 1/8/2006 for both company A and B

 

[CThai]

it seen that i may have to do this manually which is very PAINFUL - i have already created the pivot table for all the data and now just need for excel to know/recognize when company A have no data for 1/8/2006 to put zeros into the total average --- that way when i create the graph i can see that on 1/8/2006 company A have no data, currently using line chart...

 

[QuietRiot]

i have an idea

let me test it

 

[QuietRiot]

Ok, inset a column between B and C and join the date with company

it will look like this 38719A when you join date and company columns and this will be in C

so now you have A, B, C and D

Run my macro (yes, I know its sloppy codin but i just did it quick and it works)

then at the end just sort it all.

Sub addzeros()
Dim rng As String, llast As String, g As Long


Set c = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
If c Is Nothing Then
    last = 1
Else
    last = c.Row
End If
llast = last
rng = "C2:C" + llast
a = last + 1

For x = 2 To last
datte = Cells(x, 3).Value
  checkdatte = Left(datte, 5) + "B"
    g = Application.WorksheetFunction.CountIf(Range(rng), checkdatte)
If g < 1 Then
Cells(a, 3).Formula = checkdatte
Cells(a, 4).Formula = 0
Cells(a, 2).Formula = Right(checkdatte, 1)
Cells(a, 1).Formula = Left(checkdatte, 5)
a = a + 1
Else
End If
Next x
  
For x = 2 To last
datte = Cells(x, 3).Value
  checkdatte = Left(datte, 5) + "A"
    g = Application.WorksheetFunction.CountIf(Range(rng), checkdatte)
If g < 1 Then
Cells(a, 3).Formula = checkdatte
Cells(a, 4).Formula = 0
Cells(a, 2).Formula = Right(checkdatte, 1)
Cells(a, 1).Formula = Left(checkdatte, 5)
a = a + 1
Else
End If
Next x
    
Columns("A:A").Select
    Selection.NumberFormat = "m/d/yyyy"
    
 lastrow = Range("C65536").End(xlUp).Row
    For x = lastrow To 1 Step -1
 '---------------------------------------------- removed      
'duplicates part was here
End Sub

 

[CThai]

nset a column between B and C and join the date with company

it will look like this 38719A when you join date and company columns and this will be in C

so now you have A, B, C and D

i have insert a column between B and C and when i ran the codes its clearing out my data... am i doing something wrong?

 

[QuietRiot]

have it so:

A = Date

B = Company name

C = date & company name (just do =A2&B2 and drag down)

D = total

then run that macro

then when its done sort so everything is in order by date. and delete the helper column C.

it will add zeros etc just the way you want it.

 

[QuietRiot]

hmm, also just noticed

you may want to get rid of this part. it gets rid of duplicates but I think you want duplicates

lastrow = Range("C65536").End(xlUp).Row 
    For x = lastrow To 1 Step -1 
        
 If Application.WorksheetFunction.CountIf(Range("C1:C" & x), Range("C" & x).Text) > 1 Then 
            Range("C" & x).EntireRow.Delete 
        End If 
    Next x

 

[sean eamon]

You could add all possible combinations of data and company, with a value of zero, to the end of the table. Don't worry about duplications of existing data.

Then chart using a pivot table, summing the total. All the zero values will show up as zero, unless there is other data.