Exvcel create a matrix to analyze data 1

[Andrzejek]

What do you consider to be "the top 20 companies"? By "Company name" in alphabetical order? What makes them to be on the top? What ranking process do you have?

Have fun.

---- Andy

 

[SamtheMan40]

Hi Andy:

What makes the company in the top 20 is number of service calls in a particular city. Yes I would like to see list alphabetical. My target will be to identify, who has the most orders and in a particular city, so we can target cities that we don't have much of service calls. There multiple companies that service multiple cities(areas).

it will look like this

Top 20 customers
then
Top 21-50 customers service calls 21-50
Top 51-51 customers service calls 51-50
Top 51-70 customers service calls 51-70
Top 71-100-customers service calls 71Sam

 

[Andrzejek]

So your Excel looks something like:
[tt]
Company name Cities Services
ABC NYC 12
XYZ Boston 45
KLM Atlantic 56
BQY Chicago 123
...[/tt]

Can't you just order it by Services and Company Name, and then place an empty row after row 20, then 50, and so on?

I don't really know how you want your outcome to look like....

Have fun.

---- Andy

 

[SamtheMan40]

Thank you Andy for you assistance.... the report for ranking is based on the #'s of service calls the highest will appear first depending on the ranking. A company will only appear once in each instance.

company destinations service calls TOP 20 TOP 21-50 TOP 51-70 TOP 71-100
XYZ LAX 50 RFV WES TRF
ABC FLA 22 XYZ SDS
DEF SFO 14
EFRS NYC 29 ONLY ONE INTANCE OF EACH COMPANY
FTG NYC 11
SDS LAX 45
WES MON 67
WES SFO 43
XYZ NYC 200
ABC DFW 32
TRF LAX 2
SWE FLA 22
DFV SFO 14
JNM NYC 29
DWS LAX 11
RFV LAX 450
EDS LAX 34
RFV SFO 45

 

[Andrzejek]

If I take your example:
[tt]
company destinations service calls
XYZ LAX 50
ABC FLA 22
DEF SFO 14
EFRS NYC 29
FTG NYC 11
SDS LAX 45
WES MON 67
WES SFO 43
XYZ NYC 200
ABC DFW 32
TRF LAX 2
SWE FLA 22
DFV SFO 14
JNM NYC 29
DWS LAX 11
RFV LAX 450
EDS LAX 34
RFV SFO 45 [/tt]

and order it by service calls and company:
[tt]
company destinations service calls
RFV LAX 450[blue]
XYZ NYC 200[/blue][green]
WES MON 67[/green][blue]
XYZ LAX 50[/blue]
RFV SFO 45
SDS LAX 45[green]
WES SFO 43[/green]
EDS LAX 34[red]
ABC DFW 32[/red]
EFRS NYC 29
JNM NYC 29[red]
ABC FLA 22[/red]
SWE FLA 22
DEF SFO 14
DFV SFO 14
[/tt]

It is impossibile to have "ONLY ONE INTANCE OF EACH COMPANY": ABC company is listed twice, so is WES and XYZ, and RFV

Have fun.

---- Andy

 

[SamtheMan40]

Hi Andy:

I know this is complex. My goal is to identify which company has the most service calls by service Cities(destinations) if they have multiple locations, then I want to list them once respecting their highest number of service calls, and rank them based on the number of service calls. Like XYZ is one of the top performers 200 service calls so, I will only listed on the ranking top 20% vs. DFV only 14 services calls 71-100%.

Thank you again for your assistance.

Sam

 

[mintjulep]

You might be able to make use of some combination of the following functions:

RANK()
PERCENTRANK()
SUMIF()

 

[Andrzejek]

And you may (or may not) sum up companies ABC, WES, XYZ, and RFV before rankings. it all depends on your criteria.

I don't kow much about it, but PIVOT tables may be the answer to your quest....

Have fun.

---- Andy

 

[SamtheMan40]

Hi Andy:

Thank you again for help! Also, I would like to find out how do I apply the percentrank if I have the cities and the services calls.

Thanks,

Sam

 

[lionelhill]

Andrzjek is probably right to suggest pivot tables. You should be able to use one to tabulate companies with a single entry per company, which can be either the sum of service calls for all entries for that company, or the largest service-call entry for all entries for that company, you can choose. You can then arrange your pivot table sorted in whatever order you like, and copy whatever range of data you like. This would relieve you of any formulae whatsoever, and probably give you the desired result!