data extract

%3CLINGO-SUB%20id%3D%22lingo-sub-2989042%22%20slang%3D%22en-US%22%3Edata%20extract%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2989042%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20have%20uploaded%20the%20file%20%2C%20i%20have%20a%20huge%20data%20of%20such%20type%20and%20this%20is%20just%20an%20example%20data%20to%20understand.%3CBR%20%2F%3Ebasically%20i%20want%20to%20know%20a%20formula%20or%20function%20through%20which%20i%20can%20fetch%20my%20required%20data%20from%20sheet%201%20source%20data%20to%20sheet%202%20.%26nbsp%3B%3CBR%20%2F%3Ei%20want%20%3CSTRONG%3Eagent%20id%3C%2FSTRONG%3E%26nbsp%3B%20%3CFONT%20color%3D%22%23FF6600%22%3E512%3C%2FFONT%3E%26nbsp%3B%20and%20%3CFONT%20color%3D%22%23FF9900%22%3E112%3C%2FFONT%3E%20%2C%20only%20%3CFONT%20color%3D%22%233366FF%22%3Econnected%3C%2FFONT%3E%20%3CSTRONG%3Esystem%20disposition%3C%2FSTRONG%3E%20in%20sheet%26nbsp%3B%202.%26nbsp%3B%3CBR%20%2F%3Ecopy%20paste%20can%20be%20easy%20in%20a%20small%20data%20but%20between%20lacs%20of%20similar%20entries%20i%20wanted%20the%20things%20should%20be%20sorted.%20please%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2989042%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2989113%22%20slang%3D%22en-US%22%3ERe%3A%20data%20extract%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2989113%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1209178%22%20target%3D%22_blank%22%3E%40aayushmanmishra%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20Microsoft%20365%20or%20Office%202021%2C%20select%20A2%20on%20the%20Scrubbed%20sheet%20and%20enter%20the%20following%20formula%3A%3C%2FP%3E%0A%3CP%3E%3DFILTER('source%20data'!A2%3AH11%2C(('source%20data'!A2%3AA11%3D112)%2B('source%20data'!A2%3AA11%3D512))*('source%20data'!C2%3AC11%3D%22CONNECTED%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2989119%22%20slang%3D%22en-US%22%3ERe%3A%20data%20extract%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2989119%22%20slang%3D%22en-US%22%3EThanks%20!%20Will%20apply%20and%20get%20back%20to%20you%20within%20sometime.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2989123%22%20slang%3D%22en-US%22%3ERe%3A%20data%20extract%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2989123%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20m%20unable%20to%20apply%20this%20formula%20%2C%20please%20if%20you%20can%20show%20me%20once.%20In%20my%20excel%20it%20is%20not%20showing%20any%20formula%20of%20filter%20rather%20filterxml%20it%20showing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2989231%22%20slang%3D%22en-US%22%3ERe%3A%20data%20extract%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2989231%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1209178%22%20target%3D%22_blank%22%3E%40aayushmanmishra%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20don't%20have%20Microsoft%20365%20or%20Office%202021%2C%20the%20FILTER%20function%20will%20not%20be%20available.%3C%2FP%3E%0A%3CP%3EAs%20an%20alternative%2C%20you%20can%20use%20Advanced%20Filter%3A%3C%2FP%3E%0A%3CP%3E1)%20Create%20a%20Criteria%20range%20like%20this%20-%20I%20created%20it%20in%20I1%3AJ3%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0902.png%22%20style%3D%22width%3A%20213px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328372i98A878F26300504A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0902.png%22%20alt%3D%22S0902.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E2)%20Select%20A1%3AH1%20on%20the%20Scrubbed%20sheet.%2C%20then%20select%20Data%20%26gt%3B%20Advanced.%3C%2FP%3E%0A%3CP%3EExcel%20will%20display%20a%20warning%2C%20click%20OK.%3C%2FP%3E%0A%3CP%3EPopulate%20the%20dialog%20as%20follows%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0904.png%22%20style%3D%22width%3A%20228px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328374iB85FAD6750D6E66E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0904.png%22%20alt%3D%22S0904.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ETick%20the%20option%20button%20'%20Copy%20to%20another%20location'.%3C%2FP%3E%0A%3CP%3EClick%20in%20the%20List%20range%20box%20and%20select%20the%20data%20on%20the%20source%20data%20sheet.%3C%2FP%3E%0A%3CP%3EClick%20in%20the%20Criteria%20range%20box%20and%20select%20the%20criteria%20range%20on%20the%20source%20data%20sheet.%3C%2FP%3E%0A%3CP%3EClick%20in%20the%20Copy%20to%20box%20and%20select%20the%20headers%20in%20A1%3ALH1%20on%20the%20scrubbed%20sheet.%3C%2FP%3E%0A%3CP%3EClick%20OK.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

8 Replies

@aayushmanmishra 

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"))

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

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.

 

@aayushmanmishra 

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:

S0902.png

2) Select A1:H1 on the Scrubbed sheet., then select Data > Advanced.

Excel will display a warning, click OK.

Populate the dialog as follows:

S0904.png

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.

@aayushmanmishra 

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

image.png

as

let
    Source = Excel.CurrentWorkbook(){[Name="SourceData"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each  List.Contains( Filter[AGENT_ID], [AGENT_ID] ))
in
    #"Filtered Rows"
sorry i tried but failed, i m unable to understand it. and i m currently using office 2019
sorry i didn't understood.
i have a huge data to sort and fetch , it is very time consuming to select filter and copy and then paste.

@aayushmanmishra 

You don't need to select something and copy/paste. The only you need is to click on Refresh All when new data appears.