# Extract & report Duplicate Values in One Cell

##### New Member
Dear Frnds,

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.

Last edited:

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Weazel

##### Well-known Member
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
CellFormula
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>

##### New Member

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

##### Well-known Member
ahhh, ok, I misunderstood how you wanted the data returned....

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

##### New Member
Thanks Pal....... Ur Solution was a good one.

##### New Member
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

##### Well-known Member

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

Replies
2
Views
281
Replies
6
Views
754
Replies
3
Views
505
Replies
2
Views
1K
Replies
20
Views
842

1,196,021
Messages
6,012,904
Members
441,740
Latest member
Latrs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back