Formula

[sathya]

Alan,

Its amazing code and really good i learnt some formula through you.

Thanks alan and hans

[sathya]

Alan ,

What should i do if i need to add more "Name" column please?

Thanks

[sathya]

Hi Alan,

Basically your code is working great along with hans code. But the issue where i got stuck is if i add more "Name". I am adding another 20 names like "Name 1", "Name 2", ...till name 20. Some of the datas are not going to correct column heading. Please look at the attachment.

Thanks a lot again Alan and Hans

[HansV]

This version of the macro lets you specify the number of names:

Code: Select all

Sub CreateList()
    Const NumberOfNames = 7 ' Change this as needed
    Dim r As Long
    Dim m As Long
    Dim c As Long
    Dim dic As Object
    Dim dic2 As Object
    Dim d As Date
    Dim i As Long
    Dim v As Variant
    Dim t As String
    ' Clear previous data
    Range("G10:G50").Resize(, NumberOfNames).ClearContents
    ' Last used row in column B
    m = Range("B" & Rows.Count).End(xlUp).Row
    ' Loop through the names
    For c = 1 To NumberOfNames
        ' Create new Dictionary object
        Set dic = CreateObject("Scripting.Dictionary")
        Set dic2 = CreateObject("Scripting.Dictionary")
        ' Loop through the rows
        For r = 10 To m
            ' Because the names are entered inconsistently, only look at the last character
            If Range("D" & r).Value = "Name " & c Then
                ' Get the text
                t = Range("E" & r).Value
                ' Extract the date
                d = DateValue(Range("B" & r).Value)
                If Not dic2.exists(d & t) Then
                    ' Add the value of colum F to the dictionary item for the date
                    dic(d) = dic(d) + Range("F" & r).Value
                    dic2(d & t) = 1
                End If
            End If
        Next r
        ' Loop through the items we added to the dictionary
        i = 0
        For Each v In dic
            i = i + 1
            ' Write value to the appropriate cell
            Cells(9 + i, 6 + c).Value = Format(v, "dd\/mm\/yyyy") & " - " & dic(v)
        Next v
    Next c
    Set dic = Nothing
    Set dic2 = Nothing
End Sub

For this version to work, your data MUST be consistent. In cell D12, you have Name2 (without a space). Change this to Name 2 (with a space), otherwise it won't be included.

[sathya]

Thanks a lot hans,

Basically i want to add 25 types of different names how should i change in the code please?

If Range("D" & r).Value = "Name " & c Then

I know that we need to change "Name " to those 25 different types of names to be added. But dont know how to do that in your code.

Any idea ?

Sorry again for annoying u :P

[HansV]

Do you mean that you're not going to use Name 1, Name 2, ..., Name 25?

[sathya]

Yes please.

Basically i have given as name 1, name 2 .... name 25 just for your understanding. But i will use actual name (that will be 25 different names).

Thanks again

[HansV]

Enter the unique names in G9, H9, etc.

Change the line

Code: Select all

            If Range("D" & r).Value = "Name " & c Then

to

Code: Select all

            If Range("D" & r).Value = Cells(9, 6 + c) Then

In the future, please try to provide the relevant information at the beginning, instead of changing the requirements after each reply...

[sathya]

Thats great.

Thanks a lot again hans

:)

[sathya]

Code: Select all

 Dim r As Long
    Dim m As Long
    Dim c As Long
    Dim dic As Object
    Dim d As Date
    Dim i As Long
    Dim v As Variant
    ' Clear previous data
    Range("G10:J23").ClearContents
    ' Last used row in column B
    m = Range("B" & Rows.Count).End(xlUp).Row
    ' Loop through the names
    For c = 1 To 4
        ' Create new Dictionary object
        Set dic = CreateObject("Scripting.Dictionary")
        ' Loop through the rows
        For r = 10 To m
            ' Because the names are entered inconsistently, only look at the last character
            If Right(Range("D" & r).Value, 1) = c Then
                ' Extract the date
                d = DateValue(Range("B" & r).Value)
                ' Add the value of colum F to the dictionary item for the date
                dic(d) = dic(d) + Range("F" & r).Value
            End If
        Next r
        ' Loop through the items we added to the dictionary
        i = 0
        For Each v In dic
            i = i + 1
            ' Write value to the appropriate cell
            Cells(9 + i, 6 + c).Value = Format(v, "dd\/mm\/yyyy") & " - " & dic(v)
        Next v
    Next c
    Set dic = Nothing
