Need unique data line

Yashwant Singh

Hi team,

I have multiple duplicate date rows in my tracker but i need a single line for each date.. how's possible ?

need single line for like below

27-oct-2020 : CTMC-RC2/2015/WPTA/201400772K

13-feb-2021 : CTMC-RC2/BLP/2017/WPTA/201400772K

Bassam Khalil

Hi @Yashwant Singh

Hope you are fine, please try the following formula:

=JOIN(DISTINCT(COLLECT(Code:Code, Date:Date, [Unique Date]@row)))

the following screenshot shows the result:

Yashwant Singh

Hi @Bassam Khalil ,

all good at my end...thanks.!! hope also the same at your end ..

you can find unique value but how can i add unique rows in my summary / report page.

actually working on a dashboard, I have some data in which data rows are duplicate (shared image to you), and create report only for 1 single row data..

Bassam Khalil

@Yashwant Singh 

Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Yashwant Singh

Please check below.. based on CITQ Date and CITQ Query, need only one row for each query..

Bassam Khalil

@Yashwant Singh 

Did you have in your sheet the "created date" Or "modified date" column?

Yashwant Singh

no "created date" Or "modified date" column

Bassam Khalil

@Yashwant Singh

you can use the following formula and change the index row number to collect the DISTINCT value from each group:

1- Create a helper column contain series of numbers from 1 to "the number of the unique values you have "10 for example".

use the following formula for each column

CITQ-Unique =IFERROR(INDEX(DISTINCT([CITQ Query]:[CITQ Query]), series@row), "")

CITQ Query-Unique =IFERROR(INDEX(DISTINCT(CITQ:CITQ), series@row), "")

the following screenshot shows the result: