Excel Function for select data which NOT exist from the list

[wilchong via OfficeKB.com]

There are 2 data sets are assumed running in A3 and in B3 down, viz.:
In A3 down is: A, B, C, D, E, F, G, H, I, J, K and L
In B3 down is: B, A, H, C, F, I, K, J, H, C and J.

The result which I want is to list or select the data which is NOT exist in
the second set of data (Col B) when compare to the first set of data.

In my example, the result is D, E, G, and L actomactic appear in cell C3, C4,
C5 and C6 accordingly.
I need an Excel formula to have this result!

Many thanks,
Wilchong

 

[T. Valko]

Try this:

A3:A14 = rng1
B3:B13 = rng2

Enter this array formula** in C3 and copy down until you get blanks:

=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(rng2,rng1)=0)),INDEX(rng1,SMALL(IF(COUNTIF(rng2,rng1)=0,ROW(rng1)-MIN(ROW(rng1))+1),ROWS(C$3:C3))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[wilchong via OfficeKB.com]

Hello Biff,
Many thanks for your help! First of all, I tried the formula but failed to
work. May be this is my problem of understanding the formula.

In your note, it indicates that A3:A14 = rng1 & B3:B13 = rng2. As a result,
in the function of COUNTIF(rng2,rng1), I convert it to COUNTIF(B3:B13,A3), am
I right? Please help me on this!

The second question is the function of ROW(rng1), I converted it to ROW(A3:
A14), am I right?

Many thanks for your advice!
Wilchong

Try this:

A3:A14 = rng1
B3:B13 = rng2

Enter this array formula** in C3 and copy down until you get blanks:

=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(rng2,rng1)=0)),INDEX(rng1,SMALL(IF(COUNTIF(rng2,rng1)=0,ROW(rng1)-MIN(ROW(rng1))+1),ROWS(C$3:C3))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

There are 2 data sets are assumed running in A3 and in B3 down, viz.:
In A3 down is: A, B, C, D, E, F, G, H, I, J, K and L

[quoted text clipped - 11 lines]

Many thanks,
Wilchong

 

[T. Valko]

Here it is with the references:

=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0)),INDEX(A$3:A$14,SMALL(IF(COUNTIF(B$3:B$13,A$3:A$14)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3))),"")

Be sure to enter it as an array or it won't work properly:

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)



--
Biff
Microsoft Excel MVP

Hello Biff,
Many thanks for your help! First of all, I tried the formula but failed
to
work. May be this is my problem of understanding the formula.

In your note, it indicates that A3:A14 = rng1 & B3:B13 = rng2. As a
result,
in the function of COUNTIF(rng2,rng1), I convert it to COUNTIF(B3:B13,A3),
am
I right? Please help me on this!

The second question is the function of ROW(rng1), I converted it to
ROW(A3:
A14), am I right?

Many thanks for your advice!
Wilchong

Try this:

A3:A14 = rng1
B3:B13 = rng2

Enter this array formula** in C3 and copy down until you get blanks:

=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(rng2,rng1)=0)),INDEX(rng1,SMALL(IF(COUNTIF(rng2,rng1)=0,ROW(rng1)-MIN(ROW(rng1))+1),ROWS(C$3:C3))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

There are 2 data sets are assumed running in A3 and in B3 down, viz.:
In A3 down is: A, B, C, D, E, F, G, H, I, J, K and L

[quoted text clipped - 11 lines]

Many thanks,
Wilchong

 

[wilchong via OfficeKB.com]

Thank you Biff.
The formula is working, however, the formula ONLY list "D" from cell C3 to
C14.

Do you think the existing formula can be adjusted or revised to the result I
want, that is D, E, G, and L actomactic appear in cell C3, C4, C5 and C6
accordingly and C7 to C14 are the cells not display any thing.

Many thanks for your effort and time again!

Wilchong

Here it is with the references:

=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0)),INDEX(A$3:A$14,SMALL(IF(COUNTIF(B$3:B$13,A$3:A$14)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3))),"")

Be sure to enter it as an array or it won't work properly:

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Hello Biff,
Many thanks for your help! First of all, I tried the formula but failed

[quoted text clipped - 31 lines]

 

[T. Valko]

The formula works.

It sounds like you might have calculation set to manual. Make sure
calculation is set to Automatic:

Tools>Options>Calculation tab>Automatic

--
Biff
Microsoft Excel MVP

Thank you Biff.
The formula is working, however, the formula ONLY list "D" from cell C3 to
C14.

Do you think the existing formula can be adjusted or revised to the result
I
want, that is D, E, G, and L actomactic appear in cell C3, C4, C5 and C6
accordingly and C7 to C14 are the cells not display any thing.

Many thanks for your effort and time again!

