Need unique rows displayed based on car no. selected through a drop-down menu

[Miloni]

Car No.	Date of Entry	Driver Name	Trip Start Date	Trip End Date	Trip No. of Days
1234​	01-08-2020​	abc	03-05-2020​	12-05-2020​	9​
4567​	02-02-2020​	jkl	05-02-2020​	09-02-2020​	4​
2589​	03-02-2020​	hijhk	03-02-2020​	07-02-2020​	4​
2358​	04-02-2020​	srdtfyj	07-02-2020​	09-02-2020​	2​
1234​	05-02-2020​	abc	09-02-2020​	17-02-2020​	8​
4567​	02-02-2020​	jkl	05-02-2020​	09-02-2020​	4​
8512​	03-02-2020​	hijhk	03-02-2020​	07-02-2020​	4​
1234​	04-02-2020​	srdtfyj	07-02-2020​	09-02-2020​	2​

If the Car No. selected is "1234"; the expected result in a different sheet should be as follows :

Date of Entry Driver Name
01-08-2020 Abc
05-02-2020 abc
04-02-2020 srdtfyj

I tried using vlookup, which is giving me the 1st result of date of entry 01-08-2020 and driver name abc.

Is it possible to get all the results displayed whose car no. is 1234 ?

Any help appreciated.

 

[Toadstool]

Hi Miloni,

As long as the Car No. is numeric then this should work.
NOTE: You should update your profile with your Excel version as this Excel 2016 approach has simpler solutions in later versions.

Here's Sheet1 with your data and the built LoV.

Miloni.xlsx
	A	B	C	D	E	F	G	H	I
1	Car No.	Date of Entry	Driver Name	Trip Start Date	Trip End Date	Trip No. of Days			0
2	1234	01-Aug-20	abc	03-May-20	12-May-20	9			1234
3	4567	02-Feb-20	jkl	05-Feb-20	09-Feb-20	4			2358
4	2589	03-Feb-20	hijhk	03-Feb-20	07-Feb-20	4			2589
5	2358	04-Feb-20	srdtfyj	07-Feb-20	09-Feb-20	2			4567
6	1234	05-Feb-20	abc	09-Feb-20	17-Feb-20	8			8512
7	4567	02-Feb-20	jkl	05-Feb-20	09-Feb-20	4
8	8512	03-Feb-20	hijhk	03-Feb-20	07-Feb-20	4
9	1234	04-Feb-20	srdtfyj	07-Feb-20	09-Feb-20	2
10
Sheet1

Cell Formulas
Range	Formula
I2:I9	I2	=IFERROR(AGGREGATE(15,6,$A$2:$A$9999/($A$2:$A$9999>I1),1),"")

Here's Sheet2 with the Car No. selection and list of matches.

Miloni.xlsx
	A	B	C
1	Select	Date of Entry	Driver Name
2	1234	01-Aug-20	abc
3		05-Feb-20	abc
4		04-Feb-20	srdtfyj
5
6
7
8
Sheet2

Cell Formulas
Range	Formula
B2:C8	B2	=IFERROR(INDEX(Sheet1!B$2:B$9999,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$9999)-ROW(Sheet1!$A$1)/(Sheet1!$A$2:$A$9999=Sheet2!$A$2),ROW()-ROW(Sheet2!$B$1))),"")

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=OFFSET(Sheet1!$I$2,,,COUNTIF(Sheet1!$I$2:$I$9999,">0"))

 

[Miloni]

Thank you so much for your reply.

My Excel version is 2007. It does not support the Aggregate function. Is there any other way around getting the result I am looking for ?

 

[Toadstool]

OK, try this:

Miloni2.xlsx
	A	B	C	D	E	F	G	H	I
1	Car No.	Date of Entry	Driver Name	Trip Start Date	Trip End Date	Trip No. of Days			0
2	1234	01-Aug-20	abc	03-May-20	12-May-20	9			1234
3	4567	02-Feb-20	jkl	05-Feb-20	09-Feb-20	4			8512
4	2589	03-Feb-20	hijhk	03-Feb-20	07-Feb-20	4			4567
5	2358	04-Feb-20	srdtfyj	07-Feb-20	09-Feb-20	2			2358
6	1234	05-Feb-20	abc	09-Feb-20	17-Feb-20	8			2589
7	4567	02-Feb-20	jkl	05-Feb-20	09-Feb-20	4
8	8512	03-Feb-20	hijhk	03-Feb-20	07-Feb-20	4
9	1234	04-Feb-20	srdtfyj	07-Feb-20	09-Feb-20	2
10
Sheet1

Cell Formulas
Range	Formula
I2:I9	I2	=IFERROR(LOOKUP(2,1/(COUNTIF($I$1:$I1,$A$2:$A$9999)=0),$A$2:$A$9999),"")

...and this

Miloni2.xlsx
	A	B	C
1	Select	Date of Entry	Driver Name
2	1234	01-Aug-20	abc
3		05-Feb-20	abc
4		04-Feb-20	srdtfyj
5
Sheet2

