Extract & report Duplicate Values in One Cell

[loginid]

Dear Frnds,

Please see the following requirement

Req: Whenever Date is changed in any other cell it should return ALL the descriptions and Places (includes repetitions).

Start Date	Description	Place
07-05-15	Training 1	Place 1
07-06-15	Training 2	Place 2
07-05-15	Training 3	Place 3
07-06-15	Training 4	Place 4
07-07-15	Training 5	Place 1
07-07-15	Training 6	Place 3
07-08-15	Training 7	Place 4
07-09-15	Training 8	Place 1
07-08-15	Training 9	Place 2
07-09-15	Training 10	Place 3

<tbody>
</tbody>

Date: 07-07-15 (Input)

Training to be done: Training 2,Place 2 (Output)

VLOOKUP(E2,$A$2:$C$11,2,0)&","&VLOOKUP(E2,$A$2:$C$11,3,0) --- I've tried.

Now if I use the above formula Vlookup returns only the 1st Value it finds. What is required is A formula which will return all the trainings & place when Date is changed.

Thanks & regards.

 

[Weazel]

maybe something like...

Excel 2012

	A	B	C	D	E	F	G	H	I
1	Start Date	Description	Place		07/05/2015		Start Date	Description	Place
2	07/05/2015	Training 1	Place 1				07/05/2015	Training 1	Place 1
3	07/06/2015	Training 2	Place 2				07/05/2015	Training 3	Place 3
4	07/05/2015	Training 3	Place 3
5	07/06/2015	Training 4	Place 4
6	07/07/2015	Training 5	Place 1
7	07/07/2015	Training 6	Place 3
8	07/08/2015	Training 7	Place 4
9	07/09/2015	Training 8	Place 1
10	07/08/2015	Training 9	Place 2
11	07/09/2015	Training 10	Place 3

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula G2 {=IFERROR(INDEX($A$2:$C$11,SMALL(IF($A$2:$A$11=$E$1,ROW($A$2:$A$11)-ROW($A$2)+1),ROWS(G$2:G2)),COLUMNS($G2:G2)),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[loginid]

Thanks Weazel for ur reply.

Thant works fine. (alternative)

What I m seeking is ; lets say in your table (u posted) , the Cell E1 (Where the date is entered/changed) then in the single Cell G1 it should return : Date & Place i.e
E1 : 07/05/2015 G1: Training 1,Place1,Training 2,Place2 (Should get).

Thanks

 

[Weazel]

ahhh, ok, I misunderstood how you wanted the data returned....

You'll probably be better off with a VBA solution, then with formulas....

Unfortunately I can't help you with the VBA part

 

[loginid]

Thanks Pal....... Ur Solution was a good one.

 

[loginid]

Dear Weazel ..... can u pls elaborate ur formula.......
especially ....SMALL(IF($A$1:$A$10=$E$1,ROW($A$1:$A$10)-ROW($A$1)+1)

Thanks

 

[Weazel]

ROW($A$1:$A$10)-ROW($A$1)+1) would return the row number if the IF returns true.

by itself it is returning a list of values from 1 to 10 which is also flexible enough to adjust in the event rows are inserted.

essentially the small is returned a filtered list of row numbers back to the index

the rows function is an incrementor to return the K to the small, and the columns is essentially doing the same thing for the column number of the index

hopefully that helps