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

  • Thread starter Thread starter miniforexTrader
  • Start date 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
 
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
Click to expand...
I want to be able to extract the "B" and "D"
Click to expand...

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)))
 
How about
{=IF(ISNA(MATCH(A1:A5,B1:B3,0)),A1:A5,"")}
Dave D-C
Click to expand...
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.
 
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 -
Click to expand...

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.
 
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 -
Click to expand...

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
 
Back
Top