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 111222Here, in the "Uncommon" sheet, the uncommon data is listed in Col.A while Col.B gives cell reference of the uncommon data.

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 listFor this example I assumed that Sheet 1 had data in cells B2 to B20

and Sheet 2 had data in cells D2 to D20All 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 H20Enter 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$9Regards

That is very well said by humar.

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?

Hi, Please post the three formulas you have in cells:

F39

G39 and

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

****************

You ask for a Macro,to show the end result of your formulaA 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

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.

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

Thanks Humar. Yes if formula can do what a macro does then formula is a better choice.

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History