Trying to insert formulas if & only if Value is not 0

Jaybird

New Member
Joined
Oct 9, 2006
Messages
10
Hi everyone,

This is a recurring problem that's been bugging me for a while (well, 1.5 problems). I have a list of about 200 values that vary from anywhere from 1-15 entries per value. To clarify let's say it looks like this:

1) Problem 1: Assume you have this dataset:

A 3
A 2
A 6
B 1
C 9
C 11
C 4
D 8
etc.

Now I'm trying to create separate worksheets for each of these, where it would show:

A 3
A 2
A 6

on one, then afterwards

B 1

on the other etc.

I have been using vlookup tables, but the problem is since I don't know how many formulas I use per page, I always end up with a string of 0s afterwards. So the data ends up looking like:

A 3
A 2
A 6
0 0
0 0
0 0

and

B 1
0 0
0 0

etc.

Having to manually fill-down the exact amount of entries on each one is out of the question because I have hundreds of entries for each and this needs to be done every week. Since this is a recurring, random data-set though, I have to include the maximum amount of =vlookup formulas to make sure they're all included in the separate reports.

Anyone know how I can make it so the values are included If and only if they do not equal 0, otherwise it's a blank space?


(Problem 2) From the raw data, i want to create a final set of the sum of each of these, without any space in between. so for example:

A 2
A 1
A 9
C 1
F 2
etc.

So notice there is no B, D or E. However since I'm using lookup tables, I end up with this Summary Chart:

A 12
B 0
C 1
D 0
E 0
F 2

It looks very sloppy with all of those empty 0s. Any way I can clean it up and make it so that only the letters with actual values appear, without any spaces in between?:

A 12
C 1
F 2

Thanks so much in advance!
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
Jaybird,

So many choices, so little time....

Do you keypunch the 200 values? If so, you could go with a worksheet event macro that moves the data to the appropriate sheet when you key it in.

Do you mind if the lookup formula evaluates to "" which would not show the zeros? If not, when your vlookup evaluates to zero put "" in the condition.

Why do you have to move these seemingly meaningless labels and numbers to another worksheet? Are you doing other things to the data? Maybe if you share the whole thing, someone could help with the bigger picture... text to columns, pivot table, etc.

Dufus
 

Jaybird

New Member
Joined
Oct 9, 2006
Messages
10
Hey, thanks for the answer!

Basically I'm calculating payroll out of Excel. Salesmen make x amount of sales, and we're paying them based off of individually tailored percentage y per sale they make. So Salesman A makes 7 sales at 10% each, while Salesman B makes 3 sales at 40% each, etc. The amount of sales they make per period is variable, while the percentage is more or less fixed (it's actually derived in other spreadsheets but the final amount is in a lookup table)

The separate spreadsheets is their statement that I send them to show them what sales are being paid this period and what's being paid the next. I just don't want those statements to look sloppy by having a bunch of blank spots and zeroes on them (not essential I know, but aesthetic)

The skill also would help tighten up a lot of the worksheets I have that go into making the calculations.
 

Jaybird

New Member
Joined
Oct 9, 2006
Messages
10
Is there a way to generated separate Excel spreadsheets based on fields?

For example if there's a new salesman "Bob Gulligan" can I generate a new spreadsheet based on an existing template, save it as "Gulligan - (Date)" and input his name into the "Name" Field, along with the formulas that link to that name field Ideally I'd have 100 separate documents labeled with the names and dates of the fields in a master sheet, so I can send them each out individually via e-mail.
 

Jaybird

New Member
Joined
Oct 9, 2006
Messages
10

ADVERTISEMENT

Additionally, is there a way to make a Duplicate document where everything is Paste-Values instead of the formulas? This is in case I want to save backup work as numbers since my master sheets may change.
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
Jaybird,

Just to bump this back up and remind you. You didn't answer two of my questions.

Dufus
 

Jaybird

New Member
Joined
Oct 9, 2006
Messages
10

ADVERTISEMENT

Do you keypunch the 200 values? If so, you could go with a worksheet event macro that moves the data to the appropriate sheet when you key it in.

No, they're 200 rows with 60 columns of info each. It's an export from a system I don't have access to.

Do you mind if the lookup formula evaluates to "" which would not show the zeros? If not, when your vlookup evaluates to zero put "" in the condition.

That actually helped tremendously. I still can't figure out part two though, How to make:

James 8
John 0
Sarah 0
Ramsey 1
Travis 2


look like:

James 8
Ramsey 1
Travis 2

Without extra spaces in between. I was thinking the small( function maybe?


Why do you have to move these seemingly meaningless labels and numbers to another worksheet? Are you doing other things to the data? Maybe if you share the whole thing, someone could help with the bigger picture... text to columns, pivot table, etc.

Dufus
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
That actually helped tremendously. I still can't figure out part two though, How to make:

James 8
John 0
Sarah 0
Ramsey 1
Travis 2


look like:

James 8
Ramsey 1
Travis 2
Can you sort the data?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Can you sort the data?
Or filter column B(?) on greater than zero?

(It sounds like most of the rest of your questions can be done as well but it's probably best
to stick with one issue at a time.)
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Jaybird
just for you sample data... hope this works
Code:
Private Sub Worksheet_Deactivate()
Dim ws As Worksheet, a, dic As Onject, i As Long, ii As Integer, w(), x, y
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
a = Range("a1").CurrentRegion.Value
For i = 1 To UBound(a,1)
   If Not IsEmpty(a(i,1)) And a(i,2) <> 0 Then
      If Not dic.exists(a(i,1)) Then
         ReDim w(1 To UBound(a,2), 1 To 1)
         For ii = 1 To UBound(a,2) : w(ii,1) = a(i,ii) : Next
         dic.add a(i,1), w
      Else
         w = dic(a(i,1))
         ReDim Preserve w(1 To UBound(a,2), 1 To UBound(w,2) + 1)
         For ii = 1 To UBound(a,2)
           w(ii,UBound(w,2)) = a(i,ii)
         Next
         dic(a(i,1)) = w
      End If
   End If
Next
x = dic.keys : y = dic.items : Set dic = Nothing : Erase a
For i = 0 To UBound(x)
   On Error Resume Next
   Set ws = Sheets(x(i))
   If ws Is Nothing Then Set ws = Sheets.Add.Name = x(i)
   On Error GoTo 0
   ws.Range("a1").Resize(UBound(y(i),2),UBound(y(i),1)).Value = Application.Transpose(y(i))
Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,060
Messages
5,545,761
Members
410,704
Latest member
Cobber2008
Top