try changing:
' Populate the result in the output range
For i = 1 To UBound(result, 1)
outputRange.Cells(i, 1).Value = result(i, 1)
Next ito:
' Populate the result in the output range
outputRange.Value = application.transpose(result)
or:
For i = 1 To...
In the file you attached to msg#15, the formula in column F:
=INDEX($A$2:$A$126,MATCH(MIN(IF(($B$2:$B$126=B2),$E$2:$E$126,10^10)),$E$2:$E$126,0))
is dangerous in the event that if the same minimum ABS value in column E occurs in a different GenericID above, you'll get the wrong tndc returned...
A human-produced formula on the lines of @Monty 's which on cursory checking seems OK save for when there is a GenericID of 0.
=INDEX($A$2:$A$41471,MATCH(MIN(IF($B$2:$B$41471=B2,ABS($C$2:$C$41471-D2))),IF(($B$2:$B$41471=B2),ABS($C$2:$C$41471-D2)),0))
You have which version of Excel?
MS 365, for...
Really?
I'm not so sure. Take a look at genericID 101; the smallest difference from the 22% percentile is 00781305995, not the 00641141035 that @Monty 's formula returns.
Is this AI screwing up again @Monty ? Or have I misunderstood?
Later, I'll try to put something together that works.
ps...
2 goes in the attached, both very dodgy because they depend on there being the same number of lines in each cell in the same row.
1. See Power Query table at cell E5
2. See formula at cell I6 and copy across to cell K6
I would await a better answer!
In attached, see cell AA2, copy down:
=IF(COUNT(d[@['#]])=1,MAX(FILTER(d[[Over-
skridelse
5-6 min.]:[Over-
skridelse
> 7 min.]],d[ID]=A2)),"")
Independently, see Power Query table at cell AF2. Needs refreshing if the source data changes by right-clicking the table and choosing Refresh.
The attached may go some way to help.
Deceptively simple formula in cell G27 and below, with hints:
The result will Spill to the right, so you will only need to copy down (not across).
Derivation of formula (find in defined Names) can be seen in cells G43:G45. These are not necessary and...
I'd lay odds that the dropdown arrows have been removed by vba. I'll try to look into this further but your easiest option at the moment is to recreate the pivot table from scratch as I did:
This surprises me a lot! I'm not aware that it isn't available in 2016; I think it is. Could you point me to some data on this on the interweb?
Does the result table in the file I attached refresh properly?
If not, you could try removing the last step of the query (Replaced Value), it only puts...
@Susanne72 , a workbook with some realistic data in would be a great help and would save us guessing (wrongly) your setup.
While waiting for that, in the attached I've set up the bare bones of what I imagine your data looks like and added a Power Query query.
I've probably gone overboard and got...
Likewise a Power Query solution in the attached. See table at cell H1.
Needs refreshing like a pivot table (right-click and choose Refresh)
I've left HHS showing so you can see what's missing for what month.
Set WS = ShGE03 'this worksheet
For Each cll In WS.Range("C3:Q3,S3:T3,V3").Cells
cll.Clear 'may not need this line.
cll.Value = "COLUMN " & cll.Column & " (" & Split(cll.Address, "$")(1) & ")"
Next cll
End Suband horribly:
Set WS = ShGE03 'this...
Sorry, I didn't read the question fully. The attached has various ways of computing the average of last values, in this case the last 3.
Power Query at cell M11
Formulae at M18
Convoluted Pivot table at cell T2.
All except the Pivot depend on the data being sorted by ascending time.
Still showing as text. There's a unicode 8203 (zero-width space) character in the cells, version attached with those characters removed as well as added table at cell M10 and for cross-checking purposes a Power Query table at cell M16.