Post for later use
Post for later use
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
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
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
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
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
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
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
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
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
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
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
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
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
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
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
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
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
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$38Code: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$38The 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
….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
If you are my enemy, we will try to kick the fucking shit out of you…..
Winston Churchill, 1939
Save your Forum..._
_...KILL A MODERATOR!!
Posting Permissions
Reply With Quote
Bookmarks