• 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. John Jairo V

    find unique names

    Hi to all! Another option could be: =SORT(UNIQUE(TOCOL(FILTER(D2:D72,ISERR(-LEFT(D2:D72))),1))) Blessings!
  2. John Jairo V

    Maximum match column

    Hi @Pinang! Check file, with this formula applied: =IFNA(LOOKUP(1,0/(A2:D2=G2),A$1:D$1),"") Blessings!
  3. John Jairo V

    Weighted Average - With Letters

    Hi lunicus! You can use a helper table, when you assign some values to every letter (i did with equal ranges between letters, but you can change the values in the column if you want), and this values can be used to calculate the equivalent letter using weighted average. Check the file...
  4. John Jairo V

    Find position last mission

    Hi again! Just add the new condition in both parts: =IFERROR(INDEX(D$2:D$17,MATCH(AGGREGATE(14,6,B$2:B$17/(A$2:A$17=A2)/(B$2:B$17<B2)/(INT(B$2:B$17)=INT(B2)),1),(A$2:A$17=A2)*(B$2:B$17<B2)*(INT(B$2:B$17)=INT(B2))*B$2:B$17,)),"") Blessings!
  5. John Jairo V

    Find position last mission

    Hi @Pierre ! You could try this formula in E2 and drag it down: =IFERROR(INDEX(D$2:D$8,MATCH(AGGREGATE(14,6,B$2:B$8/(A$2:A$8=A2)/(B$2:B$8<B2),1),(A$2:A$8=A2)*(B$2:B$8<B2)*B$2:B$8,)),"") Blessings!
  6. John Jairo V

    Extract unique values in Excel 2019

    Hi again, @Junarkar! You have some #N/A errors in the table, exactly in column MCH. Remove or replace them, and your problem is gone! Blessings!
  7. John Jairo V

    Extract unique values in Excel 2019

    Hi to both! If I understood correctly, see attach file with some option. Recomendation: Don't use whole columns in order to compare ranges directly. Although COUNTIF, MATCH and INDEX can manage whole columns, direct comparison with a cell with whole column (like you use in an IF statement -...
  8. John Jairo V

    Help With Sumproduct and multiple criteria. (Solved)

    Hi to both! Ok... see the attachment again. Blessings!
  9. John Jairo V

    Help With Sumproduct and multiple criteria. (Solved)

    Hi, @rsd007! I leave two options in the file. Check it! Blessings!
  10. John Jairo V

    SEQUENCE in a LET formula not behaving as expected

    Hi to all! Nice catch by @Fluff13! Another way to do it: =LET(a,VALUE(TEXTSPLIT(A1,,"-")),b,INDEX(a,1),c,INDEX(a,2),d,c-b,SEQUENCE(MAX(d))) The aggregation functions (like MAX, MIN, SUM, etc.) convert the "array part" to a number. Like this, sequence can work normally. Blessings!
  11. John Jairo V

    Lookup rows until reached value

    Hi to all! Another option (With Microsoft 365) could be: =LET(o,SORT(A3:B18,{1;2},{1;-1}),s,"|", f,LAMBDA(t,p,TEXTBEFORE(t,p)),g,LAMBDA(t,p,TEXTAFTER(t,p)),n,INDEX(o,,1), a,--MAP(SCAN("|0",n&s&INDEX(o,,2),LAMBDA(i,x,f(x,s)&s&(f(i,s)=f(x,s))*g(i,s)+g(x,s))),LAMBDA(n,g(n,s)))...
  12. John Jairo V

    Unique, stacked list from range, according to tier/category

    Hi @Anon9149! I think you simply use: =UNIQUE(TOCOL(B11:F123)) Blessings!
  13. John Jairo V

    Number Classification

    Hi to all! One option with Excel 2021/365 version: =CONCAT(CHAR(96+MATCH(A2:I2,UNIQUE(A2:I2,1),))) Another option (with Excel 2016) in attach file: Blessings!
  14. John Jairo V

    Excel find which group of nunbers repeating in multiple columnes

    Hi to all! Just to put a slight variation for bines53 solution (Non CSE too): =IFERROR(AGGREGATE(15,6,A$1:A$23/(COUNTIF(B$1:D$23,A$1:A$23)=3),ROWS(F$1:F1)),"") And another way to do it (CSE version): =IFERROR(INDEX(MODE.MULT(IF(COUNTIF(A$1:D$23,A$1:D$23)=4,A$1:D$23)),ROWS(F$1:F1)),"") Blessings!
  15. John Jairo V

    Find partial string in multiple cells and Sum next cell

    Hi @Eat More Bacon If you want to get the C value into Column B when Column A contains "Tylan", you can use into B1: =COUNTIF(A1;"*Tylan*")*C1 And drag it down. But if you want to sum all the C values when Column A contains "Tylan", you can use in any cell: =SUMIF(A1:A7,"*Tylan*",C1:C7) Blessings!
  16. John Jairo V

    Sumif for merged Collums

    Hi, Henry! Two options for you. 1► With helper row. [B2] : =IF(B3,B3,A2) Drag it to the right until H2 [F11] : =SUMIFS(B6:H6,B4:H4,C8,B2:H2,E11) 2►Without helper row. [F11] : =SUMPRODUCT(--(B4:H4=C8),--(LOOKUP(COLUMN(B3:H3),COLUMN(B3:H3)/(B3:H3<>""),B3:H3)=E11),B6:H6) Check file. Blessings!
  17. John Jairo V

    Transpose Data with Criterion

    Hi again! Second try... (Microsoft 365 Insider version too): =LET(_r,VSTACK(A3:A17,C3:C17), _s,SORT(UNIQUE(FILTER(_r,_r<>""))), _a,INDEX(A3:B17,XMATCH(_s,A3:A17),{1,2}), _b,INDEX(C3:D17,XMATCH(_s,C3:C17),{1,2}), VSTACK(A2:D2,IFERROR(HSTACK(_a,_b),""))) Blessings!
  18. John Jairo V

    Transpose Data with Criterion

    Hi to both! Another catch with Microsoft 365 Insider version: =LET(_r,VSTACK(A3:A17,C3:C17), _s,SORT(UNIQUE(FILTER(_r,_r<>""))), _a,VLOOKUP(_s,A3:B17,2,), _b,VLOOKUP(_s,C3:D17,2,), VSTACK(A2:D2,HSTACK(IF(ISERROR(_a),"",IF({1,0},_s,_a)),IF(ISERROR(_b),"",IF({1,0},_s,_b))))) Blessings!
  19. John Jairo V

    Expand - Collapse multiple items of PivotTable Data Model with VBA Excel

    Thx for your help. I'd prefer to avoid the grouping and use the hidden property of sheet instead. Blessings!
  20. John Jairo V

    Expand - Collapse multiple items of PivotTable Data Model with VBA Excel

    Hi p45cal. Thx for your reply. 1. Isn't collapse/expand .ShowDetail rather than .DrilledDown? No... just work .DrilledDown (For PivotTable Data Model) 2. I note from the Help associated with PivotItem.DrilledDown: (a) 'Use this property only for OLAP data sources.' and (b) 'You cannot set...
  21. John Jairo V

    Expand - Collapse multiple items of PivotTable Data Model with VBA Excel

    Greetings to all of you! I have this VBA code: Set pT = ActiveSheet.PivotTables("Liquidation") Set pF = pT.PivotFields("[Vessel_LQ].[District].[District]") For i = 1 To UBound(a) pF.PivotItems("[Vessel_LQ].[District].&[" & a(i) & "]").DrilledDown = False Next i Initially, all the items...
  22. John Jairo V

    Need help with sumif cells containing both text and number

    Hi to all! Another option could be: No CSE option: =SUMPRODUCT(($B$4:$B$14=$B19)*(MONTH(1&C$18)=$C$2:$M$2),--(0&MID($C$4:$M$14,2,15))) Blessings!
  23. John Jairo V

    Find top 3 repeated values in a column

    Hi to both! Another way, with Microsoft 365 insider beta (similar to @Peter Bartholomew approach) : =LET(u,UNIQUE(names), c,COUNTIFS(names,u), s,SORTBY(u,c,-1), TAKE(s,3)) Blessings!
  24. John Jairo V

    Lookup Multiple Columns

    Hi to all! Another option, using traditional Excel array formulas: [F5] : =IFERROR(INDEX(A$2:A$5,AGGREGATE(15,6,ROW(A$2:A$5)-ROW(A$1)/MMULT(COUNTIF(F$2,B$2:D$5),{1;1;1}),ROWS(F$5:F5))),"") Drag it down. Blessings!
  25. John Jairo V

    How to remove special characters and words from a cell value.

    Hi to all! One option could be: [C1] : =TRIM(CONCAT(MID(SUBSTITUTE(SUBSTITUTE(B1,"*",""),",",REPT(" ",99)),1+99*{0;2},99))) And drag it down. Blessings!
Back
Top