Formula
-
- Administrator
- Posts: 12609
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Formula
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.
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.
StuartR
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: Formula
Sure. But i dont have option of changing the post title for this post. In future i will do it.
Thanks Stuart :)
Thanks Stuart :)
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Formula
I also moved this to the Excel forum as it is an Excel query, not Access/SQL.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: Formula
Rudi,
Actually this is an access query not excel :)
Thanks
Actually this is an access query not excel :)
Thanks
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Formula
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. )
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. )
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 12609
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Formula
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.sathya wrote:Sure. But i dont have option of changing the post title for this post.
StuartR
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Formula
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
Using Excel 2007 32 bit
Here is the code
_......
Here is the File with code in it
https://app.box.com/s/1uy0vjoczycc68ex289t18eugbq0eyfk" onclick="window.open(this.href);return false;
Alan
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 thisUsing 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
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Formula
Hellosathya wrote:Formula
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 10Formulas 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
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: Formula
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 :)
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 :)
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Formula
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
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
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: Formula
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 :)
Thanks again :)
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Formula
Thanks for the feedback
Good Luck
Alan
Good Luck
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: Formula
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 :)
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 :)
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Formula
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:
Alan
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:
But hopefully I may have indirectly answered at least some of what you were asking?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?...
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: Formula
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 :)
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 :)
-
- Administrator
- Posts: 12609
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Formula
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
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
You do not have the required permissions to view the files attached to this post.
StuartR
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Formula
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)
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
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_...
_...would beDoc.AElstein wrote:_........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._.....
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
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also