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

1. ## 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.

2. ## 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)

3. ## 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. ## 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. ## Re: Help aligning multiple measurement series on same date axis for line diagram

Originally Posted by Glenn Kennedy
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. ## 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.

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