Post for later use
Post for later use
If I use a Transpose function at one place instead of my preferred Index way of transposing things, then I can reduce it to a single code line: This for example will get your pasted results for the unique “aa” Missings
Code:Sub SingleLineWithTranspose()
Let Range("T2").Resize(UBound(Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(Split(Mid(Replace("#" & Join(Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0)*($A$2:$A$1000=$I$1)),ROW(F2:F463),0)"), Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)")), "#"), "#0", ""), 2), "#")), 1), 1), 1).Value = Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(Split(Mid(Replace("#" & Join(Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0)*($A$2:$A$1000=$I$1)),ROW(F2:F463),0)"), Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)")), "#"), "#0", ""), 2), "#")), 1)
End Sub
Here are some of the full workings used to get that single code line:
Code:Sub Pretty3bbaaTranspose() '
Dim arrTemp() As Variant
Rem To get the results in column T ( same as Yassers or hans Results
' Ths first forumula gives me all the matches for F in the C ( helper column ) or error for no match
Let arrTemp() = Evaluate("=If({1},MATCH(F2:F463,C2:C463,0))") ' If({1},____) may not be needed for Excel 2016 and higher The first formula does the main work
' The multiplication by $A$2:$A$1000=$I$1 limits the range used by effectively making 0 check dates outside or range of interest
Let arrTemp() = Evaluate("=IF({1},MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0))") ' $A$2:$A$1000=$I$1 gives us an array full of Falses and Trues , which Excel will interpret mathematically as 0 or 1 This has the effect of giving us a 0 multiplyer on numbers outside our range of interst, so in total a 0 for outside our range of interest. Our range of interest gets a 1 multiplier so has therefore no change and we can find those numbers whereas we wont find a 0, well actually we will find a zero if the range to search for has a zero as it does further down, so we take care of that in the next line
' The above formula has one problem with the supplied data in that empty cells are seen in this formula as 0 which gives a match
Let arrTemp() = Evaluate("=IF(F2:F463=0,0,MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0))") ' In looking in the range to find a match in ( the range to be searched we have all 0s outside the range caused by the previous $A$2:$A$1000=$I$1 So the first of these 0s will be seen as the match cell for all cells in F that are empty. So i take care here of the situation where an empty cell in F is by giving a 0 output So far two things retrn me a zero. You often find in formula building that the coercing If({1},___) suddenly is not needed. Her we find that the newly used here IF(F2:F463=0,0,___) is doing the required co oecing
' we will now do a simple If(ISERROR( ) , Row( ) , 0 ) on the above . This will give us a row indicie for the missing data, and a 0 for the found data
Let arrTemp() = Evaluate("=IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0)*($A$2:$A$1000=$I$1)),ROW(F2:F463),0)")
' At this point we have wanted data or zeros. I want to conveniently use some VB string fuction whuch annoyingly onl work on 1 D arrays, so we convert it by a transpose in the next code line
Let arrTemp() = Application.Index(arrTemp(), Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)"))
' Or
Let arrTemp() = Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0)*($A$2:$A$1000=$I$1)),ROW(F2:F463),0)"), Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)"))
' The next few lines get rid of the 0s ( 2 lines commented out to prevent the shortened line messing up )
Dim StrTemp As String: Let StrTemp = "#" & Join(arrTemp(), "#") ' Convert the array to a string with a # in between each data. The extra # allows us to remove all 0 entries via removing all #0 Without this we might get one left at the start
' Let StrTemp = Replace(StrTemp, "#0", "", 1, -1, vbBinaryCompare) ' This effectiveely removes the 0s data ( and its seperator )
' Let StrTemp = Mid(StrTemp, 2) ' Because I omit the third optional ( length ) argument I get all the remaing string after the first one. This effectively takes off the extra # which I don't need
Dim arrStrTemp() As String: Let arrStrTemp() = Split(StrTemp, "#", -1, vbBinaryCompare) ' remake the array
' Or ,
Let arrStrTemp() = Split(Mid(Replace("#" & Join(Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0)*($A$2:$A$1000=$I$1)),ROW(F2:F463),0)"), Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)")), "#"), "#0", ""), 2), "#")
' We need a "vertical" array for output, so we transpose
Let arrTemp() = Application.Transpose(arrStrTemp())
Let arrTemp() = Application.Index(Worksheets("Sheet1").Columns(6), arrTemp(), 1) ' finally we want the dates ( so far we have the row indicies obtained from Match Note. this formula has the problem that we get the results a row out of step... Its actually very convenient because if i use Cells typically, here a column then I have a nice solution
' Or
Let arrTemp() = Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(arrStrTemp()), 1) ' finally we want the dates ( so far we have the row indicies obtained from Match Note. this formula has the problem that we get the results a row out of step... Its actually very convenient because if i use Cells typically, here a column then I have a nice solution
' Or
Let arrTemp() = Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(Split(Mid(Replace("#" & Join(Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0)*($A$2:$A$1000=$I$1)),ROW(F2:F463),0)"), Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)")), "#"), "#0", ""), 2), "#")), 1)
Let Range("T2").Resize(UBound(Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(Split(Mid(Replace("#" & Join(Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0)*($A$2:$A$1000=$I$1)),ROW(F2:F463),0)"), Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)")), "#"), "#0", ""), 2), "#")), 1), 1), 1).Value = Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(Split(Mid(Replace("#" & Join(Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0)*($A$2:$A$1000=$I$1)),ROW(F2:F463),0)"), Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)")), "#"), "#0", ""), 2), "#")), 1)
Let Range("T2").Resize(UBound(arrTemp(), 1), 1).NumberFormat = "yyyy/mm/dd" ' from macro recorder .NumberFormat = "[$-1010000]yyyy/mm/dd,@"
Stop
' Range("T2").Resize(UBound(arrTemp(), 1), 1).ClearContents
End Sub
Sub SingleLineWithTranspose()
Let Range("T2").Resize(UBound(Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(Split(Mid(Replace("#" & Join(Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0)*($A$2:$A$1000=$I$1)),ROW(F2:F463),0)"), Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)")), "#"), "#0", ""), 2), "#")), 1), 1), 1).Value = Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(Split(Mid(Replace("#" & Join(Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0)*($A$2:$A$1000=$I$1)),ROW(F2:F463),0)"), Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)")), "#"), "#0", ""), 2), "#")), 1)
End Sub
post for latzer use
View North from Balcony .. a castle I don’t know the name of on the Horizon, ( the hook is part of the Father in Laws new elevator to lift up shopping etc to the Third floor
02_BalconyNorthAnotherCastleAndHookFromFatherInLaw sMotorisedLift.jpg : https://imgur.com/ZG6Dmg2
View South from Balcony – the famous Coburg Veste
03_BalconySouthVeste.jpg : https://imgur.com/uNnCO8F
View East from Balcony - Bavarian fairy land
06_PrettyViewEast.jpg : https://imgur.com/1DzYrL2
Beer Mugs: I don’t drink much, certainly not at home, and never when building. But with the Father in Law it has become a bit of a tradition .. a German beer or two.
04_CoburgBalconyBeerMugs.jpg : https://imgur.com/RDXq3HH
Our old Blue bus hidden in a back lane: View of the Veste from guest room, and at the bottom our old blue VW bus – we have to hide it as it does not fit in too well
05_GuestRoomCoburgVesteAndBlueVWBus.jpg : https://imgur.com/30B3nkp
A very bad picture or the Veste at night from the parents in law’s living room … Bavarian “Fairy land” – what a view to have..
08_VesteAtNight.jpg : https://imgur.com/5HrY1Hy
Finally, that ugly man spoiling the view again..
07_UglyManInPicture : https://imgur.com/Eic7NSD
View North from Balcony .. a castle I don’t know the name of on the Horizon, ( the hook is part of the Father in Laws new elevator to lift up shopping etc to the Third floor
02_BalconyNorthAnotherCastleAndHookFromFatherInLaw sMotorisedLift.jpg : http://i.imgur.com/ZG6Dmg2.jpg
View South from Balcony – the famous Coburg Veste
03_BalconySouthVeste.jpg : http://i.imgur.com/uNnCO8F.jpg
View East from Balcony - Bavarian fairy land
06_PrettyViewEast.jpg : http://i.imgur.com/1DzYrL2.jpg
Beer Mugs: I don’t drink much, certainly not at home, and never when building. But with the Father in Law it has become a bit of a tradition .. a German beer or two.
04_CoburgBalconyBeerMugs.jpg : http://i.imgur.com/RDXq3HH.jpg
Our old Blue bus hidden in a back lane: View of the Veste from guest room, and at the bottom our old blue VW bus – we have to hide it as it does not fit in too well
05_GuestRoomCoburgVesteAndBlueVWBus.jpg : http://i.imgur.com/30B3nkp.jpg
A very bad picture or the Veste at night from the parents in law’s living room … Bavarian “Fairy land” – what a view to have..
08_VesteAtNight.jpg : http://i.imgur.com/5HrY1Hy.jpg
Finally, that ugly man spoiling the view again..
07_UglyManInPicture : http://i.imgur.com/Eic7NSD.jpg
In support of these post
https://eileenslounge.com/viewtopic....281384#p281384
https://eileenslounge.com/viewtopic....281383#p281383
Finally, If I use a simple Dictionary way to get your unique names from your column A, then I can incorporate my ideas into a full solution that gets the same results as Hans using your uploaded test data.
Rem 1 Gets your unique names from column A
Rem 2 Loops through those unique names and each time in the loop the Function is called to get an array of your missings.
Code:Sub EvaluateRangeFormulaWay() ' http://www.eileenslounge.com/viewtopic.php?p=281315#p281315
Rem 0 worksheets info
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = ThisWorkbook.Worksheets.Item("Sheet1"): Set Ws2 = ThisWorkbook.Worksheets.Item("Sheet2Alan")
Dim Em1 As Long: Let Em1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim arrA1() As Variant: Let arrA1() = Ws1.Range("A1:A" & Em1 & "").Value2 ' All names list
Rem 1
Dim Dik1 As Object: Set Dik1 = CreateObject("Scripting.Dictionary")
' 1b) make list of unique names
Dim Cnt
For Cnt = 2 To Em1 ' Looping down all names
Let Dik1(arrA1(Cnt, 1)) = "This can be anything you like, it don't really matter. What happens here is that we try to put this text in the Item of a dictionary entry that has the key of the value of arrA1(Cnt, 1) If that entry does not exist, then the dictionary is programmed not to error , but instead make ( Add ) an entry with that key value. For our purposes we don't care what the items are. But at the end of this loop we will have effectively Added a element in the dictionary, one for each of the unique name values. We can then use the Keys() array as a convenient way to get an array of unique names"
Next Cnt
Dim arrUnics() As Variant: Let arrUnics() = Dik1.Keys() ' This is an array of our unique Names
Rem 2 Do it
Dim R3Lne As Long: Let R3Lne = 2 ' This is the next free line in second worksheet
For Cnt = 0 To UBound(arrUnics()) ' looping through all uniques names
Dim arrMisins() As Variant: Let arrMisins() = Missings(arrUnics(Cnt)) '## Go to the function that makes an array of the Missing dates based on the Name value
Dim NoMisins As Long: Let NoMisins = UBound(arrMisins(), 1)
Let Ws2.Range("A" & R3Lne & ":A" & R3Lne + (NoMisins - 1) & "").Value = arrUnics(Cnt) ' Put the name in as many cells as we have missing dates
Let Ws2.Range("B" & R3Lne & ":B" & R3Lne + (NoMisins - 1) & "").Value = arrMisins() ' Put the missing dates in
Let R3Lne = R3Lne + NoMisins ' This is the next free line in second worksheet
Next Cnt
Let Ws2.Range("B2:B" & Ws2.UsedRange.Rows.Count + 1 & "").NumberFormat = "yyyy/mm/dd"
End Sub
Function Missings(ByVal Nme As String) As Variant
Dim arrStrTemp() As String: Let arrStrTemp() = Split(Mid(Replace("#" & Join(Application.Index(Worksheets("Sheet1").Evaluate("=IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=" & """" & Nme & """" & "),0)*($A$2:$A$1000=" & """" & Nme & """" & ")),ROW(F2:F463),0)"), Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)")), "#"), "#0", ""), 2), "#")
Dim arrTemp() As Variant: Let arrTemp() = Application.Index(Worksheets("Sheet1").Columns(6), Application.Index(arrStrTemp(), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")/row(1:" & UBound(arrStrTemp()) + 1 & ")"), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")")), 1)
Let Missings = arrTemp()
End Function
Sub TestFunctionMissings()
Dim arrTemp() As Variant
Let arrTemp() = Missings("bb")
' Columns("T:T").ClearContents ' Range("T2").Resize(UBound(arrTemp(), 1), 1).ClearContents
Let Range("T2").Resize(UBound(arrTemp(), 1), 1).Value = arrTemp()
Let Range("T2").Resize(UBound(arrTemp(), 1), 1).NumberFormat = "yyyy/mm/dd"
End Sub
I have done another couple of versions, just out of interest.
I have also adjusted the code to be the same last row, but in these two versions the last row is not hard coded. I am using the last row of data. So that is found dynamically in the usual way.
Because we use the same last row, I can simplify a few things.
The difference between the two new versions is that
_ one uses the conventional Transpose function to do a couple of transposing.
_ In the other one, the same transposing is done in that strange Index function way that I personally like to do.
Index Function Way
Code:' Using the Index way for the tranposing
Sub Pretty3d() '
Rem 0 worksheets info
Dim Ws1 As Worksheet
Set Ws1 = ThisWorkbook.Worksheets.Item("Sheet1")
Dim M As Long: Let M = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim arrTemp() As Variant
Rem To get the results in column T ( same as Yassers or Hans Results
' Ths first forumula gives me all the matches for F in the C ( helper column ) or error for no match
Let arrTemp() = Evaluate("=If({1},MATCH(F2:F" & M & ",Int(B2:B" & M & "),0))") ' If({1},____) may not be needed for Excel 2016 and higher The first formula does the main work
' The multiplication by $A$2:$A$1000=$I$1 limits the range used by effectively making 0 check dates outside or range of interest
Let arrTemp() = Evaluate("=IF({1},MATCH(F2:F" & M & ",Int(B2:B" & M & ")*($A$2:$A$1000=$I$1),0))") ' $A$2:$A$1000=$I$1 gives us an array full of Falses and Trues , which Excel will interpret mathematically as 0 or 1 This has the effect of giving us a 0 multiplyer on numbers outside our range of interst, so in total a 0 for outside our range of interest. Our range of interest gets a 1 multiplier so has therefore no change and we can find those numbers whereas we wont find a 0, well actually we will find a zero if the range to search for has a zero as it does further down, so we take care of that in the next line
' The above formula has one problem with the supplied data in that empty cells are seen in this formula as 0 which gives a match
Let arrTemp() = Evaluate("=IF(F2:F" & M & "=0,0,MATCH(F2:F" & M & ",C2:C" & M & "*(A2:A" & M & "=I1),0))") ' In looking in the range to find a match in ( the range to be searched we have all 0s outside the range caused by the previous $A$2:$A$1000=$I$1 So the first of these 0s will be seen as the match cell for all cells in F that are empty. So i take care here of the situation where an empty cell in F is by giving a 0 output So far two things retrn me a zero. You often find in formula building that the coercing If({1},___) suddenly is not needed. Her we find that the newly used here IF(F2:F463=0,0,___) is doing the required co oecing
' we will now do a simple If(ISERROR( ) , Row( ) , 0 ) on the above . This will give us a row indicie for the missing data, and a 0 for the found data
Let arrTemp() = Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=I1),0)*(A2:A" & M & "=I1)),ROW(F2:F" & M & "),0)")
' At this point we have wanted data or zeros. I want to conveniently use some VB string fuction whuch annoyingly onl work on 1 D arrays, so we convert it by a transpose in the next code line
Let arrTemp() = Application.Index(arrTemp(), Evaluate("=column(A:" & CL(M - 1) & ")"), Evaluate("=column(A:" & CL(M - 1) & ")/column(A:" & CL(M - 1) & ")"))
' Or
' Let arrTemp() = Application.Transpose(arrTemp())
Let arrTemp() = Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=I1),0)*(A2:A" & M & "=I1)),ROW(F2:F" & M & "),0)"), Evaluate("=column(A:" & CL(M - 1) & ")"), Evaluate("=column(A:" & CL(M - 1) & ")/column(A:" & CL(M - 1) & ")"))
' The next few lines get rid of the 0s ( 2 lines commented out to prevent the shortened line messing up )
Dim StrTemp As String: Let StrTemp = "#" & Join(arrTemp(), "#") ' Convert the array to a string with a # in between each data. The extra # allows us to remove all 0 entries via removing all #0 Without this we might get one left at the start
' Let StrTemp = Replace(StrTemp, "#0", "", 1, -1, vbBinaryCompare) ' This effectiveely removes the 0s data ( and its seperator )
' Let StrTemp = Mid(StrTemp, 2) ' Because I omit the third optional ( length ) argument I get all the remaing string after the first one. This effectively takes off the extra # which I don't need
Dim arrStrTemp() As String: Let arrStrTemp() = Split(StrTemp, "#", -1, vbBinaryCompare) ' remake the array
' Or ,
Let arrStrTemp() = Split(Mid(Replace("#" & Join(Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=I1),0)*(A2:A" & M & "=I1)),ROW(F2:F" & M & "),0)"), Evaluate("=column(A:" & CL(M - 1) & ")"), Evaluate("=column(A:" & CL(M - 1) & ")/column(A:" & CL(M - 1) & ")")), "#"), "#0", ""), 2), "#")
' We need a "vertical" array for output, so we transpose
Let arrTemp() = Application.Index(arrStrTemp(), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")/row(1:" & UBound(arrStrTemp()) + 1 & ")"), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")"))
Let arrTemp() = Application.Index(Worksheets("Sheet1").Columns(6), arrTemp(), 1) ' finally we want the dates ( so far we have the row indicies obtained from Match Note. this formula has the problem that we get the results a row out of step... Its actually very convenient because if i use Cells typically, here a column then I have a nice solution
' Or
Let arrTemp() = Application.Index(Worksheets("Sheet1").Columns(6), Application.Index(arrStrTemp(), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")/row(1:" & UBound(arrStrTemp()) + 1 & ")"), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")")), 1) ' finally we want the dates ( so far we have the row indicies obtained from Match Note. this formula has the problem that we get the results a row out of step... Its actually very convenient because if i use Cells typically, here a column then I have a nice solution
' or
Let Range("T2").Resize(UBound(arrTemp(), 1), 1).Value = arrTemp()
Let Range("T2").Resize(UBound(Application.Index(Worksheets("Sheet1").Columns(6), Application.Index(arrStrTemp(), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")/row(1:" & UBound(arrStrTemp()) + 1 & ")"), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")")), 1), 1), 1).Value = Application.Index(Worksheets("Sheet1").Columns(6), Application.Index(arrStrTemp(), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")/row(1:" & UBound(arrStrTemp()) + 1 & ")"), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")")), 1)
Let Range("T2").Resize(UBound(arrTemp(), 1), 1).NumberFormat = "yyyy/mm/dd" ' from macro recorder .NumberFormat = "[$-1010000]yyyy/mm/dd,@"
Stop
' Range("T2").Resize(UBound(arrTemp(), 1), 1).ClearContents
End Sub
Sub ShortPretty3d()
Dim M As Long: Let M = Worksheets("Sheet1").Range("A" & Worksheets("Sheet1").Rows.Count & "").End(xlUp).Row
Dim arrStrTemp() As String: Let arrStrTemp() = Split(Mid(Replace("#" & Join(Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=I1),0)*(A2:A" & M & "=I1)),ROW(F2:F" & M & "),0)"), Evaluate("=column(A:" & CL(M - 1) & ")"), Evaluate("=column(A:" & CL(M - 1) & ")/column(A:" & CL(M - 1) & ")")), "#"), "#0", ""), 2), "#")
Let Range("T2").Resize(UBound(Application.Index(Worksheets("Sheet1").Columns(6), Application.Index(arrStrTemp(), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")/row(1:" & UBound(arrStrTemp()) + 1 & ")"), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")")), 1), 1), 1).Value = Application.Index(Worksheets("Sheet1").Columns(6), Application.Index(arrStrTemp(), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")/row(1:" & UBound(arrStrTemp()) + 1 & ")"), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")")), 1)
End Sub
Function ShortPretty3dFunction(ByVal Nme As String) As Variant
Dim M As Long: Let M = Worksheets("Sheet1").Range("A" & Worksheets("Sheet1").Rows.Count & "").End(xlUp).Row
Dim arrStrTemp() As String: Let arrStrTemp() = Split(Mid(Replace("#" & Join(Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=" & """" & Nme & """" & "),0)*(A2:A" & M & "=" & """" & Nme & """" & ")),ROW(F2:F" & M & "),0)"), Evaluate("=column(A:" & CL(M - 1) & ")"), Evaluate("=column(A:" & CL(M - 1) & ")/column(A:" & CL(M - 1) & ")")), "#"), "#0", ""), 2), "#")
Let ShortPretty3dFunction = Application.Index(Worksheets("Sheet1").Columns(6), Application.Index(arrStrTemp(), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")/row(1:" & UBound(arrStrTemp()) + 1 & ")"), Evaluate("=row(1:" & UBound(arrStrTemp()) + 1 & ")")), 1)
End Function
Sub TestShortPretty3dFunction()
Dim arrTemp() As Variant
Let arrTemp() = ShortPretty3dFunction("aa")
Range("T2").Resize(UBound(arrTemp(), 1), 1).ClearContents
Let Range("T2").Resize(UBound(arrTemp(), 1), 1).Value = arrTemp()
Let Range("T2").Resize(UBound(arrTemp(), 1), 1).NumberFormat = "yyyy/mm/dd"
End Sub
see next post
Transpose Function Way
Transpose Function Way
Code:
' Using Transpose for the transposing
Sub Pretty3dTranspose() '
Rem 0 worksheets info
Dim Ws1 As Worksheet
Set Ws1 = ThisWorkbook.Worksheets.Item("Sheet1")
Dim M As Long: Let M = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim arrTemp() As Variant
Rem To get the results in column T ( same as Yassers or Hans Results
' Ths first forumula gives me all the matches for F in the C ( helper column ) or error for no match
Let arrTemp() = Evaluate("=If({1},MATCH(F2:F" & M & ",Int(B2:B" & M & "),0))") ' If({1},____) may not be needed for Excel 2016 and higher The first formula does the main work
' The multiplication by $A$2:$A$1000=$I$1 limits the range used by effectively making 0 check dates outside or range of interest
Let arrTemp() = Evaluate("=IF({1},MATCH(F2:F" & M & ",Int(B2:B" & M & ")*($A$2:$A$1000=$I$1),0))") ' $A$2:$A$1000=$I$1 gives us an array full of Falses and Trues , which Excel will interpret mathematically as 0 or 1 This has the effect of giving us a 0 multiplyer on numbers outside our range of interst, so in total a 0 for outside our range of interest. Our range of interest gets a 1 multiplier so has therefore no change and we can find those numbers whereas we wont find a 0, well actually we will find a zero if the range to search for has a zero as it does further down, so we take care of that in the next line
' The above formula has one problem with the supplied data in that empty cells are seen in this formula as 0 which gives a match
Let arrTemp() = Evaluate("=IF(F2:F" & M & "=0,0,MATCH(F2:F" & M & ",C2:C" & M & "*(A2:A" & M & "=I1),0))") ' In looking in the range to find a match in ( the range to be searched we have all 0s outside the range caused by the previous $A$2:$A$1000=$I$1 So the first of these 0s will be seen as the match cell for all cells in F that are empty. So i take care here of the situation where an empty cell in F is by giving a 0 output So far two things retrn me a zero. You often find in formula building that the coercing If({1},___) suddenly is not needed. Her we find that the newly used here IF(F2:F463=0,0,___) is doing the required co oecing
' we will now do a simple If(ISERROR( ) , Row( ) , 0 ) on the above . This will give us a row indicie for the missing data, and a 0 for the found data
Let arrTemp() = Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=I1),0)*(A2:A" & M & "=I1)),ROW(F2:F" & M & "),0)")
' At this point we have wanted data or zeros. I want to conveniently use some VB string fuction which annoyingly on work on 1 D arrays, so we convert it by a transpose in the next code line
'Let arrTemp() = Application.Transpose(arrTemp())
' Or
Let arrTemp() = Application.Transpose(Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=I1),0)*(A2:A" & M & "=I1)),ROW(F2:F" & M & "),0)"))
' The next few lines get rid of the 0s ( 2 lines commented out to prevent the shortened line messing up )
Dim StrTemp As String: Let StrTemp = "#" & Join(arrTemp(), "#") ' Convert the array to a string with a # in between each data. The extra # allows us to remove all 0 entries via removing all #0 Without this we might get one left at the start
' Let StrTemp = Replace(StrTemp, "#0", "", 1, -1, vbBinaryCompare) ' This effectiveely removes the 0s data ( and its seperator )
' Let StrTemp = Mid(StrTemp, 2) ' Because I omit the third optional ( length ) argument I get all the remaing string after the first one. This effectively takes off the extra # which I don't need
Dim arrStrTemp() As String: Let arrStrTemp() = Split(StrTemp, "#", -1, vbBinaryCompare) ' remake the array
' Or ,
Let arrStrTemp() = Split(Mid(Replace("#" & Join(Application.Transpose(Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=I1),0)*(A2:A" & M & "=I1)),ROW(F2:F" & M & "),0)")), "#"), "#0", ""), 2), "#")
' We need a "vertical" array for output, so we transpose to the original orientation, and I need a variant type for that regardless of if i use the in built Transpose way or my preferred Index way since both those will return elements in Variant type
Let arrTemp() = Application.Transpose(arrStrTemp())
' Or
Let arrTemp() = Application.Transpose(Split(Mid(Replace("#" & Join(Application.Transpose(Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=I1),0)*(A2:A" & M & "=I1)),ROW(F2:F" & M & "),0)")), "#"), "#0", ""), 2), "#"))
Let arrTemp() = Application.Index(Worksheets("Sheet1").Columns(6), arrTemp(), 1) ' finally we want the dates ( so far we have the row indicies obtained from Match Note. this formula has the problem that we get the results a row out of step... Its actually very convenient because if i use Cells typically, here a column then I have a nice solution
' Or
Let arrTemp() = Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(arrStrTemp()), 1) ' finally we want the dates ( so far we have the row indicies obtained from Match Note. this formula has the problem that we get the results a row out of step... Its actually very convenient because if i use Cells typically, here a column then I have a nice solution
' Or
Let arrTemp() = Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(Split(Mid(Replace("#" & Join(Application.Transpose(Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=I1),0)*(A2:A" & M & "=I1)),ROW(F2:F" & M & "),0)")), "#"), "#0", ""), 2), "#")), 1)
Let Range("T2").Resize(UBound(arrTemp(), 1), 1).Value = arrTemp()
' Or
Let Range("T2").Resize(UBound(arrTemp(), 1), 1).Value = Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(Split(Mid(Replace("#" & Join(Application.Transpose(Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=I1),0)*(A2:A" & M & "=I1)),ROW(F2:F" & M & "),0)")), "#"), "#0", ""), 2), "#")), 1)
Let Range("T2").Resize(UBound(Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(Split(Mid(Replace("#" & Join(Application.Transpose(Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=I1),0)*(A2:A" & M & "=I1)),ROW(F2:F" & M & "),0)")), "#"), "#0", ""), 2), "#")), 1), 1), 1).Value = Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(Split(Mid(Replace("#" & Join(Application.Transpose(Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=I1),0)*(A2:A" & M & "=I1)),ROW(F2:F" & M & "),0)")), "#"), "#0", ""), 2), "#")), 1)
Let Range("T2").Resize(UBound(arrTemp(), 1), 1).NumberFormat = "yyyy/mm/dd" ' from macro recorder .NumberFormat = "[$-1010000]yyyy/mm/dd,@"
Stop
Range("T2").Resize(UBound(arrTemp(), 1), 1).ClearContents
End Sub
Sub SingleLinePretty3dTranspose()
Dim M As Long: Let M = Worksheets("Sheet1").Range("A" & Worksheets("Sheet1").Rows.Count & "").End(xlUp).Row
Let Range("T2").Resize(UBound(Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(Split(Mid(Replace("#" & Join(Application.Transpose(Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=I1),0)*(A2:A" & M & "=I1)),ROW(F2:F" & M & "),0)")), "#"), "#0", ""), 2), "#")), 1), 1), 1).Value = Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(Split(Mid(Replace("#" & Join(Application.Transpose(Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=I1),0)*(A2:A" & M & "=I1)),ROW(F2:F" & M & "),0)")), "#"), "#0", ""), 2), "#")), 1)
End Sub
Function ShortPretty3dFunctionTranspose(ByVal Nme As String) As Variant
Dim M As Long: Let M = Worksheets("Sheet1").Range("A" & Worksheets("Sheet1").Rows.Count & "").End(xlUp).Row
Let ShortPretty3dFunctionTranspose = Application.Index(Worksheets("Sheet1").Columns(6), Application.Transpose(Split(Mid(Replace("#" & Join(Application.Transpose(Evaluate("=IF(ISERROR(MATCH(F2:F" & M & ",Int(B2:B" & M & ")*(A2:A" & M & "=" & """" & Nme & """" & "),0)*(A2:A" & M & "=" & """" & Nme & """" & ")),ROW(F2:F" & M & "),0)")), "#"), "#0", ""), 2), "#")), 1)
End Function
Sub TestShortPretty3dFunctionTranspose()
Dim arrTemp() As Variant
Let arrTemp() = ShortPretty3dFunctionTranspose("aa")
Range("T2").Resize(UBound(arrTemp(), 1), 1).ClearContents
Let Range("T2").Resize(UBound(arrTemp(), 1), 1).Value = arrTemp()
Let Range("T2").Resize(UBound(arrTemp(), 1), 1).NumberFormat = "yyyy/mm/dd"
End Sub
Some extra solutions for this Thread
https://excelfox.com/forum/showthrea...ontains-a-sign
Excel Solution
_____ Workbook: TextWith$InIt.xls ( Using Excel 2007 32 bit )
Worksheet: Sheet2
Row\Col A B C D E F G H I J K L M 1 5465 Apples$50 Twenty =FIND("$",A1) =LEFT(A1,B1) =FIND(" ",C1) =RIGHT(C1,LEN(C1)-D1) =RIGHT(A1,LEN(A1)-B1) =FIND(" ",F1) =LEFT(F1,G1-1) =E1&H1 =RIGHT(LEFT(A1,FIND("$",A1)),LEN(LEFT(A1,FIND("$",A1)))-FIND(" ",LEFT(A1,FIND("$",A1))))&LEFT(RIGHT(A1,LEN(A1)-FIND("$",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND("$",A1)))-1) Apples$50 Apples$50 2 5687 Grapes$597 Three =FIND("$",A2) =LEFT(A2,B2) =FIND(" ",C2) =RIGHT(C2,LEN(C2)-D2) =RIGHT(A2,LEN(A2)-B2) =FIND(" ",F2) =LEFT(F2,G2-1) =E2&H2 =RIGHT(LEFT(A2,FIND("$",A2)),LEN(LEFT(A2,FIND("$",A2)))-FIND(" ",LEFT(A2,FIND("$",A2))))&LEFT(RIGHT(A2,LEN(A2)-FIND("$",A2)),FIND(" ",RIGHT(A2,LEN(A2)-FIND("$",A2)))-1) Grapes$597 Grapes$597
_____ Workbook: TextWith$InIt.xls ( Using Excel 2007 32 bit )
Worksheet: Sheet2
Row\Col A B C D E F G H I J K L M 1 5465 Apples$50 Twenty 12 5465 Apples$ 5 Apples$ 50 Twenty 3 50 Apples$50 Apples$50 Apples$50 Apples$50 2 5687 Grapes$597 Three 12 5687 Grapes$ 5 Grapes$ 597 Three 4 597 Grapes$597 Grapes$597 Grapes$597 Grapes$597
Some VBA Solutions
Code:Option Explicit
' https://excelfox.com/forum/showthread.php/2738-PQ-make-new-column-by-extracting-a-word-from-a-cell-that-contains-a-sign https://www.mrexcel.com/board/threads/power-query-make-new-column-by-extracting-a-word-from-a-cell-that-contains-a-sign.1165642/
Sub Frm1a() '
Dim vTemp As Variant ' =RIGHT(LEFT(A1,FIND(""$"",A1)),LEN(LEFT(A1,FIND(""$"",A1)))-FIND("" "",LEFT(A1,FIND(""$"",A1))))&LEFT(RIGHT(A1,LEN(A1)-FIND(""$"",A1)),FIND("" "",RIGHT(A1,LEN(A1)-FIND(""$"",A1)))-1)
Let vTemp = Evaluate("=RIGHT(LEFT(A1,FIND(""$"",A1)),LEN(LEFT(A1,FIND(""$"",A1)))-FIND("" "",LEFT(A1,FIND(""$"",A1))))&LEFT(RIGHT(A1,LEN(A1)-FIND(""$"",A1)),FIND("" "",RIGHT(A1,LEN(A1)-FIND(""$"",A1)))-1)")
Debug.Print vTemp ' http://i.imgur.com/LARD8FB.jpg
Dim Rng As Range: Set Rng = Range("A1")
Let vTemp = Evaluate("=RIGHT(LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & ")),LEN(LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & ")))-FIND("" "",LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & "))))&LEFT(RIGHT(" & Rng.Address & ",LEN(" & Rng.Address & ")-FIND(""$""," & Rng.Address & ")),FIND("" "",RIGHT(" & Rng.Address & ",LEN(" & Rng.Address & ")-FIND(""$""," & Rng.Address & ")))-1)")
'Set Rng = Range("A1:A2")
' Let vTemp = Evaluate("=RIGHT(LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & ")),LEN(LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & ")))-FIND("" "",LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & "))))&LEFT(RIGHT(" & Rng.Address & ",LEN(" & Rng.Address & ")-FIND(""$""," & Rng.Address & ")),FIND("" "",RIGHT(" & Rng.Address & ",LEN(" & Rng.Address & ")-FIND(""$""," & Rng.Address & ")))-1)")
End Sub
Sub Frm1b()
Dim Rng As Range
For Each Rng In Range("A1:A2")
Let Rng.Offset(0, 11).Value = Evaluate("=RIGHT(LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & ")),LEN(LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & ")))-FIND("" "",LEFT(" & Rng.Address & ",FIND(""$""," & Rng.Address & "))))&LEFT(RIGHT(" & Rng.Address & ",LEN(" & Rng.Address & ")-FIND(""$""," & Rng.Address & ")),FIND("" "",RIGHT(" & Rng.Address & ",LEN(" & Rng.Address & ")-FIND(""$""," & Rng.Address & ")))-1)")
Next Rng
End Sub
Sub Frm2a()
Dim Rng As Range
For Each Rng In Range("A1:A2")
Dim vTemp As Variant, vTemp1 As Variant, vTemp2 As Variant
Let vTemp = Split(Rng.Value, "$", -1, vbBinaryCompare)
Let vTemp2 = Left(vTemp(1), InStr(1, vTemp(1), " ", vbBinaryCompare) - 1)
Let vTemp1 = Split(vTemp(0), " ", -1, vbBinaryCompare)
Let vTemp1 = vTemp1(UBound(vTemp1))
Let vTemp = vTemp1 & "$" & vTemp2
Next Rng
End Sub
Sub Frm2b()
Dim Rng As Range
For Each Rng In Range("A1:A2")
Dim vTemp As Variant ' , vTemp1 As Variant, vTemp2 As Variant
Let vTemp = Split(Rng.Value, "$")
'Let vTemp2 = Left(vTemp(1), InStr(vTemp(1), " ") - 1)
'Let vTemp1 = Split(vTemp(0), " ")
'Let vTemp1 = Split(vTemp(0), " ")(UBound(Split(vTemp(0), " ")))
'Let vTemp = Split(vTemp(0), " ")(UBound(Split(vTemp(0), " "))) & "$" & Left(vTemp(1), InStr(vTemp(1), " ") - 1)
Let Rng.Offset(0, 12).Value = Split(vTemp(0), " ")(UBound(Split(vTemp(0), " "))) & "$" & Left(vTemp(1), InStr(vTemp(1), " ") - 1)
Next Rng
End Sub
Some note from following info from pconlife.com
Downloading some of their files
Info from here
All file info https://www.pconlife.com/fileinfo/wi...loadandusefile
I initially downloaded some of the zipped winhlp32.exe files, tried on several different computers to open/unzip them . None of the downloaded files will open or unzip. The error is always the same “ Invalid file” http://i.imgur.com/hthN74l.jpg
I followed their advice to try 7.zip , a free Open source program http://www.7-zip.org/
In the following posts I have the
_ downloaded zip file
_ The unzipped exe ( using 7.zip )
_ a re zipped in windows version of that unzipped exe
Windows XP Home Edition x32 Service Pack3:
5.1.2600.0 Download : https://www.pconlife.com/download/wi...134c2bb89727a/
FileVersionFile Md5File SizeFile BitFile
5.1.2600.0 (XPClient.010817-1148) 37b726c72699456bf34134c2bb89727a 8K 32bit
unpacked files in the following path:
• • C:\Windows\system32\
_ Share ‘5 1 2600 0 WINHLP32 EXE.zip’ https://app.box.com/s/tkb7lz4hprmvp2bczwjyj59k2n1tl1h6
_ ** Share ‘5 1 2600 0 WINHLP32 EXE.exe’ https://app.box.com/s/fb0xyzjh7v7oo1bf8hv5r6r986pxeuod
_ Share ‘5 1 2600 0 WINHLP32 EXE Re Zip.zip’ https://app.box.com/s/m9a9huq67rd9pac923nbf3p48ajfmaed
5.1.2600.5512 Download :
FileVersionFile Md5File SizeFile BitFile
5.1.2600.5512 (xpsp.080413-0852) 65a9495a436f5402bc1c467e1b926c27 277K 32bit
unpacked files path:
• • C:\Windows\system32\dllcache\
• • C:\Windows\
_ Share ‘5 1 2600 5512 WINHLP32 EXE.zip’ https://app.box.com/s/tkb7lz4hprmvp2bczwjyj59k2n1tl1h6
_ ** Share ‘5 1 2600 5512 WINHLP32 EXE.exe’ https://app.box.com/s/rdrrs69mpimt2rh2usf5egr3yvadbizr
_ Share ‘5 1 2600 5512 WINHLP32 EXE Re Zip.zip’ https://app.box.com/s/3w2evt1rlq75j1rjfui6bx8qohmros9c
(** These are typical warnings that are shown after a 7.zip unzipping:
http://i.imgur.com/Zg2ZWAq.jpg
http://i.imgur.com/9r2rBVa.jpg Attachment 3553
Here are the final files that I have. I changed the names slightly to help distinguish between different winhlpexe files for different operating systems
http://i.imgur.com/HpEGeig.jpg
http://i.imgur.com/x00l1dj.jpg
In support of this Thread
https://www.eileenslounge.com/viewtopic.php?f=30&t=36380
Excel 2003
Code:16777215 $B$14
16777215 $C$14
16777215 $D$14
16777215 $E$14
16777215 $F$14
16777215 $G$14
16777215 $H$14
16777215 $A$15
16777215 $B$15
16777215 $C$15
16777215 $D$15
16777215 $E$15
16777215 $F$15
16777215 $G$15
16777215 $H$15
16777215 $A$16
16777215 $B$16
16777215 $C$16
16777215 $D$16
16777215 $E$16
16777215 $F$16
16777215 $G$16
16777215 $H$16
16777215 $A$17
16777215 $B$17
16777215 $C$17
16777215 $D$17
16764057 $E$17
16777215 $F$17
16777215 $G$17
16777215 $H$17
16777215 $A$18
16777215 $B$18
16777215 $C$18
16777215 $D$18
16777215 $E$18
16777215 $F$18
16777215 $G$18
16777215 $H$18
16777215 $A$19
16777215 $B$19
16777215 $C$19
16777215 $D$19
16777215 $E$19
16777215 $F$19
16777215 $G$19
16777215 $H$19
16777215 $A$20
16777215 $B$20
16777215 $C$20
16777215 $D$20
16777215 $E$20
16777215 $F$20
16777215 $G$20
16777215 $H$20
16777215 $A$21
16777215 $B$21
16777215 $C$21
16777215 $D$21
16777215 $E$21
16777215 $F$21
16777215 $G$21
16777215 $H$21
16777215 $A$22
16777215 $B$22
16777215 $C$22
16777215 $D$22
16777215 $E$22
16777215 $F$22
16777215 $G$22
16777215 $H$22
16777215 $A$23
16777215 $B$23
16777215 $C$23
16777215 $D$23
16777215 $E$23
16777215 $F$23
16777215 $G$23
16777215 $H$23
16777215 $A$24
16777215 $B$24
16777215 $C$24
16777215 $D$24
16777215 $E$24
16777215 $F$24
16777215 $G$24
16777215 $H$24
16777215 $A$25
16777215 $B$25
16777215 $C$25
16777215 $D$25
16777215 $E$25
16777215 $F$25
16777215 $G$25
16777215 $H$25
16777215 $A$26
16777215 $B$26
16777215 $C$26
16777215 $D$26
16777215 $E$26
16777215 $F$26
16777215 $G$26
16777215 $H$26
16777215 $A$27
16777215 $B$27
16777215 $C$27
16777215 $D$27
16777215 $E$27
16777215 $F$27
16777215 $G$27
16777215 $H$27
16777215 $A$28
16777215 $B$28
16777215 $C$28
16777215 $D$28
16777215 $E$28
16777215 $F$28
16777215 $G$28
16777215 $H$28
16777215 $A$29
16777215 $B$29
16777215 $C$29
16777215 $D$29
16777215 $E$29
16777215 $F$29
16777215 $G$29
16777215 $H$29
16777215 $A$30
16777215 $B$30
16777215 $C$30
16777215 $D$30
16777215 $E$30
16777215 $F$30
16777215 $G$30
16777215 $H$30
16777215 $A$31
16777215 $B$31
16777215 $C$31
16777215 $D$31
16777215 $E$31
16777215 $F$31
16777215 $G$31
16777215 $H$31
16777215 $A$32
16777215 $B$32
16777215 $C$32
16777215 $D$32
16777215 $E$32
16777215 $F$32
16777215 $G$32
16777215 $H$32
16777215 $A$33
16777215 $B$33
16777215 $C$33
16777215 $D$33
16777215 $E$33
16777215 $F$33
16777215 $G$33
16777215 $H$33
16777215 $A$34
16777215 $B$34
16777215 $C$34
16763904 $D$34
16777215 $E$34
16763904 $F$34
16777215 $G$34
16777215 $H$34
16777215 $A$35
16777215 $B$35
16777215 $C$35
65535 $D$35
16777215 $E$35
52377 $F$35
16777215 $G$35
16777215 $H$35
16777215 $A$36
16777215 $B$36
16777215 $C$36
65535 $D$36
16777215 $E$36
65535 $F$36
16777215 $G$36
16777215 $H$36
16777215 $A$37
16777215 $B$37
16777215 $C$37
52377 $D$37
16777215 $E$37
52377 $F$37
16777215 $G$37
16777215 $H$37
16777215 $A$38
16777215 $B$38
16777215 $C$38
65535 $D$38
16777215 $E$38
52377 $F$38
16777215 $G$38
16777215 $H$38
Code:16777215 $B$14
16777215 $C$14
16777215 $D$14
16777215 $E$14
16777215 $F$14
16777215 $G$14
16777215 $H$14
16777215 $A$15
16777215 $B$15
16777215 $C$15
16777215 $D$15
16777215 $E$15
16777215 $F$15
16777215 $G$15
16777215 $H$15
16777215 $A$16
16777215 $B$16
16777215 $C$16
16777215 $D$16
16777215 $E$16
16777215 $F$16
16777215 $G$16
16777215 $H$16
16777215 $A$17
16777215 $B$17
16777215 $C$17
16777215 $D$17
16764057 $E$17
16777215 $F$17
16777215 $G$17
16777215 $H$17
16777215 $A$18
16777215 $B$18
16777215 $C$18
16777215 $D$18
16777215 $E$18
16777215 $F$18
16777215 $G$18
16777215 $H$18
16777215 $A$19
16777215 $B$19
16777215 $C$19
16777215 $D$19
16777215 $E$19
16777215 $F$19
16777215 $G$19
16777215 $H$19
16777215 $A$20
16777215 $B$20
16777215 $C$20
16777215 $D$20
16777215 $E$20
16777215 $F$20
16777215 $G$20
16777215 $H$20
16777215 $A$21
16777215 $B$21
16777215 $C$21
16777215 $D$21
16777215 $E$21
16777215 $F$21
16777215 $G$21
16777215 $H$21
16777215 $A$22
16777215 $B$22
16777215 $C$22
16777215 $D$22
16777215 $E$22
16777215 $F$22
16777215 $G$22
16777215 $H$22
16777215 $A$23
16777215 $B$23
16777215 $C$23
16777215 $D$23
16777215 $E$23
16777215 $F$23
16777215 $G$23
16777215 $H$23
16777215 $A$24
16777215 $B$24
16777215 $C$24
16777215 $D$24
16777215 $E$24
16777215 $F$24
16777215 $G$24
16777215 $H$24
16777215 $A$25
16777215 $B$25
16777215 $C$25
16777215 $D$25
16777215 $E$25
16777215 $F$25
16777215 $G$25
16777215 $H$25
16777215 $A$26
16777215 $B$26
16777215 $C$26
16777215 $D$26
16777215 $E$26
16777215 $F$26
16777215 $G$26
16777215 $H$26
16777215 $A$27
16777215 $B$27
16777215 $C$27
16777215 $D$27
16777215 $E$27
16777215 $F$27
16777215 $G$27
16777215 $H$27
16777215 $A$28
16777215 $B$28
16777215 $C$28
16777215 $D$28
16777215 $E$28
16777215 $F$28
16777215 $G$28
16777215 $H$28
16777215 $A$29
16777215 $B$29
16777215 $C$29
16777215 $D$29
16777215 $E$29
16777215 $F$29
16777215 $G$29
16777215 $H$29
16777215 $A$30
16777215 $B$30
16777215 $C$30
16777215 $D$30
16777215 $E$30
16777215 $F$30
16777215 $G$30
16777215 $H$30
16777215 $A$31
16777215 $B$31
16777215 $C$31
16777215 $D$31
16777215 $E$31
16777215 $F$31
16777215 $G$31
16777215 $H$31
16777215 $A$32
16777215 $B$32
16777215 $C$32
16777215 $D$32
16777215 $E$32
16777215 $F$32
16777215 $G$32
16777215 $H$32
16777215 $A$33
16777215 $B$33
16777215 $C$33
16777215 $D$33
16777215 $E$33
16777215 $F$33
16777215 $G$33
16777215 $H$33
16777215 $A$34
16777215 $B$34
16777215 $C$34
16763904 $D$34
16777215 $E$34
16763904 $F$34
16777215 $G$34
16777215 $H$34
16777215 $A$35
16777215 $B$35
16777215 $C$35
65535 $D$35
16777215 $E$35
52377 $F$35
16777215 $G$35
16777215 $H$35
16777215 $A$36
16777215 $B$36
16777215 $C$36
65535 $D$36
16777215 $E$36
65535 $F$36
16777215 $G$36
16777215 $H$36
16777215 $A$37
16777215 $B$37
16777215 $C$37
52377 $D$37
16777215 $E$37
52377 $F$37
16777215 $G$37
16777215 $H$37
16777215 $A$38
16777215 $B$38
16777215 $C$38
65535 $D$38
16777215 $E$38
52377 $F$38
16777215 $G$38
16777215 $H$38
The above .xls file in 2010Code:16777215 $B$14
16777215 $C$14
16777215 $D$14
16777215 $E$14
16777215 $F$14
16777215 $G$14
16777215 $H$14
16777215 $A$15
16777215 $B$15
16777215 $C$15
16777215 $D$15
16777215 $E$15
16777215 $F$15
16777215 $G$15
16777215 $H$15
16777215 $A$16
16777215 $B$16
16777215 $C$16
16777215 $D$16
16777215 $E$16
16777215 $F$16
16777215 $G$16
16777215 $H$16
16777215 $A$17
16777215 $B$17
16777215 $C$17
16777215 $D$17
16764057 $E$17
16777215 $F$17
16777215 $G$17
16777215 $H$17
16777215 $A$18
16777215 $B$18
16777215 $C$18
16777215 $D$18
16777215 $E$18
16777215 $F$18
16777215 $G$18
16777215 $H$18
16777215 $A$19
16777215 $B$19
16777215 $C$19
16777215 $D$19
16777215 $E$19
16777215 $F$19
16777215 $G$19
16777215 $H$19
16777215 $A$20
16777215 $B$20
16777215 $C$20
16777215 $D$20
16777215 $E$20
16777215 $F$20
16777215 $G$20
16777215 $H$20
16777215 $A$21
16777215 $B$21
16777215 $C$21
16777215 $D$21
16777215 $E$21
16777215 $F$21
16777215 $G$21
16777215 $H$21
16777215 $A$22
16777215 $B$22
16777215 $C$22
16777215 $D$22
16777215 $E$22
16777215 $F$22
16777215 $G$22
16777215 $H$22
16777215 $A$23
16777215 $B$23
16777215 $C$23
16777215 $D$23
16777215 $E$23
16777215 $F$23
16777215 $G$23
16777215 $H$23
16777215 $A$24
16777215 $B$24
16777215 $C$24
16777215 $D$24
16777215 $E$24
16777215 $F$24
16777215 $G$24
16777215 $H$24
16777215 $A$25
16777215 $B$25
16777215 $C$25
16777215 $D$25
16777215 $E$25
16777215 $F$25
16777215 $G$25
16777215 $H$25
16777215 $A$26
16777215 $B$26
16777215 $C$26
16777215 $D$26
16777215 $E$26
16777215 $F$26
16777215 $G$26
16777215 $H$26
16777215 $A$27
16777215 $B$27
16777215 $C$27
16777215 $D$27
16777215 $E$27
16777215 $F$27
16777215 $G$27
16777215 $H$27
16777215 $A$28
16777215 $B$28
16777215 $C$28
16777215 $D$28
16777215 $E$28
16777215 $F$28
16777215 $G$28
16777215 $H$28
16777215 $A$29
16777215 $B$29
16777215 $C$29
16777215 $D$29
16777215 $E$29
16777215 $F$29
16777215 $G$29
16777215 $H$29
16777215 $A$30
16777215 $B$30
16777215 $C$30
16777215 $D$30
16777215 $E$30
16777215 $F$30
16777215 $G$30
16777215 $H$30
16777215 $A$31
16777215 $B$31
16777215 $C$31
16777215 $D$31
16777215 $E$31
16777215 $F$31
16777215 $G$31
16777215 $H$31
16777215 $A$32
16777215 $B$32
16777215 $C$32
16777215 $D$32
16777215 $E$32
16777215 $F$32
16777215 $G$32
16777215 $H$32
16777215 $A$33
16777215 $B$33
16777215 $C$33
16777215 $D$33
16777215 $E$33
16777215 $F$33
16777215 $G$33
16777215 $H$33
16777215 $A$34
16777215 $B$34
16777215 $C$34
16763904 $D$34
16777215 $E$34
16763904 $F$34
16777215 $G$34
16777215 $H$34
16777215 $A$35
16777215 $B$35
16777215 $C$35
65535 $D$35
16777215 $E$35
52377 $F$35
16777215 $G$35
16777215 $H$35
16777215 $A$36
16777215 $B$36
16777215 $C$36
65535 $D$36
16777215 $E$36
65535 $F$36
16777215 $G$36
16777215 $H$36
16777215 $A$37
16777215 $B$37
16777215 $C$37
52377 $D$37
16777215 $E$37
52377 $F$37
16777215 $G$37
16777215 $H$37
16777215 $A$38
16777215 $B$38
16777215 $C$38
65535 $D$38
16777215 $E$38
52377 $F$38
16777215 $G$38
16777215 $H$38
Code:16777215 $C$14
16777215 $D$14
16777215 $E$14
16777215 $F$14
16777215 $G$14
16777215 $H$14
16777215 $A$15
16777215 $B$15
16777215 $C$15
16777215 $D$15
16777215 $E$15
16777215 $F$15
16777215 $G$15
16777215 $H$15
16777215 $A$16
16777215 $B$16
16777215 $C$16
16777215 $D$16
16777215 $E$16
16777215 $F$16
16777215 $G$16
16777215 $H$16
16777215 $A$17
16777215 $B$17
16777215 $C$17
16777215 $D$17
****15261367 ****** $E$17
16777215 $F$17
16777215 $G$17
16777215 $H$17
16777215 $A$18
16777215 $B$18
16777215 $C$18
16777215 $D$18
16777215 $E$18
16777215 $F$18
16777215 $G$18
16777215 $H$18
16777215 $A$19
16777215 $B$19
16777215 $C$19
16777215 $D$19
16777215 $E$19
16777215 $F$19
16777215 $G$19
16777215 $H$19
16777215 $A$20
16777215 $B$20
16777215 $C$20
16777215 $D$20
16777215 $E$20
16777215 $F$20
16777215 $G$20
16777215 $H$20
16777215 $A$21
16777215 $B$21
16777215 $C$21
16777215 $D$21
16777215 $E$21
16777215 $F$21
16777215 $G$21
16777215 $H$21
16777215 $A$22
16777215 $B$22
16777215 $C$22
16777215 $D$22
16777215 $E$22
16777215 $F$22
16777215 $G$22
16777215 $H$22
16777215 $A$23
16777215 $B$23
16777215 $C$23
16777215 $D$23
16777215 $E$23
16777215 $F$23
16777215 $G$23
16777215 $H$23
16777215 $A$24
16777215 $B$24
16777215 $C$24
16777215 $D$24
16777215 $E$24
16777215 $F$24
16777215 $G$24
16777215 $H$24
16777215 $A$25
16777215 $B$25
16777215 $C$25
16777215 $D$25
16777215 $E$25
16777215 $F$25
16777215 $G$25
16777215 $H$25
16777215 $A$26
16777215 $B$26
16777215 $C$26
16777215 $D$26
16777215 $E$26
16777215 $F$26
16777215 $G$26
16777215 $H$26
16777215 $A$27
16777215 $B$27
16777215 $C$27
16777215 $D$27
16777215 $E$27
16777215 $F$27
16777215 $G$27
16777215 $H$27
16777215 $A$28
16777215 $B$28
16777215 $C$28
16777215 $D$28
16777215 $E$28
16777215 $F$28
16777215 $G$28
16777215 $H$28
16777215 $A$29
16777215 $B$29
16777215 $C$29
16777215 $D$29
16777215 $E$29
16777215 $F$29
16777215 $G$29
16777215 $H$29
16777215 $A$30
16777215 $B$30
16777215 $C$30
16777215 $D$30
16777215 $E$30
16777215 $F$30
16777215 $G$30
16777215 $H$30
16777215 $A$31
16777215 $B$31
16777215 $C$31
16777215 $D$31
16777215 $E$31
16777215 $F$31
16777215 $G$31
16777215 $H$31
16777215 $A$32
16777215 $B$32
16777215 $C$32
16777215 $D$32
16777215 $E$32
16777215 $F$32
16777215 $G$32
16777215 $H$32
16777215 $A$33
16777215 $B$33
16777215 $C$33
16777215 $D$33
16777215 $E$33
16777215 $F$33
16777215 $G$33
16777215 $H$33
16777215 $A$34
16777215 $B$34
16777215 $C$34
15773696 $D$34
16777215 $E$34
15773696 $F$34
16777215 $G$34
16777215 $H$34
16777215 $A$35
16777215 $B$35
16777215 $C$35
65535 $D$35
16777215 $E$35
5296274 $F$35
16777215 $G$35
16777215 $H$35
16777215 $A$36
16777215 $B$36
16777215 $C$36
65535 $D$36
16777215 $E$36
65535 $F$36
16777215 $G$36
16777215 $H$36
16777215 $A$37
16777215 $B$37
16777215 $C$37
5296274 $D$37
16777215 $E$37
5296274 $F$37
16777215 $G$37
16777215 $H$37
16777215 $A$38
16777215 $B$38
16777215 $C$38
65535 $D$38
16777215 $E$38
5296274 $F$38
16777215 $G$38
16777215 $H$38