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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Jaybird,

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

Dufus
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top