Comparing two columns from one sheet with two columns from another sheet

mommacude

New Member
Joined
May 15, 2007
Messages
39
I've been searching but can't find something that matches exactly what I'm trying to do.

I have sheet which contains a list of hotels. These hotels are repeated and black out dates are in another column sort of like so:

Charlotte - Airport South 05/26/17
Charlotte - Airport South 05/27/17
Charlotte - Airport South 05/28/17
Charlotte - Airport South 06/30/17
Charlotte - Airport South 07/01/17
Charlotte - Airport South 07/02/17

I have a second sheet with similar data.

I am trying to determine if THIS hotel with THESE blackout dates is present in both files. So in other words does this hotel's dates from file "A" exist in file "B"?

I tried vlookup but that's returning only the first date, which doesn't help. I considered conditional formatting, but I'm having trouble with that. Can anyone help? Thanks so much!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
I have a feeling that someone else might have a better way to do this that's more succinct/efficient. But I would personally use a SUMPRODUCT to tackle this. Let's just call each sheet as 1 and 2. You will add a column to sheet 2 with just a bunch of 1s all the way down. In sheet 1 you'll add a column with the following equation:
=SUMPRODUCT(--(A1:A10="HOTEL"),--(B1:B10="DATE"),C1:C10)
The column with the hotel in sheet 2 will be A (range of 1 to 10 in example) and the dates in sheet 2 are B1:B10. You will likely want to lock the references with a $ to be $A$1:$A$10, etc. The new column of 1s is represented here as C1:C10. It will essentially just check to make sure both conditions are met and sum the cell of 1s where it is true. So, if you have a match for your row in sheet 1 with that of 2...you'll get a 1 in your cell of sheet 1. If you get a 0...no match. If you get more than 1...you had more than one instance of the same data popping up in sheet 2 (this means the reverse could be true too (but isn't checked for in this example)...you could have more than one instance of the same hotel/date in sheet 1).

Hope this makes sense!
Scott

 
Upvote 0
I forgot to mention...the quotations aren't likely needed in your problem...you'll just have your cell from sheet 1 in that place. Yours will likely look more like:
=SUMPRODUCT(--($A:$A=A1),--($B:$B=B1),$C:$C)

you'll likely have just the hotels in column A in sheet 2...so you just lock that column. you will allow sheet 2s value to move so that you can drag it all the way to the bottom of sheet 1...so no $s. Same is true for B and C.

Good luck :D
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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
Back
Top