Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

[DocAElstein]

I am not quite sure what got in my brain in the last post. With hind site most of what I said and done is crap. But maybe later I will twig to what was going on.

I will start again…. Or rather pick it up where I went off course…._ I have …_
_.... an awkward bollock
Variations of this come up a lot. Often an efficient cure to this awkward bollock is to include an extra separator at the start.

The general solution is fine. After adding a separator, #, at the start, I remove all #0
All is well and then I only need to get rid finally of a single # I don’t need at the start.
For that last thing, Mid(StrTemp,2) would do. So would a second Replace in this form Replace(StrTemp, "#", "", 1, 1…. Or Replace(StrTemp, "#", "", , 1….
In the Replace.. we are using the 5th (optional ) argument to restrict us to removing a single # and the convention is to start from the left so that will hit on the first.

In this complete version I use the Mid(StrTemp,2) way

Option Explicit
Sub Pretty3bbaa()  '
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.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
'
 Let Range("T2").Resize(UBound(arrTemp(), 1), 1).Value = arrTemp()
 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 SlightlySanerVersion()
Dim arrStrTemp() As String: 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), "#")
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 Range("T2").Resize(UBound(arrTemp(), 1), 1).Value = arrTemp()
Stop
 Range("T2").Resize(UBound(arrTemp(), 1), 1).ClearContents
' Or
Dim UnicNm As String: Let UnicNm = "aa" ' "aa"
 Let arrStrTemp() = Split(Mid(Replace("#" & Join(Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=" & """" & UnicNm & """" & "),0)*($A$2:$A$1000=" & """" & UnicNm & """" & ")),ROW(F2:F463),0)"), Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)")), "#"), "#0", ""), 2), "#")
 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 Range("T2").Resize(UBound(arrTemp(), 1), 1).Value = arrTemp()
End Sub

[DocAElstein]

I can sanitise the last version a bit and come up with a simple function to get you an array of your missings, where the function takes the unique name, ( the unique name in the test data is the things like aa bb cc etc. )

Sub SlightlySanerVersion()
Dim arrStrTemp() As String: 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), "#")
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 Range("T2").Resize(UBound(arrTemp(), 1), 1).Value = arrTemp()
Stop
 Range("T2").Resize(UBound(arrTemp(), 1), 1).ClearContents
' Or
Dim UnicNm As String: Let UnicNm = "aa" ' "aa"
 Let arrStrTemp() = Split(Mid(Replace("#" & Join(Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=" & """" & UnicNm & """" & "),0)*($A$2:$A$1000=" & """" & UnicNm & """" & ")),ROW(F2:F463),0)"), Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)")), "#"), "#0", ""), 2), "#")
 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 Range("T2").Resize(UBound(arrTemp(), 1), 1).Value = arrTemp()
End Sub
Sub UseNotSoInsaneFunction()
Dim arrTemp() As Variant
 Let arrTemp() = NotSoInsane("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
Function NotSoInsane(ByVal Nme As String) As Variant
Dim arrStrTemp() As String: Let arrStrTemp() = Split(Mid(Replace("#" & Join(Application.Index(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 NotSoInsane = arrTemp()
End Function
 '

[DocAElstein]

Post for later use

[DocAElstein]

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

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:

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

[DocAElstein]

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

[DocAElstein]

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.

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

[DocAElstein]

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

' 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

[DocAElstein]

Transpose Function Way

'   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

[DocAElstein]

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 )

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

Worksheet: Sheet2
_____ Workbook: TextWith$InIt.xls ( Using Excel 2007 32 bit )

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

Worksheet: Sheet2



Some VBA Solutions

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

[DocAElstein]

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

---