Picking values from a column

[steven162]

Hi all,

I have a spreadsheet which generates a column 5000 rows high, all the numbers in the column are 0, except 8 which are a positive numbers, these numbers could be anywhere within the column.

I need a way to extract these numbers into a table on another tab, then do it again on the next set of data, the numbers will move to somewhere else in the column, any ideas? I dont need to know where in the column the numbers are but I do need to get them off in the correct order (top to bottom)

I know this is pretty simple compared to most of the other stuff on here.

Thanks

Steve

 

[brengine]

AutoFilter for all columns not equal to zero?

 

[steven162]

Hi,

The autofilter works in as far as it shows all the data, what it doesnt do is let me put the data somewhere else.
With the current set of data the 8 numbers not 0 are in rows 113, 256, 1009, etc, this will not be the case with the next set of data.

I am currently playing with vlookup.

Thanks

 

[GregLocock]

I can't see a nice way with vlookup, although if your numbers are all different it might work.

Might be time to get your feet wet with vba

Cheers

Greg Locock

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[brengine]

Here's some VBA Code. Hopefully it's self-explainatory.

Sub FindNonZeroInColumn()
Dim xlWkSht1 As Worksheet
Dim iRow1 As Integer
Dim iCol1 As Integer
Dim xlWkSht2 As Worksheet
Dim iRow2 As Integer
Dim iCol2 As Integer
Dim iFoundCnt As Integer

    'Source Data Location
    Set xlWkSht1 = Excel.ActiveWorkbook.Worksheets("Sheet1")
    iRow1 = 1
    iCol1 = 1
    
    'Target Data Location
    Set xlWkSht2 = Excel.ActiveWorkbook.Worksheets("Sheet2")
    iRow2 = 1
    iCol2 = 1

    iFoundCnt = 0
    'Iterate through all values in 'Source' column until a Blank Cell is reached
    While (xlWkSht1.Cells(iRow1, iCol1).Value <> "")
        'See if cell value is non-zero
        If (xlWkSht1.Cells(iRow1, iCol1).Value <> 0) Then
            xlWkSht2.Cells(iRow2, iCol2).Value = xlWkSht1.Cells(iRow1, iCol1).Value
            iRow2 = iRow2 + 1
            iFoundCnt = iFoundCnt + 1
        End If
        iRow1 = iRow1 + 1
    Wend
    
    MsgBox "Done! " & vbCrLf & vbCrLf & iFoundCnt & " non-zero values found.", vbOKOnly, "Find Non-Zero In Column"
End Sub

 

[jghrist]

You can use Data Filter Advanced and copy to a column on the same sheet as the data. You need a heading for your column of data, a Criteria Range with the heading and >0 below it, and a range to copy to that is a cell with the heading. Then reference the column on another sheet.

Data         Criteria      Output
Range          Range        Range

data           data         data
  0             >0           1
  0                          2
  1 
  0
  2
  0

 

[bacon4life]

Looks like you already have a couple of choices, but here is one. Instead of VB or manually data filtering, this uses formulas and a couple of helper columns.

Helper column 1: turn all the zeros to empty cells and add a separator
B1 to B5000=IF(A2>0,A2&",","")

Helper column 2: concatenate all the cells in helper column 1
C2 to C5000=C1&B2

Helper column 3 & 4: separate the results in c5000 that now contain a string with your 8 numbers.
D1=C$5000
D2 to D8=RIGHT(D1,LEN(D1)-FIND(",",D1))
E1 to E8=LEFT(D1,FIND(",",D1)-1)

I have a attached an example spreadsheet.

http://files.engineering.com/getfil...949-dacd9bfd27f0&file=find_only_8_numbers.xls

 

[steven162]

Thanks all for your input, all very interesting. I have achieved what I wanted by the following-

I have the following formula in the column (H) to the left of my data column (I) "=IF(I9=0,H8,H8+1)"

Column H increases by 1 each time it sees a number not 0 in column I,

On my other tab I then use vlookup to look for 1 in column H and report out the data in column 2 of the array like this - "=VLOOKUP(1,Data!$H$6:I$10000,2,FALSE)" then look for 2 in column H thus "=VLOOKUP(2,Data!$H$6:I$10000,2,FALSE)"

Thanks again

Steve

 

[IDS]

How about sorting the data and copying the top 8 cells?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[GregLocock]

steven, sneaky!

Cheers

Greg Locock

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[IDS]

Just thinking about the sort option, I have a sort function downloadable from:

http://newtonexcelbach.wordpress.com/2009/03/23/a-sort-function/

which will return all the values greater than zero from a range by sorting from large to small, and it will update automatically, without having to do a new sort every time the data changes.

Not that there is anything wrong with Steven's method. Just another option.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MechEng2005]

Another option, since I think that multiple solutions to a problem are interesting, and I assume everybody here, as engineers, would agree...

Column A: Your data

Cell B1: =IF(A1<>0,ROW(A1)) - Copied down for all rows of data. Returns row number if data is present, otherwise FALSE.

Cell C1: =MATCH(MIN(B:B),B:B) - Returns row number of first data.

Cell C2: =MATCH(MIN(INDIRECT("B"&C1+1):B$10000),B:B) - Copy down to cell C8 (Note that the "B$10000" can be replaced with the maximum row of data.) Returns row number for 2-8 numbers.

Cell D1: =INDEX(A:A,C1) - Copy down to cell C8. Returns values of non-zero entries.

I like this method because it gives you the row number of non-zero cells, if that's important to you. Also, dragging down the formulas in columns C and D would allow any number of non-zero values, if for any reason it would be different than 8.

-- MechEng2005