Organizing a list of data from a table

[chrgrose]

I have a large table of data with various values. I want to find all the values in the table which satisfy some condition (eg. between 5 and 6) and copy them individually as a list.

I can't simply find how many there are which satisfy the condition because they are not all the same. If it helps, I can set all values in the table which do not satisfy the condition to zero or some other value.

How could this best be accomplished? I can use native code or VBA.

Thanks in advance!

 

[Manohar M G]

Hi....

If you dont mind can you explain clearly with example... it will helpful to solve it...

 

[chrgrose]

So, if I have the following table with values like so:

1.1 1.7 9.1 6.9 5.0 2.1 5.2 6.0 9.0 7.9
7.4 4.2 8.9 3.7 5.2 7.8 2.2 3.4 0.1 5.4
7.7 3.3 2.7 8.9 5.6 7.7 3.5 2.9 1.7 8.4

and the above values are in their own cells.

I want to find all values between 3 and 4 in this table and I can see that I have 4 values. I want a procedure which will return the values in a new table like so:

3.7 3.4 3.3 3.5

each value in it's own cell.

 

[Manohar M G]

Hi...

I have one question..... u mention that.

1.1 1.7 9.1 6.9 5.0 2.1 5.2 6.0 9.0 7.9
7.4 4.2 8.9 3.7 5.2 7.8 2.2 3.4 0.1 5.4
7.7 3.3 2.7 8.9 5.6 7.7 3.5 2.9 1.7 8.4

and the above values are in their own cells.. means....

1.1 is in one cell(A1) 1.7 is in another cell (A2).. like this.. or some other way..

 

[VoG]

Try this - output in column L

Sub Numbers()
Dim r As Range, c As Range
Set r = Range("A1:J3")
For Each c In r
    If c.Value >= 3 And c.Value <= 4 Then Range("L" & Rows.Count).End(xlUp).Offset(1).Value = c.Value
Next c
End Sub

 

[chrgrose]

Hi...

I have one question..... u mention that.

1.1 1.7 9.1 6.9 5.0 2.1 5.2 6.0 9.0 7.9
7.4 4.2 8.9 3.7 5.2 7.8 2.2 3.4 0.1 5.4
7.7 3.3 2.7 8.9 5.6 7.7 3.5 2.9 1.7 8.4

and the above values are in their own cells.. means....

1.1 is in one cell(A1) 1.7 is in another cell (A2).. like this.. or some other way..

Exactly.

 

[chrgrose]

Try this - output in column L

Sub Numbers()
Dim r As Range, c As Range
Set r = Range("A1:J3")
For Each c In r
    If c.Value >= 3 And c.Value <= 4 Then Range("L" & Rows.Count).End(xlUp).Offset(1).Value = c.Value
Next c
End Sub

You are a wizard among wizards. My only problem is that I'd like this to be performed for many different ranges. So If I wanted to find values between 0 and 1, 1 and 2, 2 and 3.... onto 100, could I make this perform repeatedly for increasing values and put them in new columns?

 

[Manohar M G]

Hi...

Vog is written a write code... if u want to change ranges.. u have to change the values in the code... Vog written ranges between 3 and 4... u have to write what ranges u needed in code...

You cannot add new columns.. because Vog written column "A" to Column "J".. If u want to add new columns.. u have to write column name..

 

[VoG]

Try this. It does 0:1,1:2, ... 9:10

Sub Numbers()
Dim r As Range, c As Range, i As Currency
Set r = Range("A1:J3")
For Each c In r
    For i = 0 To 9
        If c.Value >= i And c.Value <= i + 1 Then
            Cells(Rows.Count, i + 12).End(xlUp).Offset(1).Value = c.Value
            Exit For
        End If
    Next i
Next c
End Sub

 

[chrgrose]

Try this. It does 0:1,1:2, ... 9:10

Sub Numbers()
Dim r As Range, c As Range, i As Currency
Set r = Range("A1:J3")
For Each c In r
    For i = 0 To 9
        If c.Value >= i And c.Value <= i + 1 Then
            Cells(Rows.Count, i + 12).End(xlUp).Offset(1).Value = c.Value
            Exit For
        End If
    Next i
Next c
End Sub

That's incredible. Last thing: I want to return the values for a different table in the same relative position as the values this code returns. I tried using the following but it doesn't work:

Sub Numbers()
Dim r As Range, c As Range, f As Range, i As Currency
Set r = Range("A1:J3")
Set f = Range("A4:J7")
For Each c In r
    For i = 0 To 9
        If c.Value >= i And c.Value <= i + 1 Then
            Cells(Rows.Count, i + 12).End(xlUp).Offset(1).Value = f.Value
            Exit For
        End If
    Next i
Next c
End Sub

This is the last part.