GOOGLE SHEETS: How to return results from one column that are not in other column?

[joshman108]

For instance, if column A has the following values:

1
2
3
4

and column B has the values:

1
2
3
4
5
6

I would want a function that would simply return

5
6
because those are the values in column B but not in A. There will be no duplicate values within each column. Each value will be entirely unique within that column. Any ideas?

Thanks!

 

[Joe4]

Re: How to return results from one column that are not in other column?

I can think of a few ways.

You can create a calculation in column C that does a VLOOKUP between the two columns to identify the missing records, or do a COUNTIF formula to count how many times the values appear in the other column (1 or 0 if they are unique). Then you can simply filter the data on the third column to only show the missing values.

 

[Aladin Akyurek]

Re: How to return results from one column that are not in other column?

In D1 control+shift+en, not just enter:

=SUM(IF(ISNA(MATCH($B$2:$B$7,$A$2:$A$5,0)),1))

D2: List B Not In A

In D3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$3:D3)>$D$1,"",INDEX($B$2:$B$7,SMALL(IF(ISNA(MATCH($B$2:$B$7,$A$2:$A$5,0)),ROW($B$2:$B$7)-ROW($B$2)+1),ROWS($D$3:D3))))

 

[Rick Rothstein]

Re: How to return results from one column that are not in other column?

For instance, if column A has the following values:

1
2
3
4

and column B has the values:

1
2
3
4
5
6

I would want a function that would simply return

5
6
because those are the values in column B but not in A. There will be no duplicate values within each column. Each value will be entirely unique within that column. Any ideas?

Some questions...

1) Will one of the columns always be longer than the other? If so, which one?

2) Will your data ever have spaces within it (such as two words with a single space between them)?

3) Does your data start at Row 1? If not, what row do they start at?

4) Do you want the output in Column C in multiple cells (one value per cell) or as a delimited list of values within a single cell?

5) Is a VBA solution acceptable?

 

[joshman108]

Re: How to return results from one column that are not in other column?

Aladin, that worked. I don't know what you mean "D2: List B Not In A" but it couldn't have been too important because it worked without it, whatever it was. I don't suppose there is a way to get that down into one function?

Rick,
1) no not necessarily because there will be a lot of times where the columns are identical and that is fine. But in the event that there are differences between them, yes in every case the second column will be longer.
2) no, not that I can think of for any reason
3) data actually starts in row 3 (my formula below is just a test)
4) I think different cells would be easiest to read for sure, so having the results stacked one on top of another in different cells would be ideal.
5) no, I'm actually using google sheets


Thanks for all your help as always rick. It did occur to me that this is actually the exact formula I want (written in google sheets)

=ArrayFormula(if(countif(BZ18:BZ25,CA18:CA25)=0,CA18:CA25,""))

Where I am checking to see if values in range 2 (CA18:25) are in NOT in range 1 (BZ18:BZ25), if so, return me the list. The problem is that it will output a blank row for every match. For instance, per my example above if range 1 has the following values:




1
2
3
4


and range 2 has the values:


1
2
3
4
5
6

then the formula is going to return
(blank)
(blank)
(blank)
(blank)
5
6

whereas I want it condensed to just
5
6

It occurs to me I made a post on this several days ago and looking at it now the answer does not seem to make sense or work for me...

https://www.mrexcel.com/forum/excel-questions/1009504-how-get-array-not-output-blanks.html#post4845728

 

[Aladin Akyurek]

Re: How to return results from one column that are not in other column?

Aladin, that worked. I don't know what you mean "D2: List B Not In A" but it couldn't have been too important because it worked without it, whatever it was. I don't suppose there is a way to get that down into one function?
[...]

D2 is just cosmetics, a header for the results.

If you don't want the formula in D1 and the cosmetics in D2, remove them, then...

In D2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$7,SMALL(IF(ISNA(MATCH($B$2:$B$7,$A$2:$A$5,0)),ROW($B$2:$B$7)-ROW($B$2)+1),ROWS($D$2:D2)),"")

 

[Aladin Akyurek]

Re: How to return results from one column that are not in other column?

In Google Sheets...

In D2 enter and copy down:

=ARRAYFORMULA(IFERROR(INDEX($B$2:$B$7,SMALL(IF(ISNA(MATCH($B$2:$B$7,$A$2:$A$5,0)),ROW($B$2:$B$7)-ROW($B$2)+1),ROWS($D$2:D2)),""))

​

 

[Joe4]

Re: How to return results from one column that are not in other column?

5) no, I'm actually using google sheets

Please be ABSOLUTELY sure to mention that in the initial posting (or thread title). Excel and Google Sheets are NOT the same.
If you don't mention that, and post it in the "Excel Questions" section, there is no reason for any of us to think it is anything other than an Excel question.

I have moved this thread to the "General Excel Discussion & Other Questions". This is where you should post questions about Google Sheets.
I also updated your thread title.

 

[joshman108]

Re: How to return results from one column that are not in other column?

Joe, yes, I'm sorry. I will be sure to do that in the future.

Aladin, that seems to work. I will implement it and let you know if it fails in any way. Thanks!

 

[Aladin Akyurek]

Re: How to return results from one column that are not in other column?

You are welcome.