End Sub

Hi Hans,

I am using your both codes. One below and one you recently given me as well. But these codes are used in different sheets. I got another sheets where i wanted to add all the "VALUES" according to "DATES". That is if the dates are same i would like to add it and display in there respective name please.
Any idea of how to do that?

Thanks a lot again genius

[HansV]

Here is the code from Post=193473 modified to acommodate a flexible number of names.

Code: Select all

Sub CreateList()
    Const NumberOfNames = 7 ' Change this as needed
    Dim r As Long
    Dim m As Long
    Dim c As Long
    Dim dic As Object
    Dim d As Date
    Dim i As Long
    Dim v As Variant
    ' Clear previous data
    Range("G10:G50").Resize(, NumberOfNames).ClearContents
    ' Last used row in column B
    m = Range("B" & Rows.Count).End(xlUp).Row
    ' Loop through the names
    For c = 1 To NumberOfNames
        ' Create new Dictionary object
        Set dic = CreateObject("Scripting.Dictionary")
        ' Loop through the rows
        For r = 10 To m
            ' Because the names are entered inconsistently, only look at the last character
            If Range("D" & r).Value = Cells(9, 6 + c) Then
                ' Extract the date
                d = DateValue(Range("B" & r).Value)
                ' Add the value of colum F to the dictionary item for the date
                dic(d) = dic(d) + Range("F" & r).Value
            End If
        Next r
        ' Loop through the items we added to the dictionary
        i = 0
        For Each v In dic
            i = i + 1
            ' Write value to the appropriate cell
            Cells(9 + i, 6 + c).Value = Format(v, "dd\/mm\/yyyy") & " - " & dic(v)
        Next v
    Next c
    Set dic = Nothing
End Sub

If that is not what you want, please try to explain more clearly what you do want.

[sathya]

This is what exactly what i need.

Thanks a lot again :)

[Doc.AElstein]

Hi sathya
I think the issues are getting slightly mixed up
I appreciate you may be under time pressure, but you are probably taking longer in tying to go quicker.
Certainly you are giving us more work.

Please read through the entire Thread again before going further, and make sure you have understood all that has been done for you.
_..............

Let me try and clarify the last few posts. I am looking in particular at the codes discussed in the past couple of days.

Please look at your test data which I reproduced here:
http://www.eileenslounge.com/viewtopic. ... 20#p193553" onclick="window.open(this.href);return false;

I expect you had a typo where you had in row 12 the name Name2. I expect you meant Name 2

Your given hand filled output data suggested that Name2 should be taken as Name 2

So Han’s code ( and mine – I followed his ) Looks at the last character ( 2 in this case ) when considering your names , Note: - Han’s ‘ Code comment .... ..’'.... Because the names are entered inconsistently, only look at the last character......”

Han’s went further based on your results and explanations to assume your Names did take literally that form:
Name 1
Name 2
name 3
Name 4
Name5
Nme6
My name7
N8

Etc.

