How can I match data about countries drawn from two database queries?

[tettrick]

Hi,

From a database, I have imported (into Excel) two lists of statistic
by country: (list a) the 20 countries with the largest number of cel
phone users, and (list b) the 20 countries with the highest percentag
of cell phone users. I want to match these two lists by country s
that the names and their corresponding statistical information wil
appear in the same row in every case where a country exists in bot
lists. For those countries that don’t appear in both lists, I woul
like for each of them, along with its corresponding statistica
information, to appear in its own row.

Does Excel 2003 have a function or tool that can reorganize the data i
this way?

Thanks,
tettric

 

[Harlan Grove]

From a database, I have imported (into Excel) two lists of statistics
by country: (list a) the 20 countries with the largest number of cell
phone users, and (list b) the 20 countries with the highest percentage
of cell phone users. I want to match these two lists by country so
that the names and their corresponding statistical information will
appear in the same row in every case where a country exists in both
lists. For those countries that don’t appear in both lists, I would
like for each of them, along with its corresponding statistical
information, to appear in its own row.

Does Excel 2003 have a function or tool that can reorganize the data
in this way?

Don't you think it'd make a LOT more sense to do an outer join like this in
the database than in Excel?

This can be done in Excel, but not elegantly. Presumably the order in one or
the other list would be controlling. If numbers were controlling, then copy
the numbers table and supplement with another column of percentages. If the
copy of the number table began in cell A1, the formulas for percentages
would be in column C and use formulas like

C1:
=IF(COUNTIF(INDEX(PTBL,0,1),A1),VLOOKUP(A1,PTBL,2,0),"")

Fill C1 down into C2:C20. Then add lines for the records in the percentages
table that don't appear in the numbers table.

A21:
=INDEX(PTBL,MATCH(0,COUNTIF(A$1:A20,INDEX(PTBL,0,1)),0),1)

C21:
=VLOOKUP(A21,PTBL,2,0)

Fill A21:C21 down until they return errors.

 

[tettrick]

Hi Harlan,


I agree. If it were possible for me to handle this issue in th
database, it would be much easier. Unfortunately, the database reside
on a public Website, so I don’t have this kind of control over the wa
arranges the data.

As for your explanation, I didn’t understand it completely. However,
did try to follow your instructions…

I arranged the data in the cells based on the way I understood you
directions: I placed the two sets of data into two columns (A and B
instead of four columns as I had them originally. I put the countrie
from both lists in column A, placing the group with numbers in rows A
through A20 and placing the group of countries’ names from the grou
with the percentages into rows A21 through A40. In column B, I place
the each country’s corresponding number in cells B1 through B20 and th
percentages in B21 though B40. Then I tried copying and pasting th
formula you labeled “C1” into cell C1, but I got a “#NAME?” error.
copied this formula down column C through cell C20, and saw the sam
“#NAME?” error in each of the cells.

I also tried to reverse-engineer the “C1” formula that you created, i
addition to trying to understand the structure of the other tw
formulas you wrote (“A21” and “C21”). It seems as if you’ve use
different combinations of certain functions (VLOOKUP, MATCH, INDEX, an
COUNTIF) in order to get the result I’m looking for. However, I wasn’
able to determine the meaning of “PTBL”, which I saw in the formulas.
Based on “PTBL”s position in the formulas, it appears to represen
something that would go into a field rather than representing
function, but I looked in the list of functions for “PTBL” or somethin
that could be it, and I didn’t see anything that would appear t
represent.

I appreciate your willingness to help me solve this. Don’t fee
obligated to respond directly to my comments and observations. I wrot
these things to illustrate what I’ve done to try to put your advice t
use. Ultimately, I’m interested in using Excel to rearrange the dat
properly, so I welcome any additional advice or explanation that migh
help me to achieve this.

Thanks,
tettric

 

[Harlan Grove]

...
....

I arranged the data in the cells based on the way I understood your
directions: I placed the two sets of data into two columns (A and B)
instead of four columns as I had them originally. I put the countries
from both lists in column A, placing the group with numbers in rows A1
through A20 and placing the group of countries’ names from the group
with the percentages into rows A21 through A40. In column B, I placed
the each country’s corresponding number in cells B1 through B20 and
the percentages in B21 though B40. Then I tried copying and pasting
the formula you labeled “C1” into cell C1, but I got a “#NAME?”
error. I copied this formula down column C through cell C20, and saw
the same “#NAME?” error in each of the cells.

....

No.

What I meant was that you have 2 tables to begin with. One has numbers, the
other percentages. Name the former NTBL and the latter PTBL. *YOU* have to
do that first. Then you have to decide how you want the results listed. It'd
be simplest to copy one or the other preexisting tables and use it as the
starting point. I'll assume you copy NTBL, which *YOU* should have already
named, into another worksheet with the first cell of the copied table in
that worksheet's A1 cell. NOTE: AT THIS POINT YOU SHOULD HAVE COPIED *ONE*
**AND** **ONLY** *ONE* TABLE.

Next, I meant that you should supplement that copied table with data from
the other table, PTBL, which *YOU* should have already named. You should
have country name in col A and numbers of cell phones in col B. You now add
percentage cell phone penetration in col C using formulas like

C1:
=IF(COUNTIF(INDEX(PTBL,0,1),A1),VLOOKUP(A1,PTBL,2,0),"")

Note: this will (and **SHOULD**) return #NAME? error if *YOU* haven't named
that table PTBL. If you don't want to name the other table PTBL, then
replace PTBL in the formula above with the range address of the percentages
table.

This will show percentages for the country in col A if that country has an
entry in the PTBL table. Otherwise it'll display nothing.

Then you need to supplement the table with extra rows for the countries in
PTBL that aren't in NTBL. You do that using the other formulas I provided.
Those formula will also return #NAME? if *YOU* haven't named the percentages
table PTBL. Same comments as above.