Filter formula

  • Thread starter Thread starter Michel
  • Start date Start date
M

Michel

HI

I have a database with repeated informations as listed below and is
increasing daily

Nat Firm Name
SPAIN VIAJES EL CORTE INGLES
SPAIN VIAJES EL CORTE INGLES
SPAIN VIAJES EL CORTE INGLES
GREECE AMEX INTERNATIONAL- HOLLAND
GERMANY AIRTOURS INTERNATIONAL-GERMAN
ITALY DERTOUR GMBH & CO. KG
UNITED KINGDOM INTERNATIONAL TRAVEL CONNE
BELGIUM RAINBOW TRAVEL LTD

I would like to have a formula which will list the firm name once when
choosing the country. I don't want to perform it through a pivot table as I
am using the sumproduct to add the qty and amount to have a complete report.
Thks for your help
 
Tinker something along these lines ..
Assume your source data running in A2:B2 down,
where uniqueness is defined solely on col A
Here's a way to extract dynamically a uniques list from col A
In E2:
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)>1,"",ROW()))
In F2:
=IF(ROWS($1:1)>COUNT(E:E),"",INDEX(A:A,SMALL(E:E,ROWS($1:1))))
Copy E2:F2 down to cover the max expected extent of data in col A, say down
to A100? Minimize/hide away col E. The unique items in col A will be
auto-returned in col F, neatly bunched at the top. Success? hit YES below
 
Back
Top