Count duplicate in single row

[Baldowsky]

Hi,

I've been trying to figure out how to make the Frequency function work. It does work when I manually enter a value, but when I paste a value from a different workbook, it doesn't work. help please.

[Glenn Kennedy]

Is this sheet showing us when it works, or when it doesn't ? What are you trying to count the frequency of? What is your expected result?

[Baldowsky]

It shows both. if you paste a data from another workbook, it will not count the duplicate. What i'm trying to do is to count the duplicates in a single row. I tried using this formula, =COUNTIF(17:17,A17)>1, but still, when i tried to paste a data from a different workbook, it ceases to function.

[Tony Valko]

Post some sample data and tell us what result you expect.

[Baldowsky]

for example:

Please Login or Register  to view this content.

Once i've removed the duplicates, i need it to look like,

Please Login or Register  to view this content.

[Tony Valko]

for example:

123...123...123...456...456

Duplicates: 3

Ok, I can see how you'd come up with 3 duplicates on that row.

1234...1235...1236...456...456

Duplicates: 2

If the other row has 3 duplicates shouldn't this row have just 1 duplicate?

[Tony Valko]

See if this does what you want:

Data Range

	A	B	C	D	E	F	G
1	123	123	123	456	456		3
2	1234	1235	1236	456	456		1
3	------	------	------	------	------	------	------

This formula entered in G1 and copied down:

=COUNT(A1:E1)-SUM(--(FREQUENCY(A1:E1,A1:E1)>0))

[Baldowsky]

Cool, it works. thanks a lot.

[Tony Valko]

You're welcome. Thanks for the feedback!