Han’s code was fixed to 4 names. It also uses that last character, 1, 2, 3, 4 in various parts of his code. That helps to simplify the code. Taking the last character would make the code in its present form only easy to modify if names were added consecutively in an order like name5 , Name 6 etc....up to only Name 9. ( I mentioned this already in my post http://www.eileenslounge.com/viewtopic. ... 20#p193553" onclick="window.open(this.href);return false; )


Then code from Han’s here:
http://www.eileenslounge.com/viewtopic. ... 40#p193606" onclick="window.open(this.href);return false;
assumes you do not make typos, and again add names consecutively like Name 5 , Name 6 etc.. But now you could go up to names greater than 9
_...........................................
That was all just by way of trying just to help clarify the situation
_.___________________________-

I was “Off – Line" yesterday, so let me begin now to answer some questions aimed at me Yesterday:

Hi Alan,
Basically your code is working great along with hans code. But the issue where i got stuck is if i add more "Name". I am adding another 20 names like "Name 1", "Name 2", ...till name 20. Some of the datas are not going to correct column heading. ....

Basically i have given as name 1, name 2 .... name 25 just for your understanding. But i will use actual name (that will be 25 different names)..

I guessed from the Outset that you were just using those Names 1 Names 2 and Names 3 etc. as demos for actual names like anything ( such as Tom, Dick, Name1234345656789, and Harry etc.. )
You see if you do not pick carefully your example test data then you can easily confuse and set us off in the wrong direction. A more random selection as I just gave would have avoided that confusion.

However ( by luck ) I guessed what you wanted.
My Code already does what you asked for in that former quote above. My code does not use the digits or Counts 1, 2, 3, 4 , and does not rely on any particular form of the names

I simply look for Unique names whatever and how many that may be. My code would make as many columns as you have Unique names in , and paste out the wanted information for each name. ( I am surprised that you did not notice that )
So it is doing exactly what you asked for. The only subtlety is that I also followed Han’s idea to use the last character as a unique identifier allowing for poor spellings but assuming the last character was correct. I am sure you can understand that my code would have a problem if you had a name like Harry and Larry. My code would just look at the last character y for the identification and take them as the same and Paste out the results in the same Column

_.........

But now you go back a step and say you want another code like what Han’s originally thought you wanted:

...... One below and one you recently given me as well. But these codes are used in different sheets. I got another sheets where I wanted to add all the "VALUES" according to "DATES".....

And BTW. you say there “...One below...” but you gave none below!!!
Once again you are making it very hard for us to follow.

_..........................

_.._________________-

To help us all , let me try to explain once again a few parts of my code ( It is already explained fully in the ‘ code Comments )

I hope you realise at this point that my last code will already paste out as many columns and you have unique names.
Let me walk you through the modifications to do to do the following:
_1 ) remove the use of the last character as name identification and replace that with actual names, whatever they may be. NOTE: This will mean the code will only give what you want if your Names are always spelt correctly. If that is not the case, then you will get an extra column for every incorrect spelling
_2 ) Let me guess you might want to Have the Names automatically written in row 9 each time the code is run.

_3) I will do another code which follows your latest request

.. I got another sheets where I wanted to add all the "VALUES" according to "DATES".....

_.............................

I will do that on a File and confirm that it works. I will not paste the File initially. I think it would be in all our interests if you try to do these modifications yourself. Or at least have a go. Then report back on how you get on please . And once again read the entire Thread – You did not, for example reply to a question form Rudi. If you had of done you may have had your ACCESS question immediately answered, and much of our efforts may not have been needed. – At the moment I ( and I think Han’s also) are looking at this as an Excel VBA issue

_ Here we go:
_1 ) remove the use of the last character as name identification and replace that with actual names, whatever they may be.

Please look at my code
Sub sathyaIssue25OktArrays()

_ Copy it all ( Highlight it all and Hit Ctrl+C )

( here for convenience I give it again )

Code: Select all

