Get first and last item in each group using arrays

[YasserKhalil]

Hello everyone
I have sorted data in range A1:D23 and the data is sorted by column A and column B ..
How can I get the first and the last item in each group ....?
I have put some of the expected output .. I prefer public procedure as I have many arrays that are sorted and need to manipulate each one in one line within another loop

Generally here is the file with the desired output in green range
Thanks advanced for great help

[Doc.AElstein]

Hi YasserKhalil,
How are you?

I think I have seen exactly this thing done here about 2 years ago, but I could not find the Thread yet… but this attempt is similar I think .

Make a dictionary ( I use Late Early Binding Way, but you can figure out how to do it more conventionally )

Make an Array of the current data range region, the input array arrIn()

Loop “down” that array
At each “row” make an entry in a dictionary if none exists with a key of the value in “column” 1
Whether it exists or not the item in that dictionary is a number which is increased by 1 every row
In the end you get a dictionary of count equal to the unique values in column 1
Each key is a unique value from row 1 and each item is the number of rows with that unique key in it

The rest is just a bit of fiddling with numbers to get the array indices that you need to select the datra from the input data array to put in the output data array - you have all the info you need from numbers in the items of the diktionary

The end result of this code is to give your output in the array, arrOut()

I am sure you can figure out how to make the code a function. –
The Function must be a Variant type declaration so as to pass back an Array, and its argument in the signature line declaration must take the arrIn()

But note, the subtle point about passing an Array in a function
To pass by reference, this is OK
arrOut() = Function(ByRef arrIn() As Variant) As Variant.
To pass by value
arrOut() = Function(ByVal Var as Variant) as Variant
I’ll post you the Function version later, if you can’t figure it out in the meantime

Run code, _ ...
Sub Start()
_ ..., and you will get your example output in arrOut(), based on your sample data

Code: Select all

Option Explicit
Sub LateEarlyBinding() ' Early Binding quite Late, well late afternoon here actually
 On Error Resume Next '
 ThisWorkbook.VBProject.References.AddFromguid GUID:="{420B2830-E718-11CF-893D-00A0C9054228}", major:=1, Minor:=0
 On Error GoTo 0
End Sub
Sub Start()
 Call LateEarlyBinding 
 Call DoneHereBeforeCantFindItYet
End Sub
Sub DoneHereBeforeCantFindItYet()
Dim Dik As Scripting.Dictionary
 Set Dik = New Scripting.Dictionary

Dim arrIn() As Variant
 Let arrIn() = Range("A1").CurrentRegion.Value

Dim Cnt As Long
    For Cnt = 1 To UBound(arrIn(), 1)
        If Not Dik.Exists(arrIn(Cnt, 1)) Then Dik.Add Key:=arrIn(Cnt, 1), Item:=0
     Let Dik(arrIn(Cnt, 1)) = Dik(arrIn(Cnt, 1)) + 1
    Next Cnt
 Let Cnt = 0
Dim arrOut() As Variant: ReDim arrOut(1 To Dik.Count, 1 To 5)
Dim Stear As Variant
    For Each Stear In Dik.Keys()
     Let Cnt = Cnt + 1
    Dim inC As Long, inD As Long ' These are the "row" co ordinates needed
     Let inC = inD + 1: Let inD = inD + Dik.Item(Stear)
     Let arrOut(Cnt, 1) = Dik.Keys()(Cnt - 1)
     Let arrOut(Cnt, 2) = arrIn(inC, 2)
     Let arrOut(Cnt, 3) = arrIn(inD, 2)
     Let arrOut(Cnt, 4) = arrIn(inC, 3)
     Let arrOut(Cnt, 5) = arrIn(inD, 4)
    Next Stear
End Sub

Alan

[YasserKhalil]

That's amazing Mr. Alan
Thank you very much for this detailed solution. I have learned a lot from these steps
Best and kind regards

[Doc.AElstein]

Your welcome,
Here a couple of Pubic Functions with the Calling codes,
Sub Scrot1()
Sub Scrot2()

Code: Select all

Sub Scrot1()
Dim arrIn() As Variant
 Let arrIn() = Range("A1").CurrentRegion.Value
Dim arrOut() As String
 Let arrOut() = EarlyEarlyBindingArrayByRef(arrIn())
End Sub
Public Function EarlyEarlyBindingArrayByRef(ByRef arrIn() As Variant) As Variant
Dim Dik As Scripting.Dictionary
 Set Dik = New Scripting.Dictionary
