Unique List - Mechanics Shop

[cpa2838]

Hi All,
First, thanks to all that help and post to the forums. I hope someone is able to help me as well. I'm using a mechanic's shop as an example because it is a simple representation of what I'm trying to achieve. I want to pull a unique list of vehicle models that visited the shop within a date range and by vehicle type.

1	A	B	C	D	E	F
2	Model	Type	Date In	Date Out		Desired List
3	F150	Truck	4/1/2015	4/5/2015		F150
4	Taurus	Car	4/2/2015	4/4/2015		C1500
5	Malibu	Car	4/3/2015	4/4/2015		F250
6	C1500	Truck	4/5/2015	4/8/2015
7	Accord	Car	4/7/2015	4/8/2015
8	F250	Truck	4/7/2015	4/9/2015
9	C2500	Truck	4/7/2015	4/12/2015
10
11	Report Date Range		4/1/2015	4/9/2015

<tbody>
</tbody>

My formula attempt resembles this: =INDEX($A$2:$A$9,MATCH(1,($C$2:$C$9>=C11)*($D$2:$D$9<=D11)*($B$2:$B$9="Truck"),0)). It results with the F150 but if I drag it to other cells they all result in F150. In this example, the desired list is in the same worksheet but in my actual file the data is in another worksheet. I am familiar with how to reference another worksheet so that part shouldn't be a problem. I'm currently using Excel 2007.
It seems that I may be missing a COUNTIF statement but I'm not sure. Any help offered will be greatly appreciated!

 

[AhoyNC]

This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Copy formula down as needed.

Excel Workbook
	A	B	C	D	E	F	G
1	Model	Type	Date In	Date Out		Desired List
2	F150	Truck	4/1/2015	4/5/2015		F150
3	Taurus	Car	4/2/2015	4/4/2015		C1500
4	Malibu	Car	4/3/2015	4/4/2015		F250
5	C1500	Truck	4/5/2015	4/8/2015
6	Accord	Car	4/7/2015	4/8/2015
7	F250	Truck	4/7/2015	4/9/2015
8	C2500	Truck	4/7/2015	4/12/2015
9
10
11	Report Date Range		4/1/2015	4/9/2015
12
Sheet

 

[cpa2838]

AhoyNC, it works great!!! A sincere thank you! I will need to brush up on the ROW function.

 

[AhoyNC]

You're welcome. Thanks for the feedback. We're using the ROW function to return the row numbers that match your criteria.
In this example
IF($C$2:$C$9>=$C$11,IF($D$2:$D$9<=$D$11,IF($B$2:$B$9="Truck",ROW($B$2:$B$9)-ROW($B$2)+1))) returns
{1;FALSE;FALSE;4;FALSE;6;FALSE;FALSE}

 

[cpa2838]

AhoyNC, sorry to bother you again but if I wanted to add another criteria, say in column E for engine size, would that be another IF statement before the ROW functions? And thanks for the insight on the ROW function!!

 

[AhoyNC]

Yes it would.

Excel Workbook
	A	B	C	D	E	F	G
1	Model	Type	Date In	Date Out	Eng.	Desired List
2	F150	Truck	4/1/2015	4/5/2015	V8	F150
3	Taurus	Car	4/2/2015	4/4/2015	4 cyl	F250
4	Malibu	Car	4/3/2015	4/4/2015	V6
5	C1500	Truck	4/5/2015	4/8/2015	V6
6	Accord	Car	4/7/2015	4/8/2015	4 cyl
7	F250	Truck	4/7/2015	4/9/2015	V8
8	C2500	Truck	4/7/2015	4/12/2015	V6
9
10
11	Report Date Range		4/1/2015	4/9/2015
Sheet

 

[cpa2838]

Perfect AhoyNC! Thanks again for your time and consideration in helping me. I did discover one thing and it is probably due to the way I worded my request for help. If, for instance, the F150 visits the shop again the formula will show it again which means the list would not be truly unique. But, I'm thinking this will be ok because that may be best for my report. Thanks a million!

 

[AhoyNC]

This should give you a unique list.
This link should help explain the formula. Also, if you have a large worksheet the IFERROR can slow things down. If that becomes an issue you might want to look at how he uses the SUMPRODUCT to count records and use the count to keep from having an #NA where there are no records.

https://www.youtube.com/watch?v=DLgnhPSOEOw

Excel Workbook
	A	B	C	D	E	F	G
1	Model	Type	Date In	Date Out	Eng.	Desired List
2	F150	Truck	4/1/15	4/5/15	V8	F150
3	Taurus	Car	4/2/15	4/4/15	4 cyl	F250
4	Malibu	Car	4/3/15	4/4/15	V6
5	C1500	Truck	4/5/15	4/8/15	V6
6	Accord	Car	4/7/15	4/8/15	4 cyl
7	F250	Truck	4/7/15	4/9/15	V8
8	C2500	Truck	4/7/15	4/12/15	V6
9	F150	Truck	4/6/15	4/7/15	V8
10
11	Report Date Range		4/1/15	4/9/15
12
Sheet

 

[cpa2838]

Thank you AhoyNC. The file will probably have about 5000 rows by the end of the year and then start fresh for the next year. I will monitor the speed as it builds. I watched the video and it does a great job of building the array formula with explanations. I will probably watch it a few more times in addition to some of the others he has. Thank you again for all your help on this, I sincerely appreciate it!