Removing multiple duplicate events
in [Functions]
|
Prev: Unprotect problem
Next: Delete 3 end row
From: Father John on 12 Apr 2010 03:56 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" <demechanik(a)yahoo.com> wrote in message news:OX22ztc2KHA.5660(a)TK2MSFTNGP04.phx.gbl... >A slight correction to the end part of the expression. It should read: > ... )),),0)),"")),A1) >
From: Steve Dunn on 12 Apr 2010 04:57 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" <w(a)b.com> wrote in message news:WmAwn.18916$pv.9572(a)news-server.bigpond.net.au... > 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" <demechanik(a)yahoo.com> wrote in message > news:OX22ztc2KHA.5660(a)TK2MSFTNGP04.phx.gbl... >>A slight correction to the end part of the expression. It should read: >> ... )),),0)),"")),A1) >> >
From: Father John on 12 Apr 2010 05:11 Taadaa It works exactly right Many thanks Stephen "Steve Dunn" <stunn(a)sky.com> wrote in message news:ef3kP4h2KHA.3844(a)TK2MSFTNGP05.phx.gbl... > 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" <w(a)b.com> wrote in message > news:WmAwn.18916$pv.9572(a)news-server.bigpond.net.au... >> 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" <demechanik(a)yahoo.com> wrote in message >> news:OX22ztc2KHA.5660(a)TK2MSFTNGP04.phx.gbl... >>>A slight correction to the end part of the expression. It should read: >>> ... )),),0)),"")),A1) >>> >> >
From: Max on 12 Apr 2010 18:50 welcome, good to hear -- Max Singapore "Father John" <w(a)b.com> wrote in message news:_sBwn.18929$pv.10152(a)news-server.bigpond.net.au... > Taadaa > It works exactly right > Many thanks > Stephen
From: Ashish Mathur on 12 Apr 2010 20:40
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" <w(a)b.com> wrote in message news:YlAwn.18913$pv.11113(a)news-server.bigpond.net.au... > 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 >> > |