Dim Cnt As Long
    For Cnt = 1 To UBound(arrIn(), 1)
        If Not Dik.Exists(arrIn(Cnt, 1)) Then Dik.Add Key:=arrIn(Cnt, 1), Item:=0
     Let Dik(arrIn(Cnt, 1)) = Dik(arrIn(Cnt, 1)) + 1
    Next Cnt
 Let Cnt = 0
Dim arrOut() As String: ReDim arrOut(1 To Dik.Count, 1 To 5)
Dim Stear As Variant
    For Each Stear In Dik.Keys()
     Let Cnt = Cnt + 1
    Dim inC As Long, inD As Long ' These are the "row" co ordinates needed
     Let inC = inD + 1: Let inD = inD + Dik.Item(Stear)
     Let arrOut(Cnt, 1) = Dik.Keys()(Cnt - 1)
     Let arrOut(Cnt, 2) = arrIn(inC, 2)
     Let arrOut(Cnt, 3) = arrIn(inD, 2)
     Let arrOut(Cnt, 4) = arrIn(inC, 3)
     Let arrOut(Cnt, 5) = arrIn(inD, 4)
    Next Stear
 EarlyEarlyBindingArrayByRef = arrOut()
End Function


Sub Scrot2()
Dim arrIn() As Variant
 Let arrIn() = Range("A1").CurrentRegion.Value
Dim arrOut() As String
 Let arrOut() = EarlyEarlyBindingArrayByVal(arrIn())
End Sub
Public Function EarlyEarlyBindingArrayByVal(ByVal Var As Variant) As Variant
Dim Dik As Scripting.Dictionary
 Set Dik = New Scripting.Dictionary
Dim Cnt As Long
    For Cnt = 1 To UBound(Var, 1)
        If Not Dik.Exists(Var(Cnt, 1)) Then Dik.Add Key:=Var(Cnt, 1), Item:=0
     Let Dik(Var(Cnt, 1)) = Dik(Var(Cnt, 1)) + 1
    Next Cnt
 Let Cnt = 0
Dim arrOut() As String: ReDim arrOut(1 To Dik.Count, 1 To 5)
Dim Stear As Variant
    For Each Stear In Dik.Keys()
     Let Cnt = Cnt + 1
    Dim inC As Long, inD As Long ' These are the "row" co ordinates needed
     Let inC = inD + 1: Let inD = inD + Dik.Item(Stear)
     Let arrOut(Cnt, 1) = Dik.Keys()(Cnt - 1)
     Let arrOut(Cnt, 2) = Var(inC, 2)
     Let arrOut(Cnt, 3) = Var(inD, 2)
     Let arrOut(Cnt, 4) = Var(inC, 3)
     Let arrOut(Cnt, 5) = Var(inD, 4)
    Next Stear
 EarlyEarlyBindingArrayByVal = arrOut()
End Function

[YasserKhalil]

Thank you very much
I have examined the two functions to see the difference but I didn't notice any difference except the first line of each UDF
Can you please explain the difference between ByRef and ByVal as I have read about that but couldn't get the difference well?

[Doc.AElstein]

.....Can you please explain the difference between ByRef and ByVal as I have read about that but couldn't get the difference well?

Sure… .. - , it is one of those things that is obvious once you know, but is very tricky to understand initially.
Also VBA is a bit nasty. - Exactly what the difference is, will be slightly different for different variable types.
So I can only explain it in general simplified terms..

Note that in your case, whether you use ByVal or ByRef makes no difference. The reason it makes no difference is that you are not changing the contents of the array you “take in” within the Function.

_.___________

So, I will try to explain the difference in simple terms which apply generally:

ByRef Variable As…. ….

This means, in simplified terms, that you are referring to the variable itself within the functions.
In simple terms you can think of it as though you take the variable into the function.
In your case you can think of it as if you take the Array, arrIn(), into the function.

In your case the arrIn() contains initially your current data region Values.
arrIn() = Range("A1").CurrentRegion.Value
( This is the same for all codes.)

So for example arrIn(1, 1) =”A” , initially after that code line above
If, in the Function code, you were to change any value in arrIn(), then after the function is finished, the array, arrIn() would also be changed.

