Combine tables with Different Labels in Column

I have a table with a list of countries in a column with data corresponding to a dollar value by year.

5 other columns have Countries with data corresponding to a number (that is a count of # of correspondence issued).

However, the country names on the first table do not exactly match the country names on the 5 other columns.

I want to combine the tables into 1 table (if that is the best option) that compares the correspondence issued to the dollar value by year.

I would also be interested in visualization suggestions if possible.

Thanks!

So do you want 5 new columns from column G onwards, or to have a new column next to each of the ones you have now for 2014, 2015 etc.?

Pete

I think I want a new column next to the one's I have now.

So like Country - 2014Value - 2014#Correspondence - 2015Value -2015#Correspondence etc...

If possible, hopefully one get's the idea. Open for different ways one would think is better.

Thanks!

Okay, well you can insert new columns in your main table at column C, E, G, I and K, and then put appropriate titles in row 1 - the other tables will get shifted to the right.

Then you can use this formula in C2:

=IFERROR(VLOOKUP($A2,M:N,2,0),"-")

This should be copied down automatically, and will identify non-matched countries by means of the hyphen. You are bound to get some missing countries, as your first table is longer than any of the others.

Similar formula can be used in the following cells:

E2: =IFERROR(VLOOKUP($A2,P:Q,2,0),"-")
G2: =IFERROR(VLOOKUP($A2,S:T,2,0),"-")
I2: =IFERROR(VLOOKUP($A2,V:W,2,0),"-")
K2: =IFERROR(VLOOKUP($A2,Y:Z,2,0),"-")

You can then improve the display by modifying the name in column A where there is no match. For example, there is no match for row 6 (Vietnam), but simple inspection shows that is because you have Viet Nam in your other tables (i.e. with a space in the middle) - insert a space in A6 and you will have values populated. In A10 you have New Zealand with "(!)" following it - remove this and again the data will populate.

In short, improve the quality of your data in column A.

Hope this helps.

Pete

It would be easier if you arranged to have 5 contiguous columns for the $ values followed by 5 columns for the correspondence, then you could just have a SUMIFS / SUM (or even AVERAGEIFS).

Do you want me to set it up for you like this (after I have eaten) ?

Hope this helps.

Pete

Ok, makes sense, yes whatever time frame works best for you. Thanks so much for your help!

I can't seem to get the 5 contiguous, are you still willing to help me up?

Really appreciate your time

I've changed the layout for you, as suggested, and put this formula in cell L2:

=IFERROR(SUMIFS(B2:F2,G2:K2,"<>-") / SUM(G2:K2),"")

Does that do what you were asking for?

Hope this helps.

Pete

Originally Posted by Pete_UK

I've changed the layout for you, as suggested, and put this formula in cell L2:

=IFERROR(SUMIFS(B2:F2,G2:K2,"<>-") / SUM(G2:K2),"")

Does that do what you were asking for?

Hope this helps.

Pete

You’re awesome! Appreciate your time.

I was looking to do what you’ve done but also individual years.

So like x # correspondents per $ amount by country and total.

And 1 that includes every country and 1 that includes just the countries we issue correspondence

So like for 2018 1000 correspondence and $1000 dollars Mexico 0 correspondence $1000

TOTAL 1000 correspondence $2000

Then another where we don’t include Mexico because we don’t issue correspondence so it would just be China’s numbers (and whatever other countries we issue correspondence)

I'm sorry, I don't really follow what it is that you are trying to achieve. Can you mock something up and I'll take a look at it tomorrow, as it is getting a bit late here.

Pete

I think I mostly have what I need.

Mainly looking for an easy way to split out analysis for both ALL countries and then only countries we issue correspondence for. Any other ways I can more clearly represent the data.

Thanks again for all the time spent!

Sorry, I've not been able to look at this today, and I'm away tomorrow for a few days so it will be the weekend before I can pick this up again (if no-one else has chipped in).

Pete

Originally Posted by Pete_UK

Sorry, I've not been able to look at this today, and I'm away tomorrow for a few days so it will be the weekend before I can pick this up again (if no-one else has chipped in).

Pete

No problem! I just really appreciate al your help on it!