+ Reply to Thread
Results 1 to 6 of 6

Help aligning multiple measurement series on same date axis for line diagram

  1. #1
    Registered User
    Join Date
    09-29-2021
    Location
    Lund, Sweden
    MS-Off Ver
    Microsoft 365 Apps for enterprise
    Posts
    15

    Help aligning multiple measurement series on same date axis for line diagram

    Hi!

    I have a file with alot of measurment series, on sheet "Sammanst?llning". I want to compare each series as seperate lines in a line diagram on
    sheet "Diagram". Each series is framed by a thick border, within each series there is a column for date and measurment. Each series start and end date
    is different. And between some series the measurments are taken on differnt date intervals.

    I would like to sort the series so they line up between start dates, to be able to compare each series in a line diagram with a single.

    This could be done by manually dragging the series to line them up, but this would take a lot of time since there are 34 measurement series, varying in length with a couple of thousand rows. This method would also not solve the issue of some series having measurement point taken at different dates.

    Example.png

    Do you smart folks have any sorting function or macro that would be able to align all these series, to be able to combine them in a single diagram for ease of comparison?

    I'll attach the workbook (with most of the rows removed) along with a figure trying to describe the problem.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,513

    Re: Help aligning multiple measurement series on same date axis for line diagram

    I assume (it wasn't clear to me) that you want n different series, rather than 1.

    A2:

    =LET(a,Sammanställning!A3:BN24,b,COLUMNS(a)/2,c,ROWS(a),d,SORT(--TOCOL(INDEX(a,SEQUENCE(c),SEQUENCE(,b,1,2))),1),d)

    B1:
    LET(a,UNIQUE(Sammanställning!A1:BN1),SORT(FILTER(a,a<>""),1,1,1))

    C2, copied acroos:
    =LET(a,Sammanställning!$A$1:$BM$1,B,Sammanställning!$B$3:$BN$24,c,Sammanställning!$A$3:$BM$24,d,HSTACK(XLOOKUP(B1,a,c),XLOOKUP(Sheet1!B1,a,B)),IFERROR(BYROW($A2#,LAMBDA(x,FILTER(INDEX(d,,2),--INDEX(d,,1)=x))),NA()))

    Now you can plot your graph. If this is NOT what you expect to see... in terms of organisation of your raw data... explain what you DO expect to see!! The graph you can sort out yourself better than I can (probably)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

  3. #3
    Registered User
    Join Date
    09-29-2021
    Location
    Lund, Sweden
    MS-Off Ver
    Microsoft 365 Apps for enterprise
    Posts
    15

    Re: Help aligning multiple measurement series on same date axis for line diagram

    Thank you very much! You assumed correctly.

    How would I go about extending the formulas range to row 17880?

    Is this correct:

    A2:
    =LET(a,Sammanställning!A3:BN17880,b,COLUMNS(a)/2,c,ROWS(a),d,SORT(--TOCOL(INDEX(a,SEQUENCE(c),SEQUENCE(,b,1,2))),1),d)

    C2, copied acroos:
    =LET(a,Sammanställning!$A$1:$BM$1,B,Sammanställning!$B$3:$BN$17880,c,Sammanställning!$A$3:$BM$17880,d,HSTACK(XLOOKUP(B1,a,c),XLOOKUP(Sheet1!B1,a,B)),IFERROR(BYROW($A2#,LAMBDA(x,FILTER(INDEX(d,,2),--INDEX(d,,1)=x))),NA()))

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,513

    Re: Help aligning multiple measurement series on same date axis for line diagram

    I'm on the beach! Back later.

    It looks OK. Try it and report back it falls over.

  5. #5
    Registered User
    Join Date
    09-29-2021
    Location
    Lund, Sweden
    MS-Off Ver
    Microsoft 365 Apps for enterprise
    Posts
    15

    Re: Help aligning multiple measurement series on same date axis for line diagram

    Quote Originally Posted by Glenn Kennedy View Post
    I'm on the beach! Back later.

    It looks OK. Try it and report back it falls over.
    That sounds really nice!

    Changing the row value seems to have messed it up. I'll attach the workbook if you have time to check later!

    PS. Wouldn't let me attach the file, you may use my this filetransfer link instead if that is alright: https://sfta01.afry.com/link/heiSka9cyD56TQXssOAEz4

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,513

    Re: Help aligning multiple measurement series on same date axis for line diagram

    I see the problem. However, my PC is falling over, given the size of this. I suspect VBA is the only thing that will work for you. If that's OK, I'll move it to the VBA sub-forum for others to look at.

+ 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: 2
    Last Post: 06-28-2019, 03:35 AM
  2. [SOLVED] Line Graph - Different Data Series on the Same Axis
    By lango-unchained in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 08-25-2017, 03:24 AM
  3. Replies: 5
    Last Post: 03-28-2015, 03:48 PM
  4. problem in line diagram- Messy line diagram
    By meus in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 12-16-2014, 12:50 AM
  5. Multi Line Graph with series that have inconsistent X axis values
    By potejam in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-08-2013, 07:20 PM
  6. Replies: 0
    Last Post: 06-27-2012, 04:16 AM
  7. Replies: 1
    Last Post: 05-07-2012, 11:53 AM

Tags for this Thread

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