Vendor List

  • Thread starter Thread starter Andri
  • Start date Start date
A

Andri

Dear Expert,
Please help to create the list automatically.

Here is the situation, for simflify, i just take the Vendors Columns Only.

Supplier (we assume G1 - G12).
Bridgestone
Honey
Bridgestone
Enterprises
Enterprises
Enterprises
Honey
Honey
ALS
ALS
Aircraft

I did success to calculate how many vendors listed above with the following
formula:
{ =COUNT(IF(FREQUENCY(MATCH(G3:G13,G3:G13,0),MATCH(G3:G13,G3:G13,0))>0,1))}
Result = 5 Vendors.

But on the other, i would like to list automatically at Column H1 - H5 for
those 5 Vendors:
H1 = Will be Bridgestone
H2 = Will be Honey
H3 = Will be Enterprises
H4 = ALS
H5 = Aircraft.

Please help which formula can implement that result.

TIA
 
Hi Andri

You could do it with Advanced Filter

Select your range of source data>
Data>Filter>Advanced Filter>Unique values only>Copy to new
Location>select cell H12 as the new location
 
Hi,

Select your data including the header then


Excel 2003
Data|Filter|Advanced filter

Excel 2007
Data tab|Advanced

Then for either
Select copy to another location
Check 'Unique items
Enter $H$1 in the 'Copy to' box
OK

Excel may ask about headers, click YES
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Dear Mike,

thank you for your kind help and response.

but i need using the EXCEL FORMULA, e.g MATCH, OFFSET, etc to solve the
problem.

Please help further.

respectfully,
Andri
 
=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX($G$3:$G$13,SMALL(IF(MATCH($G$3:$G$13,$G$3:$G$13,)=ROW(INDIRECT("1:"&ROWS($G$3:$G$13))),MATCH($G$3:$G$13,$G$3:$G$13,)),ROWS($1:1)))))

ctrl+shift+enter, not just enter
 
Dear Mike,

thank you for your kind help and excellent solution.

the case is closed now.

highly appreciated that.

Respectfully,
Andri
 
Back
Top