Replace one column data with data from another column

[ioacym1]

Hi,

I'm looking for a desperate answer regarding my problem, so if you have any suggestion please help:

I have 3 columns who look like this:

A B C
[TABLE="width: 655"]
<tbody>[TR]
[TD]PT 199 Bragareasa[/TD]
[TD]PT 199 Bragareasa[/TD]
[TD]PT 199 Bragareasa[/TD]
[/TR]
[TR]
[TD]6 IND[/TD]
[TD]PT 130 Grindu[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11IND[/TD]
[TD]PT Parc 5 Grindu[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/483 IND[/TD]
[TD]PT 5 Grindu[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]378 IND[/TD]
[TD]PT378 Independenta[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9 IND[/TD]
[TD]PT9 Independeta[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In column A i have one list with some location's name; in B list i have another list with some athore location name. My task is to highlight the difference betwen column A and B and put the result in C. I managed to do this up to a point where the column name differ; so if you can se above i have in column A 9 IND and in column B PT9 Independenta they refer to the same location but the format is different.
So my question is:
Is there a method that can automatically replace the name in column A from B, to have a perfect match? something similar to "Find and replace" function but to do it automatically, and have to overwright myself??


Please help! It's something urgent for my work; so i would very much appreciate any god ideea.

Thank you!

 

[ace19852]

If you insert a new column in A so the original column A, B and C now become B,C and D and put the following formula in A1 and copy it down then you can compare columns A and C to do your comparision.

=IFERROR(IF(FIND(LOWER(B1),LOWER(C1),1)<>"",C1,""),B1)

Cheers

 

[azumi]

Yes it does psossible with table lookup

 

[ioacym1]

Thanks, but it's not quite what i want, your formula just copy the column B into column A; so my D column with the final result remains the same. What i want is to search of matching proprieties in column B and if something is very close, modify the column A value after column B value. Like my previous exemple:
A B C
9 IND PT 9 INDEPENDENTA FALSE (NOW IS FALSE BECAUSE THE VALUE FROM A DOSEN'T MATCH WITH THE COLUMN B VALUE)

so i wonder if i can add another column; let's say column A1 that looks up and compare A with B and if it finds a close match then to write it down.


a1 A B C
A<>B=PT 9 INDEPENDENTA 9 IND PT 9 INDEPENDENTA FALSE (NOW IS FALSE BECAUSE THE VALUE FROM A DOSEN'T MATCH WITH THE COLUMN B VALUE)

 

[ace19852]

Thanks, but it's not quite what i want, your formula just copy the column B into column A; so my D column with the final result remains the same. What i want is to search of matching proprieties in column B and if something is very close, modify the column A value after column B value. Like my previous exemple:
A B C
9 IND PT 9 INDEPENDENTA FALSE (NOW IS FALSE BECAUSE THE VALUE FROM A DOSEN'T MATCH WITH THE COLUMN B VALUE)

so i wonder if i can add another column; let's say column A1 that looks up and compare A with B and if it finds a close match then to write it down.


a1 A B C
A<>B=PT 9 INDEPENDENTA 9 IND PT 9 INDEPENDENTA FALSE (NOW IS FALSE BECAUSE THE VALUE FROM A DOSEN'T MATCH WITH THE COLUMN B VALUE)

Well I'm surprised to hear you say that the formula only copies the B column to the A column. In my file it does what I believed you requested. Ok lets take lines 3 & 4 as an example and see if we agree on the same result.

[TABLE="width: 884"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD]PT 199 Bragareasa[/TD]
[TD]PT 199 Bragareasa[/TD]
[TD]PT 199 Bragareasa[/TD]
[TD]PT 199 Bragareasa[/TD]
[/TR]
[TR]
[TD]6 IND[/TD]
[TD]6 IND[/TD]
[TD]PT 130 Grindu[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11IND[/TD]
[TD]11IND[/TD]
[TD]PT Parc 5 Grindu[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/483 IND[/TD]
[TD]7/483 IND[/TD]
[TD]PT 5 Grindu[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PT378 Independenta[/TD]
[TD]378 IND[/TD]
[TD]PT378 Independenta[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PT9 Independeta[/TD]
[TD]9 IND[/TD]
[TD]PT9 Independeta
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Above is the result I get do you get this result
Lines 3 show Columns A & B to be the same because B is not contained in C at all
However looking at
Line 4 shows A is the same as C because B is contained in C

I hope that demonstrates clearly what I was doing

Please confirm and if you have any problem I will sent a link to the file so you can see.

Cheers

 

[Teeroy]

I wasn't sure whether you only wanted to test each value against its neighbor or anywhere in the column so I've listed two ways.

Check Neighbor
D1:=IF(ISERROR(SEARCH(A1,B1)>0),FALSE,TRUE)
and copy down

Check all Column B
For each value in Column A it will identify a similar address or an #NA error.


Try the following array formula.

Sheet1

	A	B	C	D
PT 199 Bragareasa	PT 199 Bragareasa	PT 199 Bragareasa	PT 199 Bragareasa
6 IND	PT 130 Grindu		#N/A
11IND	PT Parc 5 Grindu		#N/A
7/483 IND	PT 5 Grindu		#N/A
378 IND	PT378 Independenta		PT378 Independenta
9 IND	PT9 Independeta		PT9 Independeta

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D1 {=INDEX(B$1:B$6,MATCH(MIN(MIN(IF(ISERROR(SEARCH(A1,B$1:B$6)),9.999E+307,SEARCH(A1,B$1:B$6))),256),IF(ISERROR(SEARCH(A1,B$1:B$6)),9.999E+307,SEARCH(A1,B$1:B$6)),0))} <tbody> </tbody>

<tbody>
</tbody>

Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4