Overclock.net › Forums › Software, Programming and Coding › Operating Systems › Windows › Comparing Data Across Two Excel Files
New Posts  All Forums:Forum Nav:

Comparing Data Across Two Excel Files

post #1 of 3
Thread Starter 
MasterData.xlsx 8k .xlsx file
DataToCheck.xlsx 8k .xlsx file

I have two files I want to compare in Excel. There is data in columns A, B and C in both files. The "DataToCheck" file is the one being compared against the "MasterData" file. In order to be a match, the data in all three columns across a single row must match. The data isn't necessarily in the same order either. Is there a formula to accomplish this?
LancelotX79
(16 items)
 
  
CPUMotherboardGraphicsRAM
i7-3930K @ 4.2 GHz ASRock Extreme 6 X79 AMD Radeon HD 7970 Crossfire 16 GB Samsung (1866 MHz, 9-9-10-24-84 1T) 
Hard DriveHard DriveOptical DriveCooling
2x 128 GB Samsung 830 2x 1 TB WD Black, 1 TB WD Green Samsung Writemaster DVD Corsair H80, 2x GT AP-15 
OSMonitorMonitorKeyboard
Windows 7 Professional x64 Yamakasi Q271 (2560x1440) Samsung 2493HM (1920x1200) Ducky Shine 3 YOTS Black 
PowerCaseMouseAudio
Corsair HX1000W Lian Li A70B Logitech G9 ASUS Xonar DSX 
  hide details  
Reply
LancelotX79
(16 items)
 
  
CPUMotherboardGraphicsRAM
i7-3930K @ 4.2 GHz ASRock Extreme 6 X79 AMD Radeon HD 7970 Crossfire 16 GB Samsung (1866 MHz, 9-9-10-24-84 1T) 
Hard DriveHard DriveOptical DriveCooling
2x 128 GB Samsung 830 2x 1 TB WD Black, 1 TB WD Green Samsung Writemaster DVD Corsair H80, 2x GT AP-15 
OSMonitorMonitorKeyboard
Windows 7 Professional x64 Yamakasi Q271 (2560x1440) Samsung 2493HM (1920x1200) Ducky Shine 3 YOTS Black 
PowerCaseMouseAudio
Corsair HX1000W Lian Li A70B Logitech G9 ASUS Xonar DSX 
  hide details  
Reply
post #2 of 3
Can you copy paste into one file? If so, just use an "if" statement

=if(a1=d1,"match","no match"). Copy paste down.
 
Up to no good
(13 items)
 
CPUMotherboardGraphicsRAM
4770K @ 4.7ghz @1.4v (delidded) Asus Maximus VI Hero EVGA GTX 1080 @ 2150MHz Corsair Dominator Platinums 2133 cl9 1.5v 
Hard DriveCoolingOSMonitor
Plextor M5P Extreme SSD 128gb x 2 in Raid0 Corsair H105 Windows 8 Pro DELL ULTRASHARP U3415W 21:9 Curved Ultra Widesc... 
KeyboardPowerCaseMouse
Logitech 710+ Corsair AX1200i Corsair 760T Func MS-2 
CPUMotherboardGraphicsRAM
i7 920 D0 @ 4.2 1.3v Asus Rampage II Extreme EVGA GTX 690 6gb Corsair Dominator 7-7-7-20 
Hard DriveMonitorKeyboardPower
Crucial M4 256GB 3x Dell 2405 - Eyefinity Logitech G710+ Enermax Revolution 1050W 
CaseMouseMouse Pad
Silverstone FT02 Corsair M60 Razer Destructor 
  hide details  
Reply
 
Up to no good
(13 items)
 
CPUMotherboardGraphicsRAM
4770K @ 4.7ghz @1.4v (delidded) Asus Maximus VI Hero EVGA GTX 1080 @ 2150MHz Corsair Dominator Platinums 2133 cl9 1.5v 
Hard DriveCoolingOSMonitor
Plextor M5P Extreme SSD 128gb x 2 in Raid0 Corsair H105 Windows 8 Pro DELL ULTRASHARP U3415W 21:9 Curved Ultra Widesc... 
KeyboardPowerCaseMouse
Logitech 710+ Corsair AX1200i Corsair 760T Func MS-2 
CPUMotherboardGraphicsRAM
i7 920 D0 @ 4.2 1.3v Asus Rampage II Extreme EVGA GTX 690 6gb Corsair Dominator 7-7-7-20 
Hard DriveMonitorKeyboardPower
Crucial M4 256GB 3x Dell 2405 - Eyefinity Logitech G710+ Enermax Revolution 1050W 
CaseMouseMouse Pad
Silverstone FT02 Corsair M60 Razer Destructor 
  hide details  
Reply
post #3 of 3
Thread Starter 
Quote:
Originally Posted by Inglewood78 View Post

Can you copy paste into one file? If so, just use an "if" statement

=if(a1=d1,"match","no match"). Copy paste down.
That would not be ideal. The "MasterData" file example I put up is small, but the actual one I'm working with contains over 300,000 rows of data. When someone sends me another smaller Excel file (eg DataToCheck) that is only 500 rows long, I have to check if the data in DataToCheck exists in MasterData. Additionally, the data in DataToCheck isn't necessarily in the same order.

So 'SANSA STARK' might be a name in row 10,451 in MasterData, but it might be in row 20 in DataToCheck.
LancelotX79
(16 items)
 
  
CPUMotherboardGraphicsRAM
i7-3930K @ 4.2 GHz ASRock Extreme 6 X79 AMD Radeon HD 7970 Crossfire 16 GB Samsung (1866 MHz, 9-9-10-24-84 1T) 
Hard DriveHard DriveOptical DriveCooling
2x 128 GB Samsung 830 2x 1 TB WD Black, 1 TB WD Green Samsung Writemaster DVD Corsair H80, 2x GT AP-15 
OSMonitorMonitorKeyboard
Windows 7 Professional x64 Yamakasi Q271 (2560x1440) Samsung 2493HM (1920x1200) Ducky Shine 3 YOTS Black 
PowerCaseMouseAudio
Corsair HX1000W Lian Li A70B Logitech G9 ASUS Xonar DSX 
  hide details  
Reply
LancelotX79
(16 items)
 
  
CPUMotherboardGraphicsRAM
i7-3930K @ 4.2 GHz ASRock Extreme 6 X79 AMD Radeon HD 7970 Crossfire 16 GB Samsung (1866 MHz, 9-9-10-24-84 1T) 
Hard DriveHard DriveOptical DriveCooling
2x 128 GB Samsung 830 2x 1 TB WD Black, 1 TB WD Green Samsung Writemaster DVD Corsair H80, 2x GT AP-15 
OSMonitorMonitorKeyboard
Windows 7 Professional x64 Yamakasi Q271 (2560x1440) Samsung 2493HM (1920x1200) Ducky Shine 3 YOTS Black 
PowerCaseMouseAudio
Corsair HX1000W Lian Li A70B Logitech G9 ASUS Xonar DSX 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Windows
Overclock.net › Forums › Software, Programming and Coding › Operating Systems › Windows › Comparing Data Across Two Excel Files