Formula

[sathya]

Hi Hans and Rudi,

Please check the attachment when you get time.

Thanks

[StuartR]

Sathya,

It would be really helpful if you gave your post a meaningful subject, and then explained what formula you are looking for help with in the body of your post.

[sathya]

Sure. But i dont have option of changing the post title for this post. In future i will do it.

Thanks Stuart :)

[Rudi]

I also moved this to the Excel forum as it is an Excel query, not Access/SQL.

[sathya]

Rudi,

Actually this is an access query not excel :)

Thanks

[Rudi]

My apologies...

Can I confirm then that the workbook is a representation of a table/query in Access and you need a formula in Query syntax instead of Excel syntax?
(If the above is correct, you really did not provide much clarity in your opening post. )

[StuartR]

Sure. But i dont have option of changing the post title for this post.

If you edit your post then you can change the title. This won't affect the titles of replies, but you can change the titles of posts you created.

[Doc.AElstein]

Hi sathya
You said Formula.. and Access.. _..

_.. so I did an Excel VBA code ..


The code I give will change this

Using Excel 2007 32 bit

Row\Col	B	C	D	E	F
10	11.10.2016 14:43	11.10.2016 14:44	TEXT 1	50
11	11.10.2016 14:54	11.10.2016 14:57	TEXT 2	50
12	06.10.2016 14:22	06.10.2016 14:37	TEXT 1	50
13	04.10.2016 15:44	04.10.2016 16:00	TEXT 1	50
14	11.10.2016 16:35	11.10.2016 16:36	TEXT 3	50
15	06.10.2016 10:14	06.10.2016 10:42	TEXT 1	50
16	04.10.2016 15:19	04.10.2016 15:21	TEXT 1	50
17	11.10.2016 16:51	11.10.2016 16:56	TEXT 2	50
18	11.10.2016 17:51	11.10.2016 17:58	TEXT 2	50
19	04.10.2016 16:19	04.10.2016 16:29	TEXT 1	50
20	04.10.2016 12:44	04.10.2016 12:54	TEXT 2	50
21	04.10.2016 11:44	04.10.2016 12:54	TEXT 2	50

|< < > >|_Sheet1_/___//

To this

Using Excel 2007 32 bit

Row\Col	B	C	D	E	F
10	11.10.2016 14:43	11.10.2016 14:44	TEXT 1	50	11/10/2016 - 150
11	11.10.2016 14:54	11.10.2016 14:57	TEXT 2	50	11/10/2016 - 150
12	06.10.2016 14:22	06.10.2016 14:37	TEXT 1	50	06/10/2016 - 50
13	04.10.2016 15:44	04.10.2016 16:00	TEXT 1	50	04/10/2016 - 100
14	11.10.2016 16:35	11.10.2016 16:36	TEXT 3	50	11/10/2016 - 150
15	06.10.2016 10:14	06.10.2016 10:42	TEXT 1	50	06/10/2016 - 50
16	04.10.2016 15:19	04.10.2016 15:21	TEXT 1	50	04/10/2016 - 100
17	11.10.2016 16:51	11.10.2016 16:56	TEXT 2	50	11/10/2016 - 150
18	11.10.2016 17:51	11.10.2016 17:58	TEXT 2	50	11/10/2016 - 150
19	04.10.2016 16:19	04.10.2016 16:29	TEXT 1	50	04/10/2016 - 100
20	04.10.2016 12:44	04.10.2016 12:54	TEXT 2	50	04/10/2016 - 100
21	04.10.2016 11:44	04.10.2016 12:54	TEXT 2	50	04/10/2016 - 100

|< < > >|_Sheet1_/___//

_......

Here is the code

Code: Select all

 Sub sathyaIssueGesundheit() 'http://www.eileenslounge.com/viewtopic.php?f=27&t=24852&p=192663#p192663
Dim ws1 As Worksheet
 Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim rngBD As Range
 Set rngBD = ws1.Range("B10:B" & ws1.Cells(Rows.Count, 2).End(xlUp).Row & "")
Dim BD() As Variant
 Let BD() = rngBD.Value2
Dim Steer As Range
Dim myBDikey As Object
 Set myBDikey = CreateObject("Scripting.Dictionary")
    For Each Steer In rngBD
    Dim myLong As Long
     Let myLong = myBDikey.Item(Format(Steer.Value, "dd.mm.yyyy"))
    Next Steer
Dim arrmyBDikey() As Variant
 Let arrmyBDikey() = myBDikey.keys()
Dim Cnt As Long
    For Cnt = 1 To myBDikey.Count
    Dim MyTempDik As Object
     Set MyTempDik = CreateObject("Scripting.Dictionary")
        For Each Steer In rngBD
         If Format(Steer.Value, "dd.mm.yyyy") = arrmyBDikey(Cnt - 1) Then
            If Not MyTempDik.exists(Steer.Offset(0, 2).Value) Then
             MyTempDik.Add Key:=Steer.Offset(0, 2).Value, Item:=Steer.Offset(0, 3).Value
            Else
            End If
         Else
         End If
        Next Steer
    Dim arrmyTempDik() As Variant
     Let arrmyTempDik() = MyTempDik.items()
    Dim Cnt2 As Long, SumValue As Long
        For Cnt2 = 1 To MyTempDik.Count
         Let SumValue = SumValue + arrmyTempDik(Cnt2 - 1)
        Next Cnt2
    Dim Steer2 As Range
        For Each Steer2 In rngBD
         If Format(Steer2.Value, "dd.mm.yyyy") = arrmyBDikey(Cnt - 1) Then
          Let Steer2.Offset(0, 4).Value = Replace((CStr(Format(Steer2.Value, "dd/mm/yyyy"))), ".", "/", 1, -1) & " - " & SumValue
         Else
         End If
        Next Steer2
    Let SumValue = 0
    Next Cnt
End Sub

_......

Here is the File with code in it

https://app.box.com/s/1uy0vjoczycc68ex289t18eugbq0eyfk" onclick="window.open(this.href);return false;


Alan

[Doc.AElstein]

Formula

Hello

Doing a single formula is a bit outside my abilities.
But in the enclosed File_...
issue2BU.xlsm
_.. is a Formula in column U which based on your sample data gives the requested output.
I needed to use a few helper columns.

Using Excel 2007 32 bit

Row\Col

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

9

Concatenations to get a Unique list from so as to remove Duplicates

Unique Concatenations

"Date" hopefully as text

"Date" alternative that may help if there are date Format problems

This gives one "Text Value" for every unique Text Type that each date has

Without this bodge sum does not work. I guess this makes it turn into a number

Here we have dates, one for each unique "Date and Text" Type combination

Unique dates

Total "Text Value" for each Unique date

Get the total "Text Value" for every date

Almost the final Text string

Final Text String

A bit of substituting of help columns to remove some help columns. This is simply made ba taking formula in Column S and substituting in for some columns the actual formula in that column

10

11.10.2016 TEXT 1|50

11.10.2016 TEXT 1|50

11.10.2016

11.10.2016

50

50

11.10.2016

11.10.2016

150

150

11.10.2016 - 150

11/10/2016 - 150

11/10/2016 - 150

|< < > >|_Sheet1_/___//

Using Excel 2007 32 bit

Row\Col

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

10

=TEXT(B10, "TT.MM.JJJJ") & " " & D10 & "|" & E10

=IFERROR(INDEX($H$10:$H$21, MATCH(0, COUNTIF($I$9:$I9,$H$10:$H$21), 0),1),"")

=LEFT(B10,10)

=LEFT(H10,10)

=IF(ISERROR(MID(I10,SEARCH("|",I10)+1,LEN(I10)-SEARCH("|",I10))),"",MID(I10,SEARCH("|",I10)+1,LEN(I10)-SEARCH("|",I10)))

50

=LEFT(I10,10)

=IFERROR(INDEX($N$10:$N$21, MATCH(0, COUNTIF($O$9:$O9,$N$10:$N$21), 0),1),"")

=IF($O10="","",SUMIF($N$10:$N$15,$O10,$M$10:$M$15))

=VLOOKUP($J10,$O$10:$P$21,2,FALSE)

11.10.2016 - 150

=SUBSTITUTE(R10,".","/")

=SUBSTITUTE(LEFT(TEXT(B10, "TT.MM.JJJJ") & " " & D10 & "|" & E10,10) & " - " & VLOOKUP(LEFT(TEXT(B10, "TT.MM.JJJJ") & " " & D10 & "|" & E10,10),$O$10:$P$21,2,FALSE),".","/")

|< < > >|_Sheet1_/___//

The formulas all go in Row 10

Formulas in Column I and O are CSE type 2 Formulas
To Paste those two CSE type 2 Formulas in:
Copy Formula complete to the Clipboard ( Highlight formula from last table above and Hit Ctrl+C )
Select Cell in Row 10.
Hit F8 and / or select Formula Bar
Paste Formula in ( Ctrl + V )
Hold down Keys Ctrl + Shift, and Hit Key ENTER

