Removing multiple duplicate events
in [Functions]
|
Prev: Unprotect problem
Next: Delete 3 end row
From: Father John on 11 Apr 2010 04:31 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
From: Max on 11 Apr 2010 06:38 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(SUBSTITUTE(A1,INDEX($B$1:$B$165,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($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" <w(a)b.com> wrote in message news:UNfwn.18709$pv.6048(a)news-server.bigpond.net.au... > 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
From: Max on 11 Apr 2010 19:04 A slight correction to the end part of the expression. It should read: .... )),),0)),"")),A1)
From: Ashish Mathur on 12 Apr 2010 00:25 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" <w(a)b.com> wrote in message news:UNfwn.18709$pv.6048(a)news-server.bigpond.net.au... > 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
From: Father John on 12 Apr 2010 03:55
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" <mathurashish(a)hotmail.com> wrote in message news:E2DBA464-E31C-4E18-A136-A3E080570DB6(a)microsoft.com... > 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" <w(a)b.com> wrote in message > news:UNfwn.18709$pv.6048(a)news-server.bigpond.net.au... >> 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 > |