Formula Help

C

cd22

Hello,

I have a list of names and numbers in two separate columns. Some
columns have positive or negative numbers and some only have zeros.
I'm trying to create a formula that I can put on a separate worksheet
to only pull the names and numbers from the worksheet that do not
contain zeros. In other words, I want to condense the list to only
include "active" names and numbers. Is there a formula that can be
used to do this? i.e.

Original List
John 1
Jeff -4
Vince 0
Jeremy 0
Cindy 7

List I want to end up with:
John 1
Jeff -4
Cindy 7

Thanks!
 
C

CLR

I would just copy the whole list over to the new sheet and then sort on the
column with the numbers and that woud put all the zeros together, then just
delete those rows.....you're left with only the names that have numbers....

Vaya con Dios,
Chuck, CABGx3
 
C

cd22

Hi,

Thanks for your help. I actually have a large series of data (multiple
columns) and the process will need to be repeated quite a few times, so
I'm trying to find an automated solution. i.e.

name column1 c2 c3 c4 c5
john 3 3 4 -4 0
jeff 0 2 0 1 9

I need to have a separate list for each column, so sorting won't work
(there are quite a few columns based on the same names to the left).
Therefore, in my worksheet for column one, I want to pull the names and
numbers (not the zeros) that appear in that column. Then, I have a
separate worksheet for column 2, etc. Does that explain it better?

Thanks!
 
E

ExcelPowerUser

:confused:

Maybe if your explaination was better someone could help you because
you definetly need help.
 
D

davesexcel

Sub testme02()

Dim FoundCell As Range

Do

With Worksheets("Sheet1").Range("B1").EntireColumn
Set FoundCell = .Cells.Find(What:="0", _
After:=.Cells(1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
Loop

End Sub


Copy this Code into a mudule
create a button and assign this macro to it
This code will search column b and when it finds a zero it deletes the
row,
you can fine tune it to meet your requirments, such as the column,
sheet,and criteria;)
 
C

cd22

Thank you PowerUser,

I'm not sure if your comment was really meant to help or just to make
me feel bad that I'm having trouble with this, but I will assume you
meant it well and try to explain further. I'm honestly trying hard to
explain, but am having trouble.

I was trying to make the explanation more basic, but that clearly
wasn't working. I'll try to make it more descriptive. I have a list
of accounts on the left hand side of the spreadsheet and companies
going across the columns. There are about 100 or so companies and
therefore, that many columns of data. However, not every account and
company combination will have a number. Some of them are just zero.
My goal is to pull the data to a separate worksheet for each company to
perform further analysis. However, in pulling the data to the other
worksheets, I would like to get rid of all of the accounts that do not
have activity (those are the only ones that need further analysis).
Hence, condensing the list to only the accounts that have activity
(again, per each company on the separate company spreadsheets). I do
realize that I could just copy and paste the data into the new
worksheets and sort and then delete the zeros. However, I'm trying to
make it possible to have the process be dynamic (i.e. if new data is
copied into the data worksheet, this data will flow automatically to
the separate worksheets for each company).

Hopefully, this is a better description of what I'm looking for. If
not, I sincerely apologize.

Thanks.
 
Top