Search results

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

Hi, Your DOB range contains Dates, whereas MONTH(TODAY()) will return current month number, say current month number is 5, and 5 is not equals to any of the dates mentioned in DOB range, therefore Month(TODAY())=DOB will return the array of FALSE only. You can return TRUE / FALSE array by...

Hi, Please try this: =IFERROR(INDEX(Emp_Name,AGGREGATE(15,6,1/((MONTH(DOB)=MONTH(TODAY()))/(ROW(DOB)-1)),ROWS($1:1)),),"") Red highlighted -1 assumed that data starts from 2nd Row, needs to be adjusted accordingly. Regards,

Hi Shiri, Thanks for your valued contribution, looking forward to learn more from you.

Hi, If you enter a number that has 16 digits or more, Excel changes those extra digits to zero. A workaround is to use Text Format for cells, so that the numbers after 15 digits not changed to zero. Regards,

Hi, Power Query if interested: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"File No", type text}, {"Name", type text}, {"Designation", type text}, {"Status", type text}, {"Passed Date", type any}, {"Card Status"...

Happy Birthday Chirayu Have a Great Year

Hi, You can do it with Conditional Formatting with following steps: Select your range you want to highlight (J3:J28 or J3:M28) Go to Home Tab Conditional Formatting Formula: =AND($D3>=1,$D3<=3) Format to Red Color and press OK Same steps for remaining conditions =AND($D3>=4,$D3<=7) format...

Hi, One more: =AND($D$8:$K$8="Blank") Regards,

Hi, Recently learned from Bill Jelen (MrExcel) that referencing the array like A2:A10=A2 in formula takes more calculation time, even if it is used with new Excel Calculation Engine and XLOOKUP, an alternate approach...

Hi, Make a helper Table in separate area of worksheet, say D2:E5 with input and output as shown in snap, this will help in maintaining the data: =LOOKUP(A1,D2:D5,E2:E5) Regards,