How to extract information

[eagle028]

Hi, with the following data

Number Name Sales Remarks
11233 Becky 50000 SG11233 Sales
55665 Ting 19000 Non sales record
4567 Mei 10000 SG4567 Sales
18004 Mark 75000 Non sales record
55991 Ting 20000 SG55991 Sales
60606 Sam 25000 Non sales record

Is it possible to create a formula / marco to extract the rows with the Remarks SGXXXX Sales such that the final result is something like:

Number Name Sales Remarks
11233 Becky 50000 SG11233 Sales
4567 Mei 10000 SG4567 Sales
55991 Ting 20000 SG55991 Sales

Will having the word "Sales" in Remarks help with the extraction?

 

[anandvarma]

try this code

Sub Macro1()
'
' Macro1 Macro
'

'
Range("A1:B1").Select
ActiveCell.Range("A1:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$E$7").AutoFilter Field:=4, Criteria1:=Array( _
"SG11233", "SG4567", "SG55991"), Operator:=xlFilterValues
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
End Sub

 

[eagle028]

E.g. was not clear. Have re-submitted another one.

 

[eagle028]

Hi,

Sorry, I think my e.g. was not very clear:

-------A---------B-----C----------D
1----Number---Name----Sales----Remarks
2----11233----Becky----50000----SG11233 Sales
3----55665----Ting-----19000-----Non sales record
4----4567-----Mei------10000-----SG4567 Sales
5----18004----Mark-----75000----Non sales record
6----55991----Ting-----20000-----SG55991 Sales
7----60606----Sam-----25000-----Non sales record

 

[Sanjeev1976]

Hi,

Sorry, I think my e.g. was not very clear:

-------A---------B-----C----------D
1----Number---Name----Sales----Remarks
2----11233----Becky----50000----SG11233 Sales
3----55665----Ting-----19000-----Non sales record
4----4567-----Mei------10000-----SG4567 Sales
5----18004----Mark-----75000----Non sales record
6----55991----Ting-----20000-----SG55991 Sales
7----60606----Sam-----25000-----Non sales record

Use autofilter and then copy the records

 

[eagle028]

How would a marco for the auto filter look like?

 

[eagle028]

Especially since the "sales" condition comes after the numbers?

 

[eagle028]

Is this not do-able via Macro?