Filter Two Columns in a worksheet with the same value

[Rudrabhadra]

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.

Thanks 

[#2 · Accepted solution]

@Rudrabhadra 

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:

1. Select the range of data that you want to filter, including both columns (Column D and Column E in your case).
2. Go to the "Data" tab in the Excel ribbon.
3. Click on the "Advanced" button in the "Sort & Filter" group. The "Advanced Filter" dialog box will appear.
4. In the "Advanced Filter" dialog box, select the option "Filter the list, in-place" if you want to filter the data in the same location, or select the option "Copy to another location" if you want to filter the data to a new location.
5. In the "List range" field, Excel should automatically populate the range of your selected data.
6. In the "Criteria range" field, specify the criteria for filtering the data. Create a criteria range with the same structure as your data range, where you specify the value "XYZ" in both the purchaser and seller columns. For example, you can create a new range with the same number of rows as your data range and enter "XYZ" in both columns D and E.
7. Click on the "OK" button. Excel will apply the filter based on the specified criteria.

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:

1. Select the range of data that you want to create a PivotTable from, including both columns (Column D and Column E in your case).
2. Go to the "Insert" tab in the Excel ribbon.
3. Click on the "PivotTable" button. The "Create PivotTable" dialog box will appear.
4. In the dialog box, make sure the correct range is selected for the data you want to analyze.
5. Choose whether you want to place the PivotTable in a new worksheet or in an existing worksheet.
6. Click on the "OK" button. A new worksheet or the existing worksheet will open, and the PivotTable Field List will appear on the right.
7. In the PivotTable Field List, drag the "purchaser" field to the "Rows" area, and then drag the "seller" field to the "Columns" area.
8. Now, you should see a grid with row labels representing the distinct values in the purchaser column and column labels representing the distinct values in the seller column.
9. Find and select the value "XYZ" in both the row labels and column labels. This will filter the data to show only the rows and columns where both the purchaser and seller columns contain the value "XYZ".

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.

[#3]

@Rudrabhadra 

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

[#4]

Thanks... The pivot option worked in the way i wanted.
I tried with the Filter option but returned a message " This formula is missing a range reference or a defined name." May be I have done something wrong...
Thank you very much for the help.