Re: $$Excel-Macros$$ To Extract a text with Conditions

2012-01-02 Thread Excel_Lover
Thanks Kris, it works fine On Mon, Jan 2, 2012 at 7:09 AM, Kris wrote: > In that case my UDF would be > > *Dim dic As Object > Function FINALDEST(ByRef v) As String > > Dim x, i As Long > > If TypeOf v Is Range Then v = v.Value2 > > x = Split(v, "-") > > If dic Is Nothing The

Re: $$Excel-Macros$$ To Extract a text with Conditions

2012-01-02 Thread Excel_Lover
Hi Haseeb, Great, I could not expect a solution by formula for this. awesome! On Mon, Jan 2, 2012 at 5:45 AM, Haseeb Avarakkan wrote: > Hello Faisal & Evey one, > > This is what my understanding, > > *If any code repeats after a dest, just before this code consider as > final dest.* > >

Re: $$Excel-Macros$$ To Extract a text with Conditions

2012-01-01 Thread Kris
In that case my UDF would be *Dim dic As Object Function FINALDEST(ByRef v) As String Dim x, i As Long If TypeOf v Is Range Then v = v.Value2 x = Split(v, "-") If dic Is Nothing Then Set dic = CreateObject("scripting.dictionary") With dic

Re: $$Excel-Macros$$ To Extract a text with Conditions

2012-01-01 Thread Haseeb Avarakkan
Hello Faisal & Evey one, This is what my understanding, *If any code repeats after a dest, just before this code consider as final dest.* Eg: Row 101. DMS-BAH-AUH-CGK-AUH-DMM AUH repeats after CGK, so final dest, CGK. Row 109, DMS-BAH-DAM-BAH-DMM BAH repeat after DAM, so final dest, DAM

RE: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-31 Thread Rajan_Verma
s.com [mailto:excel-macros@googlegroups.com] On Behalf Of Kris Sent: Dec/Sat/2011 02:56 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ To Extract a text with Conditions Hi Rajan, The route are different. DMS-BAH and BAH-DMM Kris -- FORUM RULES (934+ members alr

RE: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-31 Thread Rajan_Verma
Yes.. this is the logic.. and it is making it bit complicated.. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Kris Sent: Dec/Sat/2011 02:56 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ To Extract a text with Conditions Hi Rajan

Re: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-31 Thread Kris
Hi Rajan, The route are different. DMS-BAH and BAH-DMM Kris -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention

RE: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-31 Thread Rajan_Verma
) then result should be HYD From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Kris Sent: Dec/Sat/2011 02:44 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ To Extract a text with Conditions What's the logic of last destination D

Re: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-31 Thread Kris
What's the logic of last destination DAM in DMS-BAH-DAM-BAH-DMM ? -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick atten

Re: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-30 Thread Excel_Lover
l_Lover > *Sent:* Dec/Sat/2011 01:00 > > *To:* excel-macros@googlegroups.com > *Subject:* Re: $$Excel-Macros$$ To Extract a text with Conditions > > ** ** > > Hi All, > > > I am sorry, I didn't access my mail yesterday, > > Thanks to all of you for your

RE: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-30 Thread Rajan_Verma
: Dec/Sat/2011 01:00 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ To Extract a text with Conditions Hi All, I am sorry, I didn't access my mail yesterday, Thanks to all of you for your replies, @Rajan, I have checked your comparison, don,yours, Kris & also Haseeb&#x

Re: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-30 Thread Excel_Lover
Hi All, I am sorry, I didn't access my mail yesterday, Thanks to all of you for your replies, @Rajan, I have checked your comparison, don,yours, Kris & also Haseeb's, Hasseb's & Kris's giving same result, but as per your solution in some cells it results blank cells, I dont know whether I am do

Re: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-30 Thread NOORAIN ANSARI
Excellent Haseeb On Fri, Dec 30, 2011 at 9:22 AM, Haseeb Avarakkan < haseeb.avarak...@gmail.com> wrote: > Try this *Array Formula*, > > > =INDEX(MID(A6,ROW(A$1:A$50)*4-3,3),MAX(IFERROR(MATCH(MID(A6,ROW(A$1:A$50)*4-3,3),IF(MID(A6,ROW(A$1:A$50)*4-3,3)="","A",MID(A6,ROW(A$1:A$50)*4-3,3)),0),""))) >

Re: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-30 Thread dguillett1
Yes,very nice Don Guillett SalesAid Software dguille...@gmail.com From: Haseeb Avarakkan Sent: Thursday, December 29, 2011 9:52 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ To Extract a text with Conditions Try this Array Formula, =INDEX(MID(A6,ROW(A$1:A$50)*4-3,3),MAX

Re: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-30 Thread Kris
@ Haseeb, Nice :) -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a

RE: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-30 Thread Rajan_Verma
Great From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Haseeb Avarakkan Sent: Dec/Fri/2011 09:23 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ To Extract a text with Conditions Try this Array Formula, =INDEX(MID(A6,ROW(A$1:A$50)*4

Re: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-29 Thread Haseeb Avarakkan
Try this *Array Formula*, =INDEX(MID(A6,ROW(A$1:A$50)*4-3,3),MAX(IFERROR(MATCH(MID(A6,ROW(A$1:A$50)*4-3,3),IF(MID(A6,ROW(A$1:A$50)*4-3,3)="","A",MID(A6,ROW(A$1:A$50)*4-3,3)),0),""))) Copy down.. ___ HTH, Haseeb -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise,

Re: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-29 Thread dguillett1
I think this is the proper solution. Perhaps you should test yourself. Don Guillett SalesAid Software dguille...@gmail.com From: Rajan_Verma Sent: Thursday, December 29, 2011 3:47 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ To Extract a text with Conditions In attached

Re: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-29 Thread dguillett1
My solution did not give the proper result. Formulas would be a “toughie” Don Guillett SalesAid Software dguille...@gmail.com From: Aamir Shahzad Sent: Thursday, December 29, 2011 1:59 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ To Extract a text with Conditions

Re: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-29 Thread Aamir Shahzad
Wonderful solution Guillett, is it possible with formulas? Regards, Aamir Shahzad On Thu, Dec 29, 2011 at 7:33 PM, dguillett1 wrote: > Even simpler > > Option Explicit > Sub GetFinalDestinationSAS() > Dim c As Range > Dim start As String > Dim final As String > > Application.ScreenUpdating =

Re: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-29 Thread dguillett1
Even simpler Option Explicit Sub GetFinalDestinationSAS() Dim c As Range Dim start As String Dim final As String Application.ScreenUpdating = False For Each c In Range("a6:a" & Cells(Rows.Count, 1).End(xlUp).Row) start = Left(c, 3) If Not InStr(4, c, start) Then final = Right(c, 3) If InStr(8, c

Re: $$Excel-Macros$$ To Extract a text with Conditions

2011-12-29 Thread dguillett1
One way. Save your file as .xls or .xlsM to allow macros. Option Explicit Sub GetFinalDestinationSAS() Dim c As Range Dim start As String Dim final As String Application.ScreenUpdating = False For Each c In Range("a6:a" & Cells(Rows.Count, 1).End(xlUp).Row) start = Left(c, 3) If Not InStr(4, c,