Once all Formulas are added, select them all ( H10:U10)
Hover at bottom right of Cell U10 until a black + appears
Hold left Mouse down.
Drag all formulas down.

_..
You should then get this

Using Excel 2007 32 bit

Row\Col	U
10	11/10/2016 - 150
11	11/10/2016 - 150
12	06/10/2016 - 50
13	04/10/2016 - 100
14	11/10/2016 - 150
15	06/10/2016 - 50
16	04/10/2016 - 100
17	11/10/2016 - 150
18	11/10/2016 - 150
19	04/10/2016 - 100
20	04/10/2016 - 100
21	04/10/2016 - 100

|< < > >|_Sheet1_/___//

Alan

' Rem Ref ( Possibly the explanations to the formulas are here, .. somewhere )
https://app.box.com/files/0/f/366412696 ... 0464795673" onclick="window.open(this.href);return false;
http://www.mrexcel.com/forum/excel-ques ... ost4161239" onclick="window.open(this.href);return false;
http://www.mrexcel.com/forum/excel-ques ... ost4285654" onclick="window.open(this.href);return false;

EDIT: You may need to adjust this formula a bit
TEXT(B10, "TT.MM.JJJJ") & " " & D10 & "|" & E10
_.. I always get inconsistant date formats and they do not seem to translate correctly from my German Excel _.. you may need something like
TEXT(B10, "dd.mm.yyyy") & " " & D10 & "|" & E10
possibly the formula in the File I uploaded comes up OK in your Excel ? - I would be interested to know
Alan

[sathya]

Hi all,

Sorry for really late reply as i was stuck with personal issue :(.

Thanks a lot for all kind heart people who helped me.

Basically i have access with form with formulas in each Text label. I tried both the above codes but still i am not getting solution.

Any idea guys?

Thanks again :)

[HansV]

What are you showing in your spreadsheet? Is it a table, or a query, or ...? Please try to provide clear, relevant and sufficient information for us to help you.

[Doc.AElstein]

Hi sathya
Thanks for getting back.
I do not have the ACCESS Software, or any experience with ACCESS
I am not sure how to apply codes and formulas in ACCESS

I doubt if I can help with ACCESS things.

If you are having problems running my code or getting the formulas to work in a File like the one you uploaded, then if you upload your file I can take a look

If you were just using the Excel Spreadsheet to represent how something looks in some other thing then I may not be able to help.

As Han’s said, if you explain what that spreadsheet was supposed to represent then maybe someone else can help. I do not understand, for example, what this means
“….access with form with formulas in each Text label….“
But possibly someone with ACCESS experience does.

I originally saw the Thread in the Excel Sub Forum and, possibly naively, having seen your .xlsx File just did a solution on that File, which gave the results you asked for. ( I think the Thread has gone back now to the ACCESS / SQL Forum )

Alan

[sathya]

Thanks a lot for your help. Much appreciated. I definetly will use your formula incase if i cant find solution in Access. Your code is working great. Sorry i was wrong. But i am looking for formula to put in my access.
Thanks again :)

[Doc.AElstein]

Thanks for the feedback
Good Luck
Alan

[sathya]

Thanks Alan :)

[sathya]

Alan,

I am using ur issue2BU.xlsm as base reference. But the issue is i wanted to copy the formula from "row 10 to row 100000". As this slow down the excel a lot. I know that if we use formula it will slow down. Is there is anyway to put the same formula in vba which will increase the speed of the excel?

Thanks again :)

[Doc.AElstein]

Hi sathya
Assuming I had no problems with the date formats, ( which I unfortunately often do ) , then I could write a code to paste in those formulas for you. ( for just about any range like 1000000 rows ) . So that would save you having to drag them all down the worksheet manually.
If i did that I would probably then simplify them formulas a bit.
But I am not too good with formulas, and someone better could probably do a single formula for you.
But even then you would still have a Spreadsheet with lots of very long formulas in it.
Basically all my formulas would be somehow integrated into one, as I started to do – ( The formulas in Column S and U both give the same result. But column S formula requires most of the help columns. Column U formula need a few less of the help columns )
I do not have much experience with Formulas , especially lots of long ones. But I imagine it is generally not very efficient for many rows. 100000 rows sounds horrendous to me , but I have little experience with more than about 10000 . On the few occasions I played around with 10000 rows of formulas I remember often hitting the limit of my computers.

_...

My code basically does what the formulas do. I wrote the formulas in a sequence to do approximately what my code does.
I think my code is not too slow. A simple mod is to put
Application.ScreenUpdating = False
At the start and
Application.ScreenUpdating = True
at the end.

