Find Cell with Matching Date

Microsoft 365 and Office
1

I have two separate spreadsheets, each containing vehicle registrations and corresponding dates for two different locations. These dates indicate when a vehicle visited each location.

I need to identify instances where a specific vehicle visited both locations on the same date and retrieve its registration number. I’m looking for a way to match dates for a particular vehicle across the two locations.

2

Hey there, so you’ve got two spreadsheets with vehicle registrations and dates for two different locations, and you want to find instances where a vehicle visited both locations on the same date and get its registration number. That’s a pretty specific task, but it’s doable with some clever formula work in Excel.

Here’s what you can do:

  1. Assume your data is structured:

    • Let’s say you have two columns in each spreadsheet: one for the registration number and one for the date. For simplicity, let’s call the registration column “A” and the date column “B”.

  2. Combine the data from both spreadsheets:

    • You can do this by copying the data from one spreadsheet and pasting it below the data in the other spreadsheet. Make sure the columns match up.

  3. Use the COUNTIFS function:

    • This function counts the number of cells that meet multiple conditions. In your case, the conditions are:

      • The registration number is the same.
      • The date is the same.

    • The formula would look like this: =COUNTIFS(A:A, A2, B:B, B2) > 1

      • A:A and B:B are the columns containing the registration numbers and dates, respectively.
      • A2 and B2 are the cells containing the registration number and date you want to check.
      • The > 1 at the end means the formula will return TRUE if the count is more than 1, indicating the vehicle visited both locations on the same date.

  4. Apply the formula and filter:

    • Enter the formula in a new column next to your data.
    • Copy the formula down to apply it to all rows.
    • Use a filter on the new column to show only the rows where the formula returns TRUE. These are the instances where a vehicle visited both locations on the same date.

  5. Get the registration numbers:

    • Once you’ve filtered the data, you can just read off the registration numbers from the filtered rows. You can also use the FILTER function to create a new table with just the registration numbers that meet the condition.

That’s it If you’re comfortable with formulas and filtering, this should do the trick.