Selective Data

Is there a way for me to match all the cels in a column and return only the cells that do not show a zero into a new column?

That wasn't very well explained. I have a 3 columns of data containing 1000 rows. Each cell shows either a zero or a name of a case, case number or a name of a person working a case. I desperately need a sub or a formula to traspose all of the non zero data to another refined cell range. Can anyone help?

Are you trying to make a single column list?

If yes,

Start a new worksheet
copy the first column into A1 of that new sheet
copy the second column at the bottom of the first list (on the new worksheet)
then do the same with the third

Then you can sort this new giant list column and delete the cells with 0.



MattD wrote:
>
> That wasn't very well explained. I have a 3 columns of data containing
> 1000 rows. Each cell shows either a zero or a name of a case, case
> number or a name of a person working a case. I desperately need a sub
> or a formula to traspose all of the non zero data to another refined
> cell range. Can anyone help?
>
> --
> MattD
> ------------------------------------------------------------------------
> MattD's Profile: http://www.excelforum.com/member.php...o&userid=28405
> View this thread: http://www.excelforum.com/showthread...hreadid=479965

--

Dave Peterson

This probably is not exactly what you wanted, but it was hard to tell exactly
what you wanted. I put in some test data as such:
0 20 Stan
10253 0 Joe
0 0 Ralph
101 0 0
102 21 Ralph
0 0 0
103 0 0
104 22 0
0 23 Stan
and then ran the following sub:
Sub myTest()
Dim ws As Worksheet
Dim StartRow As Integer, Finalrow As Integer
Dim TestVar As Variant, NewVar As String
Dim i As Integer, j As Integer
Dim Found As Boolean
Set ws = ThisWorkbook.Worksheets("sheet1")
StartRow = 1
Finalrow = ws.Range("a" & Rows.Count).End(xlUp).Row
For i = StartRow To Finalrow
NewVar = ""
Found = False
For j = 1 To 3
TestVar = ws.Cells(i, j).Value
If TestVar <> 0 Then
NewVar = NewVar & " " & TestVar
Found = True
End If
Next j
If Found Then
ws.Cells(i, 4).Value = Trim(NewVar)
End If
Next i
End Sub
and ended up with this (which is possibly what you wanted
0 20 Stan 20 Stan
10253 0 Joe 10253 Joe
0 0 Ralph Ralph
101 0 0 101
102 21 Ralph 102 21 Ralph
0 0 0
103 0 0 103
104 22 0 104 22
0 23 Stan 23 Stan


"MattD" wrote:

>
> That wasn't very well explained. I have a 3 columns of data containing
> 1000 rows. Each cell shows either a zero or a name of a case, case
> number or a name of a person working a case. I desperately need a sub
> or a formula to traspose all of the non zero data to another refined
> cell range. Can anyone help?
>
>
> --
> MattD
> ------------------------------------------------------------------------
> MattD's Profile: http://www.excelforum.com/member.php...o&userid=28405
> View this thread: http://www.excelforum.com/showthread...hreadid=479965
>
>