Probably a few other simple Mods can improve the speed a little bit.

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

My code is what people ( or at least Me ) tends to call a “spreadsheet interaction” code. They are generally a bit slow – every interaction with a spreadsheet is like “slamming the brakes on” in a code.

The Code can be rewritten in an “VBA Array” type code. This basically captures all data from the Spreadsheet to an Array or a few very big Arrays in one go initially. Then all the processing is then done “internally” as it were. A final Array or a few big Arrays are built up of all results and then they are pasted out Finally in one or a few steps
Generally an Array code is quite a bit faster, as it minimises the direct interactions with the Worksheet. ( Excel can work very fast when manipulating Arrays as aposed to continually referrencing the spreadsheet )

_...

You could also arrange that the code be a Worksheet Change type code_...
http://www.eileenslounge.com/viewtopic. ... 29#p192702" onclick="window.open(this.href);return false;
_.... Such a code would start automatically when you type any new values in.

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

As I said before, I have no idea what so ever, how all this fits in with ACCESS things, or exactly what you want to do.
All the info I have given is assuming your requirement is an Excel Workbook.

I do not understand what one does with ACCESS. I know it is some sort of efficient data storage and retrieval system. I Personally still prefer keeping things in text files and manipulating as required in Excel.
But this is die to my ignorance of ACCESS and the fact that I do not have that Software.

_....

I do not fully understand what you are saying here:

... i wanted to copy the formula from "row 10 to row 100000". As this slow down the excel a lot. I know that if we use formula it will slow down. Is there is anyway to put the same formula in vba which will increase the speed of the excel?...

But hopefully I may have indirectly answered at least some of what you were asking?

Alan

[sathya]

Thanks a lot for your reply Alan,


I do not fully understand what you are saying here: sathya wrote:
... i wanted to copy the formula from "row 10 to row 100000". As this slow down the excel a lot. I know that if we use formula it will slow down. Is there is anyway to put the same formula in vba which will increase the speed of the excel?...



What i mean is, i wanted to replicate your formula from row 10 to row 100000. That is clicking and draging for 100000 row. Thanks a lot for your help again :)

[StuartR]

You don't have to drag to do this. Simply copy and paste.

Select the cell or cells you want to copy.
Control-C to copy them
Type the target address into the selection box at the top left in Excel
Control-V to paste.

For example if the original data is in F10 and G10 then select those cells and copy them
Then type F11:G10000 into the Name Box to the left of the formula bar and press Enter
Then type Control V to paste

[Doc.AElstein]

Hi sathya



What Stuart showed is neat – I had never tried that. That seems a neat alternative to dragging down.

So going back to my post here
http://www.eileenslounge.com/viewtopic. ... 78#p192824" onclick="window.open(this.href);return false;

Stuart’s alternative to this_...

_........Once all Formulas are added, select them all ( H10:U10)
Hover at bottom right of Cell U10 until a black + appears
Hold left Mouse down.
Drag all formulas down._.....

_...would be

Once all Formulas are added, select them all (H10:U10)
Copy to the Clipboard ( Ctrl + C )
Then type select the Name Box to the left of the formula bar
Type in H11:U21
Hit ENTER ( That should highlight Range H11:U21 )
Then type Ctrl + V to paste in

I tried, and it seems to work :)

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

If you want to paste my formulas down to further rows, then there is a subtlety, as Formulas in Column I and O are CSE type 2 Formulas.
In order for those formulas to work ( and the reason why you need to “tell” Excel that such a formula is coming with the Ctrl + Shift + Enter ) is that they contain “Arrays” of fixed size. In these formulas those Arrays are:

In Formula in Column I the fixed Range ( “Array” ) is $H$10:$H$21 ( You probably know that the $ makes the numbers fixed so that they do not change when you drag down or use Stuart’s idea )

In Formula in Column O the fixed Range ( “ Array” ) is $N$10:$N$21

You will need to change those to suit your extended data Range down to 100000 instead of 21
But I would advise if you chose to do that, to do it in stages first…

_........

How does all that sound?

I could still write a code to do the same, and / or paste in those formulas for you.
Or write a quicker code

But I am still not sure exactly what your end requirement is and / or if / how we are or can help

Remember the point of a Forum is to help you with difficulties, and help get you going.
We prefer to work with sample reduced size data, and then develop solutions which are usually applicable with little or no modification to larger actual real life data.
It can start getting a bit time consuming for us to start handling actual large amounts of data.

But let us know how you get on

Alan