Compare two columns of different worksheets

Microsoft Windows xp inside out, second...
December 19, 2009 at 00:16:23
Specs: Windows XP, pentium 4 3.00Ghz. 1gb ram
How to compare Col. B of sheet1 to col. D of sheet2?

The comparison needs to be such that unique or uncommon data is listed in a different sheet called "Uncommon" indicating unique or uncommon cell references.

For example:

	sheet1			sheet2			Uncommon sheet

	Col.B			Col.D			Col.A		Col.B	

Row2	12			6			6587		sheet1 B3
Row3	6587			111222			thomson		sheet1 B4	
Row4	thomson			Mens World		long wood	sheet1 B6
Row5	funnel			funnel			Jackson worlds	sheet2 D7
Row6	long wood		12			good boys	sheet2 D9
Row7	mens world		Jackson worlds
Row8	water supply		water supply
Row9	6			good boys
Row10	111222

Here, in the "Uncommon" sheet, the uncommon data is listed in Col.A while Col.B gives cell reference of the uncommon data.


See More: Compare two columns of different worksheets

Report •


#1
December 19, 2009 at 06:57:20
Hi,

The solution I have is effectively a two-part process.

Part 1 identifies names and sheet/cell addresses of unique names.
Part 2 places them in a single list

For this example I assumed that Sheet 1 had data in cells B2 to B20
and Sheet 2 had data in cells D2 to D20

All cells on Sheet 1 Column B, that are not used and
all cells on Sheet 2 Column D, that are not used are filled with a "-"

The reason for this is so that empty cells are seen as containing non-unique names.
Using your data resulted in Sheet 1 cells B11 to B20 containing "-" and
Sheet 2 cells D10 to D20 containing "-".

On sheet 3 enter the following:
F1 0
G1 Names
H1 Addresses
Note that F1 is a zero (required). The text in G1 and H1 is up to you
Enter the following formulas:

F2	=IF(H2="","",MAX($F$1:F1)+1)
G2	=IF(ISERROR(VLOOKUP(Sheet1!B2,Sheet2!$D$2:$D$20,1,FALSE)),Sheet1!B2,"")
H2	=IF(G2<>"",RIGHT(CELL("address",Sheet1!B2),LEN(CELL("address",Sheet1!B2))
	-FIND("]",CELL("address",Sheet1!B2),1)),"")
Note that the formula for H2 has been split onto two lines for ease of viewing and must be reassembled into a single line.

Drag the formula in F2 down to F39
Drag the formula in G2 down to G20
Drag the formula in H2 down to H20

Enter the following formulas:

G21	=IF(ISERROR(VLOOKUP(Sheet2!D2,Sheet1!$B$2:$B$20,1,FALSE)),Sheet2!D2,"")
H21	=IF(G21<>"",RIGHT(CELL("address",Sheet2!D2),LEN(CELL("address",Sheet2!D2))
-FIND("]",CELL("address",Sheet2!D2),1)),"")
Note that the formula for H21 has been split onto two lines for ease of viewing and must be reassembled into a single line.
Drag the formulas in G21 and H21 down to row 39.

Now in cells A1 and A2 enter Name and Address (The actual text is up to you)
Enter the following formulas:

A2	=IF(ISERROR(VLOOKUP(CELL("row",A1),$F$1:$H$39,2,FALSE)),"",
VLOOKUP(CELL("row",A1),$F$1:$H$39,2,FALSE))
B2	=IF(ISERROR(VLOOKUP(CELL("row",A1),$F$1:$H$39,3,FALSE)),"",
VLOOKUP(CELL("row",A1),$F$1:$H$39,3,FALSE))
Note that both formulas have been split onto two lines for ease of viewing.
Drag A2 and B2 down to row 40.
Note that in all the formulas, the $ signs are important.

You should now have the list, as shown in your post, with unique names and the sheet/cell addresses.
This is what I had:

Name		Address
6587		Sheet1!$B$3
thomson		Sheet1!$B$4
long wood	Sheet1!$B$6
Jackson worlds	Sheet2!$D$7
good boys	Sheet2!$D$9

Regards


Report •

#2
December 20, 2009 at 03:31:13
That is very well said by humar.

Report •

#3
December 20, 2009 at 20:13:55
Thanks Humar, I tested it and found that it give correct result for sheet1 data not available in sheet2 only. That is Jackson world sheet2!$D$7 and good boys Sheet2!$D$9 didn,t show up.

Can you write Macro for me to show the end result of your formula so that i heven't to remember those formula?


Report •

Related Solutions

#4
December 21, 2009 at 03:58:49
Hi,

Please post the three formulas you have in cells:
F39
G39 and
H39

Also please confirm that the cell containing 'good boys' is Sheet2 Cell D9

****************
You ask for a Macro, to show the end result of your formula

A Macro is Visual Basic code that undertakes a function, it is usually run as a program attached to a button and runs once when the button is clicked.
The solution offered uses formulas, which are always active and respond to changes in data in the referenced cells.
Please provide some more information on what you are looking for.

Regards


Report •

#5
December 22, 2009 at 02:51:11
The formula in:
F39 =IF(H39="","",MAX($F$1:F38)+1)
G39=IF(ISERROR(VLOOKUP(Sheet1!B39,Sheet2!$D$2:$D$2
0,1,FALSE)),Sheet1!B39,"")   and
H39=IF(G39<>"",RIGHT(CELL("address",Sheet1!B39),LEN(C
ELL("address",Sheet1!B39))FIND("]",CELL("address",Sheet1!
B39),1)),"")

Yes 'good boys' is in Sheet2 Cell D9.

I need macro that does exactly what your formula do. This is
because its easier to run macro then to remember and type
those lengthy formulas.



Report •

#6
December 22, 2009 at 05:07:40
Hi,

The reason the results you are getting are not correct is because you did not follow the instructions correctly.

You missed out the following:
Enter the following formulas:

G21	=IF(ISERROR(VLOOKUP(Sheet2!D2,Sheet1!$B$2:$B$20,1,FALSE)),Sheet2!D2,"")
H21	=IF(G21<>"",RIGHT(CELL("address",Sheet2!D2),LEN(CELL("address",Sheet2!D2))
-FIND("]",CELL("address",Sheet2!D2),1)),"")
Note that the formula for H21 has been split onto two lines for ease of viewing and must be reassembled into a single line.
Drag the formulas in G21 and H21 down to row 39.

Follow the above part of the instructions and you will get the correct results.

As to a macro, there are two issues:
1. If you change the data on sheet 1 or sheet 2 the results on the third sheet will not automatically update, although it would be possible to add code to the 'Worksheet Change' events on both sheets.
2. How will you define the ranges where data is stored on sheets 1 and 2. Depending on how data is stored and what else is on sheets 1 and 2, you may need to edit the macro to refer to the correct ranges, when data is stored.

A. Is there any other data on sheets 1 and 2 other than the data in columns B and D that you told us about.
B. Will there be any data in any rows below the names in columns B or D
C. What, if anything is in row 1 (columns B and D)

Overall for a macro you are likely to need code in three places, or add a button to click to manually run the macro for each update. I am not sure how this is different to using formulas. In both cases once they are written and working, that's it.

Just a thought:
If you are going to use either a macro or formulas in another workbook then you will still have to remember/copy the code or formulas to the new workbook possibly adding a button and linking the code to it. Is there much difference between the two.

Regards


Report •

#7
December 23, 2009 at 01:21:09
Thanks Humar. Yes if formula can do what a macro does then formula is a better choice.

Report •


Ask Question