Tests Copying, Pasting, API Cliipboard issues. and Rough notes on Advanced API stuff

Printable View

Show 40 post(s) from this thread on one page

03-05-2021, 05:17 PM
DocAElstein

Post for later use

Some notes from this question:
http://www.eileenslounge.com/viewtop...281312#p281312
Yasser Question.JPG

Question …
http://www.eileenslounge.com/viewtopic.php?f=30&t=36224
http://i.imgur.com/Ot6o46f.jpg
_____ Workbook: Extract missing dates for each person.xlsm ( Using Excel 2007 32 bit )

_Row\^Col A B C D E F G H I T U V

1 Name Dates Helper Check Dates Result aa Yasser Given

2 aa 2021-02-19 2021-02-19 2021-01-26 2021-01-26 2021-01-29 2021-01-29

3 aa 2021-01-26 2021-01-26 2021-01-27 2021-01-27 2021-01-30 2021-01-30

4 aa 2021-01-27 2021-01-27 2021-01-28 2021-01-28 2021-02-05 2021-02-05

5 aa 2021-01-28 2021-01-28 2021-01-29 Missing 2021-02-12 2021-02-12

6 aa 2021-01-31 2021-01-31 2021-01-30 Missing

7 aa 2021-02-01 2021-02-01 2021-01-31 2021-01-31

8 aa 2021-02-02 2021-02-02 2021-02-01 2021-02-01

9 aa 2021-02-03 2021-02-03 2021-02-02 2021-02-02

10 aa 2021-02-04 2021-02-04 2021-02-03 2021-02-03

11 aa 2021-02-06 2021-02-06 2021-02-04 2021-02-04

12 aa 2021-02-07 2021-02-07 2021-02-05 Missing

13 aa 2021-02-08 2021-02-08 2021-02-06 2021-02-06

14 aa 2021-02-09 2021-02-09 2021-02-07 2021-02-07

15 aa 2021-02-10 2021-02-10 2021-02-08 2021-02-08

16 aa 2021-02-11 2021-02-11 2021-02-09 2021-02-09

17 aa 2021-02-13 2021-02-13 2021-02-10 2021-02-10

18 aa 2021-02-14 2021-02-14 2021-02-11 2021-02-11

19 aa 2021-02-15 2021-02-15 2021-02-12 Missing

20 aa 2021-02-16 2021-02-16 2021-02-13 2021-02-13

21 aa 2021-02-17 2021-02-17 2021-02-14 2021-02-14

22 aa 2021-02-18 2021-02-18 2021-02-15 2021-02-15

23 aa 2021-02-20 2021-02-20 2021-02-16 2021-02-16

24 aa 2021-02-21 2021-02-21 2021-02-17 2021-02-17

25 aa 2021-02-22 2021-02-22 2021-02-18 2021-02-18

26 aa 2021-02-23 2021-02-23 2021-02-19 2021-02-19

27 aa 2021-02-24 2021-02-24 2021-02-20 2021-02-20

28 aa 2021-02-25 2021-02-25 2021-02-21 2021-02-21

29 bb 2021-01-27 2021-01-27 2021-02-22 2021-02-22

30 bb 2021-01-28 2021-01-28 2021-02-23 2021-02-23

31 bb 2021-01-31 2021-01-31 2021-02-24 2021-02-24

32 bb 2021-02-01 2021-02-01 2021-02-25 2021-02-25

33 bb 2021-02-03 2021-02-03

Worksheet: Sheet1

03-05-2021, 05:26 PM
DocAElstein

1 Attachment(s)

Continued from last post: Some notes from this question:
http://www.eileenslounge.com/viewtop...281312#p281312
Yasser Question.JPG https://excelfox.com/forum/showthrea...ll=1#post15418

Hans Solution. What’s he doing:

Rem 1 Make dictionary of Dictionaries2
There are two dictionary variables.
The first one contains all the unique name values from column A . So this is the unique names dictionary
We loop down to build that dictionary, and the solution is relying on a un unbroken sequential list of names, in other words no mixed up , but an order list like
Name1
Name1
Name1
Name2
Name2
..etc.
In that main loop , all the values, from column B are put in the Item ( which is itself a dictionary ) of each unique name in the unique names dictionary.
This is the clever line that does that. The line is done for each row in the data to be looked in ( column B )
Let dct1(rng1(r1, 1))(Int(rng1(r1, 2))) = 1
The 1 is arbitrary. What we are doing is like referring to ( trying to put 1 into ) the key of an item in the second dictionary that does not exist. When this is done, rather than error, the Scripting.Dictionary is programmed to make an item with that key.
The end result of all this is that we end up with a main dictionary that has a key for each unique name. The item for each name / Key has a second dictionary in it of all the Integer parts of the Date & time in column B. ( The dictionary will be seeing the basic Excel .Vaue2 of the date & time, so the Integer part will be just the date.
Here is a pseudo couple of code lines to demo that last bit

Dick1(Name1) ( 2021-02-19 ) = 1
Dick1(Name1) ( 2021-01-26 ) = 1

You see what’s going on is the following:
Dick1(Name1) will always return the same thing which is the Item in Dick1 with the Key of Name1
So Effectively those lines are pseudo

Dick2 ( 2021-02-19 ) = 1
Dick2 ( 2021-01-26 ) = 1

What those code lines try to do is put a 1 in the items of a Dick2 element that does not exist. As noted, the Scripting.Dictionary is programmed to make an item with that key rather than error if such an action is attempted.
So that is just a convenient way to make the second dictionaries – Note I said dictionaries

We end up with this:
Dick1 keys
http://i.imgur.com/zTWYpuy.jpg

Dick2KeysWichAreDicksInDick1Items.jpg
http://i.imgur.com/Jsd2kXS.jpg

These lines give me that from doing a Shift F9 on any variable
Shift F9 on vTemps for Watch Window.JPG http://i.imgur.com/Ms7HmG6.jpg

Rem 2
We have an Outer loop and an inner loop in it.
__The outer loop is done once for each unique name, so for each key of the main dictionary
____The inner loop goes down the entire F column Check dates and does a write out any missing dates, that is to say dates not in the dictionary that is the item for that unique name, key of the main dictionary

Hans macro

Code:

Option Explicit Sub ListMissing() ' ' Hans http://www.eileenslounge.com/viewtopic.php?p=281312#p281312 Dim vTemp1, vTemp2 ' For development and debug Dim wsh1 As Worksheet Dim wsh2 As Worksheet Dim rng1 As Variant Dim rng2 As Variant Dim m1 As Long Dim m2 As Long Dim r1 As Long Dim r2 As Long Dim r3 As Long Dim dct1 As Object Dim dcTemp2 As Object Dim n As Variant Set dct1 = CreateObject("Scripting.Dictionary") Set wsh1 = Worksheets("Sheet1") Let m1 = wsh1.Range("A" & wsh1.Rows.Count).End(xlUp).Row Let rng1 = wsh1.Range("A2:B" & m1).Value Rem 1 Make dictionary of Dictionaries For r1 = 1 To UBound(rng1) If Not dct1.Exists(rng1(r1, 1)) Then ' this gives us 3 elements in the dct1 that have like key aa and the item is an empty dictionary object Set dcTemp2 = CreateObject("Scripting.Dictionary") ' This effectively clears the variable used temporarily dct1.Add Key:=rng1(r1, 1), Item:=dcTemp2 End If Let dct1(rng1(r1, 1))(Int(rng1(r1, 2))) = 1 ' the 1 is arbritrary, we effectively create a Key looking like aa 2021-02-19 in the second dictionary that is the item of the unique Next r1 vTemp1 = dct1.keys() ' Dick1.JPG http://i.imgur.com/zTWYpuy.jpg vTemp2 = dct1.items() ' Dick2KeysWichAreDicksInDick1Items.jpg http://i.imgur.com/Jsd2kXS.jpg ' Let m2 = wsh1.Range("F" & wsh1.Rows.Count).End(xlUp).Row Let rng2 = wsh1.Range("F2:F" & m2).Value ' Set wsh2 = Worksheets("Sheet2Hans") wsh2.Range("A2:B" & wsh2.Rows.Count).Clear Rem 2 Go through checking for existance of an Item. For no existance , then that is missing data Let r3 = 1 ' The outer loop is done once for each unique name, so for each key of the main dictionary =========== For Each n In dct1.keys ' this and next line make it For Each of .._ Set dcTemp2 = dct1(n) ' _.. the dictionries within each item of Dick1 In other words For Each Name ' ----------------------------------------------- ' The inner loop goes down the entire F column Check dates and does a write out any missing dates, that is to say dates not in the dictionary that is the item for that unique name, key of the main dictionary For r2 = 1 To UBound(rng2) ' Going down the entire F range If Not dcTemp2.Exists(rng2(r2, 1)) Then Let r3 = r3 + 1 Let wsh2.Range("A" & r3).Value = n ' n is the key, the unique name, in the main large dictionary Let wsh2.Range("B" & r3).Value = rng2(r2, 1) ' This will be the missing entry Else End If Next r2 ' ________________________________________ Next n ' ============================================================================================== End Sub
03-05-2021, 10:51 PM
DocAElstein

1 Attachment(s)

Some notes for this question:
http://www.eileenslounge.com/viewtop...281291#p281291

For example, this bit …. using formulas like that
=IFERROR(INDEX($A$2:$C$1000,MATCH(1,($A$2:$A$1000= $I$1)*($C$2:$C$1000=F2),0),3),"Missing")
Then I manually filter by Missing and copied the results…..
That can be done in a single code line, …. _

Code:

Sub BasicOneLine() ' '.... http://www.eileenslounge.com/viewtopic.php?p=281291#p281291 Let Range("T2").Resize(UBound(Application.Index(Worksheets("Sheet1").Columns(6), Application.Index(Split(Replace(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", ""), "0#", ""), "#", -1), Evaluate("=row(1:" & UBound(Split(Replace(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", ""), "0#", ""), "#", -1)) + 1 & ")/row(1:" & UBound(Split(Replace(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", ""), "0#", ""), "#", -1)) + 1 & ")"), Evaluate("=row(1:" & UBound(Split(Replace(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", ""), "0#", ""), "#", -1)) + 1 & ")")), 1), 1), 1).Value = _ Application.Index(Worksheets("Sheet1").Columns(6), Application.Index(Split(Replace(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", ""), "0#", ""), "#", -1), Evaluate("=row(1:" & UBound(Split(Replace(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", ""), "0#", ""), "#", -1)) + 1 & ")/row(1:" & UBound(Split(Replace(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", ""), "0#", ""), "#", -1)) + 1 & ")"), Evaluate("=row(1:" & UBound(Split(Replace(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", ""), "0#", ""), "#", -1)) + 1 & ")")), 1) End Sub

Before
_____ Workbook: Extract missing dates for each person.xlsm ( Using Excel 2007 32 bit )

_Row\^Col I T U V

1 aa Yasser Given

2 2021-01-29

3 2021-01-30

4 2021-02-05

5 2021-02-12

6

Worksheet: Sheet1

After
_____ Workbook: Extract missing dates for each person.xlsm ( Using Excel 2007 32 bit )

_Row\^Col I T U V

1 aa Yasser Given

2 2021-01-29 2021-01-29

3 2021-01-30 2021-01-30

4 2021-02-05 2021-02-05

5 2021-02-12 2021-02-12

6

Worksheet: Sheet1

Run Sub BasicOneLine() on the uploaded file to demo those results
03-05-2021, 11:11 PM
DocAElstein

extended coding notes for last post
https://excelfox.com/forum/showthrea...ll=1#post15420

Code:

Sub Pretty2() ' Dim arrTemp() As Variant Rem To get the results in column T ( same as ' Ths first forumula give 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)")) ' The next few lines get rid of the 0s Dim StrTemp As String: Let StrTemp = Join(arrTemp(), "#") ' Convert the array to a string with a # in between each data Let StrTemp = Replace(StrTemp, "#0", "", 1, -1, vbBinaryCompare): StrTemp = Replace(StrTemp, "0#", "", 1, -1, vbBinaryCompare) ' This effectiveely removes the 0s data ( and its seperator ) Dim arrStrTemp() As String: Let arrStrTemp() = Split(StrTemp, "#", -1, vbBinaryCompare) ' remake the array ' 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 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 ' Or Let Range("T2").Resize(UBound(Application.Index(Worksheets("Sheet1").Columns(6), Application.Index(Split(Replace(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", ""), "0#", ""), "#", -1), Evaluate("=row(1:" & UBound(Split(Replace(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", ""), "0#", ""), "#", -1)) + 1 & ")/row(1:" & UBound(Split(Replace(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", ""), "0#", ""), "#", -1)) + 1 & ")"), Evaluate("=row(1:" & UBound(Split(Replace(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", ""), "0#", ""), "#", -1)) + 1 & ")")), 1), 1), 1).Value = _ Application.Index(Worksheets("Sheet1").Columns(6), Application.Index(Split(Replace(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", ""), "0#", ""), "#", -1), Evaluate("=row(1:" & UBound(Split(Replace(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", ""), "0#", ""), "#", -1)) + 1 & ")/row(1:" & UBound(Split(Replace(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", ""), "0#", ""), "#", -1)) + 1 & ")"), Evaluate("=row(1:" & UBound(Split(Replace(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", ""), "0#", ""), "#", -1)) + 1 & ")")), 1) Stop ' Replace(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", ""), "0#", "") Rem To get to Column N in Extract missing dates for each person.xlsm ' Ths first forumula give 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 ' The next step is to replace the errors with 0s Let arrTemp() = Evaluate("=IFERROR(IF(F2:F463=0,0,MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0)),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 ' 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)")) ' The next few lines get rid of the 0s 'Dim StrTemp As String Let StrTemp = Join(arrTemp(), "#") ' Convert the array to a string with a # in between each data Let StrTemp = Replace(StrTemp, "#0", "", 1, -1, vbBinaryCompare) ' This effectiveely removes the 0s data ( and its seperator ) 'Dim arrStrTemp() As String Let arrStrTemp() = Split(StrTemp, "#", -1, vbBinaryCompare) ' remake the array ' 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(Range("C2:C463"), arrTemp(), 1) ' finally we want the dates ( so far we have the row indicies obtained from Match Let Range("N2").Resize(UBound(arrTemp(), 1), 1).Value = arrTemp() Let Range("N2").Resize(UBound(arrTemp(), 1), 1).NumberFormat = "yyyy/mm/dd" ' from macro recorder .NumberFormat = "[$-1010000]yyyy/mm/dd,@" ' Or ' Let arrTemp() = Evaluate("=If({1},IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0)*($A$2:$A$1000=$I$1)),ROW(F2:F463),0))") 'let worksheets("Sheet2").range 'v = Join(v, "#") ' https://www.vbarchiv.net/commands/cmd_filter.html ' ' 'v = Application.Index(Range("C2:C463"), Evaluate("=If({1},MATCH(F2:F463,C2:C463*($A$2:$A$1000=$I$1),0)*($A$2:$A$1000=$I$1))"), 1) ' ' 'v = Application.Index(v, Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)")) 'v = Evaluate("=IFERROR(INDEX($A$2:$C$1000,MATCH(1,($A$2:$A$1000=$I$1)*($C$2:$C$1000=" & r.Address & "),0),3),""Missing"")") ' End Sub Sub BasicOneLine() ' '.... http://www.eileenslounge.com/viewtopic.php?p=281291#p281291
03-06-2021, 12:47 AM
DocAElstein

This is a slightly more sane version of the single line macro idea from here
https://excelfox.com/forum/showthrea...ll=1#post15420
https://excelfox.com/forum/showthrea...ll=1#post15421

Code:

