Hi,
I will really appreciate if somebody give me a solution for the attached conditions. I am looking to get different results beased on different conditions but i cannot get it. I dont know whether i am nesting wrong formula or something else.. I will trulyyyy appreciate any tips. Thanks
Waheed
Last edited by warshad; 09-19-2012 at 10:15 AM.
You don't have any data in columns S or I in the data sheet, so please re-state conditions 2 and 4.
Pete
Hi Pete ,
sorry.hey I meant to say column E not S
Thanks
Waheed
Hi Waheed,
Here's what I've got so far - I'm posting so I can reconnect tomorrow - my questions are Pete's questions.
Maybe you can complete the routine:
Sub IniSub(): Dim Exp As Date, ws As Worksheet, wd As Worksheet, eC As Long, i As Long Dim Cue As String, GBL As String, PType As String, A As Range, eA As Long, j As Long Set ws = Worksheets("Sheet1"): Set wd = Worksheets("Data to lookup the dates") eA = wd.Range("E" & Rows.Count).End(xlUp).Row eC = ws.Range("C" & Rows.Count).End(xlUp).Row Set A = wd.Range("A2:A" & eA) For i = 6 To eC GBL = ws.Range("A" & i): Cue = ws.Range("C" & i) Select Case Cue Case "Remodel": j = A.Find(GBL).Row: PType = ws.Range("B" & i) If IsDate(wd.Range("E" & j)) Then Exp = wd.Range("E" & j) ElseIf PType = "Owned" Then Exp = wd.Range("B" & j): End If Case "Top Lease" Or "Ground Lease": j = A.Find(GBL).Row If IsDate(wd.Range("C" & j)) Then Case Else ws.Range("E" & i) = #3/11/1939# End Select ws.Range("E" & i) = Exp Next i End Sub
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Hi there,
i actually dont need the VBA script.... all i need a proper and right formula to get the data. Thanks
waheed
Hi Guys,
I have revised the conditions in the file with the correct columns. Thanks
Waheed
Hi Waheed,
Here's VBA that seems to work - sorry that you don't want it - Pete can probably deliver a formula for you:
Sub IniSub(): Dim Exp As Date, ws As Worksheet, wd As Worksheet, eC As Long, i As Long Dim Cue As String, GBL As String, PType As String, A As Range, eA As Long, j As Long Set ws = Worksheets("Sheet1"): Set wd = Worksheets("Data to lookup the dates") eA = wd.Range("E" & Rows.Count).End(xlUp).Row eC = ws.Range("C" & Rows.Count).End(xlUp).Row Set A = wd.Range("A2:A" & eA) For i = 2 To eC GBL = ws.Range("A" & i): Cue = ws.Range("C" & i) PType = ws.Range("B" & i): j = A.Find(GBL).Row Select Case Cue Case "Remodel" If IsDate(wd.Range("E" & j)) Then Exp = wd.Range("E" & j) ElseIf PType = "Owned" Then Exp = wd.Range("B" & j): End If Case "I" If PType = "Top Lease" Or PType = "Ground Lease" Then If IsDate(wd.Range("C" & j)) Then Exp = wd.Range("C" & j) Else Exp = wd.Range("D" & j): End If: End If Case Else ws.Range("E" & i) = "Fix": GoTo GetNext End Select ws.Range("E" & i) = Exp GetNext: Next i End Sub
Such faith !!Originally Posted by xladept
This is close, but is not quite matching all your suggested values. I put this formula in F2:
=IFERROR(VLOOKUP(A2,'Data to lookup the dates'!A:E,IF(C2="Remodel",IF(AND(B2="Owned",VLOOKUP(A2,'Data to lookup the dates'!A:E,5,0)=0),2,5),IF(OR(B2="Top Lease",B2="Ground Lease"),IF(VLOOKUP(A2,'Data to lookup the dates'!A:E,3,0)=0,4,3))),0),"Fix")
and copied down. It's not quite working in all cases, but I'm posting it now in case I don't get chance to come back to it, as I have to catch a plane at 7:00 am in the morning, so I must go and do some packing and then an early night.
Hope this helps.
Pete
Thanks Pete. The formula is not working on some of the conditions and i tried to tweak it in so many ways but it does not seem to work. I will keep trying still. Thanks
Waheed
Here's an improvement. You didn't quite state your conditions succinctly, as the "Owned" condition seemed to apply only to "Remodel" being in C2 according to your text. There were 4 entries for Owner without Remodel which caused some problems with the other formula, so this formula (again in F2) corrects for that and now there is only one condition not being met (the last one):
=IFERROR(VLOOKUP(A17,'Data to lookup the dates'!A:E,IF(C17="Remodel",IF(AND(B17="Owned",VLOOKUP(A17,'Data to lookup the dates'!A:E,5,0)=0),2,5),IF(B17="Owned",2,IF(OR(B17="Top Lease",B17="Ground Lease"),IF(VLOOKUP(A17,'Data to lookup the dates'!A:E,3,0)=0,4,3)))),0),"Fix")
The failing condition is for a Remodel and a Top Lease, but you haven't defined a condition for Top Lease with Remodel - I suppose you want this to be treated similar to Owned and Remodel with an empty date, in which case the formula will become:
=IFERROR(VLOOKUP(A2,'Data to lookup the dates'!A:E,IF(C2="Remodel",IF(AND(B2="Owned",VLOOKUP(A2,'Data to lookup the dates'!A:E,5,0)=0),2,IF(AND(RIGHT(B2,5)="Lease",VLOOKUP(A2,'Data to lookup the dates'!A:E,5,0)=0),3,5)),IF(B2="Owned",2,IF(OR(B2="Top Lease",B2="Ground Lease"),IF(VLOOKUP(A2,'Data to lookup the dates'!A:E,3,0)=0,4,3)))),0),"Fix")
and this does indeed give you the same results as you expected.
I couldn't leave this hanging before going away, so I shall get some shut-eye now.
Hope this helps.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks