Formula

[sathya]

Thanks a lot StuartR and Alan for your kind help. I really appreciate you guys in making loads of effort in helping others. You guys along with Rudi and Hans doing an excellent job.

Thanks again :)
Sathya

[Doc.AElstein]

Yous welcome
Glad we could help

[sathya]

Hi StuartR and Alan ,

Does any one have an idea of how to write the attached excel formula in "Access Expression Builder"?

Thanks again

[HansV]

Let's say you have a table named tblData, with among others, fields Date, Text and Value.
In a query based on this table, you could use

Total: Format([Date],"dd/mm/yyyy") & " - " & DSum("Value","tblData","Format([Date],'dd\/mm\/yyyy')='" & Format([Date],"dd/mm/yyyy") & "' AND [Text]='" & [Text] & "'")

By the way, in reality, I wouldn't use Date, Text and Value as field names, since all of these are reserved words that have a special meaning, so they could easily lead to confusion and to errors. For example: instead of Date, use DateIn; instead of Text, use Description; instead of Value, use Amount.

[sathya]

Great Hans,

Thanks for your help. Other question is does adding more expression builders formula slow down the access ? Because when i open my access it taking atleast 2 to 3 mins to load all the solutions for the formula.

Is there is any other way to use the same formula in different way (like vba) to speed up the access formula?

Thanks again

[HansV]

These calculations do indeed slow down performance, especially if the number of records is large.

Do you really need to display this sum in each record?

[sathya]

Hmm... Yes please :(

[HansV]

In that case, you'll have to live with the slowness.

[HansV]

A faster alternative would be to create a totals query:

SELECT DateValue([tblData].[Date]) AS [Date], tblData.Text, Sum(tblData.Value) AS Total
FROM tblData
GROUP BY DateValue([tblData].[Date]), tblData.Text;

Create a continuous or datasheet form based on this query, and place it as a subform in the form header or form footer of your main form, without linking it to the main form.
The subform will display the total for each date and text, without causing a large delay.

[Doc.AElstein]

Hi StuartR and Alan ,
Does any one have an idea of how to write the attached excel formula in "Access Expression Builder"? ...

Hi sathya
As I mentioned before, I have no experience with ACCESS and do not have the software.
But I believe VBA codes can run in them.
As I also said, my code is not a particularly fast one, and as it stands it, I just did a quick check and it just took about 22 minutes to do 1000000 rows, and about 33 seconds to do 100000 rows ( Admittedly my computers are old and slow )
Doing some simple modifications it improved to about 30 seconds for 100000 rows.
A quick go at an Array code improved to about 5 seconds for about 100000 rows.

All these times would vary a bit depending on the actual data.
-……..
__.___________-

I believe ACCESS is designed to work with data and do “Query” type things. So I would expect you won’t get much better than the “Query” type idea from Hans.


Alan

[sathya]

Hi Alan and Hans,

Thanks for your kind help.

Please look at the attachment. I am very much happy with formula in the excel but i am stuck with another set of formula from "column G to Column J" please.
Any help is much appreciated again.
Thanks again

[HansV]

It is undoubtedly possible to do that with formulas, but I find it too difficult. Here is a macro:

Code: Select all

Sub CreateList()
    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

[sathya]

brilliant Hans,

Thanks again :)

[sathya]

Thanks Hans,

But i think i were not good in explaning before what i need clearly. Sorry abt that. In attachment i added 24/10/2016 with same repetation of "Text 1". In that case i would like to see the result as 24/10/2016 - 50 and not 24/10/2016 - 150 please.
Basically if Text 1 or 2 or 3 repeating again and again in the same name column i would like to take only one value.
That is,

04/10/2016 Name 1 Text 1 50
04/10/2016 Name 1 Text 2 50
04/10/2016 Name 1 Text 1 50
04/10/2016 Name 1 Text 1 50
04/10/2016 Name 2 Text 1 50

Solution will be

Name 1 Name 2

04/10/2016 - 100 (because adding text 1 and text 2) 04/10/2016 - 50


Thanks

[Doc.AElstein]

@ sathya
Hi sathya
I think I noticed that discrepancy and have been working on what I think is the correct solution, that is to say what you actually want. Probably Han’s will beat me to it as always, but as I am already some way in to it, I will finish it and post it anyway later when I have finished it.....

