Compare 2 Sheets - Print Variance

ArtistApart

New Member
Joined
Jan 6, 2014
Messages
2
Ever want to throw your computer?

Here's the problem i'm having: 3 Sheet Excel document- What i'm trying to do is compare the contents of Column A sheet2, with Column J sheet3.

I would like only the variances printed on Sheet A. So- Sheet A says "The following was found in Sheet2!A, but not Sheet3!J"

Demo excel spreadsheet below for reference. Comparing "NASC Column A" with "RQ4 Column J"

Thanks for any help!

http://artistapart.files.wordpress.com/2014/01/wip.xlsx
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

caabyyc

Board Regular
Joined
Oct 24, 2013
Messages
126
Per my understanding, you want to see the number in sheet 1 which only available in column A of sheet 2 and not included in column J of sheet 3.

NOTE: after checked your example data,
1. content in A of sheet 2 and J of sheet 3 is not in same format (one is in text, one is in "number")
2. Row 1 and 2 in sheet 3 is merged format

Following is my method (of course it is the best one)
1. highlight the sheet3 to cancel the "merged" format
2. Insert new column B at right of column A.
3. enter following in B2 of sheet 2, then hit "Control+Shift+enter" together
=IF(ISERROR(VLOOKUP("/"&A2,"/"&'RQ4'!$J$3:$J$9999,1,FALSE)),ROW(),"")
4. select B2 of sheet 2, then copy to the end
5. in A1 of sheet 1, enter following them copy to the end until you see the blank
=IF(ROW(A1)>COUNT(NASC!B:B),"",INDEX(NASC!A:A,SMALL(NASC!B:B,ROW(A1))))




Ever want to throw your computer?

Here's the problem i'm having: 3 Sheet Excel document- What i'm trying to do is compare the contents of Column A sheet2, with Column J sheet3.

I would like only the variances printed on Sheet A. So- Sheet A says "The following was found in Sheet2!A, but not Sheet3!J"

Demo excel spreadsheet below for reference. Comparing "NASC Column A" with "RQ4 Column J"

Thanks for any help!

http://artistapart.files.wordpress.com/2014/01/wip.xlsx
 

caabyyc

Board Regular
Joined
Oct 24, 2013
Messages
126
B1 in sheet 3 ="/"&A1, copy to bottom.
B1 in sheet 2 =IF(ISERROR(VLOOKUP("/"&A1,'RQ4'!B:B,1,FALSE)),ROW(),""), copy to bottom.
A1 in Sheet 1 =IF(ROW(A1)>COUNT(NASC!B:B),"",INDEX(NASC!A:A,SMALL(NASC!B:B,ROW(A1)))) , copy to bottom.
A1 to bottom in sheet 1 (the first blank cell) is what you need.

<colgroup><col style="width: 481pt; mso-width-source: userset; mso-width-alt: 22784;" width="641"> <tbody> </tbody>





How about if, rather than all the unnecessary information- when the cold information is entered to this spreadsheet, we just make columns A or Sheet 2 and Sheet 3 the ONLY thing with data- and assume its in the same format. it: see attached.

http://artistapart.files.wordpress.com/2014/01/wip2.xlsx

(THANKS SO MUCH FOR HELP!)
 

Forum statistics

Threads
1,137,294
Messages
5,680,654
Members
419,923
Latest member
Kalthus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top