Get specific columns from another worksheet if condition is met

Question

I have an excel workbook which is updated daily (Sheet1) . I need to create another sheet (Sheet2) which will have specific columns from first sheet (Sheet1), if condition is met (Error = YES). Below are pictures of examples how this should look like. 

What is the best solution to create report like its showed on sheet 2? Thanks in advance

Answer 1

The easiset thing to do is to select a cell in your table, choose Data  /  "From Table"  (on Get & Tranform tab) - Excel will transform your table into a Data Table, and the query dialog will open.  Filter your last column to show only Yes, and delete the columns you don't want, including the column of "Yes" Values. Then choose "Close and Load" and you're done.

Note that when your source table updates/is changed, all you need to do is use Data / Refresh All and your second table will update correctly.

Answer 2

Hi 

Easy way is transfer to table and filter the target rows, then copy manually to another sheet.

If you don't want to copy & paste manually, it should be achieved by add-ins or Macros.

You can clickHere for details.

Best

Answer 3

Ok, i will try to be more specific. On first sheet i have more than 10 000 rows and i dont have premission to change this table. I need to make automaticaly query to get some columns from first table, with specific conditition. Also, I can not use any plugin because of limitation of downloading software on my job.

Answer 4

Go to sheet 2, Press Alt+F11, Click Inset > Module and paste following Macros and run:

Sub MoveRowBasedOnCellValue()

'Updated by Extendoffice 2017/11/10

    Dim xRg As Range

    Dim xCell As Range

    Dim I As Long

    Dim J As Long

    Dim K As Long

    I = Worksheets("Sheet1").UsedRange.Rows.Count

    J = Worksheets("Sheet2").UsedRange.Rows.Count

    If J = 1 Then

    If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then J = 0

    End If

    Set xRg = Worksheets("Sheet1").Range("F1:C" & I)

    On Error Resume Next

    Application.ScreenUpdating = False

    For K = 1 To xRg.Count

        If CStr(xRg(K).Value) = "Yes" Then

            xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)

            J = J + 1

        End If

    Next

    Application.ScreenUpdating = True

End Sub

Best

Answer 5

Power query is a built-in part of Excel since Office 2016. What version are you using?