+ Reply to Thread
Results 1 to 101 of 101

Extracting data from database and pasting the extracted values on another sheet

  1. #1
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Extracting data from database and pasting the extracted values on another sheet

    Hello friends,

    I have tried a lot for the past few days but am still struggling... hence, finally posting it here...

    I have a database of approx 225 stocks. Each stock has records in approximately 1600 rows... thus the total database runs into approximately 360,000 rows... Lets cap it at 500,000 rows... since the number of stocks & number of records of each stock may increase... However, since there is a restriction on the size of the file that I can attach, I am attaching an excel file with data of only 4 stocks.....

    Now I want to extract records of 300 rows of each stock... the date can be random..... keeping in mind that the earliest date can be the 300th row in the list of dates (column T).

    I have put the formula in column "AI" to pick the 300 dates. Thereafter, based on my limited knowledge, I have put the formula for extracting the open > high > low > close > etc..... (columns AB to AP) for the first stock (NIFTY)....

    This is what I am trying to achieve:

    I want to extract data for all the stocks for the selected 300 dates... presently, I am not even able to copy the formula down..... it took me over 4 hours and only 25% had been processed.... so if I have to keep changing the date, I really don't know when & how I will be able to do my work....

    once the data is extracted, I want to copy > paste value special the extracted data in another file (MAIN_DASHBOARD.xlsm) in the same folder (on a particular sheet (Main Data) > in particular cells (column B to column P, starting in row 3 > rows will be dependant on the number of stocks and each stock will have 300 rows).... thereafter the existing formulae in this file will be computed automatically.... once the complete calculation is completed.... thereafter, the sheet named "DASHBOARD" (for which I have already defined the page set-up), in the same file needs to be saved as a separate file in .xlsx format in the same folder and the file should be saved as Dashboard_(date in 300th row in yyyymmdd format).xlsx....

    Once this process is completed I want to extract data by changing the date... starting from date in 300th row, then 301st, 302nd and so on till the last date in column T... and for each date, the entire process of copy > paste special value in the specific Main Data sheet of MAIN_DASHBOARD.xlsm and saving the Dashboard sheet should be repeated for each date....

    Can someone help me?

    Many thanks in advance....
    Attached Files Attached Files
    Regards,
    Navin Agrawal

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Extracting data from database and pasting the extracted values on another sheet

    You will speed up the calculation by replacing SUMPRODUCT with SUMIFS

    in AJ2

    =IF($AI2="NO DATA",0,SUMIFS(Database!J:J,Database!$F:$F,$AF2,Database!$I:$I,$AI2))

    Copy across and down

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    1) Assuming both "ExcelForum_Database_Navin.xlsm" and "MAIN_DASHBOARD.xlsm" are in the same folder.
    2) Close "ExcelForum_Database_Navin.xlsm", if open.
    3) Run the code from "MAIN_DASHBOARD.xlsm".

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by JohnTopley View Post
    You will speed up the calculation by replacing SUMPRODUCT with SUMIFS

    in AJ2

    =IF($AI2="NO DATA",0,SUMIFS(Database!J:J,Database!$F:$F,$AF2,Database!$I:$I,$AI2))

    Copy across and down
    Thanks John. Appreciate your suggestion.

    I did try this. It did speeden up things to some extent. But it is still very slow because for each stock there are 8 cells which contain formula each day... so each stock has 8 x 300 = 2400 cells with formulae. When computing for 225 stocks, it adds up to 540,000 cells.... which makes it criminally slow....

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Extracting data from database and pasting the extracted values on another sheet


    Hi,

    it's the classic issue when using Excel as a database software as it's just a calculation interface

  6. #6
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by Marc L View Post

    Hi,

    it's the classic issue when using Excel as a database software as it's just a calculation interface
    Any suggestions of how to overcome this one?

  7. #7
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    1) Assuming both "ExcelForum_Database_Navin.xlsm" and "MAIN_DASHBOARD.xlsm" are in the same folder.
    2) Close "ExcelForum_Database_Navin.xlsm", if open.
    3) Run the code from "MAIN_DASHBOARD.xlsm".

    Please Login or Register  to view this content.
    It does extract data.... but wrong data getting pasted in wrong columns & rows.... trying to streamline and make it simpler....

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Extracting data from database and pasting the extracted values on another sheet


    Like jindon's way, mod it to suit your need …

    Sometimes some tip can reduce execution time in a VBA/Excel code but very often slower than any database software.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    If you just want 300 random selected data for each company then REMOVE all the formula.
    Formula for this kind of job kills your excel.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jindon; 04-23-2018 at 10:08 AM.

  10. #10
    Registered User
    Join Date
    04-25-2009
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Extracting data from database and pasting the extracted values on another sheet

    Navin,

    have you tried Microsoft Access? is a better software than excel when it comes to handling larger volumes of data, plus is so much easier to match records. I would highly recommend it.

  11. #11
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by Jocote46 View Post
    Navin,

    have you tried Microsoft Access? is a better software than excel when it comes to handling larger volumes of data, plus is so much easier to match records. I would highly recommend it.
    Thanks. I have never used Access... so wouldn't know how to load the database and then how to extract in excel....

  12. #12
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    If you just want 300 random selected data for each company then REMOVE all the formula.
    Formula for this kind of job kills your excel.
    Please Login or Register  to view this content.
    Thanks... but why would I want random 300 entries.... It has to be chronological order with the chosen date as the 300th date.... the preceding 299 dates have to be from the database in chronological order....

    Guess the solution is mid-way to the 2 codes you helped me with.... The second one is extracting the data and pasting it correctly... but the dates are in random order.... In the 1st code, the data was getting pasted in the wrong columns.....

    The data from database needs to be pasted in columns B : P with the first entry of ISIN number in cell B3 of MAIN_DASHBOARD.xlsm

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    1) Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

    For normal conversational replies, try using the QUICK REPLY box below.

    2)
    the database in chronological order....
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jindon; 04-23-2018 at 02:47 PM.

  14. #14
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Thanks.... but the dates selected are still random.... even though in chronological order....

    As explained above, the date chosen in cell "T1" should be the 300th date.... the preceding 299 dates have to be from the database in chronological order....

    Thus if we select 10-Apr-2018 as the date in cell "T1", the 1st date to be extracted should be 23-Jan-2017 and thereafter in chronological order till 10-Apr-2018.

    If we select 02-Jan-2017 as the date in cell "T1", the 1st date to be extracted should be 15-Oct-2015 and thereafter in chronological order till 02-Jan-2018.

    Also, the output data should be on the sheet "Main Data" (see attached file) with the title in Row 2 (as shown in the file) and main data starting from Row 3.

    Many Thanks... You have been very kind and helpful....
    Attached Files Attached Files

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    OK, try this one then
    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Many, Many, Many Thanks.... now the output is proper....

    However, a small help...

    Pls see this area of your code:

    Next
    With Range("v1").Resize(, UBound(a, 2))
    .CurrentRegion.ClearContents
    .Value = a
    .Rows(2).Resize(n).Value = b
    .Cells(2, 1).Resize(n).Value = Evaluate("row(1:" & n & ")")
    End With
    End Sub

    The output is coming on the same sheet from cell "V1"

    The output needs to be be on the sheet "Main Data" (see file sent by me) from cell "A2"

    Once again, you have been very helpful in resolving my big problems.... God bless...

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Do you still need the result on the same sheet as well or just output to "Main Data"?

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by Navin Agrawal View Post
    once the data is extracted, I want to copy > paste value special the extracted data in another file (MAIN_DASHBOARD.xlsm) in the same folder (on a particular sheet (Main Data) > in particular cells (column B to column P, starting in row 3 > rows will be dependant on the number of stocks and each stock will have 300 rows).... thereafter the existing formulae in this file will be computed automatically.... once the complete calculation is completed.... thereafter, the sheet named "DASHBOARD" (for which I have already defined the page set-up), in the same file needs to be saved as a separate file in .xlsx format in the same folder and the file should be saved as Dashboard_(date in 300th row in yyyymmdd format).xlsx....

    Once this process is completed I want to extract data by changing the date... starting from date in 300th row, then 301st, 302nd and so on till the last date in column T... and for each date, the entire process of copy > paste special value in the specific Main Data sheet of MAIN_DASHBOARD.xlsm and saving the Dashboard sheet should be repeated for each date....
    Is ""DASHBOARD" sheet in "MAIN_DASHBOARD.xlsm" or the one in the original data?

  19. #19
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Hello,

    I had initially requested output on same sheet, but then i figured out that I will have to keep copying and pasting from here to MAIN_DASHBOARD.xlsm.... Hence, the output needs to be on the sheet named "Main Data" in the same file...

    Thereafter, cells Q303:JZ602 (300 rows for each stock) in the sheet named "Main Data" needs to be copied "n" times....

    where "n" = number of stocks for which data has been extracted - 2

    Once the calculation is completed, I want the sheet "Dashboard to be saved as a separate file" in xls format in the same folder in a sub folder "Reports" and saved as "Dashboard_(date in row 300 in format "yyyymmdd") in xlsx / csv format

    Once this process is completed I want to extract data by changing the date...

    Starting from date in 300th row, then 301st, 302nd and so on till the last date in column T... and for each date, the entire above-mentioned process needs to be repeated for each date....

    Thanks a million....

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    If you paste current result to "Main Data"!B3, pasted range will be B3:Q1203.
    Thereafter, cells Q303:JZ602 (300 rows for each stock) in the sheet named "Main Data" needs to be copied "n" times
    I don't understand this. Why col.Q up to col.JZ?

  21. #21
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    If you notice, there are 4 stocks with 300 rows for each of them.... The first one is Nifty (which is an index) and thereafter there are 3 more stocks (ABB, ACC, ADANIENT). Hence, you have data in 1200 rows (B3:P1202)..... The title will come in row 2...

    I have a list of about 217 stocks (which may increase or decrease)..... so the number of rows of data will be "n" multiplied by 300 where "n" is the number of stocks...

    So once the data for 217 stocks (217 x 300 = 65100) is extracted in the sheet "Main Data", cells Q303:JZ602 (300 rows for each stock) in the sheet named "Main Data" needs to be copied "n" times....

    where "n" = number of stocks for which data has been extracted - 2

    Once the calculation is completed, I want the sheet "Dashboard to be saved as a separate file" in the same folder in a sub folder "Reports" and saved as "Dashboard_(date in row 300 in format "yyyymmdd") in xlsx / csv format

    Once this process is completed I want to extract data by changing the date...

    Starting from date in 300th row, then 301st, 302nd and so on till the last date in column T... and for each date, the entire above-mentioned process needs to be repeated for each date....

    Many thanks

  22. #22
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    I don't understand this. Why col.Q up to col.JZ?

    I have to formulate some calculations in these columns, which I want to repeat for each stock in my list....

    Hence, I would want to copy Q303:JZ602 (300 rows for each stock) in the sheet named "Main Data" needs to be copied "n" times....

    where "n" = number of stocks for which data has been extracted - 2

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    So you have set the formula already in "Main Data".

    So, after output in "Main Data",

    Clear "Dashboad", autofilter "Main Data" by each company name and paste the result to "Dashboard!A1" and save as csv.

    repeat this n times.

    Correct?

  24. #24
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Attaching the file for understanding....
    Attached Files Attached Files

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Try
    Please Login or Register  to view this content.
    Attached Files Attached Files

  26. #26
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    When I click the action button to activate the macro, the system selects the 300th date in column "R" on sheet "Database"....

    It extracts the data for 300 days for the unique symbols in column "F" of sheet "Database"

    the date chosen in cell "T1" should be the 300th date.... the preceding 299 dates have to be from the database in chronological order....

    Thus if we select 10-Apr-2018 as the date in cell "T1", the 1st date to be extracted should be 23-Jan-2017 and thereafter in chronological order till 10-Apr-2018.

    If we select 02-Jan-2017 as the date in cell "T1", the 1st date to be extracted should be 15-Oct-2015 and thereafter in chronological order till 02-Jan-2018.

    This data is pasted on sheet "Main Data"

    So once the data for 217 stocks (217 x 300 = 65100) is extracted in the sheet "Main Data", cells Q303:JZ602 (300 rows for each stock) in the sheet named "Main Data" needs to be copied "n" times....

    where "n" = number of stocks for which data has been extracted - 2

    I have formulae in Q303:JZ602... so the formula will get copied down in blocks of 300 rows

    Once the calculation is completed, I want the sheet "Dashboard to be saved as a separate file" in the same folder in a sub folder "Reports" and saved as "Dashboard_(date in row 300 in format "yyyymmdd") in xlsx / csv format

    I have formulae put on sheet "Dashboard" as well... hence nothing has to be done on this..... when the data on sheet "Main Data" changes... the output on sheet "Dashboard" will automatically change..... and we need to save it again.......

    Once this process is completed I want to extract data by changing the date...

    Starting from date in 300th row, then 301st, 302nd and so on till the last date in column T... and for each date, the entire above-mentioned process needs to be repeated for each date....

    So the best way may be.... date changes > output data gets pasted on Main Data Sheet, cells Q303:JZ602 is copied down according to number of unique stocks > all the calculation happens > Sheet Dashboard is saved > File is saved > Data in columns A:P in sheet "Main Data" is cleared > Data in cells Q603: JZ15002 is cleared > Next date is chosen by system > the process is repeated.....

    Many thanks
    Last edited by Navin Agrawal; 04-24-2018 at 03:36 AM.

  27. #27
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    no... this is not what it was... My apologies, may be I have not explained correctly.....

    Pls see attached file....

    When I click the action button to activate the macro, the system selects the 300th date in column "R" on sheet "Database"....

    It extracts the data for 300 days for the unique symbols in column "F" of sheet "Database"

    the date chosen in cell "T1" should be the 300th date.... the preceding 299 dates have to be from the database in chronological order....

    Thus if we select 10-Apr-2018 as the date in cell "T1", the 1st date to be extracted should be 23-Jan-2017 and thereafter in chronological order till 10-Apr-2018.

    If we select 02-Jan-2017 as the date in cell "T1", the 1st date to be extracted should be 15-Oct-2015 and thereafter in chronological order till 02-Jan-2018.

    This data is pasted on sheet "Main Data"

    So once the data for 217 stocks (217 x 300 = 65100) is extracted in the sheet "Main Data", cells Q303:JZ602 (300 rows for each stock) in the sheet named "Main Data" needs to be copied "n" times....

    where "n" = number of stocks for which data has been extracted - 2

    I have formulae in Q303:JZ602... so the formula will get copied down in blocks of 300 rows

    Once the calculation is completed, I want the sheet "Dashboard to be saved as a separate file" in the same folder in a sub folder "Reports" and saved as "Dashboard_(date in row 300 in format "yyyymmdd") in xlsx / csv format

    I have formulae put on sheet "Dashboard" as well... hence nothing has to be done on this..... when the data on sheet "Main Data" changes... the output on sheet "Dashboard" will automatically change..... and we need to save it again.......

    Once this process is completed I want to extract data by changing the date...

    Starting from date in 300th row, then 301st, 302nd and so on till the last date in column T... and for each date, the entire above-mentioned process needs to be repeated for each date....

    So the best way may be.... date changes > output data gets pasted on Main Data Sheet, cells Q303:JZ602 is copied down according to number of unique stocks > all the calculation happens > Sheet Dashboard is saved > File is saved > Data in columns A:P in sheet "Main Data" is cleared > Data in cells Q603: JZ15002 is cleared > Next date is chosen by system > the process is repeated.....

    Many thanks
    Attached Files Attached Files
    Last edited by Navin Agrawal; 04-24-2018 at 03:35 AM.

  28. #28
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Once the calculation is completed, I want the sheet "Dashboard to be saved as a separate file" in the same folder in a sub folder "Reports" and saved as "Dashboard_(date in row 300 in format "yyyymmdd") in xlsx / csv format

    My prefrence is .xlsx format since it can be formatted...

    Many thanks..

  29. #29
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    See if this works
    Please Login or Register  to view this content.

  30. #30
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    There are 2 errors happening:

    Once the data for 217 stocks (217 x 300 = 65100) is extracted in the sheet "Main Data", The following step is not being executed:

    Cells Q303:JZ602 (300 rows for each stock) in the sheet named "Main Data" needs to be copied "n" times....

    where "n" = number of stocks for which data has been extracted - 2

    I have formulae in Q303:JZ602... so the formula will get copied down in blocks of 300 rows

    2nd error:
    With regard to saving sheet "Dashboard"..... nothing needs to be pasted here... I have formulae put on sheet "Dashboard" as well... hence nothing has to be done on this..... when the data on sheet "Main Data" changes... the output on sheet "Dashboard" will automatically change everytime the data changes in "Main Data" columns A:P

    The sheet "Dashboard" merely needs to be saved as a separate file" in the same folder in a sub folder "Reports" and saved as "Dashboard_(date in row 300 in format "yyyymmdd") in .xlsx and .csv format

  31. #31
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Regrading 300 rows.

    Is 300 rows for each company guaranteed all the time?
    If so, your logic should be applied, but if any one company has less than 300 records?

  32. #32
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Is 300 rows for each company guaranteed all the time?

    - yes

    If so, your logic should be applied, but if any one company has less than 300 records?

    - I had mentioned in the formulae in my very first file.... but then I thought it may get too complicated for someone helping me... so I didnt pursue it....

    To tackle that, I had put the following formulae in my earlier files.... this formula needed to be copied all the way down....

    Cell "I3" =IFERROR(OFFSET(Database!$R$1,MATCH(I3,Database!$R$2:$R$5000,)-1,0),"NO DATA")

    Cell "J3" =IF($I2="NO DATA",0,SUMIFS(Database!J:J,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "K3" =IF($I2="NO DATA",0,SUMIFS(Database!K:K,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "L3" =IF($I2="NO DATA",0,SUMIFS(Database!L:L,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "M3" =IF($I2="NO DATA",0,SUMIFS(Database!M:M,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "N3" =IF($I2="NO DATA",0,SUMIFS(Database!N:N,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "O3" =IF($I2="NO DATA",0,SUMIFS(Database!O:O,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "P3" =IF($I2="NO DATA",0,SUMIFS(Database!P:P,Database!$F:$F,$F2,Database!$I:$I,$I2))
    Last edited by Navin Agrawal; 04-24-2018 at 04:08 AM.

  33. #33
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    I had to make some changes in the formula because reference cells were different.....

    Is 300 rows for each company guaranteed all the time?

    - yes

    If so, your logic should be applied, but if any one company has less than 300 records?

    - I had mentioned in the formulae in my very first file.... but then I thought it may get too complicated for someone helping me... so I didnt pursue it....

    To tackle that, I had put the following formulae in my earlier files.... this formula needed to be copied all the way down....

    Cell "I3" =IFERROR(OFFSET(Database!$R$1,MATCH(I3,Database!$R$2:$R$5000,)-1,0),"NO DATA")

    Cell "J3" =IF($I2="NO DATA",0,SUMIFS(Database!J:J,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "K3" =IF($I2="NO DATA",0,SUMIFS(Database!K:K,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "L3" =IF($I2="NO DATA",0,SUMIFS(Database!L:L,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "M3" =IF($I2="NO DATA",0,SUMIFS(Database!M:M,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "N3" =IF($I2="NO DATA",0,SUMIFS(Database!N:N,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "O3" =IF($I2="NO DATA",0,SUMIFS(Database!O:O,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "P3" =IF($I2="NO DATA",0,SUMIFS(Database!P:P,Database!$F:$F,$F2,Database!$I:$I,$I2))

  34. #34
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Try
    Please Login or Register  to view this content.

  35. #35
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    The 2 errors are still happening:

    Once the data is extracted and pasted in the sheet "Main Data", The following step is not being executed:

    Cells Q303:JZ602 (300 rows for each stock) in the sheet named "Main Data" needs to be copied "n" times....

    where "n" = number of stocks for which data has been extracted - 2

    I have formulae in Q303:JZ602... so the formula will get copied down in blocks of 300 rows

    2nd error:
    With regard to saving sheet "Dashboard"..... nothing needs to be done on this sheet... I have formulae put on sheet "Dashboard" as well...

    Hence nothing has to be done on this..... after all the calculations are processed after the above step..... The sheet "Dashboard" merely needs to be saved as a separate file" in the same folder in a sub folder "Reports" and saved as "Dashboard_yyyymmdd" (example: Dashboard_20180410) in .xlsx and .csv format

  36. #36
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Your Question: Is 300 rows for each company guaranteed all the time?

    My Answer - yes

    Your Question: If so, your logic should be applied, but if any one company has less than 300 records?

    My answer - I had mentioned in the formulae in my very first file.... but then I thought it may get too complicated for someone helping me... so I didnt pursue it....

    To tackle that, I had put the following formulae in my earlier files.... this formula needed to be copied all the way down....

    Cell "I3" =IFERROR(OFFSET(Database!$R$1,MATCH(I3,Database!$R$2:$R$5000,)-1,0),"NO DATA")

    Cell "J3" =IF($I2="NO DATA",0,SUMIFS(Database!J:J,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "K3" =IF($I2="NO DATA",0,SUMIFS(Database!K:K,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "L3" =IF($I2="NO DATA",0,SUMIFS(Database!L:L,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "M3" =IF($I2="NO DATA",0,SUMIFS(Database!M:M,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "N3" =IF($I2="NO DATA",0,SUMIFS(Database!N:N,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "O3" =IF($I2="NO DATA",0,SUMIFS(Database!O:O,Database!$F:$F,$F2,Database!$I:$I,$I2))

    Cell "P3" =IF($I2="NO DATA",0,SUMIFS(Database!P:P,Database!$F:$F,$F2,Database!$I:$I,$I2))

  37. #37
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Cell "I3" =IFERROR(OFFSET(Database!$R$1,MATCH(I3,Database!$R$2:$R$5000,)-1,0),"NO DATA")
    Do you mean, is it in "Main data!I3"?

    When the data outputs in "Main data", it occupies from Col.B to Col.P, 16 columns...

  38. #38
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Cells Q303:JZ602 (300 rows for each stock) in the sheet named "Main Data" needs to be copied "n" times....
    And paste to where?

    The current code is pasting to "DashBoardA1" for n times.
    If it is blank, no data in Q:AJ in "Main data".

  39. #39
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Your comment:

    Cell "I3" =IFERROR(OFFSET(Database!$R$1,MATCH(I3,Database!$R$2:$R$5000,)-1,0),"NO DATA")
    Do you mean, is it in "Main data!I3"?

    My revert:
    The above formula was on the assumption formula are written in sheet Main Data "I3"....

    But u hv devised a much better system for me.... so these formula were shared with u just to give an idea of how we would treat the problem of a stock not having 300 rows....

    Your comment: When the data outputs in "Main data", it occupies from Col.B to Col.P, 16 columns...

    My revert: data output is from Col. B to Col. P is correct.... Col. A has the serial number

  40. #40
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Then you have formula in Q:JA all the way from row3 to row 1203 in "Main Data" and you want to copy Q:JA to DashBoard?

  41. #41
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Cells Q303:JZ602 (300 rows for each stock) in the sheet named "Main Data" needs to be copied "n" times....

    And paste to where?

    ANSWER:

    It needs to be pasted under this block for "n" number of times in the sheet Main Data....

    Let me explain:

    Suppose we have 217 stocks.... so the total number of rows being occupied will be 217 x 300 = 65100 rows and will occupy A3:P65102

    I am asking you to copy the block Q303: JZ602 (300 rows) and pasting it (n - 2 times).... where n = number of stocks.... in this case 217 stocks....

    Thus, we have to copy the block (Q303:JZ602) 215 times (217 - 2)... thus we will paste it from Q603:JZ65102

  42. #42
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    Then you have formula in Q:JA all the way from row3 to row 1203 in "Main Data" and you want to copy Q:JA to DashBoard?
    There are formulae in Q303:JZ602..... There is a different formula in Q3:JZ302 and nothing needs to be done to these cells..... these will always be there....

    Cells Q303:JZ602 (300 rows for each stock) in the sheet named "Main Data" needs to be copied "n" times....

    And paste to where?

    ANSWER:

    It needs to be pasted under this block for "n" number of times in the sheet Main Data....

    Let me explain:

    Suppose we have 217 stocks.... so the total number of rows being occupied will be 217 x 300 = 65100 rows and will occupy A3:P65102

    I am asking you to copy the block Q303: JZ602 (300 rows) and pasting it (n - 2 times).... where n = number of stocks.... in this case 217 stocks....

    Thus, we have to copy the block (Q303:JZ602) 215 times (217 - 2)... thus we will paste it from Q603:JZ65102




    Regarding Dashboard - NOTHING NEEDS TO BE DONE OR PASTED ON THIS SHEET..... I will be having some output formula on this sheet.... It merely needs to be saved

  43. #43
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Output only to Q303:JZ602 for each company for calculation purpose and the calculated result in Q303:AZ602 to be accumulated from row 603.

    Is that correct?
    If so, when and how do you want to save each file?

  44. #44
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    Output only to Q303:JZ602 for each company for calculation purpose and the calculated result in Q303:AZ602 to be accumulated from row 603.

    Is that correct?
    If so, when and how do you want to save each file?
    NOT THE OUTPUT.... There are formula in Q303:JZ602.... this entire block has to be copied as it is from 603 downwards.... thus, the formula get copied....

    Suppose we have 217 stocks.... so the total number of rows being occupied will be 217 x 300 = 65100 rows and will occupy A3:P65102

    I am asking you to copy the block Q303: JZ602 (300 rows) and pasting it (n - 2 times).... where n = number of stocks.... in this case 217 stocks....

    Thus, we have to copy the block (Q303:JZ602) 215 times (217 - 2)... thus we will paste it from Q603:JZ65102

  45. #45
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    Output only to Q303:JZ602 for each company for calculation purpose and the calculated result in Q303:AZ602 to be accumulated from row 603.

    Is that correct?
    If so, when and how do you want to save each file?
    REGARDING SAVING:

    ONLY SHEET NAMED DASHBOARD HAS TO BE SAVED....

    With regard to saving sheet "Dashboard"..... nothing needs to be done on this sheet... I have formulae put on sheet "Dashboard" as well...

    Hence nothing has to be done on this..... after all the calculations are processed after the above step..... The sheet "Dashboard" merely needs to be saved as a separate file" in the same folder in a sub folder "Reports" and saved as "Dashboard_yyyymmdd" (example: Dashboard_20180410) in .xlsx and .csv format

  46. #46
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Ouput is ok but copy the formula in q:ja?

    Is FillDown method suffice?

  47. #47
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    Ouput is ok but copy the formula in q:ja?

    Is FillDown method suffice?
    I guess Fill down should suffice as long as the formulae are copied.... It is Q303:JZ602..... (not Q:JA as mentioned in your comment)

  48. #48
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Hope this works.
    Please Login or Register  to view this content.

  49. #49
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    you have erased the contents I had put in Q303:JZ602 !!!!!

    These were to be copied down........

  50. #50
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Step 1: Select Date in T1 of sheet Database

    Step 2: Click the action button

    Step 3: The macro extracts the required data and pastes it on sheet Main Data

    Step 4: Copy Q303:JZ602 and paste it down till required ("n" - 2) multiplied by 300..... where "n" is number of stocks

    Step 5: Save sheet Dashboard

  51. #51
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    All are done as you requested.

    If the columns are different, it might clear one or 2 columns, but not all of them, so safely change to
    Please Login or Register  to view this content.
    This clears only col.A to col.P in "Main Data", before output.

  52. #52
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by Navin Agrawal View Post
    Step 1: Select Date in T1 of sheet Database

    Step 2: Click the action button

    Step 3: The macro extracts the required data and pastes it on sheet Main Data

    Step 4: Copy Q303:JZ602 and paste it down till required ("n" - 2) multiplied by 300..... where "n" is number of stocks

    Step 5: Save sheet Dashboard
    I still there is some disconnect in understanding.... my apologies

    Let us assume there are 4 stocks for which you have extracted data from database and pasted on the sheet Main Data....

    Once the data has been extracted and pasted in sheet Main Data "A2:P302"...... this is what needs to be done....

    There will be certain formulae in Q3:JZ302...... Please do not do anything to this block

    There are formulae in Q303:JZ602... This is a block of 300 rows

    This needs to be copied down from row 603 onwards.....

    Question is how many times?

    We have extracted data for 4 stocks.....

    The first stock already has formulae in Q3:JZ302

    The second stock: We are copying the block Q303:JZ602...

    That leaves us with 2 balance stocks.....

    Therefore we paste the block Q303:JZ602..... 2 times... from Q603:JZ1202....

    (Suppose we had extracted data for 50 stocks, we would have to copy the block Q303:JZ602 another 48 times (50 -2)

    After this, we merely save the Dashboard sheet.... which you are already doing....

    Hope this helps

  53. #53
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    So you mean what is in Q303:JZ602 as formula need to be copied as block to next 300 and next 300....
    Not filldown from Q3:JA3 to the last row of col.A?

  54. #54
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    so you mean what is in q303:jz602 as formula need to be copied as block to next 300 and next 300....
    Not filldown from q3:ja3 to the last row of col.a?
    exactly.... You are right now

  55. #55
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    This obviously will result only 1st 300 rows are different and other each 300 formula are equal to what is in Q303:AZ602.
    Please Login or Register  to view this content.

  56. #56
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    This seems to be working fine... I will need to test it rigorously....

    There is only one error.... While saving the file, it is always taking today's date.... it is supposed to take the date in cell "T1" on sheet "Database"

    Can you please fix that.....

    Many many many thanks and God bless you.....

  57. #57
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Change 2 lines of
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  58. #58
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    I made the following change:

    With ActiveWorkbook
    .SaveAs ThisWorkbook.Path & "\Reports\Dashboard_" & Format$(Date, "yyyymmdd") & ".xlsx"
    .Close False
    End With
    Sheets("dashboard").Copy
    With ActiveWorkbook
    .SaveAs ThisWorkbook.Path & "\Reports\Dashboard_" & Format$(Date, "yyyymmdd") & ".csv", xlCSV
    .Close False
    End With
    Application.ScreenUpdating = True
    End Sub


    I replaced the "Date" with "myDate" and it seems to work

    Have I done the correct thing?

  59. #59
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    1)
    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    2)
    You didn't change them
    Please Login or Register  to view this content.

  60. #60
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Thanks for pointing it out....

    Now I have one more favour to ask of you..... If possible

    Can you help me with another Macro, wherein, cell "T1" in the sheet "Database" automatically picks the 300th date in Column "R" when I click the botton for this new macro..... then the remianing process is all carried out till the point of saving the "Dashboard" sheet..... Then the Macro automatically picks the 301st date in Column "R" and does the entire process...... then it chooses 302nd date in Column "R"..... and this goes on till the last date has been reached...... so basically a loop....

    Thus I will have the option of manually choosing a particular date..... and a second option of automatically doing the process repeatedly for all the dates....

    But both macros and their buttons should be separate....

    Possible?

  61. #61
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    Regrading 300 rows.

    Is 300 rows for each company guaranteed all the time?
    If so, your logic should be applied, but if any one company has less than 300 records?
    Hello.... were you able to resolve this query of yours?

  62. #62
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Assign the macro "test" to the current button.
    Change to
    Please Login or Register  to view this content.

  63. #63
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    What is this code for?

    Do I have to make another button and assign the new macro to it?

  64. #64
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    Assign the macro "test" to the current button.
    Change to
    Please Login or Register  to view this content.
    Quote Originally Posted by Navin Agrawal View Post
    What is this code for?

    Do I have to make another button and assign the new macro to it?
    Understood.... I have to replace the existing code with this new code and then attach the existing button to the new code called "TEST"


    One more question.... If a message box pops up saying "You have less than 300 days" .... the problem will not get resolved....

  65. #65
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by Navin Agrawal View Post

    Can you help me with another Macro, wherein, cell "T1" in the sheet "Database" automatically picks the 300th date in Column "R" when I click the botton for this new macro..... then the remianing process is all carried out till the point of saving the "Dashboard" sheet..... Then the Macro automatically picks the 301st date in Column "R" and does the entire process...... then it chooses 302nd date in Column "R"..... and this goes on till the last date has been reached...... so basically a loop....

    Thus I will have the option of manually choosing a particular date..... and a second option of automatically doing the process repeatedly for all the dates....

    But both macros and their buttons should be separate....
    No need to separate.
    This should handle both, so assign "test" to current button.
    Please Login or Register  to view this content.

  66. #66
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by Navin Agrawal View Post
    Hello.... were you able to resolve this query of yours?
    What if a particular stock has less than 300 days, will it skip that particular stock? Will it not extract data for that stock for pasting on "Main Data" sheet?

    I think skipping the stock is the most practical and right way of doing it.... Can this be done?

  67. #67
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Thanks for the combined code..... You are truly very helpful & knowledgeable... God bless you...

  68. #68
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    If you want to exclude the company less than 300 then replace GetRandom with below
    Please Login or Register  to view this content.

  69. #69
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Assuming I have a database of 2000 stocks.... but I want to extract data of a particular set of 50 stocks....

    Can I list down my 50 stocks in a particular column for which I want the output.... so that data of only these stocks is extracted.....

  70. #70
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    I have to replace GetRandom code or Test code with what you have just given??????

  71. #71
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quite possible.

  72. #72
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    If you want to exclude the company less than 300 then replace GetRandom with below
    Means REPLACE GetRandom Sub Procdure code with the one I last posted.

  73. #73
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    Quite possible.
    I understand you have helped me more than I deserve and have given me your valuable time & guidance.... At the cost of being selfish, may I ask you if you will be able to help me on this?

  74. #74
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    If you upload a workbook with the list, it helps.

  75. #75
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Cant upload more than 1 Mb file.... pls suggest

  76. #76
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    No need to upload big size... you can also zip the file.

    I'll go off line for the day, so tomorrow maybe.
    Last edited by jindon; 04-24-2018 at 10:29 AM.

  77. #77
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    ok. Will try to make it as concise as possible and put down everything where I need help and upload it.... hope the zipped file statys under the limit... Many thanks and have a nice day...

  78. #78
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    No need to upload big size... you can also zip the file.

    I'll go off line for the day, so tomorrow maybe.
    is there anyway to transfer a bigger file to you. I cannot send the file with formulae and explanation as attachment here since file is big

  79. #79
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    You said
    Quote Originally Posted by Navin Agrawal View Post
    Assuming I have a database of 2000 stocks.... but I want to extract data of a particular set of 50 stocks....

    Can I list down my 50 stocks in a particular column for which I want the output.... so that data of only these stocks is extracted.....
    50 stocks and 2 stocks make no difference in coding.

    So, even list of 50, if the data is for only 3 or 4 companies, it would be still OK.

    Am I missing something?

  80. #80
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    The data itself is about 75Mb..

    Even if I extract and send only sample data, the way I have done it, The formulae etc, are so heavy that the file is more than 10 Mb.

    The formulae in the block Q3:JZ302 (which stay fixed) and in the block Q303:JZ602 (which need to be copied down) are very heavy..... don't know how to manage them

    Maybe I can send you just the structure and my comments on a word doc... is that fine?

  81. #81
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Copying the formula from Q303:JZ602 and pasting each 300 rows.
    So, Each 300 rows are exactly the same result as Q303:JZ602?

    If so, calculate only Q303:JZ602 and paste only the result, to the rest.

    I haven't seen you formula, so this is only my guess.

  82. #82
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Sorry for replying with Quote

    Yes... u r missing something....

    I hv a database of about 50 - 75 Mb

    Thereafter I need to extract data from column A:P.... presently we are extracting for all the stocks in the list.... I wanted to know if we can do it for a particular set of stocks from all the stocks... you said its possible....

    I asked you to help me do the coding in the same file as the database, not realising that the bulky data + all the formulae will make the file very heavy and slow in computing....

    To add to it, The formulae in the block Q3:JZ302 (which stay fixed) and in the block Q303:JZ602 (which need to be copied down) are very heavy..... so suppose I select 100 stocks from the entire database, I need to copy this block of Q303: JZ602 another 98 more times...... so I will have formule in 100 x 300 rows.... which will make the calculation excruciatingly and criminally slow..... don't know how to manage them

    I can either e-mail the sample file to you or can send the explanation on a word doc....

    Do you think it would make it easier and lighter if we kept the database in a file called "Dashboard_Database.csv" and kept a separate file for all the outputs, calculations and report generation.... "Dashboard_Creator.xlsm"

  83. #83
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    Copying the formula from Q303:JZ602 and pasting each 300 rows.
    So, Each 300 rows are exactly the same result as Q303:JZ602?

    If so, calculate only Q303:JZ602 and paste only the result, to the rest.

    I haven't seen you formula, so this is only my guess.
    The formulae are same, but the output will be different for each block of 300 rows....

    If you notice, the data that we extracted and pasted on "Main Data" sheet from columns A:P... are 300 rows for each unique stock.... the data is different for each unique stock.... The calculations in Q303:JZ602 will be dependant on data in A303:P602... hence it will be different from output in Q603:JA902 since that will be based on data in A603:P902

  84. #84
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    If it is copied by fill down or formular1c1, the reference will change, but now the code is Formula(as per your request).
    That means the formula that is in Q303:JZ602 will be put in each next 300 rows without changing the reference.

    Did you check that?

  85. #85
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    If it is copied by fill down or formular1c1, the reference will change, but now the code is Formula(as per your request).
    That means the formula that is in Q303:JZ602 will be put in each next 300 rows without changing the reference.

    Did you check that?
    I am not conversant with what you just said....pardon my ignorance.... if u explain, maybe I can do it at my end....

  86. #86
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    So you mean what is in Q303:JZ602 as formula need to be copied as block to next 300 and next 300....
    Not filldown from Q3:JA3 to the last row of col.A?
    Quote Originally Posted by Navin Agrawal View Post
    exactly.... You are right now
    That means if Q303 formula is =A303, Q603 formula is also =A303.
    Formula in Q603:JZ902 are EXACTLY the same as formula in Q303:JZ602.

  87. #87
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Let me explain with example:

    If Q303 = A303, then Q603 = A603

    If the value in A303 = 225, then Q303 = A303 = 225

    If the value in A603 = 500, then Q603 = A603 = 500



    If R303 = (B303 + C303) / (D303 - E303),
    THEN, R603 = (B603 + C603) / (D603 - E603)

    If B303 = 100; C303 = 50, D303 = 40, E303 = 20, THEN

    R303 = (B303 + C303) / (D303 - E303)
    R303 = (100 + 50) / (40 - 20) = (150) / (20) = 7.50



    If B603 = 200; C603 = 70, D603 = 60, E303 = 30, THEN

    R603 = (B603 + C603) / (D603 - E603)
    R303 = (200 + 70) / (60 - 30) = (270) / (30) = 9.00

    The formula are being copied down, hence the formula does not change, only the cell reference changes according to the row....

  88. #88
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Then you need to change to
    Please Login or Register  to view this content.

  89. #89
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    where do I hv to paste the above in the code? will this help speeden up the calculation of the bulky formulae?

    Also, what is your suggestion to the following:

    We keep the database in a file called "Dashboard_Database.csv" and kept a separate file for pasting the code and all the outputs, calculations and report generation.... "Dashboard_Creator.xlsm"

    Secondly, once the .csv and .xlsx files have been generated and saved, should we add a code so that A3:P60002 and Q603:JZ60002 is cleared and the file is saved... this will help keep the file light.... let this process be repeated even if we are running the loop....

  90. #90
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    Then you need to change to
    Please Login or Register  to view this content.
    I located the place where I have to paste it.... thanks

  91. #91
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    OK,

    No matter how you separate the data, 60000+ rows x 270 columns of such formula can be never speed up.
    If you explain the formula for 1 row of each column, there will be chance to speed up.

    You might have a chance to speed up if you let vba do the whole job. No formula...

  92. #92
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Hello,

    The process uses up the entire RAM and processing stalls...

    Its probably because of pasting & calculating the formulae in Q303:JZ602 for the "n" number of stocks....

    Presently I am running it for 190 stocks... hence, it has to be pasted in 188 blocks of 300 rows each and then calculate the formulae.... is there any way of doing it in a better & easier manner? I tried leaving it on at night to see how much time it takes.... but it had hardly done a few stocks and the computer was hung....

    Can u suggest....

    Many thanks & god bless....

  93. #93
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Would it help, if we paste 30 columns at a time, instead of all 270 columns at one time?

    What I mean is can we copy Q303:AT602 all the way down (for as many times as required).

    We then copy AU303:BX602 all the way down (for as many times as required).

    We continue doing this till we copy IW303:JZ602 all the way down (for as many times as required).

    Maybe this helps.... What's your opinion?

  94. #94
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    As I said already, remove all formula and let vba to do the whole job.

    Probably faster.

    Formula in 300 x 190 x 270 = 15,390,000 cells... ridiculous to me...

    Why you need to calculate in 270 columns for 1 row?

  95. #95
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    every cell does not have formulae...... Out of the (270 columns) x (300 rows) = 81000 cells, there are formulae only in 16652 cells.... the remaining are empty....

    Hence, out of 15,390,000 only 31,63,880 have formulae... which is 20.56%... the remaining are empty...

    but I need to have the block of (270 columns) x (300 rows) = 81000 cells for each stock....

    how would VBA do the job?

  96. #96
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Then my answer is 'I don't know'.

    Can not comment on what I don't really understand what it does.

  97. #97
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    I appreciate your reply. I can understand, if I am not able to explain, obviously you can't understand and if you don't understand, you can't help me.... The flaw and short coming is at my end....

    Meanwhile, what I have done is that I have physically pasted the block Q303:JZ602 right down.... what it did is made the file very bulky... but atleast my work will get done...

    Only help I need is, how do I remove the code which is copying this block down.... so that this step is completely removed....

    Pls help... and thanks for your help all this while... god bless you...

  98. #98
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extracting data from database and pasting the extracted values on another sheet

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    In "GetRandom" procedure.

  99. #99
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Many thanks... sorry, have been away... let me revert is a bit... appreciate your help, guidance & concern.... God bless...

  100. #100
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    Quote Originally Posted by jindon View Post
    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    In "GetRandom" procedure.

    This helps, but The file is still very big and takes forever to compute.... fault at my end....

    I am now changing the cell reference to "RC" format, as recommended by you.... i will then put the formulae in the block "Q303:JZ602" in VBA and then add .value = .value so that only the value get displayed (in the hope that the file is not heavy....

  101. #101
    Forum Contributor
    Join Date
    03-15-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    267

    Re: Extracting data from database and pasting the extracted values on another sheet

    There a re a couple of other things that are cropping up....

    when we save the sheet "Dashboard" as a separate .xlsx file, it is getting save as it is..... which means it is getting saved with formulae and links to the main original file.....

    The new file is therefore very heavy and keeps getting refreshed everytime the data in mail file changes....

    It needs to be saved with >paste special > values...

    We are presently saving the sheet "Dashbiard" as .xlsx (problems listed above) and in .csv format..... can we also save it as .pdf

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need Help...Extracting variables from pdf invoice extracted to excel.
    By bdpate09 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-30-2015, 06:22 PM
  2. Replies: 0
    Last Post: 10-25-2013, 12:14 PM
  3. Replies: 5
    Last Post: 07-10-2013, 02:33 PM
  4. [SOLVED] Extracting Specific Word From Extracted Innertext
    By Stonesteel15 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-06-2012, 10:18 AM
  5. Cannot view personal workbook on doc extracted from database
    By fabrecass in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-04-2011, 09:43 AM
  6. Extracting 10 random rows from one sheet, and pasting them on another.
    By gb99 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-08-2009, 11:22 PM
  7. Replies: 1
    Last Post: 02-25-2005, 06:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1