Search code examples
excelexcel-formulams-office

How to remove common value in excel under different rows?

I have 2 columns.

The first column contains the account name of clients. If the clients purchased multiple products, there is no unique value. The client’s name will have multiple entry.

The second column contains the product they purchased. Say for example: computer, keyboard, mouse.

What is the formula in Excel in case I want to identify/remove all entries/clients who purchased a computer?

I need to have the data of those who only purchased either keyboard or mouse.

Example: Column 1. Column 2. Mark. Computer Mark. Keyboard Mark. Mouse Louis. Keyboard Louis. Mouse Ryan. Computer Ryan. Computer Kenneth. Computer Herbert. Keyboard Nino. Computer Nino. Mouse Jason. Mouse

In this scenario, Louis, Herbert, and Jason are the ones who did not purchase a computer.

I need assistance in creating a formula to identify quickly the 3 clients who did not purchase a computer. In real data, it involves thousands of clients which I cannot do manually.

Thank you.

Solution

  • Excel ms365:

    As currently written, OP is looking to filter out the names of people that did not buy a single computer:

    enter image description here

    Formula in D2:

    =UNIQUE(FILTER(A2:A13,COUNTIFS(A2:A13,A2:A13,B2:B13,"Computer")=0))

    PowerQuery:

    A nice alternative, for Excel 2016 for example, could be the use of PowerQuery:

    enter image description here

    Here the M-Code I used:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Filter = let l1=Table.Column(Source, "Person"), l2=Table.Column(Source, "Item") in List.Accumulate(List.Numbers(0, List.Count(l1)),List.Distinct(l1),(s,c)=> if l2{c}="Computer" then List.RemoveItems(s, {l1{c}}) else s)
in
    Filter

    You can now load the result back to Excel. Whenever your dataset grows, you could update the query to reflect the new information in the output.

    Markdown Sample Data:

    Person Item
    Mark Computer
    Mark Keyboard
    Mark Mouse
    Louis Keyboard
    Louis Mouse
    Ryan Computer
    Ryan Computer
    Kenneth Computer
    Herbert Keyboard
    Nino Computer
    Nino Mouse
    Jason Mouse