Hi all,



apologies upfront for not beeing nowhere near the solution with reading older posts....

I have a rather boring jjob :-) and I part of it is to compare my companies capabilities in countries with the countries of our clients.....

To explain I have a table in excel stating following (thats where we are)

A1
country1
country 2
country 3
country 4
.
.
.
country 156

and I get a another column where they tell me in the same way where they are.

country 1
country 2

while it is easy for lets say 5 countries mostly I have to check if 120 of there countries are matching with our 156 and I do that one by one. Is there a formula that can do that and if yes could that formula if it find a match in the two colums "print" in a third coplumn the name of our copany there which I have in column 3,4 and 5 (Name, Contact, Emailadress)

I have attached an example how it should look like by filling it out manually as I always do.....

Thanks much and have a great day!.

Attached Files

Book1.xls (15.0 KB, 29 views)

For the layout in your example, try this:

Cheers,

amazing. Works perfect. I had to adapt a little the actual cells by moving the coloured thing when you click the formula (i learned something here) and that is tghe reason why I am telling this! :-) Thank you so much!

Hi there,

a further help would be great. It looks it up perfectly if there is one hit and finds the first country in columbn A with =VLOOKUP($P2,$A:$N,COLUMN(A$1),FALSE). Sometimes unfortunately in example the country Algeria is twice or more often in column A is there a way that excel like below:

A1
country1
country1
country1
country2
country2
country3
country4
country5
country5
country5
country5

Thanks for any help!

Your question is not clear. Can you be more specific please? It would also help if you provided a sample spreadsheet that indicates the problem and shows what you want the outcome to look like.

hi there,

sorry for beeing unprcise and thanks for the support. Please see it attached. basically if the looked up is twice in the search columns it would be great to get as an output both hits. Hoppe that clarifies in combination with the attached.

Have a lovely weekend.

Attached Files

compare countries.zip (60.8 KB, 17 views)

There are no Excel formulas that will do what you are asking. It may be possible using VBA, but for that you will need someone with programming skill. You could also do it using Microsoft Access, but that would mean changing a basic list into a database.

The simplest solution would be to rearrange your list so that there is only one country per row.

Cheers,

Hi there,
I have created a formula that I think solves your issue (see attachment). As you see, there has to be some formulas in column A and B to solve your issue (it is possible to move them further to the right if you like, but then you have to do a small modification).

In cell H2 there is a general formula, which can be copied down and right as far as you like.

I have created one "main solution", see sheet 2 (with correct error handling)
I have also to other alternatives, see sheet 3 and 4 (which creates "errors")

Kind regards
Bjorn

Attached Files

compare countries 2b.xls (99.0 KB, 13 views)

Hi again,
here is a slightly improved version. In this version, your list of countries doesn't have to be sorted.

Kind regards
Bjorn

Attached Files

compare countries 2c.xls (99.5 KB, 16 views)

May I say unbelievable great and thank you so much for helping. I will try to adapt it to the original table which I managed from the first help. The error n/a is great by the way as it simply indicates "not available" to me which is exactly what I want in the cell anyway so I cannot express how thankfull I am.

Although a bit of warning I might come and ask again for help......

Thanks all and have a great day!

Hey,
I am glad you liked it! Feel free to post more questions!
Remember to mark this issue as solved (that helps everyone using this forum).

Kind regards
Bjorn

hi Bjorn,

there was a reason why I haven't closed it..... I adapted it to my table and it works fine. Unfortunately what I did not take into consideration was that sometimes I have thirteen hits and the results it gets too wide so that I cannot put them on A4. Is there a way to make the output the other way around?

Example attached.

Thanks much and hoping you enjoy this.....

Attached Files

compare countries 3.xls (46.5 KB, 14 views)

Hi again,
here is an proposal for solution, almost completely without formulas! It actually uses a quite powerful built in functionality in Excel, called "Advanced filter".

I attach a worksheet with the result, but you need to read the steps below to create this result. Please note that my naming of dialog boxes, alternatives etc might not be 100% correct, since I am running a Swedish installation and my translation might not be 100% equal.

Due to the language effect i recommend you to try my solution out NOT using my attached file, but instead using your own file.

In column D there are the so called "filter criterias". First you have to copy the fomulas in column D to your own sheet. Actually column C would have been possible to use as criterias (only changing the header to "Country"), but then it would have the effect that a country written "Angola" would be interpreted as "Angola*" (joker sign). If you enter country "Niger" it would also select "Nigeria" which would not be correct, therefore we need column D, which says that the country name has to be exactly equal.

Now select the menu "Data" and then "Filter" and at last "Advanced filter" (for repetitive use I recommend inserting this command as an icon in your Excel sheet).

1) Select the radiobutton "Copy to another location"

2) At list area (database area?) press the button to the right and mark your area (for example A1:B21) + enter.

3) At criteria area (?) press the button to the right and mark your area for criterias (for example D131) + enter.

4) At "Copy to" (?) press the button to the right and mark the start of your area for output (for example the cell E1) + enter.

Press OK and the list is there!

You can read more using "help" in Excel and search for "Advanced filter" or look here:

http://office.microsoft.com/en-us/ex...001781033.aspx (at the very end (see the header "Notes" about 20 lines from the bottom) there is some good pieces of information about naming areas.

Kind regards
Bjorn

Attached Files

compare countries 4.xls (43.5 KB, 15 views)

Hi again again!
Here is another solution.

Enter your countries in column D, use the filter button in cell A1 to filter only "Yes".

Kind regards
Bjorn (still investigating yet another alternative)

Attached Files

compare countries 5.xls (44.5 KB, 15 views)

Hi for the third time,
here is a third solution (formula based).

There are two named areas in the sheet ("Data" and "Countries").
The error codes in column G and H you can handle according to my first very proposal(s).

Kind regards
Bjorn

Attached Files

compare countries 6.xls (50.5 KB, 16 views)