Wilchong

Here it is with the references:

=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0)),INDEX(A$3:A$14,SMALL(IF(COUNTIF(B$3:B$13,A$3:A$14)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3))),"")

Be sure to enter it as an array or it won't work properly:

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Hello Biff,
Many thanks for your help! First of all, I tried the formula but failed

[quoted text clipped - 31 lines]

Many thanks,
Wilchong

 

[Robert McCurdy]

The named ranges are self-explanatory.

=IF(SUM(N(COUNTIF(BB,AA)=0))<ROW(A1),"",INDIRECT("A"&SMALL(IF(COUNTIF(BB,AA)=0,ROW(AA)),ROW(A1))))

Put this in cell C3 using Ctrl + Shift + Enter, then fill/copy down as required.
The suggestions provided by T. Valko and this one do work, but they based on the examples you posted. If the return value is not in the first column you'll need to adjust the formula yourself - like the "A" bit for example to the letter for that column.

The reason these are different, is there are many ways to do the same thing in XL, and we do love arguing ...er, I mean discussing which one is best

The formula is working, however, the formula ONLY list "D" from cell C3 to
C14.

This indicates you have failed to understand how to enter array formulae (look it up), or have array entered all at once - not for this solution.

Regards
Robert McCurdy

Thank you Biff.
The formula is working, however, the formula ONLY list "D" from cell C3 to
C14.

Do you think the existing formula can be adjusted or revised to the result I
want, that is D, E, G, and L actomactic appear in cell C3, C4, C5 and C6
accordingly and C7 to C14 are the cells not display any thing.

Many thanks for your effort and time again!

Wilchong

Here it is with the references:

=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0)),INDEX(A$3:A$14,SMALL(IF(COUNTIF(B$3:B$13,A$3:A$14)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3))),"")

Be sure to enter it as an array or it won't work properly:

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Hello Biff,
Many thanks for your help! First of all, I tried the formula but failed

[quoted text clipped - 31 lines]

Many thanks,
Wilchong

 

[wilchong via OfficeKB.com]

Thank Robert and Biff,
Let me study your instruction carefully first, I will let your all know the
result!

Many thanks,
Wilson

The named ranges are self-explanatory.

=IF(SUM(N(COUNTIF(BB,AA)=0))<ROW(A1),"",INDIRECT("A"&SMALL(IF(COUNTIF(BB,AA)=0,ROW(AA)),ROW(A1))))

Put this in cell C3 using Ctrl + Shift + Enter, then fill/copy down as required.
The suggestions provided by T. Valko and this one do work, but they based on the examples you posted. If the return value is not in the first column you'll need to adjust the formula yourself - like the "A" bit for example to the letter for that column.

The reason these are different, is there are many ways to do the same thing in XL, and we do love arguing ...er, I mean discussing which one is best

The formula is working, however, the formula ONLY list "D" from cell C3 to
C14.

This indicates you have failed to understand how to enter array formulae (look it up), or have array entered all at once - not for this solution.

Regards
Robert McCurdy

Thank you Biff.
The formula is working, however, the formula ONLY list "D" from cell C3 to

[quoted text clipped - 22 lines]

 

[wilchong via OfficeKB.com]

Hello Biff,
I have checked with my Excel spreadsheet and confirmed to you that the
"calculation" in spreadsheet is set "Automatic"! The formula still doesn't
work exactly!

Thanks,
Wilchong

The formula works.

It sounds like you might have calculation set to manual. Make sure
calculation is set to Automatic:

Tools>Options>Calculation tab>Automatic

Thank you Biff.
The formula is working, however, the formula ONLY list "D" from cell C3 to

[quoted text clipped - 23 lines]

 

[T. Valko]

Here's a small sample file that demonstrates this:

xExtractMissing.xls 15kb

http://cjoint.com/?fcepu2wnPz

--
Biff
Microsoft Excel MVP

Hello Biff,
I have checked with my Excel spreadsheet and confirmed to you that the
"calculation" in spreadsheet is set "Automatic"! The formula still
doesn't
work exactly!

Thanks,
Wilchong

The formula works.

It sounds like you might have calculation set to manual. Make sure
calculation is set to Automatic:

Tools>Options>Calculation tab>Automatic

Thank you Biff.
The formula is working, however, the formula ONLY list "D" from cell C3
to

[quoted text clipped - 23 lines]

Many thanks,
Wilchong

 

[wilchong via OfficeKB.com]

Good Morning Biff,
I got the file, I will study it carefully!

Many thanks for your help!

Wilchong

Here's a small sample file that demonstrates this:

xExtractMissing.xls 15kb

http://cjoint.com/?fcepu2wnPz

Hello Biff,
I have checked with my Excel spreadsheet and confirmed to you that the

