Formula to Unify Text Entries in the Same Column

Dear Friends,

In the attached sheet, column B and E contain hand-entered data as part of voluntary basic information of an extensive questionnaire (sample of 950 questionnaire).

When using pivot table to group entries to summarize results, the pivot table treats (as expected) each misspelled entries or each unique word or words with extra spaces as categories.

I am looking for an innovative formula that I can enter in column C2 and then copy it down to unify entries and produce the “Ideal” outcome in column C.

My dream is that the formula to enter in C2 will look at the first entry in B2 (the word YEMENI in the sheet), then look for similar entries in the remaining of column B, find the similar word YEMEN in B8, then it enters the value of B2 (YEMENI) in both, C2 and C8. Then the formula will look in B3 ...

I am sure that I will still have to do some manual work and visually compare B and C , but I hope the formula will help. The actual data contains 13 columns and 950 rows
Many thanks in advance, Taisir

Originally Posted by FDibbins

See if this will help at all? It is based on taking the 1st x-number of characters on the word, and then looking for a match with that, in the list below. The "x" can be adjusted so you can play with it and see what tolerances suite you best.

=INDEX(B3:$B$32,MATCH(LEFT(B2,$D$1)&"*",B3:$B$32,0))
copied down
I put "x" in D1

Dear FDibbins
Many thanks for the prompt reply athough the lateness in your time zone

I experimented with the formula with 3 in D1
It misses 11 out of the 31. Most of the misses are obvious such as B8, B9, B23, B29. It seems also that the formula also misses duplicates in B such as B23 and B24.

Can I ask you please to see if we can adjust the formula? all the best

This seems to be a pretty big undertaking

Replacing Yemen with Yemeni and then replacing Syriana with Syria are opposites. If this is a one-off, I'd sort the list by nationality and manually copy the names that you want.
If it is an on-going problem, then maybe make up a vlookup on another sheet and everytime you get more data you can add any other unknowns to the list and it will learn more and more as you go.
Eventually, it will be able to correct almost any mistake or variation that people can make.

Just a thought

Thanks Beamernsw,
Did I understand that you will attach a file, per your second post?

Thanks

Sorry, I did, but I changed my mind on what I had so was in the middle of editing that post and replacing the file when you read it.

@Beamernsw

Row\Col	B	C
1	Nationality	Ideal Nationality
2	YEMENI	YEMENI
3	N/A	N/A
4	TUNIS	TUNIS
5	ALGERIA	ALGERIA
6	ALGERIAN	ALGERIA
7	TUNISIAN	TUNIS
8	JORDANIAN	JORDANIAN
9	YEMEN	YEMENI

In B2 enter and copy down:

=IF(B2="","",IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(LEFT($B$1:B1,LEN(B2)),B2),$C$1:C1),B2))

This probably yields an admissible set...

Very nice Aladin. That only had 2 incorrect in the entire list and they were very close.

Dear Aladin,
Many thanks for your innovative formula which I used immediately and it worked great on the questionnaire project.

I tried to use the same formula on another research I am doing on authors of scholarly published articles.

Attached is the full file (public domain) and how the names are structured and imported. Many authors use slightly different names styles based on the journal requirement and sometimes their names are misspelled. This is why your great formula produce different results for the same author highlighted in yellow

Can I ask you to look at the attached and advice whether the formula needs adjustment for this task or it is still valid as it is.

Initial run of your formula picked about 4600 duplicates.

Many thanks in advance Taisir

@Taisir

You did not supply the correct values the formula should produce. Judging from the variations the names appear to be associated with in your data, you might want to consider running a fuzzy matching routine like described in:

1) http://www.microsoft.com/en-gb/downl....aspx?id=15011

2) http://www.mrexcel.com/forum/excel-q...planation.html

What should the formula return given those you colored in yellow?

Row\Col	A	B
1	Serial	Authors
2	26495	AI-DEYAB, SS
3	1918	AlDEYAB, S
4	1	AlDEYAB, SS
5	721	ALDEYAB, SS
6	25795	AlDEYABAND, SS
7	25794	AlDEYABC, SS
8	25793	AlDEYABE, SS

Dear Aladin,
Many thanks again for your offer to help.

There is some tedious work on my behalf that I need to do to clean the data from obvious and unnecessary prefixes and common space-adding common in Arabic Names.

Once I arrive at the clean file, I will run your formula again and let you know, the result that the formula produces and the expected results.

I must tell you, however, that your formula is a treasure for me so far.

Take care and all the best.

Dear Aladin,

Following FDibbins suggestion and extensive manual cleaning, I arrived on cleanest list of names listed in Column C of the attached,

I applied your formula for the first 150 names in Column D.

In column E, I entered the expected/ideal outcome.

Your formula produced 29 out of the 150 NOT IDEAL outcomes (highlighted in yellow for easy identification).

Is there a room to adjust your formula in light of the attached?

Also, I tried to apply the Fuzzy match solution available at http://www.mrexcel.com/forum/excel-q...planation.html on the attached list of 29K plus names. It took more than 16 hours to complete the first 10,000 names and I aborted the process.

Thanks Taisir