+ Reply to Thread
Results 1 to 23 of 23

Create a data set by extracting data from a table? - Part 3. Transforming data in a table.

  1. #1
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Create a data set by extracting data from a table? - Part 3. Transforming data in a table.

    Hi all,

    So, I received fantastic help beyond my wildest expectations from CK76 in two prior threads. By the way, CK76 - I've already found this data very useful and interesting, so thanks again!

    Basically, what was done was to create weekly data sets based on daily data (one day = one row),i.e., 5 rows of data were transformed into 1 row. Not only that, but also data set consisting of partial weeks, i.e., a week consisting of for example Monday-Tuesday, Monday, Wednesday, etc. Excellent.

    Create a data set by extracting data from a table? - Part 2. Partial data. DataModel. DAX.


    Create a data set by extracting data from another data set? Probably complicated...


    I have since this realized that it would be very useful for me if I could have the data 'the other way around' as well - with weekly data integrated into my daily data set.

    Current data set looks like this:

    1.png

    I was wondering if this could be transformed into this:

    2.PNG

    The problem is of course that not all weeks have 5 days. Because of that - no generalized formula can be used for each day.

    I'm sure this won't be easy to accomplish. I'm not even sure it will be useful. But it's one of those things you never know until you try, I guess.

    Thanks so much in advance.

    Elijah
    Attached Files Attached Files
    Last edited by Elijah; 08-08-2019 at 11:26 AM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    See if the attached meets your needs. Some of the results do not match what was placed in the spreadsheet originally, although the results seem correct.
    Most of the columns are populated using formulas similar to: =AGGREGATE(15,6,[Date]/([Week Num.]=A7)/(YEAR([Date])=YEAR([@Date])),1)
    The Prior Week Close, W Open and W Close columns are populated using formulas similar to: =INDEX([C],MATCH(AGGREGATE(14,6,[Date]/([Week Num.]=A7-1)/(YEAR([Date])=YEAR([@Date])),1),[Date],0))
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    Hi Elijah,

    Is this what you are trying to do?
    https://www.lifewire.com/high-low-cl...-chart-3123556
    or
    http://beatexcel.com/high-low-close-chart/
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    Wow. Thanks a lot, JeteMc!

    And sorry for the late reply. Been two crazy weeks and didn't log on until now...

    I'm not able to look into this in detail, but it looks really promising so far.

    How can I populate this for my entire sheet? I tried that just now and it looks like this:

    1.PNG

    Thanks in advance.

    Best regards,

    Elijah
    Last edited by AliGW; 08-25-2019 at 05:42 PM. Reason: Please don’t quote unnecessarily!

  5. #5
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    Quote Originally Posted by MarvinP View Post

    Not quite, Marvin. But appreciate the links and your effort anyway.

    Best regards,

    Elijah

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    My guess is that the Week No. column (A) needs to be copied down to row 3474.
    Let us know if you have any questions.

  7. #7
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    Hi again, JeteMc,

    That explained it! I was too tired to spot that the other day as I had weeknumber to the far right on my table and didn't see it in column A also.

    Allright, since you're so kind, there actually are a few things I'm noticing now:

    1) Column C - F.

    The data is not formatted as text. This makes it harder to filter for example a Monday. I also checked by converting two Mondays and they have a unique number both. So sorting/filtering using some other tool is probably not easy.

    Also, I did try filtering the data with auto-filter just now and it seems to be very slow.

    The way I typically use this data is that I will do a lot of filtering back and forth to check for similar conditions.

    2) This one is a minor one and not really worth bothering you with, but it would be great to have the weekday names in English. I'm in Norway, so by default I get it in Norwegian. Not sure if there's a workaround for that. I used
    Please Login or Register  to view this content.
    to achieve the same with Month names as I had the same issue there. Column B. Maybe the same approach can be used. Didn't have time to look into it myself just now.

    I imagine one way to work around all this if no easy solution can be found with formulas is to load this table into PowerQuery and transform the data there. That should do the trick, I think. It's less than ideal, but should be sufficient for my purposes.

    Thanks in advance for all your help and patience.

    Elijah

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    I feel as if your solution to the month issue will work for columns C:F as well.
    For example the formula for column C could be modified to read: =TEXT(AGGREGATE(15,6,[Date]/([Week Num.]=A7)/(YEAR([Date])=YEAR([@Date])),1),"[$-409]dddd")
    When I tested I didn't find the filtering to be slower than normal.
    Let us know if you have any questions.

  9. #9
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    Hi, JeteMc,

    Thanks again for your assistance!

    1. I just tested the solution for the text issue and it works great. So, problem solved.

    2. Regarding the filtering - Are you sure?

    Initially, I thought maybe it's due to the sheer size of the table, so I cut it in half.

    Normally, filtering a similar table is instantly done. This one takes about 3 seconds to set a filter on my computer.

    I just did a quick test now and loading your table into PowerQuery and outputting it as a new table takes care of this problem.

    Regardless, I'm very happy with the outcome, JeteMc, and I thank you for your help once again!

  10. #10
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    I was about to mark this thread solved, but then I started to think...

    There may be a problem with this and it is of course my fault only who didn't explain it properly or think of it in advance. As often is with these problems, things pop up after one thinks oneself done or thought everything was planned out correctly.

    What I'd really want is to have this weekly derived data update during the week, i.e., a given day of the week can't have the week high/low any later than that day since it should be based upon the values up until that given day.

    Meaning:

    Every Monday in the data set would have the Monday High/Low as the Week High/Low.

    Every Tuesday in the data set would have the Week High/Low on either Mon/Tues.

    Every Wednesday in the data set would have the Week High/Low on either Mon/Tues/Wed.

    This allows me to make accurate comparison/filtering between for example a Wednesday where the Week Low is currently on Monday and the Week High is currently on Tuesday.

    If you follow?

    Just not sure if this can be done with formulas...?

    My sincere apologies for not catching this earlier, but it's been a rough patch lately and I didn't realize this until I started playing with the data tonight.

    Elijah

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    I would think that the spreadsheet already does what you describe. For example if I delete row 7 (8/2/19) so that it would be the same as if only Monday through Thursday had ever been entered, it seems to me that the high (Monday) and new low (Thursday) are what you want based on the description.
    However if not then I am not following.
    Perhaps if you could put together a small sample that illustrates the issue it will make it easier for someone to help.
    Let us know if you have any questions.

  12. #12
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    Correct.

    But what about the rest of the dataset?

    Let's say I filter out only Wednesdays.

    I then want Wednesday's to show me weekly data based upon Monday-Wednesday. Thursday and Friday is in the 'future', so we shouldn't know about those. Point is - weekly values develop as the week develops.

    As it is now, if I filter a Wednesday, the values are associated to the max/min for the entire week and may show for example week high on Friday.

    Do you follow now?

    Best regards,

    Elijah
    Last edited by AliGW; 08-25-2019 at 05:40 PM. Reason: Please don’t quote unnecessarily!

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    Let me restate to make sure that I am understanding.
    You want to filter the file, presumably the Day column, so that it will only show Monday through Wednesday of all weeks dating back to September 2005.
    You would like for the formulas in columns 1st Day through W Close to only display results based only on Monday through Wednesday in each of those weeks.
    Is that correct?

  14. #14
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    Hi, JeteMc,

    Thank you for your comment and patience.

    Wednesday was only an example. My data set has one row of data per each day.

    So, if I want to look up Wednesdays, I want them to only show/evaluate "weekly values" up until Wednesdays. If I look at Thursdays, I want them to only show "weekly values" up until Thursday.

    Monday - The week High/Low will always be the Monday High/Low.

    Tuesday - The week High/Low will be either on Monday or Tuesday.

    Wednesday - The week High/Low will be either on Monday, Tuesday or Wednesday.

    Thursday - The week High/Low will be either on Monday, Tuesday, Wednesday or Thursday.

    Friday - By now, the week is complete. So the entire week can be evaluated.

    Do you follow?

    But, I suspect it might not be easy to accomplish with formulas alone. So, please don't worry too much about it.

    Best regards,

    Elijah
    Last edited by AliGW; 08-25-2019 at 05:43 PM. Reason: Please don’t quote unnecessarily.

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    Yes, I believe that I understand what you want and no, I don't believe that formulas will accomplish that. I am not familiar with the capabilities of VBA so I can not say with confidence that VBA can accomplish this either, however I will ask some of the contributors on the VBA forum to take a look.
    I have done some updating of the file.
    I hope that you have a blessed day.
    Attached Files Attached Files

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    Administrative Note:

    Elijah - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  17. #17
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    If I'm understanding correctly, I think you can get the results you want without VBA, if not necessarily in the exact manner you proposed. Using JeteMc's work in post #15, I added a helper column, entitled "Visible?" to denote which rows survived your filters. It uses the formula:

    =SUBTOTAL(103,[@Date])

    You can then tack a "/([Visible?]=1)" term onto the end of the existing "W High" and "W Low" formulas so that they only account for rows that are visible postfilter. If you want to see the data for M-W, then filter out Thurs and Fri on the "Day" column. If you want M-Thurs, then just filter Fri, etc. Your "Week High" and "Week Low" columns respond to the "W High" and "W Low" formulas, so they don't need to be changed. For aesthetic purposes, you can hide the "Visible?" column (then bask in the irony).

    Note that these changes mean that the High and Low formulas will respond to filters on ANY column, which might be good or bad depending on how you utilize filters in other columns.

    Take a look at the attachment to see if the changes prove helpful:
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  18. #18
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    Quote Originally Posted by JeteMc View Post
    I have done some updating of the file.
    I hope that you have a blessed day.
    Thank you so much, JeteMc!

    I wish you a blessed day too.

    I just wanted to log on and give a sign of life. I have simply been to busy to check out your latest update, but I will eventually.

    Best regards,

    Elijah

  19. #19
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    Quote Originally Posted by CAntosh View Post
    Take a look at the attachment to see if the changes prove helpful:
    Hi, CAntosh,

    Thank you so much for trying to help out. I'm in a tight spot now timewise, so just letting you know that it's received on my end. I took a quick look, but not able to say for sure as it require some thinking and analyzing to be sure I follow you.

    I will see if I can find time tomorrow and report back then.

    Thanks again.

    Elijah

  20. #20
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    Hi again, Cantosh and JeteMc,

    So, I finally had the time to sit down and check this and it appears to work great!That is - the W High/Low, both weekday and values appear to be correct and update according to the filters being used on "Day" column.

    The "W Close" column does not update however. It's always showing for the last day of the week. But I notice it has a different formula than W High/W Low. Maybe the same formula can be adapted.

    Anyway, my only real complaint - and I sure hate to complain when I'm getting such excellent help for free - is that setting filters and updating the table takes quite a long time.

    I suggested (and tested myself) earlier how loading the data into PowerQuery would solve that problem. Not sure if that's possible with this new solution. Maybe the SUBTOTAL function can be loaded into PowerQuery as well?


    Quote Originally Posted by CAntosh View Post
    Note that these changes mean that the High and Low formulas will respond to filters on ANY column, which might be good or bad depending on how you utilize filters in other columns.

    Take a look at the attachment to see if the changes prove helpful:
    Can you expand on that?

    Ultimately, I will probably want to filter the other columns also.

    Like, I might want to check a Wednesday where the Week Low was on Monday and the Week High on Wednesday. Find similar "patterns" in my data set - the refine that filtering by looking at for example % Change values. In order to find similar data.

    Thanks again for all help. Really useful and really grateful for this.

    Best,

    Elijah

  21. #21
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    I have a feeling that you are working from a different or updated version of the file that CAntosh attached to post #17. If so please upload that file.
    Let us know if you have any questions.

  22. #22
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    1. In my sample file, I only changed the formulas for W High and W Low, so they are the only ones that respond to the filters. The other columns using AGGREGATE can be modified the same way, though. Just slap /([Visible?]=1) onto the end of the array portion of JeteMc's AGGREGATE formula and the new formula will look only at unfiltered rows. For instance, W Close would become:

    =INDEX([C],MATCH(AGGREGATE(14,6,[Date]/([Week Num.]=[@[Week Num.]])/(YEAR([Date])=YEAR([@Date]))/([Visible?]=1),1),[Date],0))

    2.
    Can you expand on that?

    Ultimately, I will probably want to filter the other columns also
    Any column with the /([Visible?]=1) modification will only look at visible rows, so no matter what filter(s) you use on any column(s), the fields with /([Visible?]=1) will adjust to consider only the remaining visible columns.

    3. A PowerQuery solution might be doable, but I have my doubts as to whether it will be notably faster. I'm not a PowerQuery expert, though, so I'd be happy to be proven wrong. The calculation speed is a bit sluggish because you have a number of somewhat complex formula fields acting through 14 years worth of daily data. Two options to consider are:
    a) You'll see slight speed gains if any of your formula columns can be turned into values, e.g. Month, Week Num, … maybe 1st Day and Last Day? Look at which columns are actually variable, and which ones just needed formulas during the building of the table, but don't anymore.
    b) If possible, archive some of the older data if it's no longer explicitly used.

    Hope this helps!

  23. #23
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Create a data set by extracting data from a table? - Part 3. Transforming data in a ta

    Quote Originally Posted by CAntosh View Post
    Hope this helps!
    Thanks a lot, CAntosh. It certainly helps!

    I will try and implement those formulas you posted tomorrow. Been a crazy few busy weeks in my day job, hence my very slow reply here. But I will get back to you guys eventually.

    Talk later/tomorrow.

    Best regards,

    Elijah

+ 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. Replies: 37
    Last Post: 08-02-2019, 05:47 AM
  2. Comparing cell value to a table then extracting data from second table column
    By JaredMcOffice in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-05-2017, 01:42 AM
  3. Replies: 5
    Last Post: 01-28-2016, 01:43 PM
  4. Replies: 0
    Last Post: 11-06-2014, 07:25 PM
  5. Extracting data from a table based on a common entries with another table
    By shannoncox in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-31-2012, 04:45 PM
  6. help : extracting data to create a table (employee time sheet)
    By conuk in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 01-14-2009, 02:26 PM
  7. Extracting part of a data string to create a table
    By chris.howes in forum Excel General
    Replies: 0
    Last Post: 07-31-2007, 10:11 AM

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