List repeated values in excel

Question

I have this table:

  |     A      |  B  |  C  |  D  |  E
1 | 2017-01-01 | AAA | BBB | CCC | DDD
2 | 2017-01-08 | AAA | BBB | BBB | CCC
3 | 2017-01-15 | BBB | AAA | DDD | DDD
4 | 2017-01-22 | CCC | BBB | CCC | BBB

Column A is always the previous date+7. Columns B, C, D and E are based on data valitadion lists.

And I need to list, not only highlight, repeated values in each row (date). I would like to insert the date in other sheet and below excel would list the repeated values.

Example:

2017-01-01  2017-01-08  2017-01-15  2017-01-22
            BBB         DDD         CCC
                                    BBB

If possible, it would be great to list, in a different column, which values are not present, keeping in mind I would only have "AAA, BBB, CCC and DDD".

Can someone please help?

Thanks!

Michi Michi · Accepted Answer · 2017-08-28T07:32:19

In Columns F:J do the following steps:

1) Transfer your original table into the below structure.
List your items AAA,BBB,CCC,DDD in Column F.
Insert counting numbers from 0-3 in Row 1.
In Row 2 insert the dates from your original data by using this formula: =OFFSET($A$1,G1,0) (Formula goes into Cell G2 and can be copied to the righ til Cell J2)

2) In G3 you insert this formula:
=IF(COUNTIF(INDIRECT("B"&ROW($A$1)+G$1&":E"&ROW($E$1)+G$1),$F3)>1,$F3,"")
This formula you can copy down til Row 6 and to the right til Column J and you will get the table below as a result.

  |  F  |     G      |     H      |     I      |      J       | 
1 |     |     0      |     1      |     2      |      3       |
2 |     | 2017-01-01 | 2017-01-08 | 2017-01-15 |  2017-01-22  |
3 | AAA |            |    BBB     |            |              |
4 | BBB |            |            |            |      BBB     |
5 | CCC |            |            |            |      CCC     |
6 | DDD |            |            |    DDD     |              |

List repeated values in excel

1 Answers