Merge full text with correct abbreviation

[Mikila11]

Hello Excel'ers

I have a problem, and I would be grateful for any advice.

Problem

I have 2 lists of more than 8 000 items-rows (names of journals), but:

1. First list => full name of journals (1 column)
2. Second list => abbreviation of journals + ISSN (2 columns)

Lists are not equal. (not all items are same)

Request

I would be happy, if its possible to merge (Im doing it manually right now) this two lists into one -
full name (list 1) must be correctly (approximately) assigned to abbreviation + ISSN (list 2 - entries in two columns)

- > If item in list of FULL names havent any abbreviation with ISSN - would be nice to left next cell empty
- > If item in list of abbreviation doesnt (approximately) match any FULL name = skip it

samples of manually merged Full name, abbreviation and ISSN

AAPG BULLETIN (AAPG BULL) 0149-1423
AATCC REVIEW (AATCC REV) 1532-8813
ABSTRACT AND APPLIED ANALYSIS (ABSTR APPL ****) 1085-3375
ACADEMIC EMERGENCY MEDICINE (ACAD EMERG MED) 1069-6563
ACADEMY OF MANAGEMENT JOURNAL (ACAD MANAGE J) 0001-4273

Thanks for any advice

[6StringJazzer]

I have 2 lists of more than 8 000 items-rows (names of journals), but:

1. First list => full name of journals (1 column)
2. Second list => abbreviation of journals + ISSN (2 columns)

Lists are not equal. (not all items are same)

If I understand correctly you have a list of journal names, and a list of abbreviations. There is not a one-to-one correspondence between these two lists. There is no standard for creating the abbreviations.

There is no way for Excel to match full names with abbreviations if there is no algorithm for creating the abbreviations. I'll bet there are even a few cases where it might be tough for a human.

If abbreviations are always truncated words from the full name, then you might have a chance at this. It's true in the very few examples you provided, but is it always true?

[Mikila11]

If I understand correctly you have a list of journal names, and a list of abbreviations. There is not a one-to-one correspondence between these two lists. There is no standard for creating the abbreviations.

There is no way for Excel to match full names with abbreviations if there is no algorithm for creating the abbreviations. I'll bet there are even a few cases where it might be tough for a human.

If abbreviations are always truncated words from the full name, then you might have a chance at this. It's true in the very few examples you provided, but is it always true?

Yes, you understand correct.

2 lists - different number of items - not 100% same content of both lists (thats one task to find differences) - abbreviations are truncated words from full names (if full name of journal exists, or if abbreviations), thats correct, but are trancated different.
For example:
Journal is always J
or
Medical is Med, etc.

If short names - there is full name in both lists.

[6StringJazzer]

I am writing a little VBA function to do this but note that

ABSTRACT AND APPLIED ANALYSIS (ABSTR APPL ****) 1085-3375

from your example seems to defy any rules for abbreviating. You'll have to explain that one to me.

[shg]

A-N-A-L is one of those word the forum censors.

[6StringJazzer]

A-N-A-L is one of those word the forum censors.

Seriously? I had no idea the forum did censoring. Well, that is difficult for those of us who are **** retentive

At any rate, here is one solution using a user-defined function. It will probably not work in your spreadsheet because your data will be organized differently. I suggest you attach it.

I also had to account for the fact that "AND" and "OF" are dropped in the abbreviations. Are there any other words that are dropped? Any other eccentricities I should know about? If you attach your data then I'm sure that would be answered.

[6StringJazzer]

Sorry, just noticed you use 2003. Here is the 2003 version.

[Mikila11]

Thanks so much for help, I appreciate it.

6StringJazzer:

Yes you are right again. "AND, OF, IN, AT, OR, ON..." all are dropped. "THE" too.

Special items:

TECHNICAL PHYSICS LETTERS -> TECH PHYS LETT+ 1063-7850
(that + is using not in all cases - but always: Letters = LETT)
or
BULLETIN = B
INSTITUTE = I
QUARTERLY = Q
etc.
for example:
SCIENCE = SCI (or SCI+)
REVIEW = REV (sometimes REV+)
RESEARCH = RES (or RES+)
etc.

TEXAS HEART INSTITUTE JOURNAL -> TEX HEART I J 0730-2347
but, for example:
English and Engineering = same abbreviation = ENG


Quite different procedures to create abbreviations

[Mikila11]

That script looks very good.. but Im so sorry, my bad. (my bad explanation)
I need a little bit different Output. (see attachment - there is Example of Excel sheet)

PS: Im sorry.. I guess, the problem here is word "Merge" better one is "Assign". Thats my english problem only. Im sorry again

[6StringJazzer]

Here is a revision, with your additional data and rules.

I will once again suggest that you attach your complete data.

Edit: The method I am using is for each journal name, the code reviews all of the abbreviations and finds a match. It does not try to take a journal name and synthesize an abbreviation for it. So I don't have to know exactly how abbrevations are built. I just need to be able to look at an abbreviation, and see if it can correspond to a journal name.

However, you will have problems if you have an abbreviation like:

J BIO

and journal names like:

JOURNAL OF BIOPHYSICS
JOURNAL OF BIOLOGY

[Mikila11]

Hello, and thanks so much.

I would like to attach complete data (it would be better way how to explain my problem), but I cannot. (because of rights, licences of huge journal databases.. etc etc..). Im sorry for that complications.

Same abbreviation of two or more full names is possible, but its no big problem to me (I saw only few of them), coz after all I must check it and if there is two same ISSN, Ill find the correct one. Next i must find ISSN for missing journal abbreviations and finish it at all.

Thanks again.. Im going to try..

All the best

I will once again suggest that you attach your complete data.

However, you will have problems if you have an abbreviation like:

J BIO

and journal names like:

JOURNAL OF BIOPHYSICS
JOURNAL OF BIOLOGY

[Mikila11]

Hello again.

I tried it, and its worked quite good.
But sometimes it miss some items, like in example:

R D MANAGEMENT #NOTFOUND! R&D MAG 0746-9179
RD MAGAZINE #NOTFOUND! R&D MANAGE 0033-6807
RACE CLASS #NOTFOUND! RACE CLASS 0306-3968

Is here any way how to fix it?

Thanks so much.. this helped me much.

[6StringJazzer]

In your examples, the abbreviation adds a character that doesn't exist in the full name, which doesn't really make sense. And sometimes the "&" replaces a space, and sometimes not. But I think if I get a little more creative with the pattern matching I can resolve this.

I have a busy day today but will see if I can squeeze it in later.

[Mikila11]

6StringJazzer:
Its ok. You helped me much indeed. Thanks so much.
Today I managed to assign all abbreviation with ISSN to full names (your macro helped) and now its time to go for a second phase: controlling and completing.
All the best

[Mikila11]

FINAL MESSAGE:

This macro is nice and very helpful. Thank you again.
Few problems:
1.If the name of journal has one word, script assing bad abbreviation

SUB-STANCE SUB-STANCE (S AFR J ANIM SCI 0375-1589

2. If name of journals are similar:

SURFACE SCIENCE ---- SURFACE SCIENCE (SURF SCI
SURFACE SCIENCE REPORTS ---- SURFACE SCIENCE(SURF SCI

I correct it manually - no problem - I wrote it only for that people, who could use it too.