Search results

Happy Birthday Sir

Hi, If you want to know the most repeated entry, use MODE function which works with Numbers only, for text we can use the MATCH function inside the MODE and wrap it with INDEX to return the most repeated entry: =INDEX(A2:A6,MODE(MATCH(A2:A6,A2:A6,0)),) Array formula needs to be entered with...

Hi Sachin, Thank you for the post, and Thanks to the Forum where we learned all the stuff. I missed the forum so much as currently I am not able to give much time here due to new job assignments, but I will try to manage the time for forum so that I can learn more. Take Care, & Regards,

Hi, Please post a sample file with manually entered expected output. Regards,

Hi, Data validation does not work non contiguous cells. A work around is to use helper column, say column B enter in cell B1 =A1 in B2 =A3 in B3 =A6, and then use Data Validation > List > B1:B3 Regards,

Hi Saqib, Visit the following almost 5 years old thread, you may find some interesting points: https://chandoo.org/forum/threads/split-alphanumeric.19920/ Regards,

Hi, Glad you have solved it. Keep Exceling,

Hi, Can you post a sample version ? Blind shot would be to force the lookup value and array into numbers and use this array formula: =IFERROR(VLOOKUP(LEFT(C2,20)+0,('Item Cost'!$A$2:$B$9999)+0,2,0),IFERROR(VLOOKUP(LEFT(C2,20)+0,('Cost pull from backend'!$A$2:$E$9999)+0,5,0),"No Cost"))...

Many thanks Brother, Thanks for the wishes, I wasn't available due to busy schedule in Ramadan, Thanks once again.

Hi Junaid, If you can include your all inputs with expected results, it will be easy to share targeted answers. You can also use Ceiling function: =CEILING(G4/F4,1) Regards,

Amazing solutions Bosco and Shiri :)

Hi, See the attached with few changes in formula. Regards,

Hi, You can use the Text to Column option to split your data in separate columns using comma as Delimiter, or the similar with following formula, to be entered in G2, copy across and down: =TRIM(MID(SUBSTITUTE($E2,",",REPT(" ",LEN($E2))),(LEN($E2)*(COLUMN(A1)-1))+1,LEN($E2))) Then you can...

Hi, Please post a sample excel file with expected results entered manually. Regards,

Hi, That looks like Error Checking Marker, you can try disabling some options from: Excel Option > Formulas > Error Checking Rules Though it is not recommended. Regards,

Hi Peter, Great… Yes, formatting with TEXT function is bit tricky to me as compared to Cell Formatting, I often forget the awful """ 3 inverted commas implementation. Regards,

Hi to all, Peter's solution, but with TEXT function: =TEXT((DAY(A1)=1)+(A1=EOMONTH(A1,0)),"""Not during Month"";;""During Month""") Regards,

Hi, Your formula result "200" is a text output, you need to remove inverted commas, i.e. replace "200" with 200 Regards,

Hi, Or this: =IF(COUNTIF($A$1:$A$15,B1),"Yes","No") Regards,

Hi, You can use the same steps with Native Remove Duplicate Feature. Select your data > Go to Data > Remove Duplicates > Select Columns > Ok Regards,

Hi, INDIRECT function doesn't work with closed files. Regards,

Hi Waqar, Forum is always been helpful, several questions have been answered with no time. Some members (including me) available in weekdays only. I am not much familiar with Pivot Charts, hope someone will respond. Your patience is requested.

Hi, If you can go with PQ: Convert the data into Excel Table Go to Data > Get & Transform > From Table > Select your columns Amount and Number > Go to Remove Rows > Remove Duplicates > Close & Load let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" =...

Good day Sachin, I have found this useful too, we can get the list of files in a folder with options to rename: https://strugglingtoexcel.com/2014/01/01/excel-batch-renamer/

Hi again, Glad it helped :) > and < refers to Greater than and Less than respectively, we need to use these symbols in " " inverted commas in some formulas depend on its nature, for example SUMPRODUCT or IF functions does not require inverted commas. EOMONTH function is used to get the End Of...