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

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

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

wrote in message
...
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)))

--
Biff
Microsoft Excel MVP

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

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


wrote:
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

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

On Feb 9, 12:24*pm, Dave D-C wrote:
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.

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

On Feb 8, 2:45*pm, "T. Valko" wrote:
wrote in message

...





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)))

--
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.

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

wrote in message
...
On Feb 8, 2:45 pm, "T. Valko" wrote:
wrote in message

...





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)))

--
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 DataValidation
Allow: Custom
Formula: =COUNTIF(A$1:A$10,A1)<=1
OK

--
Biff
Microsoft Excel MVP