Count unique values in list but NOT using ARRAY formula

http://www.excelforum.com/excel-form...ir-status.html

Same problem as the solved one above but MUST NOT use array formula.

1. WITHOUT using array formula
AND
2. ONLY using formula, no menu sort or macro options

ie. columnn A has list of numbers, some occur more than once.
output to two new columns listing each unique number and how often it occurs in first column.

This could instead be names instead of numbers.

If I use countifs or frequency i would still get duplicated counts.

Note: the list length may be a fixed/defined length. eg. Out of 12 values, count the unique values.
A B C
MARY MARY 3
JOHN JOHN 2
JOHN JOE 1
MARY
JOE
MARY

Thanks, can't use Pivot tables or filters either. Formula only.

This is for converting to web page via Spreadsheet Converter.

Will investigate helper columns. At least the qty of items to assess is fixed.

Originally Posted by Fotis1991

Sorry but i don't understand. Do you want to use helper-hidden columns for this or NO?

That probably would be OK for the converter app.

Just looking at Helper columns now http://www.youtube.com/watch?v=3tfwyD-T7Zs
Spreadsheet converter supports many (many) of the formula (converts functionality to javascript) but cannot do aforementioned or much/anything to do with arrays.

Sample of data and progress so far to sort.

Originally Posted by taifur_rahman

If(countif($a$1:a1,a1)>1,"",a1)

That's helped heaps!

For spreadsheet converter compatibility, had to replace "" values with 0 - zero numerical values.

I've added a column to count the above unique list.

Now just need to remove unwanted rows.

Attached solves the original problem, except for the rows with zeroes.

Originally Posted by daddylonglegs

What do you mean "without using array formulas"? If you mean formulas without CTRL+SHIFT+ENTER then give this a go.....

Hi, yes, non-"CSE" array formula. I've tested this and it indeed works fine within Excel, however Spreadsheet converter fails with the warning that array formulas only sparsely supported.

It fails on the column C formula.

I've otherwise solved it with the hint of the earlier posting (and provided a means of sorting both result columns)!.

"solved" as in acceptable by Spreadsheet Converter.

Preparing a post to close the thread. Once again, thanks.

Solved with aspects of suggested solutions above.

Taufur Rahman's elegant code started the ball rolling.

list2.xlsx

Thanks to all contributors, although the solutions ran into issues with Spreadsheet converter.