Merging 3 sheets based on one variable

[Sophal]

Hi,

I have three sheets with 188 to 211 countries using a unique three
aletter code for each country. These country codes are then followed
by columns of data for different variables. I want to create one big
sheet with only countries that are present in all three sheets.

Is there an easy way to do this? I'm clueless about programming of any
sort.

Thanks,

Sophal

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon

 

[Peo Sjoblom]

Assuming that each sheet holds country codes that are not duplicated in the
same sheet
an easy way would be to copy and paste into a new sheet. Create a header in
let's say A2
in the new sheet. Select and copy all data from the first sheet in A3 of the
new sheet.
Then copy the second sheet's data and paste below the data that was pasted
from the first
sheet, then the third. Now you would have a new sheet with all the data
copied from all 3 sheets
with the country codes in column A. Give each column a unique header..

In the new sheet put this formula in let's say J2 (leave J1 blank)

=COUNTIF($A$3:$A$500,A3)=3

now select the whole data in the new sheet from A2 (assume it would be
A2:H500)

do data>advanced filter, select copy to another location, let's say L2 which
would be

Sheet1!$L$2

replace Sheet1 with the sheet name of the new sheet

in the criteria range put

Sheet1!$J$1:$J$2

click OK.. Done.

 

[Ken Wright]

Are all 3 sheets identical in structure, such that sheet 1 has say 10 columns of
data, sheet 2 has the same and so does sheet3, and that each column in each
sheet has the same type of info in it?

If so, then copy your first sheet by right clicking on the tab and selecting
Move or Copy, then tick create copy and hit OK.

You now have a copy of your first sheet. Now just select the data in sheet 2
(not the headers though), copy it and paste it directly undeneath the data in
the copied sheet. Do the same for the 3rd sheet.

Now sleect the entire set of data, headers as well, and do data / filter /
advanced filter / tick 'copy to another location and then choose a cell in the
first column of dat a few cells under your last entry, tick 'unique values only'
and hit OK. You can now delete the first table of data above and stick with
just the copied set below.

 

[Peo Sjoblom]

Ken,

maybe I read the OP wrong but didn't he want the opposite

" want to create one big
sheet with only countries that are present in all three sheets"

That to me sounds as though he wants the values that occur 3 times
in the merged data. So maybe a combination of our methods, first extract
the values that occur 3 times using my method and then using another
advanced filter to pull out unique entries from that table?

 

[Ken Wright]

LOL - Thanks Peo - appreciate the catch.

 

[Sophal]

Many thanks for your responses. Although I have not yet been able to
do what I intended, using the suggested =IF(COUNTIF... function, I did
manage to get myself a list of only those countries that appear in all
three sheets! That's a leap forward, as far as I am concerned. All I
need now is to figure out how to bring the data/variables that were
attached to them.

Thanks,

Sophal

 

[Ken Wright]

Can you clarify what it is you intended. We can get you to whatever it is you
need, but we just need to know which way to jump here. A couple of questions:-

On each sheet, is there only one instance of any particular country, ie so if
you have 200 records, there are 200 countries.

Are you looking for a summary sheet with ALL the data from ALL 3 sheets on it,
or are you looking for a summary sheet with ONLY the records that appear on each
of the 3 sheets you have, ie, if you had 300 records on sheet1, 250 records on
sheet 2 and 200 records on sheet 3, and only 100 records in total were to be
found on all 3 sheets, then is it the 100 you want on your summary sheet.

For any one of these records that will be found on all 3 sheets, is ALL the data
for each record exactly the same, or do you have different sets of data for each
sheet.