Search results

Hi, Try this in E2 =LARGE($A$2:$A$212,ROW(A1)) in F2: =VLOOKUP(E2,$A$2:$B$212,2,0) E8: =SMALL($A$2:$A$212,ROW(A1)) F8: =VLOOKUP(E8,$A$2:$B$212,2,0) Regards,

Hi @ymdeek, Good day and welcome to the forum :awesome: Other than what Marc advised, you can also use Power Query to a new workbook with a few simple setps: Go to PQ From File Select your file Select Sheet (or table) which contain your data, say Sheet1 Press Edit Simply apply filter for <>...

Hi, Replace your 2nd IF with Countif. Regards,

Hi Kumar, Your sample file contain just one example :confused: ? Check this {array} formula: =MID(B2,MATCH(1,(CODE(MID(B2,ROW($1:$255),1))>=65)*(CODE(MID(B2,ROW($2:$255),1))<90),),MATCH(1,(CODE(MID(B2,ROW($1:$255),1))>=97)*(CODE(MID(B2,ROW($2:$255),1))<=122),)-FIND(" ",B2)-2) {array formula...

Hi, How you are getting #DIV/0 errors? This must be a formula result hmm?, try IFERROR on those #DIV/0s. Regards,

Hi Bosco, Cleaver :cool: Regards,

Hi, Then, change the range Pivot!$I$5:$I$62 to Pivot!$J$5:$J$62 Regards,

Hi Chris, Good day and Welcome to the forum :awesome: in C3: =MAX(IF(($G$3:$G$13=A3)*($H$3:$H$13<=B3),$H$3:$H$13)) in D3: =MIN(IF(($G$3:$G$13=A3)*($H$3:$H$13>=B3),$H$3:$H$13)) Both are array, CSE. If you have 2010+, you can use these non array, with just enter: in C3...

Hi, May be this in Summary Sheet, Cell R2: =INDEX(Pivot!$I$5:$I$62,MATCH(A2,Pivot!$A$5:$A$62,0)) or this: =SUMIF(Pivot!$A$5:$A$62,A2,Pivot!$I$5:$I$62) Copy down... Did you mean 32? Regards,

Hi Vletm, Congrats and welcome to the Ninja Team. Wishing you best of luck for more milestones. Thanks Hui for the news. Regards,

Hi, Try this in Sheet1, C2: =INDEX('Special Projects'!$B$2:$M$9,INT((ROWS($A$1:A1)-1)/COLUMNS($O$1:$AL$1))+1,MATCH(C$1,'Special Projects'!$B$1:$M$1,0)) Copy across and down... Regards,

Exactly, that is way I started learning PQ. :DD Good to hear that, have great days. Regards,

Hi to all, Another option, similar to r2c2 =OFFSET(A$3,MOD(ROWS(A$3:A3)-1,COUNTA(A:A)-1),INT((ROWS($A$3:A3)-1)/ROWS($A$3:$D$313))) Regards,

Hi Chihiro, Interesting.... I have nothing much except these which are helping in my day 2 day work: RDB Merge at office and home PQ at home only (using 2010) - Started using / learning recently. Hope someday I will be able to digest these things like you do. Regards, PS: Chihiro do you have...

Hi, You are at the right place, believe me there are many people (including me) who was not aware of basic functions before joining this forum. You just need to focus on provided solutions, try Evaluate option for the formula and understand how the function is working. Once you realize how...

Hi, As now you have identified "not found values" with above posted solutions, you can use this {array formula} in a separate column: =IFERROR(INDEX($C$4:$C$347,SMALL(IF(ISERROR($D$4:$D$347),ROW($D$4:$D$347)-3),ROW(A1))),"") {array formulas needs to be entered with a key combination of...

Hi @richlyn, Good day and welcome to the forum :awesome: You can also use this formula for sum of distinct values: =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8),B2:B8) Regards,

Hi, I didn't found this vehicle number in "as per ledger" sheet. I am not getting this, please elaborate with few examples. Regards,

Hi, Please try this: =LOOKUP(2,1/(SEARCH(C4,'as per ledger'!$C$2:$C$339)*('as per ledger'!$A$2:$A$339=A4)*('as per ledger'!$B$2:$B$339=B4)),'as per ledger'!$B$2:$B$339) Regards,

Just saw this amazing work. Absolutely beautiful. Regards,

Hi Bosco, Sometime your formulas looks just out of this world. Regards,

Hi Hui, Never seen anyone amazed with charts like you do. Regards,

Hi Sachin, Thanks for the news. Many Congrats Hui, always love seeing your post. Regards,

Hi John, I echo what Logit said: Excel is very smart, it automatically change the cell to display values based on user inputs. For example if you type 1-Jan-2018 Cell will be automatically shown as 01/01/2018, where as the actual cell value is 43101 If you type $43101 Cell will be shown as...

@Luis Maldonado Please do not create duplicate threads, question have been answered here: https://chandoo.org/forum/threads/help-with-model-automation.37330/#post-224276 [Thread Closed]