Sub SlightlySanerVersion() Dim arrStrTemp() As String: Let arrStrTemp() = Split(Replace(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#", ""), "#0", ""), "#") 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" Let arrStrTemp() = Split(Replace(Replace(Join(Application.Index(Evaluate("=IF(ISERROR(MATCH(F2:F463,C2:C463*($A$2:$A$1000=" & """" & UnicNm & """" & "),0)*($A$2:$A$1000=$I$1)),ROW(F2:F463),0)"), Evaluate("=column(A:QT)"), Evaluate("=column(A:QT)/column(A:QT)")), "#"), "0#", ""), "#0", ""), "#") 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

We can use the basic idea above to make a function idea to do the same

Code:

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

_Row\^Col	I	T	U
1	aa		Yasser Given
2		2021-01-29	2021-01-29
3		2021-01-30	2021-01-30
4		2021-02-05	2021-02-05
5		2021-02-12	2021-02-12
6

In support of these Threads and posts
https://excelfox.com/forum/showthrea...ll=1#post15421
http://www.eileenslounge.com/viewtopic.php?f=30&t=36224

A problem arose with testing with bb

_____ Workbook: Extract missing dates for each person bb.xlsm ( Using Excel 2007 32 bit )

_Row\^Col T U V W X Y Z

1 Yasser Given Hans Results Indicies

2 2021-01-26 2021-01-29 2021-01-29 2

3 2021-01-30 2021-01-30 0

4 2021-02-05 2021-02-05 0

5 2021-01-29 2021-02-12 2021-02-12 5

6 2021-01-30 2021-01-26 2021-01-26 6

7 2021-01-29 2021-01-29 0

8 2021-01-30 2021-01-30 0

9 2021-02-02 2021-02-02 2021-02-02 9

10 2021-02-05 0

11 2021-02-12 0

12 2021-02-05 2021-02-05 12

13 2021-02-16 0

14 2021-02-19 0

15 2021-02-25 0

16 0

17 0

18 0

19 2021-02-12 2021-02-12 19

20 2021-02-13 20

21 0

22 2021-02-15 22

23 2021-02-16 2021-02-16 23

24 0

25 0

26 2021-02-19 2021-02-19 26

27 0

28 0

29 0

30 0

31 0

32 2021-02-25 2021-02-25 32

33 2021-01-29 0

Worksheet: Sheet1

If you examine above my ( wrong) results in column T against Hans results in column V and
then look at the Debug / Immediate window info below for
before ( http://i.imgur.com/M3laahV.jpg )
and
after ( http://i.imgur.com/RUPIWIg.jpg ), where I take out the unwanted data from a text string , .._

Code:

? strtemp 2#0#0#5#6#0#0#9#0#0#12#0#0#0#0#0#0#19#20#0#22#23#0#0#26#0#0#0#0#0#32#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0 ? strtemp 2#5#6#9#12#19#222#23#26#32

_...then I can see the problem and where its coming from:

The problem is that I chose to remove the unwanted data
_ first by removing all #0 - that works fine, no problem with that as I am not expecting any real data starting with a 0
_ second I allow for the case of unwanted data at the start by removing all 0# - this can cause problems as it has in this example – It has resulted for example in this
#20#0#22
becoming this
#20#22
And then when after , (or previously) the 0# is removed/ was removed, the final result is
#222
So I loose the valid data of 20 and 22 and get a wrong data of 222 ( and in the test data, indicial 222 matches to an empty cell )
The final outcome is I loose two final date values and gain an extra unwanted empty ( nonsense date zero value ) date

There are thousands of easy ways to solve this problem , with various If Then ways. But these will “interrupt the flow” as it were, leading to inefficiency and prevent me building my final one line code way.

This first element problem is one I often refer to as 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. This wont quite for us in the case of this data, but almost.
The following variation seems OK
Consider these two lines, where the awkward bollock is dealt with second

Code:

' The next few lines get rid of the 0s Dim StrTemp As String: Let StrTemp = Join(arrTemp(), "#") ' Convert the array to a string with a # in between each data Let StrTemp = Replace(StrTemp, "#0", "", 2, -1, vbBinaryCompare): StrTemp = Replace(StrTemp, "0#", "", 1, -1, vbBinaryCompare) ' This effectively removes the 0s data ( and its seperator )

Solution:
I add some arbitrary character at the start
StrTemp = "_" & Join(arrTemp(), "#")
That wont add much extra overhead
Now deal with the awkward bollock first
StrTemp = Replace(StrTemp, "_0#", "_" ………..
That has done no extra work, just done an existing step a bit differently

So far nothing so clever. The next part allows us to do no, or little, extra work by taking advantage of a little known extra argument of the Replace
The forth (optional) argument of Replace lets us say at which character point in the original we start our returned string. That may confuse, so let me say that again with an example..
I have this xy-z-2 and I want this yz2
Most people would think they need
either
_ two Replaces , one to take out – and the other to take out x
or
_ a Replace to take out – and then some other process or function to take out the first character.

But if we choose 2 in our forth argument of the Replace that takes out the - , then our returned string will effectively have the first character removed.

Code:

' The next few lines get rid of the 0s Dim StrTemp As String: Let StrTemp = "_" & Join(arrTemp(), "#") ' Convert the array to a string with a # in between each data Let StrTemp = Replace(StrTemp, "_0#", "_", 1, 1, vbBinaryCompare): StrTemp = Replace(StrTemp, "#0", "", 2, -1, vbBinaryCompare) ' This effectiveely removes the 0s data ( and its seperator )

That seems to solve the problem
strTempAfterProblemSolved.JPG
http://i.imgur.com/Dgu8NE1.jpg

Full macro in next post

03-06-2021, 05:11 PM
DocAElstein

Full macro for last post

Code:

Sub Pretty3bbProbSolved() ' https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15426&viewfull=1#post15426 Dim arrTemp() As Variant Rem To get the results in column T ( same as ' Ths first forumula give 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 Dim StrTemp As String: Let StrTemp = "_" & Join(arrTemp(), "#") ' Convert the array to a string with a # in between each data Let StrTemp = Replace(StrTemp, "_0#", "_", 1, 1, vbBinaryCompare): StrTemp = Replace(StrTemp, "#0", "", 2, -1, vbBinaryCompare) ' This effectiveely removes the 0s data ( and its seperator ) Dim arrStrTemp() As String: Let arrStrTemp() = Split(StrTemp, "#", -1, vbBinaryCompare) ' remake the array ' Or Let arrStrTemp() = Split(Replace(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#", "_"), "#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 ' Or Let arrStrTemp() = Split(Replace(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#", "_"), "#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) ' 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() ' Or Range("T2").Resize(UBound(arrTemp(), 1), 1).ClearContents Let Range("T2").Resize(UBound(Application.Index(Worksheets("Sheet1").Columns(6), Application.Index(Split(Replace(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#", "_"), "#0", "", 2), "#", -1), Evaluate("=row(1:" & UBound(Split(Replace(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#", "_"), "#0", "", 2), "#", -1)) + 1 & ")/row(1:" & UBound(Split(Replace(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#", "_"), "#0", "", 2), "#", -1)) + 1 & ")"), Evaluate("=row(1:" & UBound(Split(Replace(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#", "_"), "#0", "", 2), "#", -1)) + 1 & ")")), 1), 1), 1).Value = _ Application.Index(Worksheets("Sheet1").Columns(6), Application.Index(Split(Replace(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#", "_"), "#0", "", 2), "#", -1), Evaluate("=row(1:" & UBound(Split(Replace(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#", "_"), "#0", "", 2), "#", -1)) + 1 & ")/row(1:" & UBound(Split(Replace(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#", "_"), "#0", "", 2), "#", -1)) + 1 & ")"), Evaluate("=row(1:" & UBound(Split(Replace(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#", "_"), "#0", "", 2), "#", -1)) + 1 & ")")), 1) End Sub
03-06-2021, 07:02 PM
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

Code:

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
03-07-2021, 03:59 PM
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. )

Code:

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 '
03-07-2021, 04:23 PM
DocAElstein

Post for later use
03-07-2021, 04:24 PM
DocAElstein

1 Attachment(s)

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
03-07-2021, 05:46 PM
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
03-07-2021, 05:47 PM
DocAElstein

1 Attachment(s)

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
03-07-2021, 05:49 PM
DocAElstein

1 Attachment(s)

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
03-08-2021, 12:53 AM
DocAElstein

1 Attachment(s)

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

1 Attachment(s)

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

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

03-30-2021, 01:14 PM
DocAElstein

pconlife.com

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
03-31-2021, 12:46 PM
DocAElstein

1 Attachment(s)

Windows XP Home Edition x32 Service Pack3:
5.1.2600.0 Download : https://www.pconlife.com/download/wi...134c2bb89727a/

FileVersionFile Md5File SizeFile BitFile
5.1.2600.0 (XPClient.010817-1148) 37b726c72699456bf34134c2bb89727a 8K 32bit

unpacked files in the following path:
• • C:\Windows\system32\

_ Share ‘5 1 2600 0 WINHLP32 EXE.zip’ https://app.box.com/s/tkb7lz4hprmvp2bczwjyj59k2n1tl1h6
_ ** Share ‘5 1 2600 0 WINHLP32 EXE.exe’ https://app.box.com/s/fb0xyzjh7v7oo1bf8hv5r6r986pxeuod
_ Share ‘5 1 2600 0 WINHLP32 EXE Re Zip.zip’ https://app.box.com/s/m9a9huq67rd9pac923nbf3p48ajfmaed

5.1.2600.5512 Download :

FileVersionFile Md5File SizeFile BitFile
5.1.2600.5512 (xpsp.080413-0852) 65a9495a436f5402bc1c467e1b926c27 277K 32bit

unpacked files path:
• • C:\Windows\system32\dllcache\
• • C:\Windows\

_ Share ‘5 1 2600 5512 WINHLP32 EXE.zip’ https://app.box.com/s/tkb7lz4hprmvp2bczwjyj59k2n1tl1h6
_ ** Share ‘5 1 2600 5512 WINHLP32 EXE.exe’ https://app.box.com/s/rdrrs69mpimt2rh2usf5egr3yvadbizr
_ Share ‘5 1 2600 5512 WINHLP32 EXE Re Zip.zip’ https://app.box.com/s/3w2evt1rlq75j1rjfui6bx8qohmros9c

(** These are typical warnings that are shown after a 7.zip unzipping:
http://i.imgur.com/Zg2ZWAq.jpg
http://i.imgur.com/9r2rBVa.jpg Attachment 3553

Here are the final files that I have. I changed the names slightly to help distinguish between different winhlpexe files for different operating systems
http://i.imgur.com/HpEGeig.jpg
http://i.imgur.com/x00l1dj.jpg
04-07-2021, 01:06 PM
DocAElstein

In support of this Thread
https://www.eileenslounge.com/viewtopic.php?f=30&t=36380

Excel 2003

Code:

16777215 $B$14 16777215 $C$14 16777215 $D$14 16777215 $E$14 16777215 $F$14 16777215 $G$14 16777215 $H$14 16777215 $A$15 16777215 $B$15 16777215 $C$15 16777215 $D$15 16777215 $E$15 16777215 $F$15 16777215 $G$15 16777215 $H$15 16777215 $A$16 16777215 $B$16 16777215 $C$16 16777215 $D$16 16777215 $E$16 16777215 $F$16 16777215 $G$16 16777215 $H$16 16777215 $A$17 16777215 $B$17 16777215 $C$17 16777215 $D$17 16764057 $E$17 16777215 $F$17 16777215 $G$17 16777215 $H$17 16777215 $A$18 16777215 $B$18 16777215 $C$18 16777215 $D$18 16777215 $E$18 16777215 $F$18 16777215 $G$18 16777215 $H$18 16777215 $A$19 16777215 $B$19 16777215 $C$19 16777215 $D$19 16777215 $E$19 16777215 $F$19 16777215 $G$19 16777215 $H$19 16777215 $A$20 16777215 $B$20 16777215 $C$20 16777215 $D$20 16777215 $E$20 16777215 $F$20 16777215 $G$20 16777215 $H$20 16777215 $A$21 16777215 $B$21 16777215 $C$21 16777215 $D$21 16777215 $E$21 16777215 $F$21 16777215 $G$21 16777215 $H$21 16777215 $A$22 16777215 $B$22 16777215 $C$22 16777215 $D$22 16777215 $E$22 16777215 $F$22 16777215 $G$22 16777215 $H$22 16777215 $A$23 16777215 $B$23 16777215 $C$23 16777215 $D$23 16777215 $E$23 16777215 $F$23 16777215 $G$23 16777215 $H$23 16777215 $A$24 16777215 $B$24 16777215 $C$24 16777215 $D$24 16777215 $E$24 16777215 $F$24 16777215 $G$24 16777215 $H$24 16777215 $A$25 16777215 $B$25 16777215 $C$25 16777215 $D$25 16777215 $E$25 16777215 $F$25 16777215 $G$25 16777215 $H$25 16777215 $A$26 16777215 $B$26 16777215 $C$26 16777215 $D$26 16777215 $E$26 16777215 $F$26 16777215 $G$26 16777215 $H$26 16777215 $A$27 16777215 $B$27 16777215 $C$27 16777215 $D$27 16777215 $E$27 16777215 $F$27 16777215 $G$27 16777215 $H$27 16777215 $A$28 16777215 $B$28 16777215 $C$28 16777215 $D$28 16777215 $E$28 16777215 $F$28 16777215 $G$28 16777215 $H$28 16777215 $A$29 16777215 $B$29 16777215 $C$29 16777215 $D$29 16777215 $E$29 16777215 $F$29 16777215 $G$29 16777215 $H$29 16777215 $A$30 16777215 $B$30 16777215 $C$30 16777215 $D$30 16777215 $E$30 16777215 $F$30 16777215 $G$30 16777215 $H$30 16777215 $A$31 16777215 $B$31 16777215 $C$31 16777215 $D$31 16777215 $E$31 16777215 $F$31 16777215 $G$31 16777215 $H$31 16777215 $A$32 16777215 $B$32 16777215 $C$32 16777215 $D$32 16777215 $E$32 16777215 $F$32 16777215 $G$32 16777215 $H$32 16777215 $A$33 16777215 $B$33 16777215 $C$33 16777215 $D$33 16777215 $E$33 16777215 $F$33 16777215 $G$33 16777215 $H$33 16777215 $A$34 16777215 $B$34 16777215 $C$34 16763904 $D$34 16777215 $E$34 16763904 $F$34 16777215 $G$34 16777215 $H$34 16777215 $A$35 16777215 $B$35 16777215 $C$35 65535 $D$35 16777215 $E$35 52377 $F$35 16777215 $G$35 16777215 $H$35 16777215 $A$36 16777215 $B$36 16777215 $C$36 65535 $D$36 16777215 $E$36 65535 $F$36 16777215 $G$36 16777215 $H$36 16777215 $A$37 16777215 $B$37 16777215 $C$37 52377 $D$37 16777215 $E$37 52377 $F$37 16777215 $G$37 16777215 $H$37 16777215 $A$38 16777215 $B$38 16777215 $C$38 65535 $D$38 16777215 $E$38 52377 $F$38 16777215 $G$38 16777215 $H$38

Code:

16777215 $B$14 16777215 $C$14 16777215 $D$14 16777215 $E$14 16777215 $F$14 16777215 $G$14 16777215 $H$14 16777215 $A$15 16777215 $B$15 16777215 $C$15 16777215 $D$15 16777215 $E$15 16777215 $F$15 16777215 $G$15 16777215 $H$15 16777215 $A$16 16777215 $B$16 16777215 $C$16 16777215 $D$16 16777215 $E$16 16777215 $F$16 16777215 $G$16 16777215 $H$16 16777215 $A$17 16777215 $B$17 16777215 $C$17 16777215 $D$17 16764057 $E$17 16777215 $F$17 16777215 $G$17 16777215 $H$17 16777215 $A$18 16777215 $B$18 16777215 $C$18 16777215 $D$18 16777215 $E$18 16777215 $F$18 16777215 $G$18 16777215 $H$18 16777215 $A$19 16777215 $B$19 16777215 $C$19 16777215 $D$19 16777215 $E$19 16777215 $F$19 16777215 $G$19 16777215 $H$19 16777215 $A$20 16777215 $B$20 16777215 $C$20 16777215 $D$20 16777215 $E$20 16777215 $F$20 16777215 $G$20 16777215 $H$20 16777215 $A$21 16777215 $B$21 16777215 $C$21 16777215 $D$21 16777215 $E$21 16777215 $F$21 16777215 $G$21 16777215 $H$21 16777215 $A$22 16777215 $B$22 16777215 $C$22 16777215 $D$22 16777215 $E$22 16777215 $F$22 16777215 $G$22 16777215 $H$22 16777215 $A$23 16777215 $B$23 16777215 $C$23 16777215 $D$23 16777215 $E$23 16777215 $F$23 16777215 $G$23 16777215 $H$23 16777215 $A$24 16777215 $B$24 16777215 $C$24 16777215 $D$24 16777215 $E$24 16777215 $F$24 16777215 $G$24 16777215 $H$24 16777215 $A$25 16777215 $B$25 16777215 $C$25 16777215 $D$25 16777215 $E$25 16777215 $F$25 16777215 $G$25 16777215 $H$25 16777215 $A$26 16777215 $B$26 16777215 $C$26 16777215 $D$26 16777215 $E$26 16777215 $F$26 16777215 $G$26 16777215 $H$26 16777215 $A$27 16777215 $B$27 16777215 $C$27 16777215 $D$27 16777215 $E$27 16777215 $F$27 16777215 $G$27 16777215 $H$27 16777215 $A$28 16777215 $B$28 16777215 $C$28 16777215 $D$28 16777215 $E$28 16777215 $F$28 16777215 $G$28 16777215 $H$28 16777215 $A$29 16777215 $B$29 16777215 $C$29 16777215 $D$29 16777215 $E$29 16777215 $F$29 16777215 $G$29 16777215 $H$29 16777215 $A$30 16777215 $B$30 16777215 $C$30 16777215 $D$30 16777215 $E$30 16777215 $F$30 16777215 $G$30 16777215 $H$30 16777215 $A$31 16777215 $B$31 16777215 $C$31 16777215 $D$31 16777215 $E$31 16777215 $F$31 16777215 $G$31 16777215 $H$31 16777215 $A$32 16777215 $B$32 16777215 $C$32 16777215 $D$32 16777215 $E$32 16777215 $F$32 16777215 $G$32 16777215 $H$32 16777215 $A$33 16777215 $B$33 16777215 $C$33 16777215 $D$33 16777215 $E$33 16777215 $F$33 16777215 $G$33 16777215 $H$33 16777215 $A$34 16777215 $B$34 16777215 $C$34 16763904 $D$34 16777215 $E$34 16763904 $F$34 16777215 $G$34 16777215 $H$34 16777215 $A$35 16777215 $B$35 16777215 $C$35 65535 $D$35 16777215 $E$35 52377 $F$35 16777215 $G$35 16777215 $H$35 16777215 $A$36 16777215 $B$36 16777215 $C$36 65535 $D$36 16777215 $E$36 65535 $F$36 16777215 $G$36 16777215 $H$36 16777215 $A$37 16777215 $B$37 16777215 $C$37 52377 $D$37 16777215 $E$37 52377 $F$37 16777215 $G$37 16777215 $H$37 16777215 $A$38 16777215 $B$38 16777215 $C$38 65535 $D$38 16777215 $E$38 52377 $F$38 16777215 $G$38 16777215 $H$38

Code:

16777215 $B$14 16777215 $C$14 16777215 $D$14 16777215 $E$14 16777215 $F$14 16777215 $G$14 16777215 $H$14 16777215 $A$15 16777215 $B$15 16777215 $C$15 16777215 $D$15 16777215 $E$15 16777215 $F$15 16777215 $G$15 16777215 $H$15 16777215 $A$16 16777215 $B$16 16777215 $C$16 16777215 $D$16 16777215 $E$16 16777215 $F$16 16777215 $G$16 16777215 $H$16 16777215 $A$17 16777215 $B$17 16777215 $C$17 16777215 $D$17 16764057 $E$17 16777215 $F$17 16777215 $G$17 16777215 $H$17 16777215 $A$18 16777215 $B$18 16777215 $C$18 16777215 $D$18 16777215 $E$18 16777215 $F$18 16777215 $G$18 16777215 $H$18 16777215 $A$19 16777215 $B$19 16777215 $C$19 16777215 $D$19 16777215 $E$19 16777215 $F$19 16777215 $G$19 16777215 $H$19 16777215 $A$20 16777215 $B$20 16777215 $C$20 16777215 $D$20 16777215 $E$20 16777215 $F$20 16777215 $G$20 16777215 $H$20 16777215 $A$21 16777215 $B$21 16777215 $C$21 16777215 $D$21 16777215 $E$21 16777215 $F$21 16777215 $G$21 16777215 $H$21 16777215 $A$22 16777215 $B$22 16777215 $C$22 16777215 $D$22 16777215 $E$22 16777215 $F$22 16777215 $G$22 16777215 $H$22 16777215 $A$23 16777215 $B$23 16777215 $C$23 16777215 $D$23 16777215 $E$23 16777215 $F$23 16777215 $G$23 16777215 $H$23 16777215 $A$24 16777215 $B$24 16777215 $C$24 16777215 $D$24 16777215 $E$24 16777215 $F$24 16777215 $G$24 16777215 $H$24 16777215 $A$25 16777215 $B$25 16777215 $C$25 16777215 $D$25 16777215 $E$25 16777215 $F$25 16777215 $G$25 16777215 $H$25 16777215 $A$26 16777215 $B$26 16777215 $C$26 16777215 $D$26 16777215 $E$26 16777215 $F$26 16777215 $G$26 16777215 $H$26 16777215 $A$27 16777215 $B$27 16777215 $C$27 16777215 $D$27 16777215 $E$27 16777215 $F$27 16777215 $G$27 16777215 $H$27 16777215 $A$28 16777215 $B$28 16777215 $C$28 16777215 $D$28 16777215 $E$28 16777215 $F$28 16777215 $G$28 16777215 $H$28 16777215 $A$29 16777215 $B$29 16777215 $C$29 16777215 $D$29 16777215 $E$29 16777215 $F$29 16777215 $G$29 16777215 $H$29 16777215 $A$30 16777215 $B$30 16777215 $C$30 16777215 $D$30 16777215 $E$30 16777215 $F$30 16777215 $G$30 16777215 $H$30 16777215 $A$31 16777215 $B$31 16777215 $C$31 16777215 $D$31 16777215 $E$31 16777215 $F$31 16777215 $G$31 16777215 $H$31 16777215 $A$32 16777215 $B$32 16777215 $C$32 16777215 $D$32 16777215 $E$32 16777215 $F$32 16777215 $G$32 16777215 $H$32 16777215 $A$33 16777215 $B$33 16777215 $C$33 16777215 $D$33 16777215 $E$33 16777215 $F$33 16777215 $G$33 16777215 $H$33 16777215 $A$34 16777215 $B$34 16777215 $C$34 16763904 $D$34 16777215 $E$34 16763904 $F$34 16777215 $G$34 16777215 $H$34 16777215 $A$35 16777215 $B$35 16777215 $C$35 65535 $D$35 16777215 $E$35 52377 $F$35 16777215 $G$35 16777215 $H$35 16777215 $A$36 16777215 $B$36 16777215 $C$36 65535 $D$36 16777215 $E$36 65535 $F$36 16777215 $G$36 16777215 $H$36 16777215 $A$37 16777215 $B$37 16777215 $C$37 52377 $D$37 16777215 $E$37 52377 $F$37 16777215 $G$37 16777215 $H$37 16777215 $A$38 16777215 $B$38 16777215 $C$38 65535 $D$38 16777215 $E$38 52377 $F$38 16777215 $G$38 16777215 $H$38

The above .xls file in 2010

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
04-07-2021, 01:34 PM
DocAElstein

2007

in support of this Thread post
https://www.eileenslounge.com/viewtopic.php?p=282274#p282274

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 15261110 $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

In one 2007 no interior color is shown ( there are errors in opening the .xlsb file ). For this same file saved as .xls I get in that 2007 this:

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 16777215 $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 16777215 $D$34 16777215 $E$34 16777215 $F$34 16777215 $G$34 16777215 $H$34 16777215 $A$35 16777215 $B$35 16777215 $C$35 16777215 $D$35 16777215 $E$35 16777215 $F$35 16777215 $G$35 16777215 $H$35 16777215 $A$36 16777215 $B$36 16777215 $C$36 16777215 $D$36 16777215 $E$36 16777215 $F$36 16777215 $G$36 16777215 $H$36 16777215 $A$37 16777215 $B$37 16777215 $C$37 16777215 $D$37 16777215 $E$37 16777215 $F$37 16777215 $G$37 16777215 $H$37 16777215 $A$38 16777215 $B$38 16777215 $C$38 16777215 $D$38 16777215 $E$38 16777215 $F$38 16777215 $G$38 16777215 $H$38

Code:

16777215 $B$14 16777215 $C$14 16777215 $D$14 16777215 $E$14 16777215 $F$14 16777215 $G$14 16777215 $H$14 16777215 $A$15 16777215 $B$15 16777215 $C$15 16777215 $D$15 16777215 $E$15 16777215 $F$15 16777215 $G$15 16777215 $H$15 16777215 $A$16 16777215 $B$16 16777215 $C$16 16777215 $D$16 16777215 $E$16 16777215 $F$16 16777215 $G$16 16777215 $H$16 16777215 $A$17 16777215 $B$17 16777215 $C$17 16777215 $D$17 15261110 $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

The above Excel with the .xls file version

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 15261110 $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
04-07-2021, 01:38 PM
DocAElstein

2010

In support of this post
https://www.eileenslounge.com/viewtopic.php?p=282275#p282275

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 ****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

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 ****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

In the above 2010 the following is from a .xls version of the file

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
04-07-2021, 05:46 PM
DocAElstein

365

From
https://eileenslounge.com/viewtopic....282284#p282284

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 **** 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
04-07-2021, 08:22 PM
DocAElstein

XP 2010

Info from
https://eileenslounge.com/viewtopic....282295#p282295
https://eileenslounge.com/viewtopic....282297#p282297

hassona229

Code:

Excel 2010 32 bit 16777215 $A$1 16777215 $B$1 16777215 $C$1 16777215 $D$1 16777215 $E$1 16777215 $F$1 16777215 $G$1 16777215 $H$1 16777215 $A$2 16777215 $B$2 16777215 $C$2 16777215 $D$2 16777215 $E$2 16777215 $F$2 16777215 $G$2 16777215 $H$2 16777215 $A$3 16777215 $B$3 16777215 $C$3 16777215 $D$3 16777215 $E$3 16777215 $F$3 16777215 $G$3 16777215 $H$3 16777215 $A$4 16777215 $B$4 16777215 $C$4 16777215 $D$4 **** 15261367 ****** $E$4 16777215 $F$4 16777215 $G$4 16777215 $H$4 16777215 $A$5 16777215 $B$5 16777215 $C$5 16777215 $D$5 16777215 $E$5 16777215 $F$5 16777215 $G$5 16777215 $H$5 16777215 $A$6 16777215 $B$6 16777215 $C$6 16777215 $D$6 16777215 $E$6 16777215 $F$6 16777215 $G$6 16777215 $H$6 16777215 $A$7 16777215 $B$7 16777215 $C$7 16777215 $D$7 16777215 $E$7 16777215 $F$7 16777215 $G$7 16777215 $H$7 16777215 $A$8 16777215 $B$8 16777215 $C$8 16777215 $D$8 16777215 $E$8 16777215 $F$8 16777215 $G$8 16777215 $H$8 16777215 $A$9 16777215 $B$9 16777215 $C$9 16777215 $D$9 16777215 $E$9 16777215 $F$9 16777215 $G$9 16777215 $H$9 16777215 $A$10 16777215 $B$10 16777215 $C$10 16777215 $D$10 16777215 $E$10 16777215 $F$10 16777215 $G$10 16777215 $H$10 16777215 $A$11 16777215 $B$11 16777215 $C$11 16777215 $D$11 16777215 $E$11 16777215 $F$11 16777215 $G$11 16777215 $H$11 16777215 $A$12 16777215 $B$12 16777215 $C$12 16777215 $D$12 16777215 $E$12 16777215 $F$12 16777215 $G$12 16777215 $H$12 16777215 $A$13 16777215 $B$13 16777215 $C$13 16777215 $D$13 16777215 $E$13 16777215 $F$13 16777215 $G$13 16777215 $H$13 16777215 $A$14 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 **** 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

Yasser's friend

Code:

Excel 2010 32 bit 16777215 $A$1 16777215 $B$1 16777215 $C$1 16777215 $D$1 16777215 $E$1 16777215 $F$1 16777215 $G$1 16777215 $H$1 16777215 $A$2 16777215 $B$2 16777215 $C$2 16777215 $D$2 16777215 $E$2 16777215 $F$2 16777215 $G$2 16777215 $H$2 16777215 $A$3 16777215 $B$3 16777215 $C$3 16777215 $D$3 16777215 $E$3 16777215 $F$3 16777215 $G$3 16777215 $H$3 16777215 $A$4 16777215 $B$4 16777215 $C$4 16777215 $D$4 **** 15261367 ****** $E$4 16777215 $F$4 16777215 $G$4 16777215 $H$4 16777215 $A$5 16777215 $B$5 16777215 $C$5 16777215 $D$5 16777215 $E$5 16777215 $F$5 16777215 $G$5 16777215 $H$5 16777215 $A$6 16777215 $B$6 16777215 $C$6 16777215 $D$6 16777215 $E$6 16777215 $F$6 16777215 $G$6 16777215 $H$6 16777215 $A$7 16777215 $B$7 16777215 $C$7 16777215 $D$7 16777215 $E$7 16777215 $F$7 16777215 $G$7 16777215 $H$7 16777215 $A$8 16777215 $B$8 16777215 $C$8 16777215 $D$8 16777215 $E$8 16777215 $F$8 16777215 $G$8 16777215 $H$8 16777215 $A$9 16777215 $B$9 16777215 $C$9 16777215 $D$9 16777215 $E$9 16777215 $F$9 16777215 $G$9 16777215 $H$9 16777215 $A$10 16777215 $B$10 16777215 $C$10 16777215 $D$10 16777215 $E$10 16777215 $F$10 16777215 $G$10 16777215 $H$10 16777215 $A$11 16777215 $B$11 16777215 $C$11 16777215 $D$11 16777215 $E$11 16777215 $F$11 16777215 $G$11 16777215 $H$11 16777215 $A$12 16777215 $B$12 16777215 $C$12 16777215 $D$12 16777215 $E$12 16777215 $F$12 16777215 $G$12 16777215 $H$12 16777215 $A$13 16777215 $B$13 16777215 $C$13 16777215 $D$13 16777215 $E$13 16777215 $F$13 16777215 $G$13 16777215 $H$13 16777215 $A$14 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 **** 15261367 ****** $E$17 16777215 $F$17 16777215 $G$17 16777215 $H$17 16777215 $A$18 16777215 $B$18 16777215 $C$18

One of my XP 2010 machines

Code:

Excel 2010 32 bit 16777215 $A$1 16777215 $B$1 16777215 $C$1 16777215 $D$1 16777215 $E$1 16777215 $F$1 16777215 $G$1 16777215 $H$1 16777215 $A$2 16777215 $B$2 16777215 $C$2 16777215 $D$2 16777215 $E$2 16777215 $F$2 16777215 $G$2 16777215 $H$2 16777215 $A$3 16777215 $B$3 16777215 $C$3 16777215 $D$3 16777215 $E$3 16777215 $F$3 16777215 $G$3 16777215 $H$3 16777215 $A$4 16777215 $B$4 16777215 $C$4 16777215 $D$4 **** 15261367 ****** $E$4 16777215 $F$4 16777215 $G$4 16777215 $H$4 16777215 $A$5 16777215 $B$5 16777215 $C$5 16777215 $D$5 16777215 $E$5 16777215 $F$5 16777215 $G$5 16777215 $H$5 16777215 $A$6 16777215 $B$6 16777215 $C$6 16777215 $D$6 16777215 $E$6 16777215 $F$6 16777215 $G$6 16777215 $H$6 16777215 $A$7 16777215 $B$7 16777215 $C$7 16777215 $D$7 16777215 $E$7 16777215 $F$7 16777215 $G$7 16777215 $H$7 16777215 $A$8 16777215 $B$8 16777215 $C$8 16777215 $D$8 16777215 $E$8 16777215 $F$8 16777215 $G$8 16777215 $H$8 16777215 $A$9 16777215 $B$9 16777215 $C$9 16777215 $D$9 16777215 $E$9 16777215 $F$9 16777215 $G$9 16777215 $H$9 16777215 $A$10 16777215 $B$10 16777215 $C$10 16777215 $D$10 16777215 $E$10 16777215 $F$10 16777215 $G$10 16777215 $H$10 16777215 $A$11 16777215 $B$11 16777215 $C$11 16777215 $D$11 16777215 $E$11 16777215 $F$11 16777215 $G$11 16777215 $H$11 16777215 $A$12 16777215 $B$12 16777215 $C$12 16777215 $D$12 16777215 $E$12 16777215 $F$12 16777215 $G$12 16777215 $H$12 16777215 $A$13 16777215 $B$13 16777215 $C$13 16777215 $D$13 16777215 $E$13 16777215 $F$13 16777215 $G$13 16777215 $H$13 16777215 $A$14 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 **** 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
04-12-2021, 05:03 PM
DocAElstein

post for later use
04-12-2021, 05:07 PM
DocAElstein

Some extra macros for this Thread
https://eileenslounge.com/viewtopic.php?f=27&t=36401
post
https://eileenslounge.com/viewtopic....282498#p282498

Code:

Option Explicit Sub VergeltungswaffeV1V2() ' http://www.eileenslounge.com/viewtopic.php?f=27&t=36401 Dim Ar As Long, Em As Long Let Em = Range("A" & Rows.Count).End(xlUp).Row Dim A() As Variant: Let A() = Range("A1:A" & Em & "").Value2 ' The main data from column 1 Dim V1() As Variant, V2() As Variant: ReDim V1(1 To Em): ReDim V2(1 To Em) ' These will need to be variant because each element is itself an array ( a 1 D array ) For Ar = 1 To Em ' The main data rows range Let V1(Ar) = Split(A(Ar, 1), " ", 2, vbBinaryCompare) ' I am splitting each data into 2 bits, the first is the data ID the second is all the rest Let V2(Ar) = Split(StrReverse(V1(Ar)(1)), " ", 6, vbBinaryCompare) ' We are splitting the reversed string, because my second data CITY might have a few words, I split the backward string in just enough bits so that the last element is the data CITY regardles of how many words are in it Let V2(Ar)(0) = StrReverse(V2(Ar)(0)): V2(Ar)(1) = StrReverse(V2(Ar)(1)): V2(Ar)(2) = StrReverse(V2(Ar)(2)): V2(Ar)(3) = StrReverse(V2(Ar)(3)): V2(Ar)(4) = StrReverse(V2(Ar)(4)): V2(Ar)(5) = StrReverse(V2(Ar)(5)) ' The problem with the last line is that all my data words and data numbers are in reverse , so I need to put each data back the correct way around Next Ar ' The end result of the above is that we have two 1 D arrays, V1() and V2(). Each element is itself a 1 D array. We find that strangely that INDEX seems to treat such arrays as like 2 D arrays as long as all the 1 D array elements have the same number of elements. This allows us to use the Index(arr(), Rws(), Clms()) way to get out our final range in any order we like. Let Range("B2:B" & Em + 1 & "").Value = Application.Index(V1(), Evaluate("=Row(1:" & Em & ")"), Array(1)) ' We only want the first column from V1() Let Range("C2:H" & Em + 1 & "").Value = Application.Index(V2(), Evaluate("=Row(1:" & Em & ")"), Array(6, 5, 4, 3, 2, 1)) ' We want all the data columns from V2() but need them in the reverse order because we split the reversed string Range("A1:H1").EntireColumn.AutoFit End Sub Sub VergeltungswaffeV1V2_() Dim Ar As Long, Em As Long Let Em = Range("A" & Rows.Count).End(xlUp).Row Dim A() As Variant: Let A() = Range("A1:A" & Em & "").Value2 ' The main data from column 1 Dim V1() As Variant, V2() As Variant: ReDim V1(1 To Em): ReDim V2(1 To Em) ' These will need to be variant because each element is itself an array ( a 1 D array ) For Ar = 1 To Em ' The main data rows range Let V1(Ar) = Split(A(Ar, 1), " ", 2, vbBinaryCompare) ' I am splitting each data into 2 bits, the first is the data ID the second is all the rest Let V2(Ar) = Split(StrReverse(V1(Ar)(1)), " ", 6, vbBinaryCompare) ' We are splitting the reversed string, because my second data CITY might have a few words, I split the backward string in just enough bits so that the last element is the data CITY regardles of how many words are in it Let V2(Ar) = Array(StrReverse(V2(Ar)(0)), StrReverse(V2(Ar)(1)), StrReverse(V2(Ar)(2)), StrReverse(V2(Ar)(3)), StrReverse(V2(Ar)(4)), StrReverse(V2(Ar)(5))) ' The problem with the last line is that all my data words and data numbers are in reverse , so I need to put each data back the correct way around Next Ar ' The end result of the above is that we have two 1 D arrays, V1() and V2(). Each element is itself a 1 D array. We find that strangely that INDEX seems to treat such arrays as like 2 D arrays as long as all the 1 D array elements have the same number of elements. This allows us to use the Index(arr(), Rws(), Clms()) way to get out our final range in any order we like. Let Range("B2:B" & Em + 1 & "").Value = Application.Index(V1(), Evaluate("=Row(1:" & Em & ")"), Array(1)) ' We only want the first column from V1() Let Range("C2:H" & Em + 1 & "").Value = Application.Index(V2(), Evaluate("=Row(1:" & Em & ")"), Array(6, 5, 4, 3, 2, 1)) ' We want all the data columns from V2() but need them in the reverse order because we split the reversed string Range("A1:H1").EntireColumn.AutoFit End Sub Sub VergeltungswaffeV1V2__() Dim Ar As Long, Em As Long Let Em = Range("A" & Rows.Count).End(xlUp).Row Dim A() As Variant: Let A() = Range("A1:A" & Em & "").Value2 ' The main data from column 1 Dim V1() As Variant, V2() As Variant: ReDim V1(1 To Em): ReDim V2(1 To Em) ' These will need to be variant because each element is itself an array ( a 1 D array ) For Ar = 1 To Em ' The main data rows range Let V1(Ar) = Split(A(Ar, 1), " ", 2, vbBinaryCompare) ' I am splitting each data into 2 bits, the first is the data ID the second is all the rest Let V2(Ar) = Split(StrReverse(V1(Ar)(1)), " ", 6, vbBinaryCompare) ' We are splitting the reversed string, because my second data CITY might have a few words, I split the backward string in just enough bits so that the last element is the data CITY regardles of how many words are in it Let V2(Ar) = Array(StrReverse(V2(Ar)(5)), StrReverse(V2(Ar)(4)), StrReverse(V2(Ar)(3)), StrReverse(V2(Ar)(2)), StrReverse(V2(Ar)(1)), StrReverse(V2(Ar)(0))) ' The problem with the last line is that all my data words and data numbers are in reverse , so I need to put each data back the correct way around Next Ar ' The end result of the above is that we have two 1 D arrays, V1() and V2(). Each element is itself a 1 D array. We find that strangely that INDEX seems to treat such arrays as like 2 D arrays as long as all the 1 D array elements have the same number of elements. This allows us to use the Index(arr(), Rws(), Clms()) way to get out our final range in any order we like. Let Range("B2:B" & Em + 1 & "").Value = Application.Index(V1(), Evaluate("=Row(1:" & Em & ")"), Array(1)) ' We only want the first column from V1() Let Range("C2:H" & Em + 1 & "").Value = Application.Index(V2(), Evaluate("=Row(1:" & Em & ")"), Array(1, 2, 3, 4, 5, 6)) ' We want all the data columns from V2() but need them in the reverse order because we split the reversed string Range("A1:H1").EntireColumn.AutoFit End Sub
04-12-2021, 05:10 PM
DocAElstein

Some extra macros for this Thread
https://eileenslounge.com/viewtopic.php?f=27&t=36401
post
https://eileenslounge.com/viewtopic....282498#p282498

Code:

Option Explicit Sub Dik1Dik2_() ' Dim Ar As Long, Em As Long Let Em = Range("A" & Rows.Count).End(xlUp).Row Dim A() As Variant: Let A() = Range("A1:A" & Em & "").Value2 ' The main data from column 1 Dim Dik1 As Object, Dik2 As Object: Set Dik1 = CreateObject("Scripting.Dictionary"): Set Dik2 = CreateObject("Scripting.Dictionary") For Ar = 1 To Em ' The main data rows range Let Dik1(Ar) = Split(A(Ar, 1), " ", 2, vbBinaryCompare) ' I am splitting each data into 2 bits, the first is the data ID the second is all the rest Let Dik2(Ar) = Split(StrReverse(Dik1(Ar)(1)), " ", 6, vbBinaryCompare) ' We are splitting the reversed string, because my second data CITY might have a few words, I split the backward string in just enough bits so that the last element is the data CITY regardles of how many words are in it Let Dik2(Ar) = Array(StrReverse(Dik2(Ar)(0)), StrReverse(Dik2(Ar)(1)), StrReverse(Dik2(Ar)(2)), StrReverse(Dik2(Ar)(3)), StrReverse(Dik2(Ar)(4)), StrReverse(Dik2(Ar)(5))) ' The problem with the last line is that all my data words and data numbers are in reverse , so I need to put each data back the correct way around Next Ar Dim v: v = Dik1.items() ' The end result of the above is that we have two 1 D arrays in the items of the dictionaries, one in each. Each element is itself a 1 D array. We find that strangely that INDEX seems to treat such arrays as like 2 D arrays as long as all the 1 D array elements have the same number of elements. This allows us to use the Index(arr(), Rws(), Clms()) way to get out our final range in any order we like. Let Range("B2:B" & Em + 1 & "").Value = Application.Index(Dik1.items(), Evaluate("=Row(1:" & Em & ")"), Array(1)) ' We only want the first column from V1() Let Range("C2:H" & Em + 1 & "").Value = Application.Index(Dik2.items, Evaluate("=Row(1:" & Em & ")"), Array(6, 5, 4, 3, 2, 1)) ' We want all the data columns from V2() but need them in the reverse order because we split the reversed string Range("A1:H1").EntireColumn.AutoFit End Sub Sub Dik1Dik2__() ' Dim Ar As Long, Em As Long Let Em = Range("A" & Rows.Count).End(xlUp).Row Dim A() As Variant: Let A() = Range("A1:A" & Em & "").Value2 ' The main data from column 1 Dim Dik1 As Object, Dik2 As Object: Set Dik1 = CreateObject("Scripting.Dictionary"): Set Dik2 = CreateObject("Scripting.Dictionary") For Ar = 1 To Em ' The main data rows range Let Dik1(Ar) = Split(A(Ar, 1), " ", 2, vbBinaryCompare) ' I am splitting each data into 2 bits, the first is the data ID the second is all the rest Let Dik2(Ar) = Split(StrReverse(Dik1(Ar)(1)), " ", 6, vbBinaryCompare) ' We are splitting the reversed string, because my second data CITY might have a few words, I split the backward string in just enough bits so that the last element is the data CITY regardles of how many words are in it Let Dik2(Ar) = Array(StrReverse(Dik2(Ar)(5)), StrReverse(Dik2(Ar)(4)), StrReverse(Dik2(Ar)(3)), StrReverse(Dik2(Ar)(2)), StrReverse(Dik2(Ar)(1)), StrReverse(Dik2(Ar)(0))) ' The problem with the last line is that all my data words and data numbers are in reverse , so I need to put each data back the correct way around Next Ar Dim v: v = Dik1.items() ' The end result of the above is that we have two 1 D arrays in the items of the dictionaries, one in each. Each element is itself a 1 D array. We find that strangely that INDEX seems to treat such arrays as like 2 D arrays as long as all the 1 D array elements have the same number of elements. This allows us to use the Index(arr(), Rws(), Clms()) way to get out our final range in any order we like. Let Range("B2:B" & Em + 1 & "").Value = Application.Index(Dik1.items(), Evaluate("=Row(1:" & Em & ")"), Array(1)) ' We only want the first column from V1() Let Range("C2:H" & Em + 1 & "").Value = Application.Index(Dik2.items, Evaluate("=Row(1:" & Em & ")"), Array(1, 2, 3, 4, 5, 6)) ' We want all the data columns from V2() but need them in the reverse order because we split the reversed string Range("A1:H1").EntireColumn.AutoFit End Sub
04-12-2021, 05:12 PM
DocAElstein

An extra macro for this Thread
https://eileenslounge.com/viewtopic.php?f=27&t=36401
post
https://eileenslounge.com/viewtopic....282498#p282498

Code:

Option Explicit Sub AL1AL2__() ' Dim Ar As Long, Em As Long Let Em = Range("A" & Rows.Count).End(xlUp).Row Dim A() As Variant: Let A() = Range("A1:A" & Em & "").Value2 ' The main data from column 1 Dim AL1 As Object, AL2 As Object: Set AL1 = CreateObject("System.Collections.ArrayList"): Set AL2 = CreateObject("System.Collections.ArrayList") For Ar = 1 To Em ' The main data rows range AL1.Add Split(A(Ar, 1), " ", 2, vbBinaryCompare) ' I am splitting each data into 2 bits, the first is the data ID the second is all the rest AL2.Add Split(StrReverse(AL1.Item(Ar - 1)(1)), " ", 6, vbBinaryCompare) ' We are splitting the reversed string, because my second data CITY might have a few words, I split the backward string in just enough bits so that the last element is the data CITY regardles of how many words are in it Let AL2.Item(Ar - 1) = Array(StrReverse(AL2.Item(Ar - 1)(5)), StrReverse(AL2.Item(Ar - 1)(4)), StrReverse(AL2.Item(Ar - 1)(3)), StrReverse(AL2.Item(Ar - 1)(2)), StrReverse(AL2.Item(Ar - 1)(1)), StrReverse(AL2.Item(Ar - 1)(0))) ' The problem with the last line is that all my data words and data numbers are in reverse , so I need to put each data back the correct way around Next Ar ' The end result of the above is that we have two 1 D arrays in the Array Lists, one in each. Each element is itself a 1 D array. We find that strangely that INDEX seems to treat such arrays as like 2 D arrays as long as all the 1 D array elements have the same number of elements. This allows us to use the Index(arr(), Rws(), Clms()) way to get out our final range in any order we like. Let Range("B2:B" & Em + 1 & "").Value = Application.Index(AL1.toarray(), Evaluate("=Row(1:" & Em & ")"), Array(1)) ' We only want the first column from V1() Let Range("C2:H" & Em + 1 & "").Value = Application.Index(AL2.toarray(), Evaluate("=Row(1:" & Em & ")"), Array(1, 2, 3, 4, 5, 6)) ' We want all the data columns from V2() but need them in the reverse order because we split the reversed string Range("A1:H1").EntireColumn.AutoFit End Sub
04-23-2021, 09:41 AM
DocAElstein

1 Attachment(s)

Some extra notes for the solution to this Thread
https://excelfox.com/forum/showthrea...lines-by-codes

This what C2 looks like
_____ Workbook: LisaExSampleFile.xlsm ( Using Excel 2007 32 bit )

_Row\^Col
C

2
655; 661; 663; 665; 667; 6688; 670; 677; 678; 68860-68861; 68864; 68877; 6889; 689; 690; 810; 820

Worksheet: Old

Code:

"655" & ";" & " 661" & ";" & " 663" & ";" & " 665" & ";" & " 667" & ";" & " 6688" & ";" & " 670" & ";" & " 677" & ";" & " 678" & ";" & " 68860" & "-" & "68861" & ";" & " 68864" & ";" & " 68877" & ";" & " 6889" & ";" & " 689" & ";" & " 690" & ";" & " 810" & ";" & " 820"

"655" & ";" & " 661" & ";" & " 663" & ";" & " 665" & ";" & " 667" & ";" & " 6688" & ";" & " 670" & ";" & " 677" & ";" & " 678" & ";" & " 68860" & "-" & "68861" & ";" & " 68864" & ";" & " 68877" & ";" & " 6889" & ";" & " 689" & ";" & " 690" & ";" & " 810" & ";" & " 820"

Code:

' https://excelfox.com/forum/showthread.php/2747-VBA-Macro-which-create-new-lines-by-codes ' https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=15531&viewfull=1#post15531 Sub AlexSaltColumnB() Dim WsOld As Worksheet: Set WsOld = Workbooks("LisaExSampleFile.xlsm").Worksheets("Old") Dim strC2 As String: Let strC2 = WsOld.Range("C2").Value2 ' https://excelfox.com/forum/showthread.php/2302-quot-What%E2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string ' https://excelfox.com/forum/showthread.php/2302-quot-What%E2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string?p=11015&viewfull=1#post11015 ' http://www.eileenslounge.com/viewtopic.php?f=30&t=35732&p=278061#p278061 ' https://excelfox.com/forum/showthread.php/2302-quot-What%e2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string?p=15522&viewfull=1#post15522 ' https://pastebin.com/HatYwAAD Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(strC2) ' A function of mine which i wrote. this analyses all characters in a given text string, in this case a cell in column C End Sub ' "655" & ";" & " 661" & ";" & " 663" & ";" & " 665" & ";" & " 667" & ";" & " 6688" & ";" & " 670" & ";" & " 677" & ";" & " 678" & ";" & " 68860" & "-" & "68861" & ";" & " 68864" & ";" & " 68877" & ";" & " 6889" & ";" & " 689" & ";" & " 690" & ";" & " 810" & ";" & " 820"

_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

_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

_Row\^Col	C
2	655; 661; 663; 665; 667; 6688; 670; 677; 678; 68860-68861; 68864; 68877; 6889; 689; 690; 810; 820

In support of this answer
https://excelfox.com/forum/showthrea...5532#post15532

Old Worksheet:

_____ Workbook: Task.xls ( Using Excel 2007 32 bit )

_Row\^Col A B C D E F G H

1 Name Number Code Note Date Currency Min Max

2 John 43 655; 661; 663; 665; 667; 6688; 670; 677; 678; 68860-68861; 68864; 68877; 6889; 689; 690; 810; 820 03-01-2021 USD 19.83 24.79

3 Steve 43 660; 67833; 67890; 67891; 68183; 699 03-01-2021 USD 17.38 21.73

4 Tom 43 6600; 6990 03-01-2021 USD 17.38 21.73

5 Anthony 43 644; 664; 680; 681; 688; 69981-69982; 69988-69989 03-01-2021 USD 17.38 21.73

Worksheet: Old

New worksheet , Before running macro

_Row\^Col A B C D E F G H I

1 Name Number Code Note Date Currency Min Max

2

Worksheet: New

New worksheet After running Sub Alex1()

_Row\^Col A B C D E F G H

1 Name Number Code Note Date Currency Min Max

2 John 43 655 03-01-2021 USD 19.83 24.79

3 John 43 661 03-01-2021 USD 19.83 24.79

4 John 43 663 03-01-2021 USD 19.83 24.79

5 John 43 665 03-01-2021 USD 19.83 24.79

6 John 43 667 03-01-2021 USD 19.83 24.79

7 John 43 6688 03-01-2021 USD 19.83 24.79

8 John 43 670 03-01-2021 USD 19.83 24.79

9 John 43 677 03-01-2021 USD 19.83 24.79

10 John 43 678 03-01-2021 USD 19.83 24.79

11 John 43 68860 03-01-2021 USD 19.83 24.79

12 John 43 68861 03-01-2021 USD 19.83 24.79

13 John 43 68864 03-01-2021 USD 19.83 24.79

14 John 43 68877 03-01-2021 USD 19.83 24.79

15 John 43 6889 03-01-2021 USD 19.83 24.79

16 John 43 689 03-01-2021 USD 19.83 24.79

17 John 43 690 03-01-2021 USD 19.83 24.79

18 John 43 810 03-01-2021 USD 19.83 24.79

19 John 43 820 03-01-2021 USD 19.83 24.79

20 Steve 43 660 03-01-2021 USD 17.38 21.73

21 Steve 43 67833 03-01-2021 USD 17.38 21.73

22 Steve 43 67890 03-01-2021 USD 17.38 21.73

23 Steve 43 67891 03-01-2021 USD 17.38 21.73

24 Steve 43 68183 03-01-2021 USD 17.38 21.73

25 Steve 43 699 03-01-2021 USD 17.38 21.73

26 Tom 43 6600 03-01-2021 USD 17.38 21.73

27 Tom 43 6990 03-01-2021 USD 17.38 21.73

28 Anthony 43 644 03-01-2021 USD 17.38 21.73

29 Anthony 43 664 03-01-2021 USD 17.38 21.73

30 Anthony 43 680 03-01-2021 USD 17.38 21.73

31 Anthony 43 681 03-01-2021 USD 17.38 21.73

32 Anthony 43 688 03-01-2021 USD 17.38 21.73

33 Anthony 43 69981 03-01-2021 USD 17.38 21.73

34 Anthony 43 69982 03-01-2021 USD 17.38 21.73

35 Anthony 43 69988 03-01-2021 USD 17.38 21.73

36 Anthony 43 69989 03-01-2021 USD 17.38 21.73

Worksheet: New

05-07-2021, 10:38 AM
DocAElstein

1 Attachment(s)

Some further tests in support of this Thread: https://excelfox.com/forum/showthrea...lines-by-codes
this post: https://excelfox.com/forum/showthrea...ll=1#post15539

Some transpose tests using this test macro

Code:

Sub TransposyTests() ' https://excelfox.com/forum/showthread.php/2747-VBA-Macro-which-create-new-lines-by-codes?p=15539&viewfull=1#post15539 Dim strTst As String Let strTst = "068 069" Dim arrOutTempC() As String ' Let arrOutTempC() = Split(strTst, " ", -1, vbBinaryCompare) Dim arrOutTempCT1() As Variant, arrOutTempCT2() As Variant, arrOutTempCT3() As Variant Let arrOutTempCT1() = Application.Index(arrOutTempC(), Evaluate("=row(1:" & UBound(arrOutTempC()) + 1 & ")/row(1:" & UBound(arrOutTempC()) + 1 & ")"), Evaluate("=row(1:" & UBound(arrOutTempC()) + 1 & ")")) Let arrOutTempCT2() = Application.Transpose(arrOutTempC()) Dim Cnt: ReDim arrOutTempCT3(1 To 2, 1 To 1) For Cnt = 0 To UBound(arrOutTempC()) Let arrOutTempCT3(Cnt + 1, 1) = arrOutTempC(Cnt) Next Cnt Stop End Sub

Running that macro then stopping it before it ends, then highlighting the array variables followed by hitting Shift+F9 will reveal the contents in the Watch Window

http://i.imgur.com/ZZHD5qf.jpg
Attachment 3575
https://i.imgur.com/ZZHD5qf.jpg

At first glance it looks like the transpose is not the problem
05-07-2021, 11:03 AM
DocAElstein

1 Attachment(s)

Continued from last post

If you then look once again at array contents, then you still have what you want : For example in your test data for row with 18; 061-069, this here is what you see.
Attachment 3576
http://i.imgur.com/jbwTQdl.jpg
https://i.imgur.com/jbwTQdl.jpg

Once again, the transpose is not the problem
05-09-2021, 12:08 PM
DocAElstein

Another alternative solution for
https://excelfox.com/forum/showthrea...ll=1#post15552

Code:

Sub AlexAlanPascal() ' https://excelfox.com/forum/showthread.php/2747-VBA-Macro-which-create-new-lines-by-codes?p=15549#post15549 https://excelfox.com/forum/showthread.php/2747-VBA-Macro-which-create-new-lines-by-codes?p=15539&viewfull=1#post15539 https://excelfox.com/forum/showthread.php/2747-VBA-Macro-which-create-new-lines-by-codes Rem 1 Worksheets info Dim WsOld As Worksheet, WsNew As Worksheet Set WsOld = ThisWorkbook.Worksheets("Old"): Set WsNew = ThisWorkbook.Worksheets("New") Dim Lr As Long: Let Lr = WsOld.Range("A" & WsOld.Rows.Count & "").End(xlUp).Row ' https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files-or-single-Excel-File?p=11467&viewfull=1#post11467 Rem 2 Dim ACel As Range, TLeft As Long: Let TLeft = 2 ' This variable holds the position of the next section in the New worksheet For Each ACel In WsOld.Range("A2:A" & Lr & "") ' main loop going down all name cells ====== Dim AName As String: Let AName = ACel.Value2 Dim CVal As String: Let CVal = ACel.Offset(0, 2).Value2 & ";" ' I need the extra ; or otherwise I might miss the last number range ( number range is something like 45-48 ) if there is one, because I look for the ; in order to determine where that number rang ends ' 2b modifying any 3-5 type data Dim PosDsh As Long: Let PosDsh = InStr(1, CVal, "-", vbBinaryCompare) Do While PosDsh > 0 ' Position of the dash will be returned as 0 by the Instr function if the Instr function cannot find a next dash. Also my coding below might retun me -1 at this line ---### Dim StrtN As String, StpN As String ' I use these variables initially for the position of the number and then the actual number Let StrtN = InStrRev(Left(CVal, PosDsh), " ", -1, vbBinaryCompare) + 1 Let StrtN = Mid(CVal, StrtN, PosDsh - StrtN) Let StpN = InStr(PosDsh, CVal, ";", vbBinaryCompare) Let StpN = Mid(CVal, PosDsh + 1, (StpN - 1) - PosDsh) Dim NRng As String Let NRng = StrtN & "-" & StpN Dim Cnt As Long, Padding As Long Let Padding = Len(StrtN) For Cnt = StrtN To StpN Step 1 Dim NRngMod As String ' Dim FrstSym As String ' Let FrstSym = Left(NRng, 1) ' If FrstSym = 0 Then ' Let NRngMod = NRngMod & "0" & Cnt & "; " ' Else ' Let NRngMod = NRngMod & Cnt & "; " ' End If Let NRngMod = NRngMod & Format(Cnt, Application.Rept(0, Padding)) & "; " Next Cnt Let NRngMod = Left(NRngMod, Len(NRngMod) - 2) ' I don't need the last 2 characters of "; " Let CVal = Replace(CVal, NRng, NRngMod & "|", 1, 1, vbBinaryCompare) ' I haver a temporary "|" to indicate the end of the last modified bit Let PosDsh = InStr((InStr(1, CVal, "|", vbBinaryCompare)), CVal, "-", vbBinaryCompare) - 1 ' because I start looking at just after the last modified part, this will return me the position of the next one, ( or 0 if none is found ) -1 is because I am reducing the length by 1 in the next code line ---### Let CVal = Replace(CVal, "|", "", 1, 1, vbBinaryCompare) Let NRngMod = "" ' rest this variable for next use ' Loop ' 2c Modified column C output Let CVal = Replace(CVal, ";", "", 1, -1, vbBinaryCompare) ' I don't want any ; in the modified list Dim arrOutTempC() As String ' Let arrOutTempC() = Split(CVal, " ", -1, vbBinaryCompare) Dim arrOutTempCT() As Variant Let arrOutTempCT() = Application.Index(arrOutTempC(), Evaluate("=row(1:" & UBound(arrOutTempC()) + 1 & ")/row(1:" & UBound(arrOutTempC()) + 1 & ")"), Evaluate("=row(1:" & UBound(arrOutTempC()) + 1 & ")")) ' 2d All New column output Let WsNew.Range("C" & TLeft & ":C" & TLeft + UBound(arrOutTempCT(), 1) - 1 & "").Value2 = arrOutTempCT() Let WsNew.Range("A" & TLeft & ":A" & TLeft + UBound(arrOutTempCT(), 1) - 1 & "").Value2 = ACel.Value2 ' Name Let WsNew.Range("B" & TLeft & ":B" & TLeft + UBound(arrOutTempCT(), 1) - 1 & "").Value2 = ACel.Offset(0, 1).Value2 ' Number Let WsNew.Range("E" & TLeft & ":E" & TLeft + UBound(arrOutTempCT(), 1) - 1 & "").Value2 = ACel.Offset(0, 4).Value2 ' Date Let WsNew.Range("E" & TLeft & ":E" & TLeft + UBound(arrOutTempCT(), 1) - 1 & "").NumberFormat = "m/d/yyyy" Let WsNew.Range("F" & TLeft & ":F" & TLeft + UBound(arrOutTempCT(), 1) - 1 & "").Value2 = ACel.Offset(0, 5).Value2 ' Currency Let WsNew.Range("G" & TLeft & ":G" & TLeft + UBound(arrOutTempCT(), 1) - 1 & "").Value2 = ACel.Offset(0, 6).Value2 ' Min Let WsNew.Range("H" & TLeft & ":H" & TLeft + UBound(arrOutTempCT(), 1) - 1 & "").Value2 = ACel.Offset(0, 7).Value2 ' Max Let TLeft = TLeft + UBound(arrOutTempCT(), 1) ' this should adjust our top left cell for next range of new columns Next ACel ' ' main loop going down all name cells ========= End Sub

_Row\^Col	A	B	C	D	E	F	G	H
1	Name	Number	Code	Note	Date	Currency	Min	Max
2	John	43	655; 661; 663; 665; 667; 6688; 670; 677; 678; 68860-68861; 68864; 68877; 6889; 689; 690; 810; 820		03-01-2021	USD	19.83	24.79
3	Steve	43	660; 67833; 67890; 67891; 68183; 699		03-01-2021	USD	17.38	21.73
4	Tom	43	6600; 6990		03-01-2021	USD	17.38	21.73
5	Anthony	43	644; 664; 680; 681; 688; 69981-69982; 69988-69989		03-01-2021	USD	17.38	21.73

_Row\^Col	A	B	C	D	E	F	G	H	I
1	Name	Number	Code	Note	Date	Currency	Min	Max
2

_Row\^Col	A	B	C	D	E	F	G	H
1	Name	Number	Code	Note	Date	Currency	Min	Max
2	John	43	655		03-01-2021	USD	19.83	24.79
3	John	43	661		03-01-2021	USD	19.83	24.79
4	John	43	663		03-01-2021	USD	19.83	24.79
5	John	43	665		03-01-2021	USD	19.83	24.79
6	John	43	667		03-01-2021	USD	19.83	24.79
7	John	43	6688		03-01-2021	USD	19.83	24.79
8	John	43	670		03-01-2021	USD	19.83	24.79
9	John	43	677		03-01-2021	USD	19.83	24.79
10	John	43	678		03-01-2021	USD	19.83	24.79
11	John	43	68860		03-01-2021	USD	19.83	24.79
12	John	43	68861		03-01-2021	USD	19.83	24.79
13	John	43	68864		03-01-2021	USD	19.83	24.79
14	John	43	68877		03-01-2021	USD	19.83	24.79
15	John	43	6889		03-01-2021	USD	19.83	24.79
16	John	43	689		03-01-2021	USD	19.83	24.79
17	John	43	690		03-01-2021	USD	19.83	24.79
18	John	43	810		03-01-2021	USD	19.83	24.79
19	John	43	820		03-01-2021	USD	19.83	24.79
20	Steve	43	660		03-01-2021	USD	17.38	21.73
21	Steve	43	67833		03-01-2021	USD	17.38	21.73
22	Steve	43	67890		03-01-2021	USD	17.38	21.73
23	Steve	43	67891		03-01-2021	USD	17.38	21.73
24	Steve	43	68183		03-01-2021	USD	17.38	21.73
25	Steve	43	699		03-01-2021	USD	17.38	21.73
26	Tom	43	6600		03-01-2021	USD	17.38	21.73
27	Tom	43	6990		03-01-2021	USD	17.38	21.73
28	Anthony	43	644		03-01-2021	USD	17.38	21.73
29	Anthony	43	664		03-01-2021	USD	17.38	21.73
30	Anthony	43	680		03-01-2021	USD	17.38	21.73
31	Anthony	43	681		03-01-2021	USD	17.38	21.73
32	Anthony	43	688		03-01-2021	USD	17.38	21.73
33	Anthony	43	69981		03-01-2021	USD	17.38	21.73
34	Anthony	43	69982		03-01-2021	USD	17.38	21.73
35	Anthony	43	69988		03-01-2021	USD	17.38	21.73
36	Anthony	43	69989		03-01-2021	USD	17.38	21.73

In support of this Thread
https://excelfox.com/forum/showthrea...gure-(cricket)

_____ Workbook: Stats_Template.xls ( Using Excel 2007 32 bit )

_Row\^Col A B C D E F G H I J K L M

1 Player 1 Overs Maiden Runs Wickets Bwl Ave Econ Wides No Balls balls strike rate 5w BBI

2 Match 1 1 1 1.00 n/a 0 0.00

3 Match 2 1 1 1.00 n/a 0 0.00

4 Match 3 1 2 0.50 n/a 0 0.00

5 Match 4 1 2 0.50 n/a 0 0.00

6 Match 5 70 3 23.33 n/a 0 0.00

7 Match 6 1 1 1.00 n/a 0 0.00

8 Match 7 1 1 1.00 n/a 0 0.00

9 Match 8 1 1 1.00 n/a 0 0.00

10 Match 9 32 3 10.67 n/a 0 0.00

11 Match 10 1 1 1.00 n/a 0 0.00

12 Match 11 1 1 1.00 n/a 0 0.00

13 Match 12 1 1 1.00 n/a 0 0.00

14 Match 13 1 1 1.00 n/a 0 0.00

15 Match 14 1 1 1.00 n/a 0 0.00

16 Match 15 1 1 1.00 n/a 0 0.00

17 Match 16 1 1 1.00 n/a 0 0.00

18 Match 17 1 1 1.00 n/a 0 0.00

19 Match 18 1 1 1.00 n/a 0 0.00

20 Match 19 1 1 1.00 n/a 0 0.00

21 Match 20 1 1 1.00 n/a 0 0.00

22 Player 1 0 0 120 26 4.62 0.00 0 0 0 0.00 0

23

24 Player 2 Overs Maiden Runs Wickets Bwl Ave Econ Wides No Balls balls strike rate 5w

25 Match 1 n/a n/a 0 n/a

26 Match 2 n/a n/a 0 n/a

Worksheet: Sheet3

A basic formula to get a maximum value:

_____ Workbook: Stats_Template.xls ( Using Excel 2007 32 bit )

_Row\^Col
N

8
MxD

9

3

Worksheet: Sheet3

_____ Workbook: Stats_Template.xls ( Using Excel 2007 32 bit )

_Row\^Col
N

8
MxD

9

=MAX(E2:E21)

Worksheet: Sheet3

08-11-2021, 09:34 AM
DocAElstein

.... test post for later

Hi prkhan56
Welcome to ExcelFox

I am sorry you have had no reply.
We don’ t have many Word experts popping by excelfox much these days.

I don’t know much about Word VBA, and have never done anything with images so I don’t really understand what is wanted here. I don’t see the relation to images , pictures , “moving images”.

I have manipulated Word files with some VBA code working from Excel. Sometime my files were saved as extension type .htm – those files were normal word files with a lot of text and tables in them and the coding handled them the same as any files of extension type .doc or .docx or .docm

So I am not really so well qualified to help on what you want, but I will have a go…..

I took a quick look at this macro , Sub GetPicturesFromWordDocument() ,
I have rewritten, or rather just re arranged slightly the macro and made some minor changes as I went along and added some 'comments . I did this to help me understand what is going on.
( Here is my version: https://excelfox.com/forum/showthrea...ll=1#post15614 )

Here is a walk through my version:
For the sake of explanation, let me assume that when you run this macro you have a Word document open , which is active, and it has the name MyDoc.doc

The macro stores the current active document name ( but without the extension type) in strDocumentName. So if the active document was MyDoc.doc , then strDocumentName will have MyDoc in it.
We also store the path to the current active document in strPath
The macro seems to save the active document under its existing name, at the existing place, but with the extension type changed to .htm , so you would have then the active document, if it was MyDoc.doc now saved as MyDoc.htm - …. It is not clear to me why that is being done??

The macro makes a folder, MovedToHere in the same place as where the current active document is. I have slightly modified this code line, to prevent it erroring if the folder already exists: It only makes the folder if the folder does not exist. - If you try to make a folder when it already exists, then that would chuck up an error

__ The main outer loop === is doing the following:
It is looping 4 times, going through all your file extension types, .png .jpeg .jpg and .bmp. ( The loop control variable, lngLoop , is going from 0 To 3 )
__ For each file extension type it is looking for files which are in a folder which, using the same example, would have a name like MyDoc_files . That folder is looked for at the same path as the current active document.
So for example, the first loop is looking for files of the extension type .png in that folder
____ The purpose of the Do While __ Loop _ loop is to keep going while you still find files of the extension type currently being looked for. ( The use of Dir on its own, without any bracket ( ) stuff tells VBA to look again for the next file of the same type and in the same place that it looked the last time )
____ Each of the files you find gets copied to folder, MovedToHere and has its name modified a bit to have the text "New " added at the start, like for example, Filex.png would become New Filex.png ( Note: actually we are not really copying – we are moving – the original file gets effectively deleted )

Once we have finished doing all that copying, we close the current active document. It is not clear to me why that is being done. In particular it’s not clear to me why it is done at this point. We could have closed it immediately after we created it, since we have done nothing with it since creating it

We now open the original file we had open at the start of running the macro. Its not clear to me why we do that, other than maybe to get back to having the same file open and active that we had when we started running the macro.

Now we go on to killing ( deleting ) a few things.
The code line Kill strPath & "" & strDocumentName & ".htm*" does not error for me. I can not see why it should, since it is trying to delete all files of the extension type .htm , html etc. in the folder where we made like our MyDoc.htm
Since we should have at least that one file there, MyDoc.htm , then that at least that is there to be deleted
The next code line, Kill strPath & "" & strDocumentName & "_files\*.*" could error , if, for example, you had only had files of the type .png .jpeg .jpg or .bmp originally in that folder with the name like MyDoc_files . The reason for that is because the VBA Name statement renames a file, in other words it moves , or in other words it copies the file to somewhere and then deletes the original. So effectively it will be removing all files of the type .png .jpeg .jpg or .bmp from that folder.
So I have modified that code line so that it only tries to delete files if there are any files there to delete.
I expect the reason the code line is there is so that the next code line works. – This next code line, RmDir strPath & "" & strDocumentName & "_files" , tries to delete the original folder, and that code line would error if any files were in that folder.

The last few lines are not needed in VBA. Those code lines were considered good practice in programming earlier, I think. Possibly they may have sometimes been needed previously. I am not sure.

I am not sure if I can help much further, since I cannot reproduce your error. The macro version of mine ( Here: https://excelfox.com/forum/showthrea...ll=1#post15614 ) does not error, but I may have missed something due to my lack of experience with Word VBA.

Quote:

Originally Posted by prkhan56

I want to fix this code ...... Can someone fix this issue ...

I cant fix the code for you , because I cannot see the problem with it. But I am also not 100% sure of why some things are being done in the macro.

Quote:

Originally Posted by prkhan56

.....and also amend to run on all the sub folders.....

I don’t think you can amend a macro like this one to do that. The reason for me saying that is that the main process we are using to look at, and get at files, is the Dir function, and in particular the code line of Dir within a loop. This restricts us to one “folder level”.
We are using a fairly simple macro, like the one you are using.
Its this sort of thing: https://excelfox.com/forum/showthrea...ull=1#post6175
To look at sub folders we would usually use a different macro type, one which uses recursion. This sort of thing:
https://excelfox.com/forum/showthrea...ll=1#post10420
https://excelfox.com/forum/showthrea...ll=1#post10421
https://excelfox.com/forum/showthrea...ll=1#post10422

As you can see, that is a rather complex thing. Depending on your knowledge of VBA, that could be a rather time consuming thing to get across to you, especially as we don’t have the simpler issue fixed of why you are getting the error in the simpler macro

I expect it could take me a long time to help you further. I am busy all this week, and could take another look for you next week.

Alternatively you might want to try one of the other forums where a lot more people usually are, and certainly more people clued up on Word VBA
Here a couple of places :
https://www.excelforum.com/word-programming-vba-macros/
http://www.eileenslounge.com/viewforum.php?f=26

Please note that most forums have what they call a “cross posting rule”. This means that you should tell everyone everywhere about where else you have posted the same question.
So for example you should pass on these URL link to your questions here
https://excelfox.com/forum/showthread.php/2760-Get-Pictures-from-Word-Documents-in-All-Sub-Folders?p=15605#post15605
https://excelfox.com/forum/showthread.php/2761-Get-Pictures-from-Word-Documents-in-All-Sub-Folders?p=15613#post15613
One last tip here: If you are posting for the first time at some forums then a spam filter will prevent you posting those links. To get over that you need to disguise them when posting. You could add some spaces like this
h t t p s:/ /excelfox . com/forum/showthread.php/2760-Get-Pictures-from-Word-Documents-in-All-Sub-Folders?p=15605#post15605
h t t p s:/ /excelfox . com/forum/showthread.php/2761-Get-Pictures-from-Word-Documents-in-All-Sub-Folders?p=15613#post15613
Or alternatively try fooling the filter by posting using some BB code for black color to disguise the link – that way the filter does not see the link, but it comes out in the final post as you want it
htt[color=Black]p[/color]s:[color=Black]/[/color]/excelfox[color=Black].c[/color]om/forum/showthread.php/2760-Get-Pictures-from-Word-Documents-in-All-Sub-Folders?p=15605#post15605
htt[color=Black]p[/color]s:/[color=Black]/[/color]excelfox[color=Black].c[/color]om/forum/showthread.php/2761-Get-Pictures-from-Word-Documents-in-All-Sub-Folders?p=15613#post15613

Alan
08-22-2021, 02:32 PM
DocAElstein

testing image links

Hallo Seit dem letzten Mal habe ich viele Male versucht... Auch habe ich es in einem anderen Drucker versucht. Außerdem habe ich weitere Patronen gekauft.
Die meisten Patronen funktionieren die meiste Zeit in allen Druckern, abgesehen von Ihren 2 Patronen, die immer noch in keinem Drucker funktionieren...
_.. <stellen Sie sicher..die schwarze Abdeckung entfernt wurde, bevor Sie die Tinte installieren….. Wischen Sie die Chips und den Kontaktpunkt …> . - Ja, ich kenne das alles schon sehr gut und habe es schon oft versucht
_..<.. Bersetzen Sie durch Ihre eigene Patrone, ob Problem verschwindet …> .. - Ja, ich habe viele meiner Patronen ausprobiert und das Problem ist dann immer verschwunden
_...< Fotos der auf Ihrem Drucker angezeigten Fehlermeldung…> rote lichter zeigen immer - http://i.imgur.com/p04geAd.jpg
_..< .. Foto zeigt Ihr Druckermodell ..> ich habe 2 http://i.imgur.com/6pR66Gk.jpg . Eins benutze ich viel, http://i.imgur.com/k8ldcax.jpg , das andere ist eine Reserve und wird nicht viel verwendet , http://i.imgur.com/SsIaPZN.jpg
Ich habe auch mit dem meiner Frau experimentiert , http://i.imgur.com/3D9GVBt.jpg
ALLE SIND DAS GLEICHE MODELL HP Deskjet 1050A - http://i.imgur.com/DigfVU5.jpg
_..<.. Wie viele Tintenpatronen haben Sie ausprobiert .. welche haben nicht funktioniert? ..> - Ich habe 6 meiner Patronen ausprobiert. Die Patronen stammen aus 3 verschiedenen Quellen:-
_ Ich habe neu im lokalen Geschäft gekauft - http://i.imgur.com/2js4bjY.jpg
_ Ich habe neu im Internet - http://i.imgur.com/Ln6DkAG.jpg
_ Ich habe auch zwei von einem kaputten Drucker - http://i.imgur.com/YwqulO8.jpg , http://i.imgur.com/BdRbG1W.jpg , http://i.imgur.com/MnTGALx.jpg

Also habe ich 6 verschiedene Patronen von mir ausprobiert. (Alle sind original HP http://i.imgur.com/x8IYpp4.jpg , http://i.imgur.com/lxjGZqq.jpg , http://i.imgur.com/xhF5tnO.jpg , http://i.imgur.com/9G9HuUt.jpg , http://i.imgur.com/MnTGALx.jpg ). - Alle funktionieren die meiste Zeit in allen meinen Druckern, - http://i.imgur.com/YOFWpT1.jpg , http://i.imgur.com/xJL04SQ.jpg , http://i.imgur.com/sL0VbdT.jpg , http://i.imgur.com/WvrC9D3.jpg

Die Patronen von Ihnen funktionieren immer noch in keinem meiner Drucker - http://i.imgur.com/p04geAd.jpg

Alan

Hallo Seit dem letzten Mal habe ich viele Male versucht... Auch habe ich es in einem anderen Drucker versucht. Außerdem habe ich weitere Patronen gekauft.
Die meisten Patronen funktionieren die meiste Zeit in allen Druckern, abgesehen von Ihren 2 Patronen, die immer noch in keinem Drucker funktionieren...
_..<stellen Sie sicher..die schwarze Abdeckung entfernt wurde, bevor Sie die Tinte installieren….. Wischen Sie die Chips und den Kontaktpunkt …> . - Ja, ich kenne das alles schon sehr gut und habe es schon oft versucht
_..<.. Bersetzen Sie durch Ihre eigene Patrone, ob Problem verschwindet …> .. - Ja, ich habe viele meiner Patronen ausprobiert und das Problem ist dann immer verschwunden
_...< Fotos der auf Ihrem Drucker angezeigten Fehlermeldung…> rote lichter zeigen immer - http://i.imgur.com/p04geAd.jpg
_..< .. Foto zeigt Ihr Druckermodell ..> ich habe 2 http://i.imgur.com/6pR66Gk.jpg . Eins benutze ich viel, http://i.imgur.com/k8ldcax.jpg , das andere ist eine Reserve und wird nicht viel verwendet , http://i.imgur.com/SsIaPZN.jpg
Ich habe auch mit dem meiner Frau experimentiert , http://i.imgur.com/3D9GVBt.jpg
ALLE SIND DAS GLEICHE MODELL HP Deskjet 1050A - http://i.imgur.com/DigfVU5.jpg
_..<.. Wie viele Tintenpatronen haben Sie ausprobiert .. welche haben nicht funktioniert? ..> - Ich habe 6 meiner Patronen ausprobiert. Die Patronen stammen aus 3 verschiedenen Quellen:-
_ Ich habe neu im lokalen Geschäft gekauft - http://i.imgur.com/2js4bjY.jpg
_ Ich habe neu im Internet - http://i.imgur.com/Ln6DkAG.jpg
_ Ich habe auch zwei von einem kaputten Drucker - http://i.imgur.com/YwqulO8.jpg , http://i.imgur.com/BdRbG1W.jpg , http://i.imgur.com/MnTGALx.jpg

Also habe ich 6 verschiedene Patronen von mir ausprobiert. (Alle sind original HP http://i.imgur.com/x8IYpp4.jpg , http://i.imgur.com/lxjGZqq.jpg , http://i.imgur.com/xhF5tnO.jpg , http://i.imgur.com/9G9HuUt.jpg , http://i.imgur.com/MnTGALx.jpg ). - Alle funktionieren die meiste Zeit in allen meinen Druckern, - http://i.imgur.com/YOFWpT1.jpg , http://i.imgur.com/xJL04SQ.jpg , http://i.imgur.com/sL0VbdT.jpg , http://i.imgur.com/WvrC9D3.jpg

Die Patronen von Ihnen funktionieren immer noch in keinem meiner Drucker - http://i.imgur.com/p04geAd.jpg

Alan

Hallo Seit dem letzten Mal habe ich viele Male versucht... Auch habe ich es in einem anderen Drucker versucht. Außerdem habe ich weitere Patronen gekauft.
Die meisten Patronen funktionieren die meiste Zeit in allen Druckern, abgesehen von Ihren 2 Patronen, die immer noch in keinem Drucker funktionieren...
_.. < stellen Sie sicher..die schwarze Abdeckung entfernt wurde, bevor Sie die Tinte installieren….. Wischen Sie die Chips und den Kontaktpunkt …> . - Ja, ich kenne das alles schon sehr gut und habe es schon oft versucht
_..<.. Bersetzen Sie durch Ihre eigene Patrone, ob Problem verschwindet …> .. - Ja, ich habe viele meiner Patronen ausprobiert und das Problem ist dann immer verschwunden
_...< Fotos der auf Ihrem Drucker angezeigten Fehlermeldung…> rote lichter zeigen immer - http://i.imgur.com/p04geAd.jpg
_..< .. Foto zeigt Ihr Druckermodell ..> ich habe 2 http://i.imgur.com/6pR66Gk.jpg . Eins benutze ich viel, http://i.imgur.com/k8ldcax.jpg , das andere ist eine Reserve und wird nicht viel verwendet , http://i.imgur.com/SsIaPZN.jpg
Ich habe auch mit dem meiner Frau experimentiert , http://i.imgur.com/3D9GVBt.jpg
ALLE SIND DAS GLEICHE MODELL HP Deskjet 1050A - http://i.imgur.com/DigfVU5.jpg
_..<.. Wie viele Tintenpatronen haben Sie ausprobiert .. welche haben nicht funktioniert? ..> - Ich habe 6 meiner Patronen ausprobiert. Die Patronen stammen aus 3 verschiedenen Quellen:-
_ Ich habe neu im lokalen Geschäft gekauft - http://i.imgur.com/2js4bjY.jpg
_ Ich habe neu im Internet - http://i.imgur.com/Ln6DkAG.jpg
_ Ich habe auch zwei von einem kaputten Drucker - http://i.imgur.com/YwqulO8.jpg , http://i.imgur.com/BdRbG1W.jpg , http://i.imgur.com/MnTGALx.jpg

Also habe ich 6 verschiedene Patronen von mir ausprobiert. (Alle sind original HP http://i.imgur.com/x8IYpp4.jpg , http://i.imgur.com/lxjGZqq.jpg , http://i.imgur.com/xhF5tnO.jpg , http://i.imgur.com/9G9HuUt.jpg , http://i.imgur.com/MnTGALx.jpg ). - Alle funktionieren die meiste Zeit in allen meinen Druckern, - http://i.imgur.com/YOFWpT1.jpg , http://i.imgur.com/xJL04SQ.jpg , http://i.imgur.com/sL0VbdT.jpg , http://i.imgur.com/WvrC9D3.jpg

Die Patronen von Ihnen funktionieren immer noch in keinem meiner Drucker - http://i.imgur.com/p04geAd.jpg

Alan

Hallo
Seit dem letzten Mal habe ich viele Male versucht... Auch habe ich es in einem anderen Drucker versucht. Außerdem habe ich weitere Patronen gekauft.
Die meisten Patronen funktionieren die meiste Zeit in allen Druckern, abgesehen von Ihren 2 Patronen, die immer noch in keinem Drucker funktionieren...
_.. < stellen Sie sicher..die schwarze Abdeckung entfernt wurde, bevor Sie die Tinte installieren….. Wischen Sie die Chips und den Kontaktpunkt …> . - Ja, ich kenne das alles schon sehr gut und habe es schon oft versucht
_..<.. Bersetzen Sie durch Ihre eigene Patrone, ob Problem verschwindet …> .. - Ja, ich habe viele meiner Patronen ausprobiert und das Problem ist dann immer verschwunden
_...< Fotos der auf Ihrem Drucker angezeigten Fehlermeldung…> - rote lichter zeigen immer - http://i.imgur.com/p04geAd.jpg
_..< .. Foto zeigt Ihr Druckermodell ..> ich habe 2 http://i.imgur.com/6pR66Gk.jpg . Eins benutze ich viel, http://i.imgur.com/k8ldcax.jpg , das andere ist eine Reserve und wird nicht viel verwendet , http://i.imgur.com/SsIaPZN.jpg
Ich habe auch mit dem meiner Frau experimentiert , http://i.imgur.com/3D9GVBt.jpg
ALLE SIND DAS GLEICHE MODELL HP Deskjet 1050A - http://i.imgur.com/DigfVU5.jpg
_..<.. Wie viele Tintenpatronen haben Sie ausprobiert .. welche haben nicht funktioniert? ..> - Ich habe 6 meiner Patronen ausprobiert. Die Patronen stammen aus 3 verschiedenen Quellen:-
_ Ich habe neu im lokalen Geschäft gekauft - http://i.imgur.com/2js4bjY.jpg
_ Ich habe neu im Internet - http://i.imgur.com/Ln6DkAG.jpg
_ Ich habe auch zwei von einem kaputten Drucker - http://i.imgur.com/YwqulO8.jpg , http://i.imgur.com/BdRbG1W.jpg , http://i.imgur.com/MnTGALx.jpg

Also habe ich 6 verschiedene Patronen von mir ausprobiert. (Alle sind original HP http://i.imgur.com/x8IYpp4.jpg , http://i.imgur.com/lxjGZqq.jpg , http://i.imgur.com/xhF5tnO.jpg , http://i.imgur.com/9G9HuUt.jpg , http://i.imgur.com/MnTGALx.jpg ). - Alle funktionieren die meiste Zeit in allen meinen Druckern, - http://i.imgur.com/YOFWpT1.jpg , http://i.imgur.com/xJL04SQ.jpg , http://i.imgur.com/sL0VbdT.jpg , http://i.imgur.com/WvrC9D3.jpg

Die Patronen von Ihnen (http://i.imgur.com/NwM9JBg.jpg , http://i.imgur.com/byeNd0X.jpg ) funktionieren immer noch in keinem meiner Drucker - http://i.imgur.com/p04geAd.jpg

Alan

https://i.postimg.cc/wxsdHN33/CodeTags.jpg
08-26-2021, 10:30 AM
DocAElstein

In support of these forum Threads:
https://excelfox.com/forum/showthrea...5605#post15605
https://excelfox.com/forum/showthrea...5613#post15613

Code:

Sub GetPicturesFromWordDocument() ' https://excelfox.com/forum/showthread.php/2760-Get-Pictures-from-Word-Documents-in-All-Sub-Folders?p=15605#post15605 https://excelfox.com/forum/showthread.php/2761-Get-Pictures-from-Word-Documents-in-All-Sub-Folders?p=15613#post15613 Dim strFile As String, strFileType As String, strPath As String, strOriginalFile As String, strDocumentName As String Dim lngLoop As Long Let strFileType = "*.png;*.jpeg;*.jpg;*.bmp" 'Split with semi-colon if you want to specify more file types Let strOriginalFile = ActiveDocument.FullName Let strDocumentName = Left(ActiveDocument.Name, InStrRev(ActiveDocument.Name, ".") - 1) ' The macro stores the current active document name ( but without the extension type) in strDocumentName. So if the active document was MyDoc.doc , then strDocumentName will have MyDoc in it. Let strPath = ActiveDocument.Path ' We also store the path to the current active document in strPath ActiveDocument.SaveAs strPath & "\" & strDocumentName, wdFormatHTML, , , , , True ' The macro seems to save the active document under its existing name, at the existing place, but with the extension type changed to .htm , so you would have then the active document, if it was MyDoc.doc now saved as MyDoc.htm - …. It is not clear to me why that is being done?? If Dir(strPath & "\MovedToHere", vbDirectory) = "" Then MkDir strPath & "\MovedToHere" ' The macro makes a folder, MovedToHere in the same place as where the current active document is. I have slightly modified this code line, to prevent it erroring if the folder already exists: It only makes the folder if the folder does not exist. - If you try to make a folder when it already exists, then that would chuck up an error For lngLoop = LBound(Split(strFileType, ";")) To UBound(Split(strFileType, ";")) ' ======================== The main outer loop is doing the following: It is looping 4 times, going through all your file extension types, .png .jpeg .jpg and .bmp. ( The loop control variable, lngLoop , is going from 0 To 3 ) For each file extension type it is looking for files which are in a folder which, using the same example, would have a name like MyDoc_files . That folder is looked for at the same path as the current active document.So for example, the first loop is looking for files of the extension type .png in that folder Let strFile = Dir(strPath & "\" & strDocumentName & "_files\" & Split(strFileType, ";")(lngLoop)) Do While strFile <> "" ' The purpose of the Do While __ Loop _ loop is to keep going while you still find files of the extension type currently being looked for. Name strPath & "\" & strDocumentName & "_files\" & strFile As strPath & "\MovedToHere\" & "New " & strFile ' Each of the files you find gets copied to folder, MovedToHere and has its name modified a bit to have the text "New " added at the start, like for example, Filex.png would become New Filex.png Let strFile = Dir ' The use of Dir on its own, without any bracket ( ) stuff tells VBA to look again for the next file of the same type and in the same place that it looked the last time Loop Next lngLoop ' ============================================================================================ ActiveDocument.Close 0 ' Once we have finished doing all that copying, we close the current active document. It is not clear to me why that is being done. In particular it’s not clear to me why it is done at this point. We could have closed it immediately after we created it, since we have done nothing with it since creating it Documents.Open strOriginalFile ' We now open the original file we had open at the start of running the macro. Its not clear to me why we do that, other than maybe to get back to having the same file open and active that we had when we started running the macro. Kill strPath & "\" & strDocumentName & ".htm*" If Not Dir(strPath & "\" & strDocumentName & "_files\*.*") = "" Then Kill strPath & "\" & strDocumentName & "_files\*.*" ' Kill strPath & "" & strDocumentName & "_files\*.*" could error , if, for example, you had only had files of the type .png .jpeg .jpg or .bmp originally in that folder with the name like MyDoc_files . The reason for that is because the VBA Name statement renames a file, in other words it moves , or in other words it copies the file to somewhere and then deletes the original. So effectively it will be removing all files of the type .png .jpeg .jpg or .bmp from that folder. So I have modified that code line so that it only tries to delete files if there are any files there to delete. I expect the reason the code line is there is so that the next code line works. – This next code line, RmDir strPath & "" & strDocumentName & "_files" , tries to delete the original folder, and that code line would error if any files were in that folder. RmDir strPath & "\" & strDocumentName & "_files" ' https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/rmdir-statement 'strFile = vbNullString ' These last few lines are not needed in VBA. These code lines were considered good practice in programming earlier, I think. Possibly they may have sometimes been needed previously. I am not sure. 'strFileType = vbNullString 'strPath = vbNullString 'lngLoop = Empty End Sub
10-19-2021, 02:38 PM
DocAElstein

Some extra notes in support of this Thread post:
https://www.dsl-forum.de/threads/251...l=1#post169630
Einige zusätzliche Hinweise zur Unterstützung dieses Thread-Beitrags:
https://www.dsl-forum.de/threads/251...l=1#post169630

Anfangs, als Laie, dachte ich, dass eine Fritzbox keine Zugangsdaten braucht. Das war falsche. Aber hier erkläre ich warum ich das gedacht haben:

Diese Screenshots zeigen einen typischen automatisierten Prozess, der beim ersten Anschließen eines neuen FRITZ!Box 7590 Routers startet
https://i.postimg.cc/vTJ9T8b9/FRITZ-...-First-use.jpg
https://i.postimg.cc/hGcLVGx1/FRITZ-...-First-use.jpg
https://i.postimg.cc/gjX8GLFm/FRITZ-...-First-use.jpg
https://i.postimg.cc/fbCxChfn/FRITZ-...-Empholung.jpg
https://i.postimg.cc/wTRQpZgL/FRITZ-...-Empholung.jpg
https://i.postimg.cc/rs3G4CCD/FRITZ-...-Empholung.jpg
https://i.postimg.cc/6QfhPZwP/FRITZ-...-Empholung.jpg
https://i.postimg.cc/MpkbK5p4/FRITZ-...-Empholung.jpg
https://i.postimg.cc/SsQGbKxx/FRITZ-...-Empholung.jpg
https://i.postimg.cc/4NYB3nbK/FRITZ-...-Empholung.jpg
https://i.postimg.cc/3Jz907nt/FRITZ-...-Empholung.jpg

und die letzten leeren Zugangsdatenfelder auch nach Neustarts leer bleiben.
https://i.postimg.cc/66jMwCRJ/After-...-all-works.jpg

Aber der Router funktioniert, um Ihnen Internet zur Verfügung zu stellen, daher gehe ich davon aus, dass die verwendeten Zugangsdaten irgendwo innerhalb der Router an einem Ort gespeichert sind, auf den Sie keinen Zugriff haben.

(Wenn Sie später Zugangsdaten manuell hinzufügen, werden die intern gespeicherten Zugangsdaten mit Ihren Eingaben überschrieben und Ihre Eingaben werden in diesen letzten Feldern später immer angezeigt.)
https://i.postimg.cc/Hn2Xm6mM/FRITZ-...ugansdaten.jpg
https://i.postimg.cc/prTX9C8z/FRITZ-...s-Kennwort.jpg

Anfangs dachte ich fälschlicherweise, dass eine Fritzbox keine Zugangsdaten braucht.
10-19-2021, 03:15 PM
DocAElstein

Some extra notes in support of this Thread post:
https://www.dsl-forum.de/threads/251...l=1#post169630
Einige zusätzliche Hinweise zur Unterstützung dieses Thread-Beitrags:
https://www.dsl-forum.de/threads/251...l=1#post169630

Kopien einiger der fehlenden Frageposts
Copies of some of the missing initial question posts

Speedport W504V - kann nicht kommunizieren. Online, verbunden, aber antwortet nicht auf 192.168.2.1 oder https://speedport.ip in der Adressleiste
( DSL alles in Ordnung, aber nur 4 grün licht beleuchtet und keine funktionieren Telefon oder Internet

Hello
Ich bin zum ersten Mal hier und meine Muttersprache ist nicht Deutsch, also gehen Sie bitte schonend mit mir um, und ich entschuldige mich im Voraus für fehlerhafte Posting-Protokolle :)

Ich habe schon oft einige ähnliche forumsbeitrag durchgelesen, beispielsweise:
https://www.dsl-forum.de/threads/184...241#post120241
https://www.dsl-forum.de/threads/18403-speedport-w504v-nicht-ansprechbar?p=120241#post120241
https://www.dsl-forum.de/threads/212...-mehr-moeglich
https://www.dsl-forum.de/threads/21258-speedport-w504v-kein-zugriff-mehr-moeglich

Sie scheinen um das Problem herumzureden, kamen aber nie zu einer vollständigen Lösung.
Jetzt habe ich das gleiche Problem und suche Hilfe

Das spezifische aktuelle Problem:
Mein Router, ein Speedport W504V, verbindet sich erfolgreich mit jedem meiner Computer.(WLAN oder LAN kable)
Verschiedene Dinge bestätigen mir, dass die Speedport W504V die IP-Adresse 192.168.2.1 hat, wie es sollte.
Verschiedene Dinge und Leute haben bestätigt, dass mit dem DSL-Anschluss alles in Ordnung ist.
Verschiedene Dinge deuten darauf hin, dass ich online bin und eine funktionierende DSL-Verbindung habe.
Ich habe nie bewusst versucht, interne Einstellungen des Routers zu ändern
Aber das Problem:
_ Internet (und Telefon) funktioniert jetzt nicht. (Es hat für viele Jahre zuvor funktioniert)
_ beim IP 192.168.2.1 oder https://speedport.ip in die Adresszeile eingebe, kommt nur "Fehler: Netzwerk-Zeitüberschreitung - Der Server unter speedport.ip braucht zu lange, um eine Antwort zu senden." Egal mit welchem Browser oder Computer, ( und ich habe viele Computern und Browsers ).

Einige allgemeinere Hintergründe/ wie das Problem entstanden ist
Für etwa 10 Jahr habe ich eine Speedport W504V als teil meine Haupt Festnetz DSL Flatrate Internet Haus Anschluss Auftrag mit Deutscher Telekom
Meist war es zuverlässig. Wenn alles gut funktioniert, funktionieren Telefon und Internet und alle 5 grünen Lichter am Speedport W504V Leuchten: Von links nach rechts werden diese grünen Lichter wie folgt angezeigt:
Power *
DSL *
Online *
WLAN *
Telefon *

In der Vergangenheit es trat oft ein ähnliches Problem auf, aber im **Durchschnitt nur einmal in der Woche: Internet und Telefon funktionierten nicht mehr. Fast immer, wenn dies geschah, das letzte Licht, das Telefon licht, leuchtete nicht mehr. Es werden also nur 4 der 5 Lichter angezeigt.
(Ich habe noch nie eine andere Situation erlebt, außer
_ dass alle 5 Lichter aufleuchten, wenn alles funktioniert,
oder
_ nur die ersten 4 Lichter aufleuchten, wenn Telefon und Internet verloren gehen).

( ** aber ich betone im Durchschnitt: Manchmal kann es ein paar Mal in der Woche passieren, manchmal kann es nur einmal im Monat passieren)

Normalerweise wird das Problem behoben, indem man den Stecker aus dem Router (Telekom Speedport W504V) zieht, 30 Sekunden wartet und dann wieder verbindet. Meist klappt es beim erste versuche, konnte aber mal das bis eine Stunde und mehrerer versuche bis alles wieder gut war – Aber eine lange Pause ohne Internet und Telefon war eine Ausnahme

Zunehmend seit etwa die letzte 2 Monaten, war es langer bis die „Stecker raus, warten, Stecker rein Lösung“ funktioniert. Also öfter mal eine Pause im Internet und Telefon. Es trat ein paar Stunden auf, dann war es ein paar Mal für einen Tag weg und das neueste Problem, das ist noch seit fast zwei Woche. Also mit dem Speedport bin ich jetzt in die 4 grün licht keine Internet oder Telefone zustand jetzt dauernde.
Mein erster Hilferuf ging an die Telekom. Es folgten viele Telefon- und E-Mail-Gespräche mit verscheiedener Leute von Telekom. Viele von ihnen haben die Telefonleitung überprüft - alles gut - in ihren Worten: Alle Versuche, die "Synchronisation" zu überprüfen, sagen ihnen, dass die Leitung in Ordnung ist. Verscheiedener Leute von Telekom können auch von ihrem Ende aus , aus der Ferne sehen, wenn ich das "stecker raus, warten, stecker rein" mache.
Aber sie können nicht verstehen, warum ich nicht mit meinem Speedport W 504V kommunizieren kann.
Also gaben sie auf.

Der Speedport W504V ist mein Router - ich habe ihn bei der Telekom gekauft, nicht gemietet. Es ist außerhalb der Garantiezeit und sie wollten es sowieso nicht ersetzen.
Ich miete jetzt einen neuen Router von Telekom. ( FRITZ!Box 7590 ). Es funktioniert .. meistens hab ich Internet an manche meine Computern , aber auch nicht ohne einige neue Probleme - aber das ist ein separates Thema.

Zu meiner eigenen Zufriedenheit würde ich gerne weiterhin sehen, ob ich den Speedport W504V wieder zum Laufen bringen kann, weil, in Zukunft könnte ich viel mehr auf ein funktionierendes Internet angewiesen sein und von zu Hause aus arbeiten, daher möchte ich so viel Kontrolle und Verständnis dafür haben.
Telekom nützt mir in dieser Speedport W 504V Frage / Probleme nichts mehr.

Kann mir hier jemand etwas zum Ausprobieren empfehlen?

Bitte, ich bin ein Laie mit fast keinen Computerkenntnissen. Aber ich bin lernbegierig. Ich habe Computer mit XP, Vista, Windows 7 und Windows 10 für mich verfügbar. Ich bin mit den älteren Betriebssystemen vertrauter, aber ich kann alle Vorschläge mit jedem System ausprobieren, aber ich bräuchte aufgrund meiner begrenzten Computerkenntnisse klare Anweisungen.
Außerdem: Bitte verzeihen Sie mir und denken Sie nicht, dass ich Sie ignoriere, wenn ich lange brauche, bis ich antworte. Ich habe derzeit nur eingeschränkten Zugang zum Internet. Aber ich werde hier auf jeden Fall häufig nachsehen und so schnell wie möglich Antworten geben. Aber das kann dauern

Danke
Alan Elston

( PS Eine Sache, ich bin mir nicht sicher, ob das relevant sein könnte. Telekom sagt mir, dass mein Anschluss ein dynamischer / automatischer ist: Wenn ich die neue Fritzbox anschließe, bekomme ich VDSL aus der festnetz ; Wenn ich den älteren Speedport anschließe, wird es automatisch auf geschaltet gib das langsamere DSL aus der festnetz

PS 2 Hier der Link zu meiner diesbezüglichen Frage im Telekom Hilfeforum:
https://telekomhilft.telekom.de/t5/T...s/td-p/5342695
https://telekomhilft.telekom.de/t5/Telefonie-Internet/Ich-kann-keinen-Ausfall-unseres-Telefon-und-Internet-Festnetzes/td-p/5342695
** Es ist Gelöst markiert, ist aber nicht gelöst , meine Meinung nach )

Edit PS 3 Eine letzte Sache, etwas Seltsames. Ich verwende manchmal ein kostenpflichtiges VPN. ( hide.me über OpenVPN oder SoftEther ). In der Vergangenheit, als ich die "4 nur Lichter kein Internet- oder Telefon problem" hatte, passierte oft etwas Seltsames. Wenn ich einen Computer über VPN verbunden hatte, dann hatte dieser Computer noch einige Zeit einen funktionierenden Internetzugang. Bei meinem aktuellen Problem hatte ich immer noch Internet auf einem Computer über SoftEther für zwei Tage in dem aktuellen Langzeitproblem!!! - Wenn ich während dieser Zeit das VPN auf diesem Computer trennte, hatte ich ohne VPN kein Internet. Durch das erneute Verbinden dieses Computers über VPN hatte ich wieder ein funktionierendes Internet
( Ich sollte hier auch sagen, dass ich bei meinen verschiedenen Experimenten zur Lösung des Problems manchmal alle Computer getrennt und ausgeschaltet habe. Das hat mir nicht geholfen das Problem zu lösen
)
10-19-2021, 03:24 PM
DocAElstein

Some extra notes in support of this Thread post:
https://www.dsl-forum.de/threads/251...l=1#post169630
Einige zusätzliche Hinweise zur Unterstützung dieses Thread-Beitrags:
https://www.dsl-forum.de/threads/251...l=1#post169630

Kopien einiger der fehlenden Frageposts ( Nicht komplete )
Das sind nur einige meiner groben Notizen

Copies of some of the missing initial question posts ( Not complete )
They are just some of my rough notes

Beispiels: ....... mit dem Speedport W 504V wird es ab Montag nicht mehr funktionieren, da dieser kein VDSL verarbeiten kann. https://telekomhilft.telekom.de/t5/T.../true#M1390337
....... Die Leitung schaltet sich Montag automatisch rauf und lässt sich nicht mehr stoppen. Der VDSL-Port ist variabel und schaltet sich runter, damit dein Speedport 504V das auch packt https://telekomhilft.telekom.de/t5/T.../true#M1390697

Vielleicht die Frage anders stellen. Sagen wir, ich habe einen perfekten, voll funktionsfähigen Speedport W504V. (Ich werde wahrscheinlich in ein oder zwei Tagen haben, weil ich ein paar bei ebay vorgestern gekauft habe)
Das hätte vor ein paar Wochen bei meinem bisherigen Telekom DSL 16.000 RAM IP funktioniert

Ich habe jetzt VDSL 50. Ich weiß, dass die Fritzbox funktioniert, (meistens)

Aber wird irgendwo etwas zurückfallen, Rückfall oder sonst was zaubern, damit ein Speedport noch funktioniert.

Einige Leute bei der Telekom haben mir gesagt, dass es so sein wird. Einige waren anderer Meinung. Telekom wissen es also nicht!

Hi Broesel
Danke für die Antwort.

Quote:

Originally Posted by Broesel

deine Lan-Verbindungen an der Frizbox 7590 (Netzwerkkabel-Verbindungen) funktionieren ja laut deiner Beschreibung.--

Gerade jetzt scheint die Fritz Box 7590 auf den meisten meiner Computer korrekt zu funktionieren und bietet ein stabiles Internet über WLAN oder LAN.
Daran möchte ich jetzt noch nichts machen, bin aber natürlich daran sehr interessiert, eventuell gegen Ende der Woche noch einmal zu experimentieren, also werde ich dann jeden Vorschläge ausprobieren.

Quote:

Originally Posted by Broesel

... funktioniert aber nur wenn du SSID und WLAN-Schlüssel deines Speedport W 504 V "nie geändert" hast.
Schau mal auf die Rückseite/Unterseite deines Speedport W 504 V....

Ich hatte es neu gekauft, es hat noch nie jemand etwas geändert, und zum Beispiel nutze ich die WLAN-Info auf die Rückseite, wenn ich nach Passwörtern usw. gefragt werde. Das alles hat immer funktionieret mit dem info von die Rückseite

Zu den Einstellungen an der Fritz Box 7590:
Ich verstehe nicht allzu viel über die technischen Details der Einstellungen. Nach einigem Herumspielen am vor letzten Wochenende, als ich es ausgepackt und zum ersten Mal angeschlossen habe, hat es irgendwann beim LAN Verbindung kable zu einer meine Laptops , einen automatischen Einrichtungsvorgang gestartet, den ich laufen gelassen habe, und einfach alle vorgeschlagenen Einstellungen belassen und auf "Weiter" klicken "-Taste, bis es fertig war (Die ganze Geschichte hier: https://telekomhilft.telekom.de/t5/T.../true#M1390641 )
Am Ende des Prozesses wurden mir diese Informationen präsentiert:
https://i.postimg.cc/HW8m5XYb/Fritzb...inrichtung.jpg
https://i.postimg.cc/sDFrJxxq/Fritzb...inrichtung.jpg
https://i.postimg.cc/jdzr3TJ5/Fritzb...inrichtung.jpg
Gleichzeitig begann das Internet auf allen bisher angeschlossenen Computern voll zu funktionieren

Quote:

Originally Posted by Broesel

...Du könntest z.B. die SSID und den WLan-Schlüssel deines Speedport W 504 V auf die Fritzbox 7590 übertragen (umschreiben).
Das speichern nicht vergessen......
So mach ich es immer wenn ich mal einen anderen WLan-Router benutze und i.d.R. klappt das auch.
Das merk ich z.B. daran wenn bei uns zu Hause niemand meckert.

gerade benutze und experimentiere nur ich mit Dingen, daher denke ich, dass ich die Dinge vorerst so lassen werde, wie sie sind, aber das könnte eine Idee für später sein, danke für den Vorschlag.

Quote:

Originally Posted by Broesel

....
Am besten auf Verschlüsselungsstärke WPA/WPA2. Dein Speedport und die Fritzbox können das.

Ich bin mir nicht sicher, wie ich das machen würde. Vielleicht habe ich diese Einstellung schon? - hier wird ein WPA2 angezeigt https://i.postimg.cc/HW8m5XYb/Fritzb...inrichtung.jpg

Alan

Hi
Ja, ich vermutete auch, dass die Situation einigermaßen günstig war, mir einen VDSL-Vertrag zu unterjubeln.
Aber nach 10 Jahren habe ich erwartet, dass es an der Zeit sein könnte, ein wenig zu aktualisieren, zumal ich in ein paar Monaten zu Hause möglicherweise mehr beschäftigt und auf ein gutes Internet angewiesen bin.
Vielleicht war das Mieten des Modems/Routers im Nachhinein keine so gute Idee. Ich hatte nicht gedacht, dass ich sie so günstig kaufen kann. Auf der anderen Seite sollte ich bei einem gemieteten Gerät bessere Hilfe erwarten, wenn ich irgendwelche Probleme habe, die mit dem Modem/Router zusammenhängen könnten.

Mein alter Vertrag ( DSL 16.000 ) hat 35 Euro komplett im Monat gekostet. Anscheinend habe ich den Speedport gekauft - ich erinnere mich jetzt nicht mehr. Der neue Vertrag ist auf 35 Euro für 6 Monate festgelegt, danach kostet er 40 Euro pro Monat. Der Vertrag hat eine Mindestlaufzeit von 24 Monaten, was der übliche Deal ist. Die FRITZ!Box 7590 kostet 8 Euro monatlich, 12 Monate Mindestmietdauer.

Vielleicht hätte ich einen besseren Deal machen können, aber wenn sich die Fritzbox beruhigt und ich das verbleibende seltsame Problem lösen kann, das bei einigen meiner Computer festgestellt wurde, dann werde ich vielleicht die Dinge so lassen, wie sie sind.

Ich denke, ein Speedport W723V ist für VDSL in Ordnung, und sie scheinen genauso billig zu sein wie ein Speedport W504V bei ebay. Vielleicht werde ich mir also ein paar davon kaufen, nur um es in den nächsten Tagen zum Spaß auszuprobieren.

Quote:

Originally Posted by Hardwaremensch

....dein W504 hat definitiv KEINEN variablen Schalter und wird an einem VDSL-Anschluß nicht funktionieren. ....

Ich habe mich wohl nicht ganz klar erklärt. Was ich meinte war, dass einige Leute bei der Telekom vorgeschlagen haben, dass sich die automatische Umschaltung in einer nahegelegenen Box befindet, wahrscheinlich was ich nahe meinem neresten Nachbar gesehen habe: Vor einigen Jahren tauchte neben der alten kleineren eine viel größere Telekom-Box auf. Bei Recherchen im Internet sieht es so aus:
https://de.wikipedia.org/wiki/Very_H...ubscriber_Line
https://i.postimg.cc/qhWstCrv/290px-...he-Telekom.jpg
Was ich dachte, verstanden zu haben, war, dass sich der Switch in dieser Box befindet und es irgendwie bermerkt , welchen Router ich habe und das gelieferte Signal entsprechend auf DSL oder VDSL ( https://telekomhilft.telekom.de/t5/T.../true#M1390697 …… Der VDSL-Port ist variabel und schaltet sich runter, damit dein Speedport 504V das auch packt. Also einfach laufen lassen und alles wird gut ….. )
Aber vielleicht habe ich das nicht richtig verstanden.
Jedenfalls haben einige andere bei der Telekom gesagt, dass es keine Chance gibt, dass der Speedport W504V jetzt für mich funktioniert.

Die Informationen, die ich habe, sind also unsicher.

Meine Vermutung eines ungebildeten Laien, basierend auf meinen jüngsten Experimenten, ist, dass ich seit der "Umstellung" vor einer Woche keinen Speedport W504V niemals verwenden könnten , auch wenn er einwandfrei funktionierte so wie er soll.. Ich begründe diese Schlussfolgerung auf folgendem: Seit dem "Schalter" vor einer Woche verhält es sich meind speedport W504V anders:
Wie zuvor leuchten das erste grüne Licht ( Power ) und das vierte grüne Licht ( WLAN ) dauerhaft. Aber jetzt das grüne DSL-Licht blinkt ständig und das grüne OnLine-Licht geht nie an. ( Vorher hatte ich die Situation wie in meinem beschrieben hier – entweder 4 oder 5 grün licht dauende an.
So wie ich es verstehe, bzw. vermute, deutet ein endlos blinkendes DSL-Licht darauf hin, dass es versucht, Kontakt aufzunehmen, aber nie dort ankommt, oder einige Worte in diesem Sinne.

Hello Hardwaremensch.
Ich wollte dich nicht wieder mit einer langen Geschichte langweilen, aber wie du fragst...

Quote:

Originally Posted by Hardwaremensch

…WARUM willst du ihn widerrufen?

Ich bin mir noch nicht sicher, ob ich das machen will, aber wenn ja, muss ich es in den nächsten Tagen tun

Quote:

Originally Posted by Hardwaremensch

Welchen Vertrag hast du denn,

Ich verstehe nicht allzu viel von , aber Du kannst von hier aus sehen,
https://telekomhilft.telekom.de/t5/T.../true#M1389675
dass Deutscher Telekom mir sagen, dass ich das früher hatte
DSL 16.000 RAM IP ( mit das evtl. jetzt defekt Speedport W504V Typ A )
Und jetzt anscheinend das habe
VDSL 50 und einer FRITZ!Box 7590

Aus einigen anderen Vertrag Papierkram sehe ich, dass ich sie hatte „Magenta Zuhaus S“
Download: Max. 16 MBit/s, Normal 9,5 MBit/s, Min. 6,304 MBit/s
Upload: Max. 2,4 MBit/s, Normal 1,5 MBit/s, Min. 0,704 MBit/s

Und jetzt das neue Vertrag, was ich evtl. widerrufen ist „Magenta Zuhaus M“ VDSL 50
Download: Max. 50 MBit/s, Normal 47 MBit/s, Min. 27,9 MBit/s
Upload: Max. 10 MBit/s, Normal 9,4 MBit/s, Min. 2,7 MBit/s

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Player 1	Overs	Maiden	Runs	Wickets	Bwl Ave	Econ	Wides	No Balls	balls	strike rate	5w	BBI
2	Match 1			1	1	1.00	n/a			0	0.00
3	Match 2			1	1	1.00	n/a			0	0.00
4	Match 3			1	2	0.50	n/a			0	0.00
5	Match 4			1	2	0.50	n/a			0	0.00
6	Match 5			70	3	23.33	n/a			0	0.00
7	Match 6			1	1	1.00	n/a			0	0.00
8	Match 7			1	1	1.00	n/a			0	0.00
9	Match 8			1	1	1.00	n/a			0	0.00
10	Match 9			32	3	10.67	n/a			0	0.00
11	Match 10			1	1	1.00	n/a			0	0.00
12	Match 11			1	1	1.00	n/a			0	0.00
13	Match 12			1	1	1.00	n/a			0	0.00
14	Match 13			1	1	1.00	n/a			0	0.00
15	Match 14			1	1	1.00	n/a			0	0.00
16	Match 15			1	1	1.00	n/a			0	0.00
17	Match 16			1	1	1.00	n/a			0	0.00
18	Match 17			1	1	1.00	n/a			0	0.00
19	Match 18			1	1	1.00	n/a			0	0.00
20	Match 19			1	1	1.00	n/a			0	0.00
21	Match 20			1	1	1.00	n/a			0	0.00
22	Player 1	0	0	120	26	4.62	0.00	0	0	0	0.00	0
23
24	Player 2	Overs	Maiden	Runs	Wickets	Bwl Ave	Econ	Wides	No Balls	balls	strike rate	5w
25	Match 1					n/a	n/a			0	n/a
26	Match 2					n/a	n/a			0	n/a

_Row\^Col	N
8	MxD
9	3

_Row\^Col	N
8	MxD
9	=MAX(E2:E21)

Show 40 post(s) from this thread on one page

All times are GMT +5. The time now is 03:34 PM.

ExcelFox is Not Associated With Microsoft®

_Row\^Col	A	B	C	F	G	I	T	U
1	Name	Dates	Helper	Check Dates	Result	aa		Yasser Given
2	aa	2021-02-19	2021-02-19	2021-01-26	2021-01-26		2021-01-29	2021-01-29
3	aa	2021-01-26	2021-01-26	2021-01-27	2021-01-27		2021-01-30	2021-01-30
4	aa	2021-01-27	2021-01-27	2021-01-28	2021-01-28		2021-02-05	2021-02-05
5	aa	2021-01-28	2021-01-28	2021-01-29	Missing		2021-02-12	2021-02-12
6	aa	2021-01-31	2021-01-31	2021-01-30	Missing
7	aa	2021-02-01	2021-02-01	2021-01-31	2021-01-31
8	aa	2021-02-02	2021-02-02	2021-02-01	2021-02-01
9	aa	2021-02-03	2021-02-03	2021-02-02	2021-02-02
10	aa	2021-02-04	2021-02-04	2021-02-03	2021-02-03
11	aa	2021-02-06	2021-02-06	2021-02-04	2021-02-04
12	aa	2021-02-07	2021-02-07	2021-02-05	Missing
13	aa	2021-02-08	2021-02-08	2021-02-06	2021-02-06
14	aa	2021-02-09	2021-02-09	2021-02-07	2021-02-07
15	aa	2021-02-10	2021-02-10	2021-02-08	2021-02-08
16	aa	2021-02-11	2021-02-11	2021-02-09	2021-02-09
17	aa	2021-02-13	2021-02-13	2021-02-10	2021-02-10
18	aa	2021-02-14	2021-02-14	2021-02-11	2021-02-11
19	aa	2021-02-15	2021-02-15	2021-02-12	Missing
20	aa	2021-02-16	2021-02-16	2021-02-13	2021-02-13
21	aa	2021-02-17	2021-02-17	2021-02-14	2021-02-14
22	aa	2021-02-18	2021-02-18	2021-02-15	2021-02-15
23	aa	2021-02-20	2021-02-20	2021-02-16	2021-02-16
24	aa	2021-02-21	2021-02-21	2021-02-17	2021-02-17
25	aa	2021-02-22	2021-02-22	2021-02-18	2021-02-18
26	aa	2021-02-23	2021-02-23	2021-02-19	2021-02-19
27	aa	2021-02-24	2021-02-24	2021-02-20	2021-02-20
28	aa	2021-02-25	2021-02-25	2021-02-21	2021-02-21
29	bb	2021-01-27	2021-01-27	2021-02-22	2021-02-22
30	bb	2021-01-28	2021-01-28	2021-02-23	2021-02-23
31	bb	2021-01-31	2021-01-31	2021-02-24	2021-02-24
32	bb	2021-02-01	2021-02-01	2021-02-25	2021-02-25
33	bb	2021-02-03	2021-02-03

_Row\^Col	T	U	V	W	Z
1		Yasser Given	Hans Results		Indicies
2	2021-01-26	2021-01-29	2021-01-29		2
3		2021-01-30	2021-01-30		0
4		2021-02-05	2021-02-05		0
5	2021-01-29	2021-02-12	2021-02-12		5
6	2021-01-30		2021-01-26	2021-01-26	6
7			2021-01-29	2021-01-29	0
8			2021-01-30	2021-01-30	0
9	2021-02-02		2021-02-02	2021-02-02	9
10				2021-02-05	0
11				2021-02-12	0
12	2021-02-05		2021-02-05		12
13				2021-02-16	0
14				2021-02-19	0
15				2021-02-25	0
16					0
17					0
18					0
19	2021-02-12		2021-02-12		19
20			2021-02-13		20
21					0
22			2021-02-15		22
23	2021-02-16		2021-02-16		23
24					0
25					0
26	2021-02-19		2021-02-19		26
27					0
28					0
29					0
30					0
31					0
32	2021-02-25		2021-02-25		32
33			2021-01-29		0