How to extract Right Data

[niladri20052006]

Hi All,

Please find below link to get a sample file.

http://www.box.net/shared/thmvi5blh13eehfdqz24

Here you will get two sheets as Sheet1 and Sheet2. the first column is Remarks.

In the second sheet (Sheet2) I have some common columns which are also in Sheet1.

I want all the info in sheet2 (mentioned columns) where it will get "YES" from remarks column from Sheet1.

Over all I want only specific info in sheet2 whre it will get "YES" from remarks column from Sheet1.

Please revert if you have any question(s).

Thanks

Niladri

 

[Ron Coderre]

Here's an approach to try:
(It's less complicated than it might look)
Assumptions:
Sheet1 contains your data in cells A1:BG20 with the headings in Row_1
Sheet2 is the destination for the extracted data
and you want to pull Sheet1 data rows where the Remark = YES

Using Sheet2:
AB1: Remark
AB2: YES

A1:Z1 contains the column headings from Sheet1 that you want to return

On Sheet2
• Data.Advanced_Filter (for Excel 2007 and later...otherwise: Data.Filter.Advanced_Filter)
...Check: Copy to another location
...List Range: Select Sheet1!$A$1:$BG$20
...Criteria Range: Select Sheet2!$AB$1:$AB$2
...Copy To: Select Sheet2!$A$1:$Z$1
...Click [OK]
(Notice: you are on Sheet2 and but the source data is on Sheet1)
The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.

Is that something you can work with?

 

[niladri20052006]

Hi Ron,

This is exactly the same what I was looking for...

It is amazing!!

Thanks a lot Ron

 

[Akinrotimi]

Hi,

In case your data expands as I expect over time you can use the code below(for up to 1,000 rows of data).This way you won't need to tweak the process every time.

Application.ScreenUpdating = False
    Sheets("Sheet1").Select
    Range("B27").Select
    Sheets("Sheet2").Select
     Range("a2:z100").Select
    Selection.ClearContents
    Cells.Select
    Selection.NumberFormat = "General"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC=""yes"",Sheet1!RC[52],""""),"""")"
    Range("A2").Select
    Sheets("Sheet1").Select
 
    Sheets("Sheet2").Select
    Selection.AutoFill Destination:=Range("A2:A13"), Type:=xlFillDefault
    Range("A2:A13").Select
    Selection.AutoFill Destination:=Range("A2:A28"), Type:=xlFillDefault
    Range("A2:A28").Select
    Sheets("Sheet2").Select
    Selection.AutoFill Destination:=Range("A2:A1001"), Type:=xlFillDefault
    Range("A2:A1001").Select
 
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC53,""""),"""")"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A1001")
    Range("A2:A1001").Select
 
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:B2"), Type:=xlFillDefault
    Range("A2:B2").Select
    Columns("B:B").EntireColumn.AutoFit
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:Z2"), Type:=xlFillDefault
    Range("B2:Z2").Select
    Range("I2").Select
 
    Range("B2").Select
    Sheets("Sheet1").Select
 
    ActiveWindow.SmallScroll ToRight:=3
 
    Range("N1").Select
    Sheets("Sheet2").Select
 
    Sheets("Sheet1").Select
    Range("M1").Select
    Columns("M:M").EntireColumn.AutoFit
    Range("M2").Select
    Sheets("Sheet2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC13,""""),"""")"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B1001")
    Range("B2:B1001").Select
    Range("C2").Select
    Sheets("Sheet1").Select
 
    Range("AL1").Select
 
    Range("W1").Select
 
    Range("AE1").Select
 
    Range("AJ1").Select
 
    Range("AS34").Select
    Sheets("Sheet2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC38,""""),"""")"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC9,""""),"""")"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC10,""""),"""")"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC11,""""),"""")"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC20,""""),"""")"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC18,""""),"""")"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC23,""""),"""")"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC42,""""),"""")"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC27,""""),"""")"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:N2"), Type:=xlFillDefault
    Range("K2:N2").Select
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC28,""""),"""")"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC29,""""),"""")"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC30,""""),"""")"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC53,""""),"""")"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC31,""""),"""")"
    Range("P2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC43,""""),"""")"
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC39,""""),"""")"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC41,""""),"""")"
    Range("S2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC40,""""),"""")"
    Range("T2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC50,""""),"""")"
    Range("U2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC32,""""),"""")"
    Range("V2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC33,""""),"""")"
    Range("W2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC34,""""),"""")"
    Range("X2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC35,""""),"""")"
    Range("Y2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC36,""""),"""")"
    Range("Z2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(Sheet1!RC1=""yes"",Sheet1!RC59,""""),"""")"
    Range("A2:Z2").Select
    Range("Z2").Activate
    ActiveWindow.SmallScroll ToRight:=9
    Range("B2:Z2").Select
    Range("Z2").Activate
 
    Range("C2:Z2").Select
    Selection.AutoFill Destination:=Range("C2:Z1001")
    Range("C2:Z1001").Select
    Columns("Z:Z").EntireColumn.AutoFit
 
    Range("A1:Z1001").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Columns("A:A").EntireColumn.AutoFit
    Application.CutCopyMode = False
    Cells.Select
    Cells.EntireRow.AutoFit
    Cells.EntireColumn.AutoFit
 
    Range("A1:Z1001").Select
    Range("Z1").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
 
 
    Columns("a:a").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(23, 1)), TrailingMinusNumbers:=True
    Range("A6").Select
 
    Columns("a:a").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Application.ScreenUpdating = True
 
    Range("A1").Select
End Sub

Regards

Rotimi

 

[Ron Coderre]

Hi Ron,

This is exactly the same what I was looking for...

It is amazing!!

Thanks a lot Ron

I'm glad I could help.