Vendor List
 

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(G 3: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

Vendor List
 

Hi Andri

You could do it with Advanced Filter

Select your range of source data
DataFilterAdvanced FilterUnique values onlyCopy to new
Locationselect cell H12 as the new location

--
Regards
Roger Govier

Andri wrote:
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(G 3: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

Vendor List
 

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.


"Andri" wrote:

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(G 3: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

Vendor List
 

Typo

H12 should have read H1 of course

--
Regards
Roger Govier

Roger Govier wrote:
Hi Andri

You could do it with Advanced Filter

Select your range of source data
DataFilterAdvanced FilterUnique values onlyCopy to new
Locationselect cell H12 as the new location

--
Regards
Roger Govier

Andri wrote:
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(G 3: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

Vendor List
 

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" wrote:

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.


"Andri" wrote:

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(G 3: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

Vendor List
 

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(INDIRE CT("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.


"Andri" wrote:

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" wrote:

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.


"Andri" wrote:

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(G 3: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

Vendor List
 

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX($G$3:$G$13,S MALL(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" wrote:

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(G 3: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

Vendor List
 

Dear Mike,

thank you for your kind help and excellent solution.

the case is closed now.

highly appreciated that.

Respectfully,
Andri

"Mike H" wrote:

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(INDIRE CT("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.


"Andri" wrote:

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" wrote:

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.


"Andri" wrote:

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(G 3: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