Finding info of one column and removing it from another

[Johnny B]

My question may be difficult to understand. Here is my situation: I have two
columns (ex: column 1 and column 2). Column 1 has information repeated in
column 2. I would like to
identify info from column 1 and remove it from column 2.
For example

Column 1 | Column 2
|
American Online | American Online, 1234 USA street |
AMD Anthlon | AMD Anthlon, Customer Support |

I would like it to identify American Online, and AMD anthlon from Column 1
and remove it from Column 2... Giving me:

Column 1 | Column 2 |
American Online | 1234 USA street |
AMD Anthlon | Customer Support |

I would really appreciate if anyone could help me out with this problem.
Thank you very much for your time.

~Johnny B

 

[BoniM]

copy column 2 to column a on a new worksheet, choose Text to Columns from the
Data menu/gallery depending on version of excel - choose delimited, by
commas, general and your data should then look exactly how you want it.
have fun!

 

[BoniM]

If you have, as you probably do, additional columns of data, copy your new
columns 1 and 2 back over the old ones...
You could do Text to Columns in the original sheet by moving column 2 to
replace column 1, and making sure you have the necessary number of blank
columns to the right, but you can loose data in adjoining cells, if you end
up having commas in unexpected places!

 

[Johnny B]

Thanks for your help, BoniM, but if i am understanding you correctly, i think
the problem is that if i use Text to Column and deliminate it based on commas
or spaces... i get multiple different columns:

(ex: Column 1 | Column 2| Column 3 | Column 4| Column 5)
(___American |__Online___| 1234__|___fake__|__street)

I need to somehow detect the repeated company name and only remove correct
matches it from the address column. Rather than jus separating out the
repated company name

 

[David Biddulph]

If your data are in columns A & B, put this in column C
=SUBSTITUTE(B3,A3&", ","")
If you want to, you can then copy the result from C and paste special values
if you want to get rid of the original column B.

 

[Johnny B]

Hi David Biddulph,

Thank you for your time and reply . but im not very familiar with Excel
and i pasted "=SUBSTITUTE(B3,A3&", ","")" into a cell on column C and it
didnt change anything globally.... rather it just repeated what was in Column
B on the 3rd row?... am i inserting it wrong?

Thanks for your time!

~Johnny B

 

[David Biddulph]

You need to copy it down the rest of the rows. Either:
1 select the cell, copy, select the cells down the rest of the column (as
far down as you have data in A and B), and paste, or
2 select the square grab handle in the bottom right of the cell, & drag
to copy for the rest of the column as above, or
3 double-click on the square grab handle, and that will automatically
copy down as far as you have data in B.

 

[BoniM]

don't delimit based on commas or spaces... ONLY on commas. If there is a
check in spaces, remove it.

This:
American Online, 1234 USA street
AMD Anthlon, Customer Support

will become this:
American Online 1234 USA street
AMD Anthlon Customer Support

 

[Johnny B]

Hey David,

Wow you're genius... this forumla works beautifully except some cells do not
work.. meaning the column will still repeat the company name... for example

Column A | Column B | Column C (=SUBSTITUTE(B3,A3&", ","") |
American Online | American Online 1234 Fake St. | American Online 1234 Fake
St.

Other cells seem to work... for example

Column A | Column B | Column C (=SUBSTITUTE(B3,A3&", ","") |
American Online | American Online 1234 Fake St. | 1234 Fake St. |

I dont understand any reason for the discrepancy.

Thank you so much for your help!

~Johnny B

 

[David Biddulph]

The reason should be obvious if you look at the formula, Jonny. It is
looking for the content of column A, followed by a comma and a space, as
that was the format that you originally specified.

You can easily alter the formula to do the substitution working only on the
column A content, if there isn't always a comma and space, and you could
deal with the comma and space with a separate part of the formula if they
are there.