''10   '      http://www.eileenslounge.com/viewtopic.php?f=29&t=24852&start=20#p193529
Sub sathyaIssue25OktArrays() ' http://www.eileenslounge.com/viewtopic.php?f=29&t=24852&start=20#p193470
20   'Dim v As Variant ' For debugging test
30   ' For some time tests
40   Dim stTime As Date: Let stTime = Now()
50   'Application.Calculation = xlCalculationManual
60   'Application.ScreenUpdating = False
70   ' Some initial Worksheets data Range info and put columns in Arrays
73   Dim WB As Workbook                                                 ' Dim:  ' Preparing a "Pointer" to an Initial "Blue Print" in Memory of the Object ( Pigeon Hole with a bit of paper or code lines on that can be filled in to refer to a specific Objec of this type ) . This also us to get easily at the Methods and Properties throught the applying of a period ( .Dot) ( intellisense )                     '
76    Set WB = ThisWorkbook                                             ' Set now (to This Workbook - the one this code is in), so that we carefull allways referrence this so as not to go astray through Excel Guessing inplicitly not the one we want...         Set: Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed.      http://www.excelforum.com/excel-prog...ml#post4387191
80   Dim ws1 As Worksheet
85    Set ws1 = WB.Worksheets("Sheet1") ' This helps to referrence later exacty where we are   ' 'The Worksheets Collection Object of WB is used referrening by name.      Set ___ = ActiveSheet ' Alternative to make code apply to the current active sheet, - That being "looked at" when running this code        '
90   Dim Lr As Long   '   Last data Row                                 ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in.  '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
100   Let Lr = ws1.Cells(Rows.Count, 2).End(xlUp).Row                   ' '                                             ' The Range Object ( cell ) that is the last cell in the column of interest (CHOOSE a column typically that will always have a last Entry in any Data) ,( Last Row Number given by .Count Property applied to ( any Worksheet would do, so leaving unqualified is OK here, ) Spreadsheet Range Rows Property)    has the Property .End ( argument "Looking back up" ) appled to it. This Returns a new Range ( cell ) object which is that of the first Range ( cell ) with something in it "looking back up" in the XL spreadsheet from that last Cell. Then the .Row Property is applied to return a long number equal to the Row number of that cell    Rows.Count is the very last row number in your sheet. Its different for earlier versions of Excel.  The End(xlUp) is the same as pressing a Ctrl+UpArrow key combination. The final ".Row" returns the row where the cursor stops after moving up.
110  Dim rngBD As Range
120   Set rngBD = ws1.Range("B10:B" & Lr & "") ' Buildng the String range address "B10:Bx", where x is the last Row,  Lr. This is done by concatenating the part we know that is fixed, and the part given by the VBA Variable Lr. Concatenating is just a big word for sticking intemately together. The syntax in VBA for this is &  which is the "glue" if you like which holds them intimately together
130  Dim BD() As Variant   '   The .Value or .Value2 properties  is used in the next rows. this when applied to a range Object of more than one Cell returns a Field ( Array ) of variant types. - So the Array types declared here must be of variant. The Array must be dynamic as then ......
140   Let BD() = rngBD.Value ' Value used here to get string Date format, not the Double  Number as in Value2 for a date ##                                                                                                                      ...... a Field may be applied directly in VBA to a Dynamic Array, as long as the Types match up
150  Dim rngDN As Range: Set rngDN = ws1.Range("D10:D" & Lr & ""): Dim DN() As Variant: Let DN() = rngDN.Value2
160  Dim rngET As Range: Set rngET = rngBD.Offset(0, 3): Dim ET() As Variant: Let ET() = rngET.Value2 '  .Value2 Property is slightly different to .Value Property . .Value is the moost fundamental underlining    .Value may contain some Format infomation such as if the number is to beconverted to a date  ( The underligning value of a date, for example ) is a Doulle Type Number )
170  Dim rngFV As Range: Set rngFV = rngBD.Offset(0, 4): Dim FV() As Variant: Let FV() = rngFV.Value2
180  ' Main unique Names list for outer loop for all Names '
190  Dim Cnt As Long 'Dim Steer As Range
200  Dim DikName As Object
210   Set DikName = CreateObject("Scripting.Dictionary")
220      For Cnt = 1 To UBound(DN(), 1) '
230      Dim myLong As Long
240       Let myLong = DikName.Item(Right(DN(Cnt, 1), 1)) ' Han's : " ...' Because the names are entered inconsistently, only look at the last character ... "
250      Next Cnt
260  ' Unique Date List
270  Dim DikDate As Object
280   Set DikDate = CreateObject("scripting.dictionary")
290      For Cnt = 1 To UBound(BD(), 1) '
300       'Let myLong = DikDate.Item(Format(BD(Cnt, 1), "dd.mm.yyyy")) ' - this alternative would work for .Value2 also ##
310       Let myLong = DikDate.Item(DateValue(BD(Cnt, 1))) '  This  'v = DateValue(BD(Cnt, 1))  will error for .Value2
320      Next Cnt
330  ' Use an Array of Arrays for final output for flexibility
340  Dim arrSOut() As Variant: ReDim arrSOut(1 To DikName.Count) 'Set aside a place in this Array for each Name column Output
350  Dim CntDikName As Long ' use extra variable to help keep track of what is going on
360      For CntDikName = 1 To DikName.Count 'Main outer Loop for each unique Name =============================
370      Dim TempDatesListArray As Object 'I want to build up a list, by setting at next line it will be cleared for each uniqu name run
380       Set TempDatesListArray = CreateObject("System.Collections.ArrayList") 'Without a reference to the library: 'late binding' with the use of a variable:  http://www.snb-vba.eu/VBA_Arraylist_en.html#L_4.2.1
390      Dim CntDikDate As Long
400          For CntDikDate = 1 To DikDate.Count ' For each name look for every unique date and ... ------------
410          Dim BigCnt ' An extra variable for counting ("down") the main Big data Range
420          ' Here a Temporary list of values to be summed based on Unique "Name" & "Text Type" will be |||
430          Dim TempSumDik As Object: Set TempSumDik = CreateObject("scripting.dictionary")
440              For BigCnt = 1 To UBound(BD(), 1) ' ... we are taking at each name and at that each unq Dte ...
450                  If DateValue(BD(BigCnt, 1)) = DikDate.keys()(CntDikDate - 1) And Right(DN(BigCnt, 1), 1) = DikName.keys()(CntDikName - 1) Then
460                      If Not TempSumDik.exists(Right(DN(BigCnt, 1), 1) & ET(BigCnt, 1)) Then ' ||| added to if a unique Key:=("Name" & "Text Type"), Item:=the coresponding "Text Value"
470                       TempSumDik.Add Key:="" & Right(DN(BigCnt, 1), 1) & ET(BigCnt, 1) & "", Item:=FV(BigCnt, 1) '
480                      Else ' we are at looked at Name and Date , but had hit on such a pair before so we do nothing. redundant code
490                      End If
500                  Else ' we are not at a hit for the Name and Date pair being considered in Big loop. Do nothing. Rewdundant code
510                  End If
520              Next BigCnt ' ............have finished a Big down "rows" check  to get Names Date Sum.........
530              If TempSumDik.Count > 0 Then ' only if we have any entries in put Temp Sum  Dik ( and therefore have a new date match ) we start
540              Dim SumV As Long, SumCnt As Long 'now we have in the Temporary Dik Items the values to be summed so ...
550                  For SumCnt = 1 To TempSumDik.Count ' ... go through them and ...
560                   Let SumV = SumV + TempSumDik.Items()(SumCnt - 1) ' ... add them
570                  Next SumCnt
580              Dim strConCat As String ' we are at the point of having a sum and unique date pair for the current name so ...
590               Let strConCat = Format(DikDate.keys()(CntDikDate - 1), "dd\/mm\/yyyy") & " - " & SumV ' ... build the required string
600               TempDatesListArray.Add strConCat ' Add the entry to an the temporary Arraylist
610               Let SumV = 0 ' empty sum for next usage
620              Else ' here we had no date match so made no temporary sum so do nothing. Redundant code
630              End If
640          Next CntDikDate ' Finished each unique date at current unique Name, so go to next unique Name------
650       Let arrSOut(CntDikName) = TempDatesListArray.ToArray ' Add as an Array the current ListArray for the Current Name
660      Next CntDikName ' Finished Main outer Loop for each unique Name =======================================
670  ' We have our Final Array of Arrrays with all required Output data. So Paste out where wanted.
680      For CntDikName = LBound(arrSOut()) To UBound(arrSOut()) 'We have as many Arrays in this Array as Names so we could ...
690      'For CntDikName = 0 To DikName.Count - 1 '  ... could also do this
695       'ws1.Cells(10, 7 + CntDikName).Resize(UBound(arrSOut()(CntDikName)) + 1, 1)
700       Let ws1.Cells(10, 7 + CntDikName).Resize(UBound(arrSOut()(CntDikName)) + 1, 1).Value = Application.Transpose(arrSOut()(CntDikName)) ' before resizing, we select Top left Cell  where the outout should go. This is resized in rows to the row number given by the number of entries in the current Name Entry Array ( + 1 as these Arrays start at indicia 0 ). The columns are set to 1. The values in this range are then given to the Transposed Names Array. We transpose as The Names Arrays are 1 Dimensional which Excel VBA conventionally sets at a Row - but we want it pasted out to a column, so we transpose it
710      Next CntDikName
720  Application.Calculation = xlCalculationAutomatic
730  Application.ScreenUpdating = True
740   MsgBox prompt:="Took  " & Format((Now() - stTime), "hh:nn:ss")
End Sub


