n3a5p7s9t1e3r
Reputation: 286
return a list of all elements present in one column that is not present in the other
I'm trying to create column where there are hundreds of items in a and b column, and I want to remove common items in b column and list them in different column in excel or google sheet.
| a | b | items present in b column only |
|---|---|---|
| a1 | a1 | a5 |
| a2 | a2 | a6 |
| a3 | a5 |
|
| a4 | a6 |
Upvotes: 0
Views: 290
Answers (1)
JvdV
Reputation: 75860
Excel:
Formula in C2:
=FILTER(B2:B5,COUNTIF(B2:B5,A2:A5)=0)
Google-Sheets:
Almost the same, but less explicit: =FILTER(B2:B,COUNTIF(B2:B,A2:A)=0)
Upvotes: 6
Related Questions
- Find all items on list B that are not on list A, Excel.
- How do i pull out unique values that are in one column but not another? Excel
- How do I return missing data when comparing two columns?
- Formula to display values in one column that do NOT appear in another column
- Excel: find values in column A but not in column B, return values in column C
- Query to list data that's not listed in another column
- how do I find the values that are in one column but not in the other
- Compare two Excel columns, output cells in A that do not appear in B
- excel:how to select values from column A which its values don't exist in column B
- Return values only if they are not present in current column