Search list, find value and return binary value.

[TMorville]

Hi.

I need to look through about 25000 rows of information about publications from PHd's, and kategorize what part of the world they are from.

A cell of info, would look like this:


"Univ Copenhagen, Fac Life Sci, Dept Agr Sci, DK-1871 Copenhagen, Denmark | Govt Eritrea, Minist Agr, NARI, Asmera, Eritrea | Int Ctr Agr Res Dry Areas, Aleppo, Syria | GCSAR, Damascus, Syria"


So, for example - i want my code to look thorugh the "Countrys"-sheet and recognize Eritrea as an african country and return me a "1" into a collum called "Africa" - and the same thing with Syria, only returning me a "1" into the "Middleeast" collum.


I have the entire country list, and my data is in cells like the above.


Hope you can help.


Thanks!

It should be added, that ive already googeled it, and searched the forums, but ive found nothing that i feel like i can use, or build on to.

[TMorville]

Im imagining something like:

Search cell a1 for countryname X
If countryname X found in continent collum Y in sheet1
then return 1 in continent Y collum in sheet 2

[mdmackillop]

Welcome to VBAX
Can you post a small sample workbook? Use Manage Attachments in the Go Advanced reply section.
Regards
MD

[TMorville]

Sure.

Theres two sheets. One called Publications, wich is a small sample of data, and something that i've filled out manually - to give an idea how i would like it to end up. The other sheet, "countrys" are all the countrys, subcategorized into 7(?) different parts of the world.

Hope you can help.

Thanks.

[GTO]

Maybe too tire, but in your example, on Published:

Row 3, you return 1 for Africa re Eritrea. Would not there also be 2 under Middle East for the two Syrias referenced?

On Row 4, you return 1 for Western Europe re Sweden. There are 2.

Or - are we only looking between the first and second vertical bars?

(or I am simply confused...)

On the off chance thats any where near correct, can we safely look only after the last comma in the column?

Mark

[TMorville]

Sorry for the lack of alloboration for my manual metod.

You are right. Sweden does appear twice, and the same story with syria. If possible, is there any way that this may be ignored, so that only counting sweden once per cell? Hence returning a 1 (or 2,3,4... depending on the part of the world)?

In other words, to ignore how many times a country appears, and just return their "part of the world number"


Thanks for the help so far

[Paul_Hossler]

I did a little User Defined Function (UDF) that takes a string of countries and returns the unique number of countries in a region.


Function CountryCount(RegionOfWorld As Range, Country As String) As Long
Dim rCell As Range, rData As Range
Dim n As Long

Set rData = Range(RegionOfWorld.Cells(2, 1), RegionOfWorld.Cells(2, 1).End(xlDown))

n = 0
For Each rCell In rData.Cells
If InStr(Country, rCell.Value) > 0 Then n = n + 1
Next
CountryCount = n
End Function


I did re-arrange the Region sheet, and added Denmark and some other edits (marked in red and yellow)

Paul

[TMorville]

I did a little User Defined Function (UDF) that takes a string of countries and returns the unique number of countries in a region.


Function CountryCount(RegionOfWorld As Range, Country As String) As Long
Dim rCell As Range, rData As Range
Dim n As Long

Set rData = Range(RegionOfWorld.Cells(2, 1), RegionOfWorld.Cells(2, 1).End(xlDown))

n = 0
For Each rCell In rData.Cells
If InStr(Country, rCell.Value) > 0 Then n = n + 1
Next
CountryCount = n
End Function


I did re-arrange the Region sheet, and added Denmark and some other edits (marked in red and yellow)

Paul

Thanks alot!

Its on purpose that i've removed denmark, as it will be in every publication.

[TMorville]

Hi again.

Any reason why this should not work in excel 2007?

I've allowed all VBA code.

When i copy the formula over some new publications, it just copies the value from the cell i've highlighted..

Any help would be appriciated.

[TMorville]

And the plot thickens...

If i manually write in the function, and open the little formulabox - then designate the formula to pick the correct cells, it can understand and tell me if ill get an 0 or 1 - and the formula works in that single cell!

But if i try, in any way at all, to copy it - it just returns me the value of the cell im copying. Even tough i can see the formula, in the formula bar, and its set to all the right cells, i still just get that damn value.

[Paul_Hossler]

I did the UDF in 2007

You did save it as a "Macro Enabled workbook", = .XLSM?

Paul