Col-1 Col-2 Col-3 Col-4
1 6 a b
1 6 a b
1 5 a b
2 7 a b
3 3 a b
3 9 a b
4 6 a b
4 4 a b
4 3 a b
5 3 a b
6 8 a b
7 8 a b
8 8 a b
8 8 a b
9 4 a b
9 4 a b
10 4 a b

I am interested to create a macro that we allow me to retrieve the data
1,4,and 8 from Col-1 and add the fifth Col with with "I want". My ouput
result should be :

Col-1 Col-2 Col-3 Col-4 Col-5
1 6 a b I want
1 6 a b I want
1 5 a b I want
4 6 a b I want
4 4 a b I want
4 3 a b I want
8 8 a b I want
8 8 a b I want


My data file contains hundred of lines. I need to retrive data by
user-define in this case it is 1,4,8!

Many thanks!


--
tbobo
------------------------------------------------------------------------
tbobo's Profile: http://www.excelforum.com/member.php...o&userid=31530
View this thread: http://www.excelforum.com/showthread...hreadid=519777

dont need a macro , in col 5 put

=IF(OR(A1=1,A1=4,A1=8),"I want","")

paste it down all of col 5

Rich


"tbobo" wrote:


Col-1 Col-2 Col-3 Col-4
1 6 a b
1 6 a b
1 5 a b
2 7 a b
3 3 a b
3 9 a b
4 6 a b
4 4 a b
4 3 a b
5 3 a b
6 8 a b
7 8 a b
8 8 a b
8 8 a b
9 4 a b
9 4 a b
10 4 a b

I am interested to create a macro that we allow me to retrieve the data
1,4,and 8 from Col-1 and add the fifth Col with with "I want". My ouput
result should be :

Col-1 Col-2 Col-3 Col-4 Col-5
1 6 a b I want
1 6 a b I want
1 5 a b I want
4 6 a b I want
4 4 a b I want
4 3 a b I want
8 8 a b I want
8 8 a b I want


My data file contains hundred of lines. I need to retrive data by
user-define in this case it is 1,4,8!

Many thanks!


--
tbobo
------------------------------------------------------------------------
tbobo's Profile: http://www.excelforum.com/member.php...o&userid=31530
View this thread: http://www.excelforum.com/showthread...hreadid=519777

Hi,
A macro solution:

Enter data as 1,4,8


Dim lastrow As Long, r As Long, i As Integer
Dim v As Variant
Dim ws1 As Worksheet, ws2 As Worksheet

Myvals = Application.InputBox("Enter selection criteria (n,n,n)", Type:=2)
If Myvals = o Then Exit Sub

v = Split(Myvals, ",")
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")

With ws1
For r = 1 To .Cells(Rows.Count, "a").End(xlUp).Row
For i = LBound(v) To UBound(v)
If .Cells(r, 1) = CInt(v(i)) Then
.Rows(r).Copy ws2.Cells(Rows.Count, "a").End(xlUp)(2)
ws2.Cells(ws2.Cells(Rows.Count, "a").End(xlUp).Row, 5) = "I
want"
Exit For
End If
Next i
Next r
End With
End Sub


"Rich Mcc" wrote:

dont need a macro , in col 5 put

=IF(OR(A1=1,A1=4,A1=8),"I want","")

paste it down all of col 5

Rich


"tbobo" wrote:


Col-1 Col-2 Col-3 Col-4
1 6 a b
1 6 a b
1 5 a b
2 7 a b
3 3 a b
3 9 a b
4 6 a b
4 4 a b
4 3 a b
5 3 a b
6 8 a b
7 8 a b
8 8 a b
8 8 a b
9 4 a b
9 4 a b
10 4 a b

I am interested to create a macro that we allow me to retrieve the data
1,4,and 8 from Col-1 and add the fifth Col with with "I want". My ouput
result should be :

Col-1 Col-2 Col-3 Col-4 Col-5
1 6 a b I want
1 6 a b I want
1 5 a b I want
4 6 a b I want
4 4 a b I want
4 3 a b I want
8 8 a b I want
8 8 a b I want


My data file contains hundred of lines. I need to retrive data by
user-define in this case it is 1,4,8!

Many thanks!


--
tbobo
------------------------------------------------------------------------
tbobo's Profile: http://www.excelforum.com/member.php...o&userid=31530
View this thread: http://www.excelforum.com/showthread...hreadid=519777

Let's say I enter 1,4,8. I have the data follow: "it starts with row 2
and row 1 empty."

How can I have row 1 has the colum header to be displayed witht the
data below:

1 6 a b I want
1 6 a b I want
1 5 a b I want
4 6 a b I want
4 4 a b I want
4 3 a b I want
8 8 a b I want
8 8 a b I want

It works great! Thank you much for the quick reply. I need the column
header for row 1. Please help!


--
tbobo
------------------------------------------------------------------------
tbobo's Profile: http://www.excelforum.com/member.php...o&userid=31530
View this thread: http://www.excelforum.com/showthread...hreadid=519777

Sub a()
Dim lastrow As Long, r As Long, i As Integer
Dim v As Variant
Dim ws1 As Worksheet, ws2 As Worksheet

Myvals = Application.InputBox("Enter selection criteria (n,n,n)", Type:=2)
If Myvals = o Then Exit Sub

v = Split(Myvals, ",")
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
' Change array below if you want different headings .....
ws2.Cells(1, 1).Resize(1, 5) = Array("Col-1", "Col-2", "Col-3", "Col-4",
"Col-5")
With ws1
For r = 2 To .Cells(Rows.Count, "a").End(xlUp).Row
For i = LBound(v) To UBound(v)
If .Cells(r, 1) = CInt(v(i)) Then
.Rows(r).Copy ws2.Cells(Rows.Count, "a").End(xlUp)(2)
ws2.Cells(ws2.Cells(Rows.Count, "a").End(xlUp).Row, 5) = "I
want"
Exit For
End If
Next i
Next r
End With
End Sub

"tbobo" wrote:


Let's say I enter 1,4,8. I have the data follow: "it starts with row 2
and row 1 empty."

How can I have row 1 has the colum header to be displayed witht the
data below:

1 6 a b I want
1 6 a b I want
1 5 a b I want
4 6 a b I want
4 4 a b I want
4 3 a b I want
8 8 a b I want
8 8 a b I want

It works great! Thank you much for the quick reply. I need the column
header for row 1. Please help!


--
tbobo
------------------------------------------------------------------------
tbobo's Profile: http://www.excelforum.com/member.php...o&userid=31530
View this thread: http://www.excelforum.com/showthread...hreadid=519777