For example, In this code in this post, I added the code line_..
Let arrayIn(1, 1) = “Hello Yasser”
_...inside the function..
arrayIn() is the array being worked on in the function. It is the actual array taken in, arrIn() from the main code.
Both those variable names, arrIn() and arrayIn(), refer to the same thing. That is what the code part here: _..
ByRef
_.. means.

Run the code, Sub ScrotErm1(), and see what happens: The message box gives a different value before and after the Function

Code: Select all

Sub ScrotErm1()
Dim arrIn() As Variant
 Let arrIn() = Range("A1").CurrentRegion.Value
MsgBox "In the first Element of arrIn(), I have " & vbCrLf & arrIn(1, 1)
Dim arrOut() As String
 Let arrOut() = EarlyEarlyBindingArrayByRef(arrIn())
MsgBox "In the first Element of arrIn(), I now have " & vbCrLf & arrIn(1, 1)
End Sub
Public Function EarlyEarlyBindingArrayByRef(ByRef arrayIn() As Variant) As Variant
Dim Dik As Scripting.Dictionary
 Set Dik = New Scripting.Dictionary
Dim Cnt As Long
    For Cnt = 1 To UBound(arrayIn(), 1)
        If Not Dik.Exists(arrayIn(Cnt, 1)) Then Dik.Add Key:=arrayIn(Cnt, 1), Item:=0
     Let Dik(arrayIn(Cnt, 1)) = Dik(arrayIn(Cnt, 1)) + 1
    Next Cnt
 Let Cnt = 0
Dim arrOut() As String: ReDim arrOut(1 To Dik.Count, 1 To 5)
Dim Stear As Variant
    For Each Stear In Dik.Keys()
     Let Cnt = Cnt + 1
    Dim inC As Long, inD As Long ' These are the "row" co ordinates needed
     Let inC = inD + 1: Let inD = inD + Dik.Item(Stear)
     Let arrOut(Cnt, 1) = Dik.Keys()(Cnt - 1)
     Let arrOut(Cnt, 2) = arrayIn(inC, 2)
     Let arrOut(Cnt, 3) = arrayIn(inD, 2)
     Let arrOut(Cnt, 4) = arrayIn(inC, 3)
     Let arrOut(Cnt, 5) = arrayIn(inD, 4)
    Next Stear
 EarlyEarlyBindingArrayByRef = arrOut()
 Let arrayIn(1, 1) = "Hello Yasser"
End Function

You see I effectively changed the value in arrIn(1, 1)_..
_.. When I was in the Function I was referring to the variable, arrIn(), in the main code.
Pseudo like:
arrayIn() Is arrIn(). They are the same thing but just with a different name. They refer to the same thing
arrayIn() is an array
arrIn() is the same array
_ - One array, two different names for it
_._________________

[Doc.AElstein]

ByVal Variable As ….…

