1

I have two columns of data, A and B. I would like to merge these columns such that only the unique values are included in the final list. At the moment, I'm using an array formula from https://www.get-digital-help.com/2009/06/16/extract-an-unique-distinct-list-from-two-columns-using-excel-2007-array-formula/:

{=IFERROR(IFERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$20), 0)), INDEX($B$2:$B$7, MATCH(0, COUNTIF($C$1:C1, $B$2:$B$7), 0))), "")} 

While this works wonderfully, I'd like to avoid array formulas if at all possible, as they significantly slow our already crappy PCs. We're also not able to use VBA for reasons, so that's not an option either.

Is there any way to do this?

  • 1
    Presumably you don't mean "manually" – Yorik Aug 31 '17 at 16:42
0

Well, I found a way to do it that seems to be working for now, but it sure as hell ain't pretty.

Column A has a set of data, say 200 total values.

Column B has a second set of data, say 175 values.

Column C checks if the value in column B is present in column A

 =IF(COUNTIF($A$2:$A$750,B2)>0,"Match","No Match")

Column E has a count that just keeps track of where we are relative to the end of the set of data in column A

=IF(A2=0,MAX($E$1:E1)+1,"")

Column F has a count that increments whenever Column C indicates a unique value

=IF(C2="No Match",MAX($F$1:F1)+1,"")

Column G is where the two sets of data are combined. First, it lists the values in A. Once it reaches the end of the list and starts finding 0s, it uses the index created in column E to find the first unique value in B and increments up until it reaches the final value.

=IF(A2=0,IF(E2>MAX(F:F),"",INDEX(B:B,MATCH(E2,F:F))),A2)
|improve this answer|||||
0

There's a good discussion of how to extract unique and distinct values at this Excel tutorials website.

And the answer to your question is yes, it just involves using an extra INDEX() for each part. Here is the modification:

=IFERROR(INDEX($A$2:$A$20,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$20),0),0)),INDEX($B$2:$B$7,MATCH(0,INDEX(COUNTIF($C$1:C1,$B$2:$B$7),0),0)))

I left off the outermost IFERROR(). Hope this helps.

|improve this answer|||||

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.