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
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
RE: Picking values from a column
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
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
CODE
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
CODE
Range Range Range
data data data
0 >0 1
0 2
1
0
2
0
RE: Picking values from a column
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://f
RE: Picking values from a column
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
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Picking values from a column
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
http
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
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