Recent content by Khalid NGO

Hi Waqar, I hope you will find PQ solution, I am sharing formula seems to be working on posted samples...

Hi, You can edit your query and Go to File > Options and Settings > Regional Settings > Local (for changing Regional Setting) follow below screenshot. Meanwhile "Stay Home, Stay Safe" :) Regards,

Hi, Use this for F column: =IFERROR(SUBSTITUTE(F4,"DR","")*-1,"") and this for Column G: =IFERROR(SUBSTITUTE(G5,"CR","")*1,"") or single formula for both columns: =IFERROR(IF(ISNUMBER(SEARCH("dr",F4)),SUBSTITUTE(F4,"DR","")*-1,SUBSTITUTE(F4,"CR","")*1),"") Regards,

Hi Arslan, See the attached file, you can change the status in cell A1 to see results. Following Formula needs to be adjusted as per your actual file range: =IFERROR(INDEX('SHEET 1'!$A$3:$C$99,AGGREGATE(15,6,1/('SHEET 1'!$D$3:$D$99=$A$1)*ROW('SHEET 1'!$D$3:$D$99)-2,ROW(A1)),COLUMN(A1)),"")...

Hi, If it is okay to have result in separate cells, you can use this in AQ2 and then copy across: =IFERROR(INDEX(ALE!$Q$2:$Q$5,AGGREGATE(15,6,1/(ALE!$A$2:$A$5=$B2)*ROW(ALE!$Q$2:$Q$5)-1,COLUMN(A1)),),"") Regards,

Hi, You need to enter wildcard like: =COUNTIF($A$1:$A$10,"*"&D1) Regards,

Hi Decio, "We are all very ignorant. What happens is that not all ignore the same things." AE Regards,

The formula posted by decoig is also an Array Formula, will work with Ctrl+Shift+Enter

Hi, Or use this {array formula} =SUM(IFERROR(A1:A7*B1:B7,0)) {array formula needs to be entered with Ctrl+Shift+Enter} Regards,

Hi, Two more solutions (non array) =AGGREGATE(14,6,1/(A1:A12=D1)*B1:B12,1) =SUMPRODUCT(MAX((A1:A12=D1)*B1:B12)) Regards,

Hi, Please check this: =LEFT(A1)&TEXT(MID(A1,2,LEN(A1))+1,REPT(0,LEN(A1)-1)) Again it is based on your provided pattern, it is best to include all possible inputs in initial post, so that members can share targeted solutions. Regards,

Hi, If the pattern is same: =LEFT(A1)&MID(A1,2,LEN(A1))+1 Regards,

Hi, For Formula based solution, you can use the following in any other empty column: =A2&TEXT(COUNTIF(A$2:A2,A2),"0000") Regards,

Hi Pete, As vletm pointed out about the Match Function Syntax, you also need to adjust the range holiday_list to one column, so that the Match function works properly, and the best rule for working with dates is "Always Enter Proper (Number Formatted) Dates". Regards,

Hi, Yes AGGREGATE takes care of CSE in most of the formulas. I am not sure what did wrong, just make sure your range Emp_Name and CSS!$G$3:$G$22 are of same sizes. You can always upload sample file with same structure of original file without any sensitive information, this will lead us to...