In this case, in very simplified terms, I do not take the variable into the Function.
I simply take it in, as it were, at its value. I take its value in. ( or values in the case of an array )
To say it slightly more technically: , I make a local copy variable and place the value or values in that local copy variable .
Within the function below, I have a Variant variable , VaryantIn , which has an array within it. At the “signature line” of the Function here:_...
Pubic Function EarlyEarlyBindingArrayByVal(ByVal VaryantIn As Variant) As Variant
_... I effectively fill that array within VaryantIn with the values which were in the array, arrIn(), at the time that the function starts at that “signature line”.
( That local copy variable is only of existence in the function, whilst the function is running . It “dies” when the function Ends
( The reason why my array this time is "housed inside" a Variant variable ( like Var=arr() ) , rather than just diretly using the array as like arr() , is not too relavant to the current explanation - but I will explain that at the end ******** )

If I add the same code line_..
Let VaryantIn(1, 1) = "Hello Yasser"
_... in this code version, ( the ByVal case ), then see what happens:
Run Code Sub ScrotErm2()

Code: Select all

Sub ScrotErm2()
Dim arrIn() As Variant
 Let arrIn() = Range("A1").CurrentRegion.Value
MsgBox "In the first Element of arrIn(), I have " & vbCrLf & arrIn(1, 1)
Dim arrOut() As String
 Let arrOut() = EarlyEarlyBindingArrayByVal(arrIn())
MsgBox "In the first Element of arrIn(), I still have " & vbCrLf & arrIn(1, 1) & vbCrLf & " I was not referring to it in the function"
End Sub
Public Function EarlyEarlyBindingArrayByVal(ByVal VaryantIn As Variant) As Variant
Dim Dik As Scripting.Dictionary
 Set Dik = New Scripting.Dictionary
Dim Cnt As Long
    For Cnt = 1 To UBound(VaryantIn, 1)
        If Not Dik.Exists(VaryantIn(Cnt, 1)) Then Dik.Add Key:=VaryantIn(Cnt, 1), Item:=0
     Let Dik(VaryantIn(Cnt, 1)) = Dik(VaryantIn(Cnt, 1)) + 1
    Next Cnt
 Let Cnt = 0
Dim arrOut() As String: ReDim arrOut(1 To Dik.Count, 1 To 5)
Dim Stear As Variant
    For Each Stear In Dik.Keys()
     Let Cnt = Cnt + 1
    Dim inC As Long, inD As Long ' These are the "row" co ordinates needed
     Let inC = inD + 1: Let inD = inD + Dik.Item(Stear)
     Let arrOut(Cnt, 1) = Dik.Keys()(Cnt - 1)
     Let arrOut(Cnt, 2) = VaryantIn(inC, 2)
     Let arrOut(Cnt, 3) = VaryantIn(inD, 2)
     Let arrOut(Cnt, 4) = VaryantIn(inC, 3)
     Let arrOut(Cnt, 5) = VaryantIn(inD, 4)
    Next Stear
 EarlyEarlyBindingArrayByVal = arrOut()
Let VaryantIn(1, 1) = "Hello Yasser"
End Function

You see the code line_..
Let VaryantIn(1, 1) = “Hello Yasser”
_..was applied to the local copy variable, and not to the variable in the main code. So the value in the variable in the main code stayed at its original value.
When I was in the Function I was not referring to the variable, arrIn(), in the main code.
VaryantIn Is Not arrIn().
VaryantIn does not have arrIn() in it
arrIn() is an array in the main code
VaryantIn is a Variant variable in the Function which contains a different array.
arrIn() and VaryantIn are not referring to the same thing. They are two separate things.
arrIn() is an array
VaryantIn is effectively a different array ( The array happens to be in a Variant variable, - that point is not so significant in the explanation of the differences in ByVal and ByRef generally****** ) *******
So in this post we have two different arrays, each has its own name
( VaryantIn is a Variant variable , but for the purposes of a simplified explanation of ByRef and ByVal, we can consider it as approximately an array )

******Note finally:
This is a separate point, but it is one of those nasty VBA things that leads to confusion.
I am using a Variant , VaryantIn, to “house” my array in the second, ByVal, Function. The reason I did not use the specific array type of variable, arrayIn(), or , VaryantIn(), or arr() , is simply that it is a nasty “quirk” of VBA that it will not allow you to take a variable of that specific array, () , type into a Function. ( generaly in VBA, the use of () tends to mean that the thing before it is either an array or it means you are looking in what is before for a field of values , so like = Dik.Keys() gives you an array, ( = ( "A", "B", "C", ............. ) in your case ) - In the case of Diktionary keys ( or items = Dik.Items() ) ) the following: Dik.Keys or Dik.Items , also works - but things don't always work when you misss out the () )

A Function will error if you try to do any variations like these where you are specifically using the array type variable
Pubic Function EarlyEarlyBindingArrayByVal(ByVal VaryantIn() As Variant) As Variant
Pubic Function EarlyEarlyBindingArrayByVal(ByVal anyarr() As Variant) As Variant
Pubic Function Wotever(ByVal arr() As String)
_.. etc..
( This, as comparison, won't generally error
Pubic Function Wotever(ByRef arr() As String) )

Some people argue that you cannot pass an array By Value, but possibly that is depending on what excactly you are calling an array. From a practical point of view... well.. it seem that you can pass a field of values By Value as demonstrated above. i suppose it is academic whether that field of values is technically an array. Things do often work a bit differently when they are "housed in a Variant". That is the case with the array here. But from a practical point of view it seems to "work" that "it" goes By Value..


Alan




http://www.eileenslounge.com/viewtopic.php?f=30&t=28583" onclick="window.open(this.href);return false;
https://www.excelforum.com/development- ... ost4381420" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic. ... 61#p227910" onclick="window.open(this.href);return false;

[YasserKhalil]

Thank you very very much for this amazing explanation of the difference between ByRef and ByVal .. Examples are main factor for understanding it in an efficient way
Kind Regards