Search results

Hi Neeraj, You can understand the logic with this too: =AGGREGATE(15,6,1/(CriteriaColumn=Criteria)*Values,1) 15 = function number i.e. SMALL 6 = used for ignoring error values CriteriaColumn = Specify your criteria range, for example A2:A10 Criteria = Specify your criteria, for example single...

Hi Neeraj, Pattern of using AGGREGATE function is almost similar to the regularly used function SUBTOTAL. If you are okay with SUBTOTAL then you can easily understand the logic of AGGREGATE. You will find several links on the web and on this forum too by using search option...

Hi to all, Similar as LM solution but with normally entered: =IFERROR(INDEX(A:A,AGGREGATE(15,6,1/($C$2:$C$100<>$F$2:$F$100)*ROW($C$2:$C$100),ROWS(C$2:C2))),"") =IFERROR(INDEX(F:F,AGGREGATE(15,6,1/($C$2:$C$100<>$F$2:$F$100)*ROW($C$2:$C$100),ROWS(D$2:D2))),"") Regards,

Hi Lori, That's we always do whenever see your interesting and insightful post. Thanks for sharing.

Hi Neeraj, As Marc advised, follow post # 2. Just use Wildcard for example: +* Regards,

Hi to all, Another option can be used with COUNTIF + cell formatting: =COUNTIF(A2,"*green*") Format the cell as: "Green";;"Non Green" Similarly with TEXT function without changing cell formats: =TEXT(COUNTIF(A2,"*green*"),"""Green "";;""Non Green """) Regards,

Hi @EDISON CARAOS Good day and welcome to the forum :awesome: Please start a new question at appropriate section of the forum, and before you start I suggest you to please: 1) Take a few moments to read our basic forum rules: https://chandoo.org/forum/threads/new-users-please-read.294/ 2)...

Hi, 9^9 is generally referenced to return a big number in excel, often used to find the last value in a given range. ROW($1:$16) is used in Bosco's formula to return the array of numbers from 1 to 16 i.e. {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16} HTH,

Hi to all, AGGREGATE function can be used with just Enter: =INDEX(Sheet2!$A$1:$A$36,AGGREGATE(15,6,1/(Sheet2!$K$1:$K$36=C$1)*ROW(A$1:A$36),ROWS(A$1:A1))) Regards,

Hi Riyaz, I may have missed your question, but I think your formula is doing the same thing as these: =VLOOKUP("*",A2:A20,1,0) =INDEX(A2:A20,MATCH("*",A2:A20,0)) You can also add a check something like: =IF(SUM(COUNTIF(A2:A20,{"DIAMOND","PREMIERE PLUS","PLATINUM","PREMIERE"}))>0...

Hi David, This seems to work: =TEXT(A1,REPT("0 ",LEN(A1))) Regards,

Hi to all, Congrats Narayan Sir. Best Wishes and Blessings,

Hi @vletm, Just awesome. Works like a ♪♫♬ Many thanks. Blessings

Hello guys, good day. Need help in copying data from Database file (Sales sheet column A to M) to another file named CC-KHI-S1, base on cell N1 value say CC-KHI-S1. Explaining a bit further, what I want is macro to look in CC-KHI-S1 file cell N1, and match it with all the rows found in...

Hi to all, DateValue function is very useful in this case, for example this can be used: =IF(ISNUMBER(G2),G2,DATEVALUE(G2)) Regards,

Hi YL, Try googling with following keywords: clear last cell in excel You will find several solutions including this: https://www.mrexcel.com/forum/excel-questions/73426-reset-last-cell-worksheet.html HTH

:) Great... Glad it helped. Thanks for the feedback. Take Care

Hi Thong, I mean can you include your expected results manually? so that we can cross check. As i guessed for result in separate columns, you can use above formula. For result in single column, try this in G2 and copy down: =SUMPRODUCT(($A$2:$D$10=F2)+0) This should work or I've completely...

Hi thong, Can you include your expected results? Just a guess, a simple countif can be used in G2: =COUNTIF(A$2:A$10,$F2) Copy down and across... Regards,

Hi David, Thank you :) Yes, AGGREGATE function is amazing, I have recently learned to replicate CSE formulas with this function. Regards,

Hi to all, One more with just enter: =INDEX($B$2:$B$6,AGGREGATE(15,6,(ROW($B$2:$B$6)-1)/($A$2:$S$6=A12), COUNTIF(A$12:A12,A12))) You can replace the Countif part with Row(A1) if your lookup values are identical. Regards,

Hi, Your cells are formatted as text :) Just change it to general or number (as required) and then re-enter. Regards,

Hi @Mie Olsen Good day and welcome to the forum... May be there are some spaces or any other hidden character like char(160) Without seeing your file it is hard to provide targeted solution, Can you post a sample file? Regards,

Hi @gmfston Good day, and welcome to the forum. I am not sure about your expected result, please give it a try: =SUMIFS(IF(C5=10,'Sheet 2'!$M$5:$M$272,'Sheet 2'!$N$5:$N$272),'Sheet 2'!$H$5:$H$272,"*"&D5) To be entered in Sheet1 E5, and copied down. Regards,

Hi, I have sent you images for step by step procedure for using vba code tags via PM. No Problem, Thread is open, please continue. Regards,