Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 06-28-2011, 01:14 PM
kmorrin kmorrin is offline Windows XP Office 2007
Novice
 
Join Date: Jun 2011
Posts: 2
kmorrin is on a distinguished road
Question Compare 4 columns & find dissimilar

I have 2 spreadsheets with 2 columns in each that I need to compare. Sheet A has a part and a price column, as does Sheet B. I need to compare the part column in A to B and then the price column in A to B and if the price of the part in A is different than the price of the part in B, I need to calculate the difference in a separate column. Can anyone help?
Reply With Quote
  #2  
Old 06-28-2011, 02:07 PM
OTPM OTPM is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi
Please find attached your solution.
Hope this helps.
Tony(OTPM)
Attached Files
File Type: xls CompareValues.xls (15.5 KB, 18 views)
Reply With Quote
  #3  
Old 06-28-2011, 02:19 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 381
Colin Legg will become famous soon enough
Default

Welcome to the forum.

I think I'd use something along these lines:
Code:
=IFERROR(B1-INDEX(SheetB!B:B,MATCH(A1,SheetB!A:A,0)),"")
This assumes that there are no duplicate parts in either sheet (which is implied by your question).
Reply With Quote
  #4  
Old 06-29-2011, 01:41 PM
kmorrin kmorrin is offline Windows XP Office 2007
Novice
 
Join Date: Jun 2011
Posts: 2
kmorrin is on a distinguished road
Default

Thanks! Is it possible to have the parts in sheet A check all of the parts in sheet B looking for a match? There are about 800 more parts in B than A. If not, I'll go thru them and make them match line to line.
Reply With Quote
  #5  
Old 06-29-2011, 04:38 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 381
Colin Legg will become famous soon enough
Default

The formula I posted does check all of the parts in Sheet B, but if there are multiple matches then it only finds the first one.
Reply With Quote
Reply

Tags
excel compare

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare and Update Macro AaronMoss Excel Programming 2 05-06-2011 04:54 AM
How to compare 2 columns with other two columns in EXECL 2007? Learner7 Excel 5 06-12-2010 09:54 AM
Compare document enthu Word 1 05-19-2010 10:32 PM
Compare data in different columns in an Excel sheet kgfendi Excel 5 05-16-2009 05:42 PM
compare files rmartin8 Word 1 02-10-2009 10:27 AM


All times are GMT -7. The time now is 07:44 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft