Ubuntu
openoffice.org package

How to count lines and block duplicates (CALC)?

Asked by ttry72

I have approx 1600 data rows and the content looks following:

AGED1
AGED1
AGED1
AGED1
AGED1
AGED2
AGED2
AGED5
AGED6
AGED6
AGED6
..jne.

How can I count how many rows I have in the spreadsheet excluding duplicates? Only one row per AGED -type should be included.

Can you help me, please?

Question information

Language:
English Edit question
Status:
Solved
For:
Ubuntu openoffice.org Edit question
Assignee:
No assignee Edit question
Solved by:
ttry72
Solved:
Last query:
Last reply:
Revision history for this message
Hemanth (hemanth-hm) said : 		#1

Go to function wizard {f(x)}

the go to structure tab , select array , then after = in the right side

If you don't have a header and your data starts in row 1, and it goes down to row 150, for Column A, code the following formula in Column C, Row 1 (C1)

=SUM(1/COUNTIF(A1:A150,A1:A150))

get the result

or

=SUMPRODUCT( NOT(ISERROR((1/COUNTIF(A1:A150,A1:A150))))*1)

Revision history for this message
ttry72 (tuory) said : 		#2

Thank you very much for helpful hint.

It started working very well, I used following:

=IF(COUNTIF($A$1:A1;A1)=1;1;0)

Just copy this to the cell and take a sum function at the end.