Vendor List

[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

 

[Roger Govier]

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

 

[Mike H]

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.

 

[Roger Govier]

Typo

H12 should have read H1 of course

 

[Andri]

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

 

[Mike H]

Andri,

I don't understand why you want to make it hard for yourself but if you want
a formula try this ARRAY formula

Put this in h1

=G1

Now put the ARRAY formula in H2 and have a look below on how to enter an
array formula. When ARRAY entered drag the formula down until it starts
producing errors

=OFFSET($G$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($G$1:$G$13)=H$1:H1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.
--
Mike

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

 

[Teethless mama]

=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

 

[Andri]

Dear Mike,

thank you for your kind help and excellent solution.

the case is closed now.

highly appreciated that.

Respectfully,
Andri