[quoted text clipped - 18 lines]

 

[wilchong via OfficeKB.com]

Dear Biff,
Thank you so much for your Excel demo file. I finally found out what wrong
with me! It is because my insufficient knowledge regarding the function
"enter" procedure. The reason I did wrong is because I highlighed the whole
cell range from C3 to C14 and press CTRL, SHIFT and ENTER at the same time.
Before, it is the only way I know. Right now, I know CTRL, SHIFT and ENTER
can enter in one cell first and copy!

Actually, may I ask you one question, what is the logic in your function
which can list the missing data from second set of data? I saw you have put --
infront of COUNTIF function, what is that mean?

I am so appreciate your effort, time and patient!
So thankful!
Wilchong

Here's a small sample file that demonstrates this:

xExtractMissing.xls 15kb

http://cjoint.com/?fcepu2wnPz

Hello Biff,
I have checked with my Excel spreadsheet and confirmed to you that the

[quoted text clipped - 18 lines]

 

[T. Valko]

Actually, may I ask you one question, what is the logic in your function

which can list the missing data from second set of data? I saw you have
put --
infront of COUNTIF function, what is that mean?

We use COUNTIF to identify which items are missing from column B.

INDEX(A$3:A$14,SMALL(IF(COUNTIF(B$3:B$13,A$3:A$14)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3)))

The COUNTIF function returns an array of counts like this:

COUNTIF(B$3:B$13,A3) = 1
COUNTIF(B$3:B$13,A4) = 1
COUNTIF(B$3:B$13,A5) = 2
COUNTIF(B$3:B$13,A6) = 0
COUNTIF(B$3:B$13,A7) = 0
etc
etc

Where the result of that count is 0 we use this expression to calculate what
row the corresponding value is located on.

ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1

That number is then passed to the INDEX function to produce the desired
result.


--
Biff
Microsoft Excel MVP

Dear Biff,
Thank you so much for your Excel demo file. I finally found out what
wrong
with me! It is because my insufficient knowledge regarding the function
"enter" procedure. The reason I did wrong is because I highlighed the
whole
cell range from C3 to C14 and press CTRL, SHIFT and ENTER at the same
time.
Before, it is the only way I know. Right now, I know CTRL, SHIFT and
ENTER
can enter in one cell first and copy!

Actually, may I ask you one question, what is the logic in your function
which can list the missing data from second set of data? I saw you have
put --
infront of COUNTIF function, what is that mean?

I am so appreciate your effort, time and patient!
So thankful!
Wilchong

Here's a small sample file that demonstrates this:

xExtractMissing.xls 15kb

http://cjoint.com/?fcepu2wnPz

Hello Biff,
I have checked with my Excel spreadsheet and confirmed to you that the

[quoted text clipped - 18 lines]

Many thanks,
Wilchong

 

[wilchong via OfficeKB.com]

Dear Biff,
Many thank for your explanation. Actually, another point of the Excel
function is that why you put "--" before (COUNTIF(B$3:B$13,A$3:A$14)=0)),
INDEX(……………!

What is the purpose of "--" if mix with Excel function!

Thank for your time and effort again!
Wilchong

Actually, may I ask you one question, what is the logic in your function
which can list the missing data from second set of data? I saw you have
put --
infront of COUNTIF function, what is that mean?

We use COUNTIF to identify which items are missing from column B.

INDEX(A$3:A$14,SMALL(IF(COUNTIF(B$3:B$13,A$3:A$14)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3)))

The COUNTIF function returns an array of counts like this:

COUNTIF(B$3:B$13,A3) = 1
COUNTIF(B$3:B$13,A4) = 1
COUNTIF(B$3:B$13,A5) = 2
COUNTIF(B$3:B$13,A6) = 0
COUNTIF(B$3:B$13,A7) = 0
etc
etc

Where the result of that count is 0 we use this expression to calculate what
row the corresponding value is located on.

ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1

That number is then passed to the INDEX function to produce the desired
result.

Dear Biff,
Thank you so much for your Excel demo file. I finally found out what

[quoted text clipped - 28 lines]

 

[T. Valko]

The "--" converts TRUE and FALSE to 1 and 0 respectively.

This expression will return an array of either TRUE or FALSE:

COUNTIF(B$3:B$13,A$3:A$14)=0

The "--" converts those either 1 or 0:

--(COUNTIF(B$3:B$13,A$3:A$14)=0)

Then the SUM function adds those 1s and 0s to get a total:

SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0))


--
Biff
Microsoft Excel MVP

