Prev: Sumproduct formula
Next: pivot table
From: Michel on 19 May 2010 15:25
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

--
Michel

From: Max on 19 May 2010 19:25
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
--
Max
Singapore
---
"Michel" wrote:
> 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
>
> --
> Michel
>
 | 
Pages: 1
Prev: Sumproduct formula
Next: pivot table