Alan

[sathya]

Thanks a lot. I just got stuck with that vb code. Any help is much appreciated.

Thanks

[HansV]

Here is a new version (sorry, Alan):

Code: Select all

Sub CreateList()
    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: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")
        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 Right(Range("D" & r).Value, 1) = 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

[sathya]

Thanks a lot :)

[Doc.AElstein]

_......
Well, a few hours later I finally got there. My final results match up with those of Han’s latest. .. I will take that as meaning my code works ..


@ Hans
Hi Hans,
I am sure you are bored with the times this has been said to you. But your speed at picking up and giving solutions is amazing. How I wish I could grasp a requirement and write a code so quickly. I cannot imagine I could ever reach that level of excellence. I learnt a lot from your code and was able to do my attempt better..
But quite honestly, I think I give up attempting to answer any threads here :(

I see what you are doing in your code. – Similar to me but just more efficiently. For example you make a Key , and item for a unique list all in one go. And in your final outputting line, your steering Element, v, seems to work on the Key. I would have never guessed that. – I do not understand why it chooses the key and not the Item. .....

I do note one small thing in passing, that your code relies on the names must have an integer as the character. ( But it would take you a ms to change that to work with any names )

Alan

_.._____________

Possibly, my alternative never the less adds to the Thread... so
_.______

@ sathya
Hi sathya
_ Please try to make clear your request. As well as giving the “after” hand filled in data, please put into words exactly what you want.

_ I noticed that Han’s code did not reproduce the answer you gave !

_ Also my formulas are set to work on the range B10:F21 ( I explained how to alter this here: http://www.eileenslounge.com/viewtopic. ... 52#p192987" onclick="window.open(this.href);return false; )
It was not clear if you were wanting the results for your new requirement to apply to that range or
B10:F25
So this uncertainty confused the issue again.

Anyway, here we go :)
As I prepared this summary anyway, I will add it anyway...

You gave this

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

|< < > >|_Sheet1_/___//

Han’s first code gave this:

Name 1	Name 2	Name 3	Name 4
11/10/2016 - 200	06/10/2016 - 100	04/10/2016 - 150	04/10/2016 - 100
	11/10/2016 - 100	12/10/2016 - 100
	18/10/2016 - 50

|< < > >|_Sheet1_/___//

_......

Your given hand filled in results suggest to me you want this ( approximately ** ), in words:

_1) You consider each name, let’s say Name 1.
_2) For that name you want a list based on the Dates in column B which have the Name 1 in column D, but in addition:
_3) it would appear that the new requirement is similar to your original, based on your hand filled in sample output. As such you concatenate each date with a “ – “ and the total for the values in column F for each unique Text Type. In the case of Name 1 this means Text 1 Text 2 and text 3. This gives you 50 + 50 + 50 = 150
So your final result as you have given would be
11/10/2016 – 150
Han’s original code adds ... ' Add the value of column F to the dictionary item for the date ... that is to say all Column F values for the Date and Name. This then includes that for the Duplicated Text 2 giving 50 + 50 + 50 + 50 = 200. Hence Han’s first code gives you
11/10/2016 – 200

