Create a diff between two columns

[goodmachine]

I have two worksheets (Sheet 1 & 2) where column A on both worksheets has a list of codes. Some of the codes are duplicated on both worksheets. Some codes are unique for each sheet.

I would like to create a DIFF on Sheet 3, Column A, that lists all of the like codes from sheet 1 and 2.

On worksheet 1, I would like to add a formula in column B that returns a true/false value if that code is also used in column A of Sheet 2. Similarly, I would like to create the same formula in column B of worksheet 2.

Thanks for the help,
goodmachin

 

[Snakehips]

goodmachine,

When you say some codes are duplicated on both worksheets do you mean can occur more than once in the same list, as well as well as can occur in the other list?

Also does your sheet 3 DIFF list need to be contiguous?

 

[goodmachine]

Hi snakehips,

When I say duplicated, I mean that the same code may appear in the same column on both sheets. But the codes will not appear more than once in either column. For example, code "ABC" may be in both sheets, but will not appear more than once in either column.

Could you elaborate on what you mean by contiguous?

Thanks,
goodmachine

 

[Snakehips]

goodmachine,

I mean the list continuous as in one cell below the other without blanks. The alternative and easier to achieve is that the duplicates are in sheet 3 in the same rows as they appear in one or other of sheet 1 & 2 lists. That way there would be some blank cells here and there in the list.

To achieve the former I may have to utilise a 'helper' column which of course you can always hide.

Get back to me and I will try and help asap. It may have to be my tomorrow before I can respond.

Tony

 

[nodroj81]

In column B of Sheet1, beginning at cell B1, use this formula to compare sheet1 to sheet2 and then return TRUE/FALSE value:

=VLOOKUP(Sheet1!A1,Sheet2!$A$1:$A$xxxx,1,FALSE)=Sheet1!A1

where xxxx is the number of columns

Formula to return TRUE/FALSE value if you have unique values in either list

=IF(ISERROR(VLOOKUP(Sheet1!A1,Sheet2!$A$1:$A$xxxx,1,FALSE)=Sheet1!A1),"FALSE",VLOOKUP(Sheet1!A1,Sheet2!$A$1:$A$xxxx,1,FALSE)=Sheet1!A1)

Formula to return TURE/FALSE value if you have unique values and/or blank cells in either list

=IF(ISERROR(VLOOKUP(Sheet1!A1,Sheet2!$A$1:$A$xxxx,1,FALSE)),"FALSE",AND(NOT(ISBLANK(Sheet1!A1)),VLOOKUP(Sheet1!A1,Sheet2!$A$1:$A$xxxx,1,FALSE)=Sheet1!A1))

Same formula can be used for to compare sheet 2 to sheet 1.

=IF(ISERROR(VLOOKUP(Sheet2!A1,Sheet1!$A$1:$A$xxxx,1,FALSE)),"FALSE",AND(NOT(ISBLANK(Sheet2!A1)),VLOOKUP(Sheet2!A1,Sheet1!$A$1:$A$xxxx,1,FALSE)=Sheet2!A1))

 

[Snakehips]

goodmachine,

Looks like you have 2 for the price of 1 !!

Here is my take on this.....

The formulas are currently refering to a row range of 1 to 20 obviously you may need to adapt the start and end row numbers to suit your data ranges.
Do be very careful to keep the $ characters correct as per below.
Enter the formulas in your first row and drag down as far as you need.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 70px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #ffff00">ABC</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #ffff00">DEF</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BACKGROUND-COLOR: #ffff00">GHI</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BACKGROUND-COLOR: #ffff00">KLM</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BACKGROUND-COLOR: #ffff00">NOP</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BACKGROUND-COLOR: #ffff00">QRS</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BACKGROUND-COLOR: #ffff00">PQR</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="BACKGROUND-COLOR: #ffff00">WXY</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="BACKGROUND-COLOR: #ffff00">ZAB</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="BACKGROUND-COLOR: #ffff00">CDE</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="BACKGROUND-COLOR: #ffff00">FGH</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(A1="","",IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$20,0)),FALSE,TRUE))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4



Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #c0c0c0">IJK</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #c0c0c0">NOP</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BACKGROUND-COLOR: #c0c0c0">ABC</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BACKGROUND-COLOR: #c0c0c0">OPQ</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BACKGROUND-COLOR: #c0c0c0">RST</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BACKGROUND-COLOR: #c0c0c0">DEF</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BACKGROUND-COLOR: #c0c0c0">GHI</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="BACKGROUND-COLOR: #c0c0c0">JKL</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="BACKGROUND-COLOR: #c0c0c0">MNO</TD><TD style="TEXT-ALIGN: right">FALSE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="BACKGROUND-COLOR: #c0c0c0">PQR</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(A1="","",IF(ISNA(MATCH(A1,Sheet1!$A$1:$A$20,0)),FALSE,TRUE))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Sheet3

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>NOP</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>ABC</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>DEF</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>GHI</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>PQR</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A1</TD><TD>{=IF(ROWS(A$1:A1)>COUNTIF(Sheet2!$B$1:B$20,TRUE),"",INDEX(Sheet2!A$1:A$20,SMALL(IF(Sheet2!$B$1:$B$20=TRUE,ROW(Sheet2!$B$1:$B$20)),(ROWS(Sheet2!B$1:B1)))))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!


</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


You must remember to use Ctrl + Shift + Enter to enter the formula in Sheet3 A1 Then drag it down as far as you need.

If ever you edit this last formula always re-enter with CSE !!

Let us know how you get on.