data extract

Question

i have uploaded the file , i have a huge data of such type and this is just an example data to understand.
basically i want to know a formula or function through which i can fetch my required data from sheet 1 source data to sheet 2 .
i want agent id 512 and 112 , only connected system disposition in sheet 2.
copy paste can be easy in a small data but between lacs of similar entries i wanted the things should be sorted. please help.

hansvogelaar · Answer

aayushmanmishra&nbsp;
If you have Microsoft 365 or Office 2021, select A2 on the Scrubbed sheet and enter the following formula:
=FILTER('source data'!A2:H11,(('source data'!A2:A11=112)+('source data'!A2:A11=512))*('source data'!C2:C11="CONNECTED"))

aayushmanmishra · Answer

Thanks ! Will apply and get back to you within sometime.

aayushmanmishra · Answer

i m unable to apply this formula , please if you can show me once. In my excel it is not showing any formula of filter rather filterxml it showing.&nbsp;

hansvogelaar · Answer

aayushmanmishra&nbsp;
If you don't have Microsoft 365 or Office 2021, the FILTER function will not be available.
As an alternative, you can use Advanced Filter:
1) Create a Criteria range like this - I created it in I1:J3:

2) Select A1:H1 on the Scrubbed sheet., then select Data &gt; Advanced.
Excel will display a warning, click OK.
Populate the dialog as follows:

Tick the option button ' Copy to another location'.
Click in the List range box and select the data on the source data sheet.
Click in the Criteria range box and select the criteria range on the source data sheet.
Click in the Copy to box and select the headers in A1:LH1 on the scrubbed sheet.
Click OK.

sergeibaklan · Answer

aayushmanmishra&nbsp;
As variant that could be Power Query with two steps if create helper table with codes to filter

as
let
    Source = Excel.CurrentWorkbook(){[Name="SourceData"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each  List.Contains( Filter[AGENT_ID], [AGENT_ID] ))
in
    #"Filtered Rows"

Forum Discussion

data extract

8 Replies

Resources