select list with no duplicate

  • Thread starter Thread starter Tang
  • Start date Start date
T

Tang

i have a list where combox box will refer

Expense
Expense
Sales
Sales
Expense
Debtors
Sales
Debtors

However, i just want the combo box to list down
Expense
Sales
Debtors

rather than the whole list.

I am thinking creating another column to list the items uniquely using
formula, but don't know who to do it

Appreicate your help.
 
Hi
if you only have text values with no blank cells in between you may try
the following formulas (don't need a helper column):
Assumption: Your data is in cells A1:A10

Enter the following formulas:
B1:
=A1
B2: enter the array formula (entered with CTRL+SHIFT+ENTER):
=INDEX($A$1:$A$10,MATCH(0,COUNTIF(B$1:B1,$A$1:$A$10),0))

and copy B1 down as far as needed. If you need to prevent the errors
which occur after you have exhausted the distinct elements try the
following array formula in B2 instead:
=IF(ISNA(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$10),0)),"",INDEX($A$1:$A$10,MAT
CH(0,COUNTIF(B$1:B1,$A$1:$A$10),0)))
 
Hi Aladin

though I like the approach described on this page I think it won't help
the OP as it deals only with numeric values.
I remember seeing a very similar approach from you dealing with text
values (using REPT(...)) but couldn't find it in the archives.


Frank
 
The formula system is not dependent on whether the data is numeric or text
at all. It is a way faster than the one you appear to have posted.

If the OP sorts the input data in ascending order (which is not a
requirement), the result list of distinct items will also be sorted wihout
additional costs.
 
Hi Aladin
sorry for that. It was too late and I only saw the LOOKUP part (using
the 'big number'). And as the thread was titled 'list of inique
numbers' I made the wrong assumption.

And yes this is definetly faster than my variant (that was why I stated
that 'I like this approach'). It is now just a decision of the OP to
have either a fster formula and use a helper column or a slower formula
without a helper column

Regards
Frank
 
Back
Top