Jun 05 2023 12:43 AM
Jun 05 2023 12:43 AM
Need to filter data with some values in two columns at a time.
Eg.. Column D : purchaser
Column E : Seller
Both columns may have the same name "XYZ".. Is it possible that I can get "XYZ" occurrences filtered from both the columns at a time...
I had tried pivot option as well I couldn't get a solution.... Appreciate if anyone help on this.
Jun 05 2023 01:08 AMSolution
It is possible to filter data based on values in two columns simultaneously in Excel. You can achieve this by using the Advanced Filter feature in Excel.
Here is how you can do it:
The data will be filtered to show only the rows where both the purchaser and seller columns contain the value "XYZ". The filtered data will be displayed either in the same location or in the new location, depending on the option you selected in step 4.
Note: If you selected the option "Copy to another location," make sure to specify the destination range where you want the filtered data to be copied to in the "Copy to" field of the "Advanced Filter" dialog box.
By using the Advanced Filter feature and specifying the criteria for both columns, you can effectively filter data based on values appearing in both columns simultaneously.
You can also use a PivotTable to filter data based on values in two columns simultaneously in Excel. Here's how you can do it:
By using a PivotTable and selecting the appropriate row and column labels, you can filter the data based on values appearing in both columns simultaneously. The PivotTable will dynamically update based on your selections, allowing you to easily analyze and view the filtered data.
Note: The steps provided may vary slightly depending on your version of Excel, but the general concept of using a PivotTable to filter data based on two columns remains the same.
Jun 05 2023 02:11 AM
For 365 users there are formulae which generate the required formats whilst leaving the original data untouched. The basic array formula would be
= FILTER(Table1, (Table1[purchaser]="XYZ")+(Table1[seller]="XYZ"))
Slightly more complicated would be to treat the second party company names as a 2-column array and process the values by row
= FILTER(Table1, BYROW(company="XYZ", ORλ)) ORλ = LAMBDA(x, OR(x))
Jun 05 2023 03:00 AM