Mulitple Find and replace pairs in one go

[hmalone]

Hi,

I was hoping someone may be able to help me.
I have a workbook with two sheets. The first sheet has a list of countries in column B and a list of reference numbers in column A
EG.
1 Argentina
2 Australina
5 Columbia

The second sheet has data in colum A, numbers in column B and COUNTRY=1( or other number) in Column C
And sum total of column B and a blank line at the end.
eg
A B C
text 3 Country=1
text 2 Country=1
5
text 8 Country=2

Hopefully you get the idea.
Want I want to be able to do is to replace Country=1 with the country name - as found in the first worksheet, without having to do a find and replace one by one.

This may be stretching the friendship as well, but what I need to end up with is a worksheet that shows the country name, and the total number from column b.

eg.
A B
5 Argentina
8 Australia

If there's anyone out there who is determined to teach the world the joys of excel, I'd also love to know whether there's a way I can automatically insert a row at the end of each different country name, instead of having to do it manually.

Thanks
Halina

[Carim]

Why don't you upload a zipped copy of your workbook ...?

[hmalone]

Good idea!

Thanks for that,

Halina

[Carim]

Hi Halina,

See attached ...

[hmalone]

WOW!

I'm gobsmacked, that's brilliant, thank you for that. This is a report that I have to pull together every 6 months, Is there a formula that I can copy into a cell for the next time?

I've tried to re-create what you've done with a new sheet, and have come a bit unstuck. I've imported the data into a brand new workbook. I've added the worksheet with the country and reference numbers (I'll be using the same worksheet each time)

I've used subtotal to get the totals from column B and collapsed it all to make it easy to read. Now to the explanation of what you have done, Is there a formula that I can use to replace Country=number Count with the country's name.

Oh, and I've just tried to sort the results, and it doesn't want to sort it by column B. Why is that?

THe final spreadsheet ideally would display the populay countries in decending order
EG.
Country Number
Australia 27
New Zealand 26
USA 23

Thank again for any help