×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Picking values from a column

Picking values from a column

Picking values from a column

(OP)
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   

RE: Picking values from a column

AutoFilter for all columns not equal to zero?

RE: Picking values from a column

(OP)
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

RE: Picking values from a column

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

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Picking values from a column

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

CODE

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

RE: Picking values from a column

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.

CODE

Data         Criteria      Output
Range          Range        Range

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

RE: Picking values from a column

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/getfile.aspx?folder=c97a3601-ea69-44ea-b949-dacd9bfd27f0&amp;file=find_only_8_numbers.xls

RE: Picking values from a column

(OP)
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

RE: Picking values from a column

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

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Picking values from a column

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/
 

RE: Picking values from a column

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  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources