Array Formula to Compare Columns and Return Unique Entries

[dlamarche]

Hello. Can one post a question about an array formula here or is it only VBA related questions?

Than you.

[TonyJollans]

Go right ahead - you can pose questions about any aspect of Excel.

[dlamarche]

Thank you Tony. I hate to double post but I have a hard time to be understood clearly in the other one. I will try here ok?

I need to compare two columns of text and come up ONLY with those in column B that are not in column A. All the solutions I've seen and tried came up with a blank for each cell that is common. For instance those that appeared in both occupied a cell but were blank and those unique to column B were visible.

I am convinced that an array formula can display ONLY those that only appear in column B and ignore the existence of those in both.

For instance: (I used dots in lieu if spaces)

A........................................B
Montreal.........................Montreal
Laval..............................Hull
Quebec...........................Ottawa
Hull................................Toronto
Toronto..........................Delson
Gaspe
Alma

Selecting a few cells, typing the array formula and pressing Ctrl+Shift+Enter should display Ottawa and Delson at the top and not further down because Montreal and Hull appear in both columns. The other selected cells selected prior to typing the multicell array formula will display nothing.

Some have replied with the following formula :

=IF(COUNTIF($A$1:$A$7,B1)=0, B1,"")

But I need to copy it down to the last cell in column B and this creates many blanks (except for those that are only in B). If the first value that is in B and not in A is found in row 135 then the formula that produces a result 135 row below. I want it to be at the top. The second unique value should appear immediately below the first one even if the formula found it 55 rows further below.

Hum I wonder if I explained it clearly. I asked my girlfriend my question (and she doesn't know a thing about Excel) and she understood it right away!

I will be very impressed and grateful for any reply.

[TonyJollans]

Hi dlamarche,

Welcome to VBAX!

This is a trifle difficult! This array formula will give the row numbers of the unique towns in column B (and #NUM errors at the end which can be weeded out if you want)

=SMALL(IF(COUNTIF($A$1:$A$7,$B$1:$B$7)=0,ROW($B$1:$B$7),FALSE),ROW(INDIRECT("1:7")))

But I am having difficulty translating that into values in the same formula - neither OFFSET nor INDIRECT want to work for me.

If you put the above in C1:C7 and then in D1: D7 put

=OFFSET($B$1,C1-1,0)

with Ctrl+Enter (i.e. not an array)

That will give your results, but it is a two step process.

[dlamarche]

Okayyyy Tony. You are not a mentor for nothing coz it worked perfectly. Yes in two steps but that's ok.

I added some IF(ISERROR( ... ) then, else) stuff to both functions and it worked!

I will implement this at my customer and show them how to copy it using the Fill Handle and that should do the job!

Thank you a bunch Tony

[Jacob Hilderbrand]

I don't think you will be able to get away from using two columns, but you do not need to use an array formula.

In C1 put:

=IF(COUNTIF($A$1:$A$7,$B1)=0,ROW($B1),"")

And in D1 put:

=IF(ISERROR(INDIRECT("B" &SMALL(C$1:C$5,ROW(A1)))),"" ,INDIRECT("B" &SMALL(C$1:C$5,ROW(A1))))

Fill down.

[Krishna Kumar]

Hi dlamarche,

Try in C1 and copied down,

=IF(COUNTIF($A$1:$A$7,B1)=0,B1,0)

In D1 and copied down,

=IF(ROW()-0>COUNTIF($C$1:$C$7, "<>0"), "", INDEX($C$1:$C$7, SMALL(IF($C$1:$C$7<>0, ROW($C$1:$C$7)), ROW()-0)))

confirm with Ctrl+Shift+Enter

HTH

Kris