Three Columns (one with value and two with time ranges) and I need to match on all three

[k______S]

Hello,

I have an excel sheet with two columns. The first column has several hundred product ID's that have been utilized and a column with the date they were utilized. I have another excel sheet that has three columns. In the first column is the list of all product ID's that we need to identify followed by a start date column and an end date column for a consideration time range.

I need to match product ID's from the first list with those in the second list but only if the date from the first list is after the start date column and before the end date column in the second list

Any thoughts on how I can accomplish this?

List one:
* Utilized product ID's
* Date utilized

List two:
* List of all product ID's I need to identify
* Start date for consideration
* End date for consideration

Thanks!

 

[bellman101]

Hi there,

You have not specified what you would like returned if there is a match, but you can use various methods, including COUNTIFS() as follows:

=COUNTIFS(ListTwo!A:A,A2,ListTwo!B:B,"<="&B2,ListTwo!C:C,">="&B2)

Change 'ListTwo' for the sheet name that List two sits in. I have assumed the first formula will go into row 2. If not adjust A2 and B2 accordingly.

So this will tell you how many matches there are. If the maximum number of matches is 1, then this will return a 1 or a 0.

So if you want to display particular text where there is a match, you can incorporate the above into an IF() formula:

=IF(COUNTIFS(ListTwo!A:A,A2,ListTwo!B:B,"<="&B2,ListTwo!C:C,">="&B2)>0,"Match Found","")

Hope that helps.

Cheers,
JB

 

[Max1616]

Hello,

Assumptions:
Column A = Utilized product ID's
Column B = Date utilized

Column F = List of all product ID's I need to identify
Column G = Start date for consideration
Column H = End date for consideration

You can use this array formula (to make a formula an array formula: Type formula into a cell, press control+shift+enter. Brackets will appear around the formula to show that this was done correcetly)
{MIN(IF(A:A=F2,IF(B:BLessThanH2,IF(B:BGreaterThanG2,B2))))<h2,if(b:b>}

For some reason the Greater than and less than symbols dont work

If there are two cases of a product being used within the time frame, the formula will return the oldest date
If your product id is not used within the time frame, the formula will return 0 (or 1/0/1900 if the column is formatted as a date)

Let me know if you have any questions.</h2,if(b:b>

 

[Nutster]

How do you want this information represented? Are you looking for a count of the item when it was used between the start and end dates? If so, add a column after the end date as:

=COUNTIFS(Sheet1!$A:$A,A2,Sheet1!$B:$B,">="&B2, Sheet1!$B:$B,"<="&C2)

then just copy and paste this formula down for all items on Sheet2.

COUNTIFS looks in each range for the given value or criteria, which is formed from several pairs of parameters. The first parameter of each pair is the range to look in; the second parameter of each pair is the value or criteria to find in that range. This set of ranges and criteria must be the same size and either a column or row.

This example has three pairs of parameters. So the first criteria in this case looks in column A in the first sheet for the item code in column A in the second sheet. The next criteria is comparing the date of the transaction with the Start Date for the same rows. The third criteria is comparing the transaction date with the Ending Date for those rows. If everything fits the given criteria list, then it gets counted.

 

[bellman101]

How do you want this information represented? Are you looking for a count of the item when it was used between the start and end dates? If so, add a column after the end date as:

=COUNTIFS(Sheet1!$A:$A,A2,Sheet1!$B:$B,">="&B2, Sheet1!$B:$B,"<="&C2)

then just copy and paste this formula down for all items on Sheet2.

COUNTIFS looks in each range for the given value or criteria, which is formed from several pairs of parameters. The first parameter of each pair is the range to look in; the second parameter of each pair is the value or criteria to find in that range. This set of ranges and criteria must be the same size and either a column or row.

This example has three pairs of parameters. So the first criteria in this case looks in column A in the first sheet for the item code in column A in the second sheet. The next criteria is comparing the date of the transaction with the Start Date for the same rows. The third criteria is comparing the transaction date with the Ending Date for those rows. If everything fits the given criteria list, then it gets counted.

That formula looks kinda familiar...

 

[k______S]

So my expectation is that if a product appears on list one it will match if the following three criteria are met:

1) the product ID from column A of list one appears in column A of list 2 (similar to a vlookup between column's A of both lists)
2) the date immediately to the right of the matched product ID in column B of list one, which shows when it was utilized, is greater than column B on list two
3) the date immediately to the right of the matched product ID in column B of list one, which shows when it was utilized, is less than the date on column C of list two

Separated conditions I am hoping to combine in one formula that provides a "Y" if all criteria match and "N" if any of them do not
1) individual cell from List1!A:A is contained within List2!A:A
2) If List1!A2 matches with any cell in List2!A:A then it searches the date from List1!B2 to see if the cell is GREATER than the start date range from the cell in List2!B:B immediately to the right of the cell it matched in List2!A:A
3) If the greater than match is true then it would perform the same search but within the List2!C:C cell immediately to the right of the cell in List2!A:A that matched against List1!A2.