' Rem Ref  Simple MSRD Inique List   Loopy DiK    http://www.eileenslounge.com/viewtopic.php?f=30&t=24955&p=193413#p193413
'   Datevalue Stuff  Han's      http://www.eileenslounge.com/viewtopic.php?f=29&t=24852&start=20#p193473
'  ArrayList Dictionary Alternative   http://www.snb-vba.eu/VBA_Arraylist_en.html

Paste it into any code Module and change its name to
Sub sathyaIssue26OktArrays() ' http://www.eileenslounge.com/viewtopic. ... 28#p193596" onclick="window.open(this.href);return false;
( Select a large Code Window in the VB Editor Window ( Hit Alt + F11 to get the VB Editor Window )

_ In the Spreadsheet Worksheet named “Sheet1” add a few columns where you have your names columns so as to shift all my formulas to the right and give us enough columns to accommodate more Names columns

_ Examine my code ( Hit Alt + F11 ) to get the VB Editor. If you cannot find the code, try these alternative steps to find and pick out the code and get it in front of you
DevelopmentToolsWorkOnMacro123.JPG
http://imgur.com/I91Yckx" onclick="window.open(this.href);return false;
Or
ViewWorkOnMacro1234.JPG
http://imgur.com/4jovFhV" onclick="window.open(this.href);return false;
Or
with Keyboard Short Cut Alt + F8 ( when in Excel Spreadsheet )


_ In my and Some of Han’s codes you will see code bits like
Right(DN(BigCnt, 1)
or
Right(Range("D" & r).Value, 1)

Putting that in simple terms

Right( SomeCharacterString , 1 )

That returns the last character, so if SomeCharacterString = “Harry”, then you get “y” returned
( looking in “Harry” , at 1 character looking from the right

So if you change all those to just

SomeCharacterString
Or
DN(BigCnt, 1)
Or
Range("D" & r)

Etc.. etc...
Then if for example, SomeCharacterString = “Harry” then you get “Harry” returned.

This basically is the change which makes the code Look at all unique names, regardless of they are Name 5, Name2, or Fred etc.. etc..

_ Just to help you. I just did this and had to change 4 code bits in my code

_So do that change and run the code on your test data as I reproduced here
http://www.eileenslounge.com/viewtopic. ... 20#p193553" onclick="window.open(this.href);return false;

You should get this now.

Row\Col	H	I	J	K	L
9	Name 1	Name 2	Name 3	Name 4
10	11/10/2016 - 150	06/10/2016 - 50	04/10/2016 - 100	11/10/2016 - 100	04/10/2016 - 100
11			12/10/2016 - 100	06/10/2016 - 50
12				18/10/2016 - 50

|< < > >|_Sheet1_/___//


Do you see the difference? Name2 and Name 2 are taken as different names. No other modification is required to add as many new names as you like. The code will as already written , paste out as many columns as you have different ( unique ) names. But the problem of course is that Column I is now referring to Name2 and column K is referring to Name 2. You would need to change these headings in row 9 manually

Hence I suggest the further requirement that you would like. . – Thjis would be to paste in those names automatically


_.__________

_2 ) Have the Names automatically written in row 9 each time the code is run

There are various advantage and disadvantages of the code writing technique. For a final code in production you should always take a code given by someone like Han’s. There will be years of experience behind the thinking there that leads to the most efficient code. The code will necessarily be optimised and not so easy to modify, hence you should try to give as much correct information at the outset.
My code is more rambling and inefficient, but does make later modification easier.
My code collects the final Names columns data in an Array of Arrays . The Arrays are one for each column. The Array itself therefore has one element ( An column data Array ) per Name. This was 4 previously, but would have inceased to 5 afte modification _1) ( assuming agaoin the test data I indicated http://www.eileenslounge.com/viewtopic. ... 20#p193553" onclick="window.open(this.href);return false; )

Examine this line, which is the start of a Loop
680 For CntDikName = LBound(arrSOut()) To UBound(arrSOut()) ''We have as many Arrays in this Array as Names ...
So I already loop through every Name and paste out in line 700 the column data for every Name
Now, hopefully you have realised that my code holds a list of all the different ( unique ) Names, These are the Keys in a Microsoft Scripting Runtime Dictionary. In my code all these Dictionary Key Members can be referenced at once by
DikDate.keys()
Alternatively each Member can be got at through the index ( which starts at 0, so goes 0 , 1 , 2 etc... ).
So for example
DikDate.keys()(0)
is the member “Name 1” as this was the first one my code encountered when adding keys to the Dictionary of Names ( That was done in Line 240 )

So we now add one simple line
705 Let ws1.Cells(9, 7 + CntDikName).Value = DikName.keys()(CntDikName - 1) ' ' -1 is required as my Array of Arrays was declared as staring at 1, but the Dictionary keys start counting at 0

After this last simple modification, you now should get this ( using again your test data which I reproduced here
http://www.eileenslounge.com/viewtopic. ... 20#p193553" onclick="window.open(this.href);return false;

Row\Col	H	I	J	K	L
9	Name 1	Name2	Name 3	Name 2	Name 4
10	11/10/2016 - 150	06/10/2016 - 50	04/10/2016 - 100	11/10/2016 - 100	04/10/2016 - 100
11			12/10/2016 - 100	06/10/2016 - 50
12				18/10/2016 - 50

|< < > >|_Sheet1_/___//
The names are pasted out by the code as well as the column data



_...................

Regarding
_3)

.. I got another sheets where I wanted to add all the "VALUES" according to "DATES".....

Perhaps it would be wise if I wait until you have had a chance to review the Thread fully, and are clear as to exactly what you want.

If you wish further help please review fully all you have been given and try to be clear on all you requirements. Code writing is such that it is not always efficient to try and develop a code bit by bit, as modifications often require or are easier to achieve with a complete re write.

( My “bad” code writing style does at least make changing requirements a bit easier to do , but I am not a professional programmer. If you are a programmer or are being employed to do some programming, then you probably should not get into my bad habits.. L0l... But hopefully i am helping you with your learning. )

Alan


_._________________________------



Edit:
A small Note about Worksheet things. Part of the “extra” not always necessary stuff that my code does is at the start of my codes is using a variable for the Worksheet. Then in the rest of the code it references that worksheet when doing anything to or with that variable.
If you miss that out, as in Han’s code then any Range reference will
_ For a Code in Worksheet Code Module it will refer to the range in the Worksheet of that Worksheet code Module
_ For most other cases it refers to the Application Range. This is somewhat more tricky to explain, so I will not just now, as many of the best experts do not understand it either. Suffice to say, in most cases for code written in a Normal code module this will default to the Active Worksheet.
_...
So if , for example, you use Han’s code, either you must ensure that the Worksheet you want it to apply to is Active at the time that the cod is run. Or put the code in the appropriate Worksheet code Module. To get at the Worksheet code module
Either
In the Worksheet of interest, Right click on the tab and select something like “View Code”
wsCodeModuleTab.JPG
http://imgur.com/sOzyEon" onclick="window.open(this.href);return false;

or
in the VBA Project Explorer window. Double click on the appropriate Worksheet
wsCodeModuleVBEditor.JPG
http://imgur.com/8FwncMe" onclick="window.open(this.href);return false;

( I have a habit of using Worksheet Code modules most of the time. But that is unusual and just a personal preferrence. I always use a variable to referrence all worksheets I am interested in. So mostly mycodes can go anywhere )

[sathya]

Thats awesome. Thanks a lot :)

[Doc.AElstein]

Hi sathya,
Thanks for the Feedback
Sorry if the post is a bit long and off putting.
I try to be thorough so that you can learn. ( It helps me to remember as well if I need to refer back !! )
Alan

[sathya]

Alan,

No at all. Post is not long. This shows ur interest in helping others and i learnt a lot from rudi, hans and urself.

Thanks again