I know many questions have been asked for using INDEX and MATCH with criteria and returning a unique list without duplicates. And I have that much working. But I ran into a problem.

Here is my data:

enter image description here

Here is the list i uniquely generate:

enter image description here

The problem is, it skips Tom Smith because it thinks that Smith, Tom is a duplicate so it removes it. This is a problem. Here is the formula I'm using:

=INDEX('C:\path\to\file\[schedule sample.xlsx]Sheet1'!$B$2:$B$108, MATCH(0, COUNTIF($A$2:A2, 'C:\path\to\file\[schedule sample.xlsx]Sheet1'!$B$2:$B$108), 0))

how do I prevent the problem of excluding names if only column B matches?


You need to use a concatenation of all relevant columns for the comparison.

For the given example, this result in rather complicated formulas, I am afraid. That's why Excel has a function in the filtering where it does exactly this (check 'unique').

With formulas, you would need to put into E2 =B2&'|'&C2&'|'&D2 and copy it down. Then you run your whole copy-to-another-sheet logic on the E2 column only, and in the target sheet, split the string again at the '|' into three columns.

Depending on your limitations and requirements, it could be much easier to instead simply run the 'unique' filter on that E column, and get the result in the F column for wahtever you want to do with it. This is an action everytime data changes though; if you want it to work 'magically' through the formulas, you will need the more complex approach.

  • thanks for taking the time to lay this out. I get what you're saying about concatenation, I'll look how to incorporate it. I am well aware of the unique filter function but wanted something more automated. – William May 17 '16 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.