• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. p45cal

    MMULT, TRANSPOSE AND TOCOL

    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...
  2. p45cal

    Defining Range to use based on a matching value

    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...
  3. p45cal

    Defining Range to use based on a matching value

    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...
  4. p45cal

    Defining Range to use based on a matching value

    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...
  5. p45cal

    Splitting data from merged cells into separate rows

    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!
  6. p45cal

    Max per ID (variable number of ID's)

    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.
  7. p45cal

    Take a value, divide by X, Apply the quotient across X number of Columns

    say in cell D4: =IF(($D$1:$P$1>=C4)*($D$1:$P$1<=EDATE(C4,B4-1)),A4/B4,"") copy up/down
  8. p45cal

    Construction Budget distribution using GAMMADIST Function

    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...
  9. p45cal

    Add filter in pivot table

    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:
  10. p45cal

    Identifying dates overlapping - Power Query

    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...
  11. p45cal

    Need help in making 3D Pie-Chart

    Add a pivot table and chart, move the chart to Sheet1. See attached.
  12. p45cal

    Assistance Needed with VLOOKUP Function in Excel

    I think your formula in cell F2 should be: =VLOOKUP(E2,'Customer Info'!A$2:B$100,2,0) and copy down. (Note the 0 at the end)
  13. p45cal

    Identifying dates overlapping - Power Query

    @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...
  14. p45cal

    The specified sort criteria is invalid. Details: [List]

    Not at a PC at the moment, but could it be the user added some steps between existing steps which got rid of that column name before the the sorting?
  15. p45cal

    The specified sort criteria is invalid. Details: [List]

    I was able to duplicate the error message: The fault? No such column header as Custome.
  16. p45cal

    VBA Macro to compare a column with a list of values

    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.
  17. p45cal

    Correct Excel formula

    It is an Excel formula; you need to make your data range into a named range called data first.
  18. p45cal

    How To Combine Rows In Power Query

    See PQ table at cell A25. Similar to the solution at your cross-post but the text is more closed up in column Libellé de l'opération.
  19. p45cal

    shorten Column ID code to use for multiple columns.

    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...
  20. p45cal

    6M Trend

    Is it (Closed)-(Lease End Date) or (Lease End Date)-(Closed) If the result, in one case, is negative 10 days (-10), which bucket should it be in?
  21. p45cal

    Help with returning data averaging x rows returned

    re: This version is independent of the source data sorting for all methods.
  22. p45cal

    Help with returning data averaging x rows returned

    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.
  23. p45cal

    Help with returning data averaging x rows returned

    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.
  24. p45cal

    Adding XL version

    Nor me. I've added my Excel version in User Preferences, but I can't see it showing up anywhere else.
Back
Top