Dear Biff,
Many thank for your explanation. Actually, another point of the Excel
function is that why you put "--" before (COUNTIF(B$3:B$13,A$3:A$14)=0)),
INDEX(.....!

What is the purpose of "--" if mix with Excel function!

Thank for your time and effort again!
Wilchong

Actually, may I ask you one question, what is the logic in your function
which can list the missing data from second set of data? I saw you have
put --
infront of COUNTIF function, what is that mean?

We use COUNTIF to identify which items are missing from column B.

INDEX(A$3:A$14,SMALL(IF(COUNTIF(B$3:B$13,A$3:A$14)=0,ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1),ROWS(C$3:C3)))

The COUNTIF function returns an array of counts like this:

COUNTIF(B$3:B$13,A3) = 1
COUNTIF(B$3:B$13,A4) = 1
COUNTIF(B$3:B$13,A5) = 2
COUNTIF(B$3:B$13,A6) = 0
COUNTIF(B$3:B$13,A7) = 0
etc
etc

Where the result of that count is 0 we use this expression to calculate
what
row the corresponding value is located on.

ROW(A$3:A$14)-MIN(ROW(A$3:A$14))+1

That number is then passed to the INDEX function to produce the desired
result.

Dear Biff,
Thank you so much for your Excel demo file. I finally found out what

[quoted text clipped - 28 lines]

Many thanks,
Wilchong

 

[wilchong via OfficeKB.com]

Dear T. Valko,
Really thank for your help. I am thinking how to revise the existing Excel
function if I want the Excel formula extracts the data which has appear in
col B. In my early example, the result will be A, B, C, F, H, I, J,and K
actomactic appear from the cell C3 to C10 accordingly.

I need an Excel formula to have this result!

It is just the OPPOSITE what the function do for "extracting missing data"!

Many thanks,
Wilchong

The "--" converts TRUE and FALSE to 1 and 0 respectively.

This expression will return an array of either TRUE or FALSE:

COUNTIF(B$3:B$13,A$3:A$14)=0

The "--" converts those either 1 or 0:

--(COUNTIF(B$3:B$13,A$3:A$14)=0)

Then the SUM function adds those 1s and 0s to get a total:

SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0))

Dear Biff,
Many thank for your explanation. Actually, another point of the Excel

[quoted text clipped - 39 lines]

 

[T. Valko]

You can use the same formula just change:

=0 to >0 (in 2 places)

=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(rng2,rng1)>0)),INDEX(rng1,SMALL(IF(COUNTIF(rng2,rng1)>0,ROW(rng1)-MIN(ROW(rng1))+1),ROWS(C$3:C3))),"")

Don't forget: array entered!

--
Biff
Microsoft Excel MVP

Dear T. Valko,
Really thank for your help. I am thinking how to revise the existing Excel
function if I want the Excel formula extracts the data which has appear in
col B. In my early example, the result will be A, B, C, F, H, I, J,and K
actomactic appear from the cell C3 to C10 accordingly.

I need an Excel formula to have this result!

It is just the OPPOSITE what the function do for "extracting missing
data"!

Many thanks,
Wilchong

The "--" converts TRUE and FALSE to 1 and 0 respectively.

This expression will return an array of either TRUE or FALSE:

COUNTIF(B$3:B$13,A$3:A$14)=0

The "--" converts those either 1 or 0:

--(COUNTIF(B$3:B$13,A$3:A$14)=0)

Then the SUM function adds those 1s and 0s to get a total:

SUM(--(COUNTIF(B$3:B$13,A$3:A$14)=0))

Dear Biff,
Many thank for your explanation. Actually, another point of the Excel

[quoted text clipped - 39 lines]

Many thanks,
Wilchong

 

[wilchong via OfficeKB.com]

Dear T. Valko,
Many thanks! It works extremely well!
Thanks,
Wilchong

You can use the same formula just change:

=0 to >0 (in 2 places)

=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(rng2,rng1)>0)),INDEX(rng1,SMALL(IF(COUNTIF(rng2,rng1)>0,ROW(rng1)-MIN(ROW(rng1))+1),ROWS(C$3:C3))),"")

Don't forget: array entered!

Dear T. Valko,
Really thank for your help. I am thinking how to revise the existing Excel

[quoted text clipped - 29 lines]

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Dear T. Valko,
Many thanks! It works extremely well!
Thanks,
Wilchong

You can use the same formula just change:

=0 to >0 (in 2 places)

=IF(ROWS(C$3:C3)<=SUM(--(COUNTIF(rng2,rng1)>0)),INDEX(rng1,SMALL(IF(COUNTIF(rng2,rng1)>0,ROW(rng1)-MIN(ROW(rng1))+1),ROWS(C$3:C3))),"")

Don't forget: array entered!

Dear T. Valko,
Really thank for your help. I am thinking how to revise the existing
Excel

[quoted text clipped - 29 lines]

Many thanks,
Wilchong