Formula Help

[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!

[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


"cd22" <cd22.245s1a_1141498205.6327@excelforum-nospam.com> wrote in message
news:cd22.245s1a_1141498205.6327@excelforum-nospam.com...
>
> 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!
>
>
> --
> cd22
> ------------------------------------------------------------------------
> cd22's Profile:
http://www.excelforum.com/member.php...o&userid=32151
> View this thread: http://www.excelforum.com/showthread...hreadid=519018
>

[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!

[ExcelPowerUser]

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

[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

[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.

[cd22]

Thank You Davesexcel!

I'll give that a try.