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

Hi,

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?

Thanks,
tettrick

"tettrick" wrote...
>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.

Hi Harlan,


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

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

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.

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

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

Thanks,
tettrick

"tettrick" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
....
>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.

"tettrick" wrote...
>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" wrote...
....
>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.