array formula to extract members of list A that are not in list B

  • Thread starter miniforexTrader
  • Start date
M

miniforexTrader

Hi,

Lets say I have data in a range A1:A5 that contains
"A","B","C","D","E" in each cell respectively. In Range B1:B3, the
values are "A","D","E".

I want to be able to extract the "B" and "D" using array formula.

I created a formula {=MATCH(A1:A5,B1:B3,0)} which creates a list of
numbers (index value of the location of the matches in the list) where
there are matches and #N/A where there are no matches. I want to be
able to pull out the #N/A index location so that I can identify the
"B" and then the "D", perhaps using a Small function but I have gotten
stuck....

Please advise.

TIA
 
T

T. Valko

Hi,

Lets say I have data in a range A1:A5 that contains
"A","B","C","D","E" in each cell respectively. In Range B1:B3, the
values are "A","D","E".

I want to be able to extract the "B" and "D" using array formula.

I created a formula {=MATCH(A1:A5,B1:B3,0)} which creates a list of
numbers (index value of the location of the matches in the list) where
there are matches and #N/A where there are no matches. I want to be
able to pull out the #N/A index location so that I can identify the
"B" and then the "D", perhaps using a Small function but I have gotten
stuck....

Please advise.

TIA
I want to be able to extract the "B" and "D"

Shouldn't that be "B" and "C"?

Enter this array formula in D1 and copy down until you get #NUM! errors
meaning all data has been extracted:

=INDEX(A$1:A$5,SMALL(IF(COUNTIF(B$1:B$3,A$1:A$5)=0,ROW(A$1:A$5)-MIN(ROW(A$1:A$5))+1),ROWS(D$1:D1)))
 
M

miniforexTrader

How about
{=IF(ISNA(MATCH(A1:A5,B1:B3,0)),A1:A5,"")}
Dave D-C
Hi Dave D-C

This solution requires a formula to be dragged the entire length of
the table being checked - it is an okay solution but not where I
wanted to go with this.

The correct application of this solution would utilize absolute
addresses for the range $B$1:$B$3 and for the $A$5 and relative
adresses for the A1 so that it checks each value in A as you copy down
the list.

={IF(ISNA(MATCH(A1:$A$5,$B$1:$B$3,0)),A1:$A$5,"")}

Thanks for your interest.
 
M

miniforexTrader

Shouldn't that be "B" and "C"?

Enter this array formula in D1 and copy down until you get #NUM! errors
meaning all data has been extracted:

=INDEX(A$1:A$5,SMALL(IF(COUNTIF(B$1:B$3,A$1:A$5)=0,ROW(A$1:A$5)-MIN(ROW(A$1­:A$5))+1),ROWS(D$1:D1)))

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Thanks Biff - this is the type of solution I was looking for - solves
the base problem exactly how I envisioned.

Do you have a way to prevent the return of duplicates in Column A -
say you had "A","B","C","D","E","A","B" -

Is there a way we could ad a countif to check the solution set (those
cells that are in the range from the first row of the solution to the
row above the current row as well as check B1:B3 (criteria range)?

Thanks again.
 
T

T. Valko

Shouldn't that be "B" and "C"?

Enter this array formula in D1 and copy down until you get #NUM! errors
meaning all data has been extracted:

=INDEX(A$1:A$5,SMALL(IF(COUNTIF(B$1:B$3,A$1:A$5)=0,ROW(A$1:A$5)-MIN(ROW(A$1­:A$5))+1),ROWS(D$1:D1)))

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Thanks Biff - this is the type of solution I was looking for - solves
the base problem exactly how I envisioned.

Do you have a way to prevent the return of duplicates in Column A -
say you had "A","B","C","D","E","A","B" -

Is there a way we could ad a countif to check the solution set (those
cells that are in the range from the first row of the solution to the
row above the current row as well as check B1:B3 (criteria range)?

Thanks again.
**********

I'm not sure I understand what you want but to prevent duplicates from being
entered in a range you can use data validation.

Assume the range of interest is A1:A10
Select the range A1:A10
Goto the menu Data>Validation
Allow: Custom
Formula: =COUNTIF(A$1:A$10,A1)<=1
OK
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top