8. Controlling Repeated values using “Name manager”


 PROBLEM:  How to detect repeated rows or partial repeated?


=2.COUNTA(SOURCE[BATCH])1. - (ROWS(UNIQUE(SOURCE[DESCRIPTION])))>0;
  1.  Returns the number of rows that are unique;

  2.  In this case, returns the number of rows not empty.
ROWS

Rows - Syntax

ROWS(array)

It determines the number of rows. In this case: 5-1=4 The "-1" it excludes every empty row.

At our table:

Rows
4
COUNTA

Counta - Syntax

COUNTA (value1, [value2],...)

At our table:

COUNTA
4
UNIQUE

Unique - Syntax

UNIQUE (array, [by_col], [occurs_once])

Returns unique values

At our table:

DescriptionBatch
GDNS6
CPKJ2
HPBC2
BOAH2

Go to "Formula/Name Manager" and create "New". In this case the name given was "Repe". Insert the formula above. In a cell, insert the formula =IF(Repe;"Repeated batch";"")