Cell Formulas
Range	Formula
B2:C5	B2	=IFERROR(INDEX(Sheet1!B$2:B$9999,SMALL(IF(Sheet1!$A$2:$A$9999=$A$2,ROW(Sheet1!$B$2:$B$9999)-MIN(ROW(Sheet1!$B$2:$B$9999))+1),ROW()-ROW($K$1))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=OFFSET(Sheet1!$I$2,,,COUNTIF(Sheet1!$I$2:$I$9999,">0"))

 

[Miloni]

I2:I9

I2

=IFERROR(LOOKUP(2,1/(COUNTIF($I$1:$I1,$A$2:$A$9999)=0),$A$2:$A$9999),"")

What I understood is that the above formula is helping us to get a list of unique values from Column A (Car No.). Is that correct ?
I have already created a drop-down menu list for the unique car numbers, so I guess this will not be needed. Please correct me if I am wrong.

=IFERROR(INDEX(Sheet1!B$2:B$9999,SMALL(IF(Sheet1!$A$2:$A$9999=$A$2,ROW(Sheet1!$B$2:$B$9999)-MIN(ROW(Sheet1!$B$2:$B$9999))+1),ROW()-ROW($K$1))),"")

Can you please explain me the concept behind this formula and also what is the use of $K$1 ?
I tried using this formula in my sheet and I am getting a blank cell. No error, no result. I pressed Ctrl + Shift + Enter as well.

Thank you so much.

 

[Toadstool]

What I understood is that the above formula is helping us to get a list of unique values from Column A (Car No.). Is that correct ?
I have already created a drop-down menu list for the unique car numbers, so I guess this will not be needed. Please correct me if I am wrong.

That is correct so not needed.

Can you please explain me the concept behind this formula and also what is the use of $K$1 ?
I tried using this formula in my sheet and I am getting a blank cell. No error, no result. I pressed Ctrl + Shift + Enter as well.

Thank you so much.

SMALL returns the kth occurrence. The IF is checking if the Car No. matches the selected Car No. and if it does it returns the ROW number to the INDEX.
The $K$1 is giving the kth value to the SMALL, so in B2 the ROW()-ROW($K$1) gives a 1 so SMALL returns the 1st row matching the IF. In B3 the ROW()-ROW($K$1) is 3-1=2 so the SMALL returns the 2nd occurrence, etc.

 

[Miloni]

Thank you so much for the detailed explanation. But still the formula is giving me a blank result. No error no result. What could be the reason ?

 

[Toadstool]

Probably an incorrect cell address but without seeing your sheet then I can't really say.

Here's the Sheet2 with the selection and results moved to see if that prompts something for you.

Miloni2.xlsx
	A	B	C	D	E	F
1
2
3				Select	Date of Entry	Driver Name
4				1234	01-Aug-20	abc
5					05-Feb-20	abc
6					04-Feb-20	srdtfyj
7
8
Sheet2

Cell Formulas
Range	Formula
E4:E8	E4	=IFERROR(INDEX(Sheet1!B$2:B$9999,SMALL(IF(Sheet1!$A$2:$A$9999=$D$4,ROW(Sheet1!$B$2:$B$9999)-1),ROW()-ROW($E$3))),"")
F4:F8	F4	=IFERROR(INDEX(Sheet1!C$2:C$9999,SMALL(IF(Sheet1!$A$2:$A$9999=$D$4,ROW(Sheet1!$B$2:$B$9999)-MIN(ROW(Sheet1!$B$2:$B$9999))+1),ROW()-ROW($F$3))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
D4	List	=OFFSET(Sheet1!$I$2,,,COUNTIF(Sheet1!$I$2:$I$9999,">0"))

 

[Miloni]

Thank you so much. It still isn't working. The data is all dummy.

All my data will be in Driver and Trip Data sheet.
Driver Bhattu and Trip report will give me a report based on the car no chosen from the drop down menu
Cell with Green background has the formula you gave me and it's returning blank to me.

Hope this helps you in guiding me.

Thank you so much

 

[Toadstool]

So is B23 the first cell you expect to see data returned? Then the row counter should point at the heading in B22.

Miloni2-2.xlsx
	A	B	C	D	E	F
1	Select
2	1234
3
20
21
22		Date of Entry	Driver Name	Trip Start Date	Trip End Date	Trip No. of Days
23		01-Aug-20	abc	03-May-20	12-May-20	9
24		05-Feb-20	abc	09-Feb-20	17-Feb-20	8
25		04-Feb-20	srdtfyj	07-Feb-20	09-Feb-20	2
26
Driver Bhatu and Trip Report

Cell Formulas
Range	Formula
B23:F26	B23	=IFERROR(INDEX('Driver and Trip Data'!B$2:B$9999,SMALL(IF('Driver and Trip Data'!$A$2:$A$9999=$A$2,ROW('Driver and Trip Data'!$B$2:$B$9999)-MIN(ROW('Driver and Trip Data'!$B$2:$B$9999))+1),ROW()-ROW(B$22))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=OFFSET('Driver and Trip Data'!$I$2,,,COUNTIF('Driver and Trip Data'!$I$2:$I$9999,">0"))