( All the above is based on you using the newly ( extended ) given by you data range
Using Excel 2007 32 bit

Row\Col	B	C	D	E	F
9	Date	Out Time	Name	TEXT	Value
10	11.10.2016 14:43	11.10.2016 14:44	Name 1	TEXT 1	50
11	11.10.2016 14:54	11.10.2016 14:57	Name 1	TEXT 2	50
12	06.10.2016 14:22	06.10.2016 14:37	Name2	TEXT 1	50
13	04.10.2016 15:44	04.10.2016 16:00	Name 3	TEXT 1	50
14	11.10.2016 16:35	11.10.2016 16:36	Name 2	TEXT 3	50
15	06.10.2016 10:14	06.10.2016 10:42	Name 2	TEXT 1	50
16	04.10.2016 15:19	04.10.2016 15:21	Name 4	TEXT 1	50
17	11.10.2016 16:51	11.10.2016 16:56	Name 2	TEXT 2	50
18	11.10.2016 17:51	11.10.2016 17:58	Name 1	TEXT 2	50
19	04.10.2016 16:19	04.10.2016 16:29	Name 3	TEXT 1	50
20	04.10.2016 12:44	04.10.2016 12:54	Name 4	TEXT 2	50
21	04.10.2016 11:44	04.10.2016 12:54	Name 3	TEXT 2	50
22	18.10.2016 11:44	18.10.2016 12:54	Name 2	TEXT 1	50
23	11.10.2016 14:54	11.10.2016 14:57	Name 1	TEXT 3	50
24	12.10.2016 11:44	12.10.2016 12:54	Name 3	TEXT 1	50
25	12.10.2016 12:44	12.10.2016 12:54	Name 3	TEXT 2	50

|< < > >|_Sheet1_/___//
_......

_....
_._________________

I agree with Han’s that a Code is the more sane approach for this, at least comparing with my attempt at a Formula !!, Lol !!,
This would be my attempt based on the above data Range and assuming my suggestions of what you are actually wanting is correct. EDIT: It appears now I assumed correctly....

I went straight into an Array type code. ( I discussed the various merits and comparisons of this type of code with you here http://www.eileenslounge.com/viewtopic. ... 20#p193389" onclick="window.open(this.href);return false; In addition as the final Output is first built into an Array , we are very flexible in where we finally decide to paste it out, which is somewhat wise considering how your final spreadsheet is being changed as you go along.

In the following code I am using some extra variables and more ‘Comments, so as to keep track of things as I am getting a bit confused with the changing requirements.. !! ( I am outputting in Columns H to K )

**_.... There may still be some discrepancies with your various given hand filled in results and what I have obtained. But for the same data Range as given above I get the same results as Han’s latest code, so I am happy with that. !! :)

I am pasting out in columns H to K instead of column G to J. The following results are what I get from my code. Also if I modify slightly Han’s latest code to use for output columns H and K i get identical results. But as my code puts the entire output results into a final Array of Arrays, you can be fairly flexible where you paste those all out. ( Han’s code is just so easy to modify actually )

_...........
My final Results. ( for the data range shoown above )

Using Excel 2007 32 bit

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

|< < > >|_Sheet1_/___//






Code: ( It is a bit easier to see in the VB Editor code window or here
http://www.excelforum.com/showthread.ph ... ost4508316" onclick="window.open(this.href);return false;
and
http://www.excelforum.com/showthread.ph ... ost4508913" onclick="window.open(this.href);return false;

Code: Select all

'      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
'Dim v As Variant ' For debugging test
' For some time tests
Dim stTime As Date: Let stTime = Now()
'Application.Calculation = xlCalculationManual
'Application.ScreenUpdating = False
' Some initial Worksheets data Range info and put columns in Arrays
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("Sheet1") ' This helps to referrence later exacty where we are
Dim Lr As Long
 Let Lr = ws1.Cells(Rows.Count, 2).End(xlUp).Row
Dim rngBD As Range
 Set rngBD = ws1.Range("B10:B" & Lr & "")
Dim BD() As Variant
 Let BD() = rngBD.Value ' Value used to get string Date format, not the Long Number as in Value2 for a date ##
Dim rngDN As Range: Set rngDN = ws1.Range("D10:D" & Lr & ""): Dim DN() As Variant: Let DN() = rngDN.Value2
Dim rngET As Range: Set rngET = rngBD.Offset(0, 3): Dim ET() As Variant: Let ET() = rngET.Value2
Dim rngFV As Range: Set rngFV = rngBD.Offset(0, 4): Dim FV() As Variant: Let FV() = rngFV.Value2
' Main unique Names list for outer loop for all Names '
Dim Cnt As Long 'Dim Steer As Range
Dim DikName As Object
 Set DikName = CreateObject("Scripting.Dictionary")
    For Cnt = 1 To UBound(DN(), 1) '
    Dim myLong As Long
     Let myLong = DikName.Item(Right(DN(Cnt, 1), 1)) ' Han's : " ...' Because the names are entered inconsistently, only look at the last character ... "
    Next Cnt
' Unique Date List
Dim DikDate As Object
 Set DikDate = CreateObject("scripting.dictionary")
    For Cnt = 1 To UBound(BD(), 1) '
     'Let myLong = DikDate.Item(Format(BD(Cnt, 1), "dd.mm.yyyy")) ' - this alternative would work for .Value2 also ##
     Let myLong = DikDate.Item(DateValue(BD(Cnt, 1))) '  This  'v = DateValue(BD(Cnt, 1))  will error for .Value2
    Next Cnt
' Use an Array of Arrays for final output for flexibility
Dim arrSOut() As Variant: ReDim arrSOut(1 To DikName.Count) 'Set aside a place in this Array for each Name column Output
Dim CntDikName As Long ' use extra variable to help keep track of what is going on
    For CntDikName = 1 To DikName.Count 'Main outer Loop for each unique Name =============================
    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
     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
    Dim CntDikDate As Long
        For CntDikDate = 1 To DikDate.Count ' For each name look for every unique date and ... ------------
        Dim BigCnt ' An extra variable for counting ("down") the main Big data Range
        ' Here a Temporary list of values to be summed based on Unique "Name" & "Text Type" will be |||
        Dim TempSumDik As Object: Set TempSumDik = CreateObject("scripting.dictionary")
            For BigCnt = 1 To UBound(BD(), 1) ' ... we are taking at each name and at that each unq Dte ...
                If DateValue(BD(BigCnt, 1)) = DikDate.keys()(CntDikDate - 1) And Right(DN(BigCnt, 1), 1) = DikName.keys()(CntDikName - 1) Then
                    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"
                     TempSumDik.Add Key:="" & Right(DN(BigCnt, 1), 1) & ET(BigCnt, 1) & "", Item:=FV(BigCnt, 1) '
                    Else ' we are at looked at Name and Date , but had hit on such a pair before so we do nothing. redundant code
                    End If
                Else ' we are not at a hit for the Name and Date pair being considered in Big loop. Do nothing. Rewdundant code
                End If
            Next BigCnt ' ............have finished a Big down "rows" check  to get Names Date Sum.........
            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
            Dim SumV As Long, SumCnt As Long 'now we have in the Temporary Dik Items the values to be summed so ...
                For SumCnt = 1 To TempSumDik.Count ' ... go through them and ...
                 Let SumV = SumV + TempSumDik.items()(SumCnt - 1) ' ... add them
                Next SumCnt
            Dim strConCat As String ' we are at the point of having a sum and unique date pair for the current name so ...
             Let strConCat = Format(DikDate.keys()(CntDikDate - 1), "dd\/mm\/yyyy") & " - " & SumV ' ... build the required string
             TempDatesListArray.Add strConCat ' Add the entry to an the temporary Arraylist
             Let SumV = 0 ' empty sum for next usage
            Else ' here we had no date match so made no temporary sum so do nothing. Redundant code
            End If
        Next CntDikDate ' Finished each unique date at current unique Name, so go to next unique Name------
     Let arrSOut(CntDikName) = TempDatesListArray.ToArray ' Add as an Array the current ListArray for the Current Name
    Next CntDikName ' Finished Main outer Loop for each unique Name =======================================
' We have our Final Array of Arrrays with all required Output data. So Paste out where wanted.
    For CntDikName = LBound(arrSOut()) To UBound(arrSOut()) 'We have as many Arrays in this Array as Names so we could ...
    'For CntDikName = 0 To DikName.Count - 1 '  ... could also do this
     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
    Next CntDikName
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
 MsgBox prompt:="Took  " & Format((Now() - stTime), "hh:nn:ss")
End Sub

_...____

My last comment regarding a formula to do the same.

The basic information and formulas required for that are in the existing Formulas I gave you, I think.
By working through them it should be possible to get the Formulas’ you want.

That may , however, not be the most wise approach. As I mentioned before, my understanding of formulas is not too great, and the formulas I gave may not be the most efficient anyway.

The spirit of this Forum is that you go away a bit wiser. I would recommend having a go at that yourself, initially at least. If I can do it , I doubt that many people should not be able to.. lol

Certainly you should try please to be very clear on exactly what you want,.. and how. .....

Good luck.

Alan

[HansV]

Alan, I know that the speed at which I reply can be discouraging to others, but please don't let it keep you from posting your solutions. There is more than one road that leads to Rome. We all (me included!) learn from studying different approaches to a problem.