CODEWITHSUNDEEP
excelexcel-formula
KN Suiteacc
KN Suiteacc

Reputation: 3

How do I separate the matches between two columns into a column of their own?

I'm doing inventory at work, basically I have 2 columns of data: one is what is expected to be here, and one column is what I've found. I'm trying to set it up where I can compare the two columns and then have those that are found either be moved to a different column (preferably), or be sorted to the top so it'd show the found and not found separately. I'm currently using =IF(COUNTIF($B:$B, $A1)=0, "No match in B", "") to check what's there/not there, but this gives the answers out of order. Please help if able and thank you in advance. :)

Edit:

My actual table is almost 4k rows so here is an example.

apple apple bananna grape orange lime kiwi lemon grape orange

found expected
apple apple
orange grape
orange lime
grape lemon
grape orange

I'd need the formula to check if column A has a match in column B (which I already have working). And then I want to have the ones that DO have a match, have that row in column A be moved to a separate column/table (basically so I can send what we have)

Upvotes: 0

Views: 77

Answers (1)

P.b
P.b

Reputation: 11468

When using Office 365 you could use =UNIQUE(FILTER(A2:A6,COUNTIF(B2:B6,A2:A6)))

Older versions can use and copy down =IFERROR(INDEX($A:$A,AGGREGATE(15,6,MATCH($B$2:$B$6,$A$2:$A$6,0)+1,ROW(A1))),"") array function needs entered with ctrl+shift+enter

This provides the unique distinct values that occur in the range of column B. +1 behind the match result is because we start searching from row 2, therefore the match result will represent the row # minus 1. +1 corrects this.

Upvotes: 0

Related Questions