Nov 20 2021 10:38 AM
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.
Nov 20 2021 11:08 AM
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"))
Nov 20 2021 11:22 AM - edited Nov 20 2021 11:34 AM
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.
Nov 20 2021 12:23 PM
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 > 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.
Nov 20 2021 12:23 PM
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"
Nov 20 2021 09:24 PM
Nov 20 2021 09:27 PM
Nov 21 2021 05:12 AM
You don't need to select something and copy/paste. The only you need is to click on Refresh All when new data appears.