Making data readable.

[GAJITCS]

My data exists in the following format and I've been asked to summarize it for the sales team.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]0.25[/TD]
[TD]Austria[/TD]
[TD]Alpha[/TD]
[/TR]
[TR]
[TD]0.50[/TD]
[TD]Austria[/TD]
[TD]Alpha[/TD]
[/TR]
[TR]
[TD]0.75[/TD]
[TD]Austria[/TD]
[TD]Alpha[/TD]
[/TR]
[TR]
[TD]1.00[/TD]
[TD]Austria[/TD]
[TD]Bravo[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[TD]Austria[/TD]
[TD]Bravo[/TD]
[/TR]
[TR]
[TD]3.00[/TD]
[TD]Austria[/TD]
[TD]Alpha[/TD]
[/TR]
[TR]
[TD]4.00[/TD]
[TD]Austria[/TD]
[TD]Charlie[/TD]
[/TR]
[TR]
[TD]5.00[/TD]
[TD]Austria[/TD]
[TD]Charlie[/TD]
[/TR]
[TR]
[TD]6.00[/TD]
[TD]Austria[/TD]
[TD]Bravo[/TD]
[/TR]
[TR]
[TD]7.00[/TD]
[TD]Austria[/TD]
[TD]Alpha[/TD]
[/TR]
</tbody>[/TABLE]

















Column A extends in increments of 1 for another 23 rows. These rows then repeat for a number of other countries (column B)

Column C we can term as being the carrier. This could include any of 30 carriers.

What I've been asked for is a summary that for the above example, would look something like:

Austria: Alpha, 0.25-0.75, 3.00, 7.00 . Bravo, 1.00-2.00,6.00 . Charlie, 4.00-5.00

Note that Alpha is summarized as 0.25-0.75 . This includes the 0.50, but 0.50 is not mentioned in the summary

The carriers would only be listed in this summary if they exist for that country. Delta, for example, may not appear in the Austria list.

I think I understand the question, just haven't a clue as to how to answer it.

Many thanks.

 

[Special-K99]

So you want a MIN and MAX value for each carrier per country.

Maybe a Pivot Table or someone will probably say PowerQuery.

You might need to create two Helper columns for each carrier per country and run a pivot table from that.
e.g.

=MIN(IF((B$1:B$10=B1)*(C$1:C$10=C1),A$1:A$10))
and
=MAX(IF((B$1:B$10=B1)*(C$1:C$10=C1),A$1:A$10))

These are both Array formulas, use Ctrl-Shift-Enter