Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default Removing multiple duplicate events

Hi

I have a spreadsheet that has a complete address in one column (a)
In column (b) (B1:B165) I have the name of the suburbs I wish to remove from
col (a)

Start
Col (A)
15 Smith Street Blacktown

Col (B7) contains the suburb Blacktown (I have 165 suburbs in this column)

I want to remove that same data from (A) so I just end up with 15 Smith
Street.

Any ideas?

Thanks in advance - Stephen

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Removing multiple duplicate events

Lightly tested, this should work ok ..
The reference suburbs in B1:B165 are assumed fully populated
With your source addresses in A1 down,
Put this in C1, normal ENTER to confirm will do:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($B$1:$B$165,A1)))0,TRIM(SUBSTITUT E(A1,INDEX($B$1:$B$165,MATCH(TRUE,INDEX(ISNUMBER(S EARCH($B$1:$B$165,A1)),),0)),"")),"")
Copy C1 down to the last row of source data in col A. Col C should return
the desired cleansed results. Success? wave it here ..
--
Max
Singapore

"Father John" wrote in message
...

I have a spreadsheet that has a complete address in one column (a)
In column (b) (B1:B165) I have the name of the suburbs I wish to remove
from col (a)

Start
Col (A)
15 Smith Street Blacktown

Col (B7) contains the suburb Blacktown (I have 165 suburbs in this column)

I want to remove that same data from (A) so I just end up with 15 Smith
Street.

Any ideas?

Thanks in advance - Stephen



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Removing multiple duplicate events

A slight correction to the end part of the expression. It should read:
.... )),),0)),"")),A1)

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Removing multiple duplicate events

Hi,

Assume 15 Smith Street Blacktown is in cell A7. In cell C7, enter the
following formula

=TRIM(LEFT(A7,SEARCH(B7,A7)-1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Father John" wrote in message
...
Hi

I have a spreadsheet that has a complete address in one column (a)
In column (b) (B1:B165) I have the name of the suburbs I wish to remove
from col (a)

Start
Col (A)
15 Smith Street Blacktown

Col (B7) contains the suburb Blacktown (I have 165 suburbs in this column)

I want to remove that same data from (A) so I just end up with 15 Smith
Street.

Any ideas?

Thanks in advance - Stephen


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default Removing multiple duplicate events

Ashish
The only problem is the data in col. B is a list of the suburbs in a region
and your formula relies by chance as one of the suburbs being on the same
line in the sheet
It would need to say if any of the data in col b. = the same as col a.
delete it from col. A

Many thanks
Stephen


"Ashish Mathur" wrote in message
...
Hi,

Assume 15 Smith Street Blacktown is in cell A7. In cell C7, enter the
following formula

=TRIM(LEFT(A7,SEARCH(B7,A7)-1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Father John" wrote in message
...
Hi

I have a spreadsheet that has a complete address in one column (a)
In column (b) (B1:B165) I have the name of the suburbs I wish to remove
from col (a)

Start
Col (A)
15 Smith Street Blacktown

Col (B7) contains the suburb Blacktown (I have 165 suburbs in this
column)

I want to remove that same data from (A) so I just end up with 15 Smith
Street.

Any ideas?

Thanks in advance - Stephen





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default Removing multiple duplicate events

Hi Max
Thanks for the effort here!
Not sure now of the entire structure of the expression as the correction
doesn't show if I am replacing a section or adding to it would you be able
to retype the entire line?
With thanks
Stephen West
Gold Coast, Australia

"Max" wrote in message
...
A slight correction to the end part of the expression. It should read:
... )),),0)),"")),A1)


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default Removing multiple duplicate events

Stephen,

just replace the last "" with A1.

Max,

that's pretty neat! I particularly like the INDEX(array,) wrapping to
elimate the need for array entry. Definitely something I'll be putting to
good use. Cheers.

Steve D.



"Father John" wrote in message
...
Hi Max
Thanks for the effort here!
Not sure now of the entire structure of the expression as the correction
doesn't show if I am replacing a section or adding to it would you be able
to retype the entire line?
With thanks
Stephen West
Gold Coast, Australia

"Max" wrote in message
...
A slight correction to the end part of the expression. It should read:
... )),),0)),"")),A1)



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default Removing multiple duplicate events

Taadaa
It works exactly right
Many thanks
Stephen


"Steve Dunn" wrote in message
...
Stephen,

just replace the last "" with A1.

Max,

that's pretty neat! I particularly like the INDEX(array,) wrapping to
elimate the need for array entry. Definitely something I'll be putting to
good use. Cheers.

Steve D.



"Father John" wrote in message
...
Hi Max
Thanks for the effort here!
Not sure now of the entire structure of the expression as the correction
doesn't show if I am replacing a section or adding to it would you be
able
to retype the entire line?
With thanks
Stephen West
Gold Coast, Australia

"Max" wrote in message
...
A slight correction to the end part of the expression. It should read:
... )),),0)),"")),A1)




  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Removing multiple duplicate events

welcome, good to hear
--
Max
Singapore

"Father John" wrote in message
...
Taadaa
It works exactly right
Many thanks
Stephen



  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Removing multiple duplicate events

Hi,

You may try this array formula (Ctrl+Shift+Enter)

=TRIM(LEFT(A7,MAX(IF(ISNUMBER(SEARCH(B1:B165,A7)), SEARCH(B1:B165,A7)))-1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Father John" wrote in message
...
Ashish
The only problem is the data in col. B is a list of the suburbs in a
region and your formula relies by chance as one of the suburbs being on
the same line in the sheet
It would need to say if any of the data in col b. = the same as col a.
delete it from col. A

Many thanks
Stephen


"Ashish Mathur" wrote in message
...
Hi,

Assume 15 Smith Street Blacktown is in cell A7. In cell C7, enter the
following formula

=TRIM(LEFT(A7,SEARCH(B7,A7)-1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Father John" wrote in message
...
Hi

I have a spreadsheet that has a complete address in one column (a)
In column (b) (B1:B165) I have the name of the suburbs I wish to remove
from col (a)

Start
Col (A)
15 Smith Street Blacktown

Col (B7) contains the suburb Blacktown (I have 165 suburbs in this
column)

I want to remove that same data from (A) so I just end up with 15 Smith
Street.

Any ideas?

Thanks in advance - Stephen





  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Removing multiple duplicate events

On Mon, 12 Apr 2010 23:18:48 -0400, Ron Rosenfeld
wrote:

There is a potential problem with Max's solution if one suburb name is a part
of a subsequent suburb name, or if a suburb name happens to also be a street
name. The problem arises because Max's algorithm does not check that the
position of the match is at the end of the string.


Of course, even with this solution, one could still have suburb name
combinations that would return unwanted results, if the repeated word were at
the end of the suburb name, and occurred first in order.
--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
removing duplicate entries in multiple worksheets monkeytrader Excel Worksheet Functions 1 July 8th 08 06:24 AM
Removing duplicate rows Roger Bell New Users to Excel 2 January 4th 08 02:25 PM
Removing duplicate entries Roger Bell New Users to Excel 2 June 23rd 07 10:20 AM
How to remove duplicate events in column Scott New Users to Excel 7 March 30th 06 04:54 PM
removing duplicate rows exceluser2 Excel Discussion (Misc queries) 1 March 2nd 06 10:01 AM


All times are GMT +1. The time now is 08:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"