Suppose you have been given a word say "and" and you need to find which word position is this In Mr. and Mrs. Smith – Position of "and" is 2 as this is the 2nd word, the position is not 5. In Samarand Smith and Kittie Smith – the position is 3 not 2 as…
Solution – Challenge 65 – How Many Sundays on Last Date of Months
Below is a possible solution to the Challenge 65 – How Many Sundays on Last Date of Months =SUMPRODUCT((ROW(INDIRECT($B$1&":"&$B$2))=EOMONTH(ROW(INDIRECT( $B$1&":"&$B$2)),0))*(TEXT(ROW(INDIRECT($B$1&":"&$B$2)),"ddd")="Sun")) Below is an Excel sheet containing the solution Solution – Challenge 65 – How Many Sundays on Last Date of Months
Tips & Tricks 163 – Convert Alphabets to Numbers
If you want to convert a, b, c….z to 1, 2,3….26, the you can use following type of formula – =CODE(UPPER(A1))-64
VBA – Macro to Combine (Append) Sheets
Many times, we need to combine worksheets together. Below is a Macro to do this. You just need to change the parameter in "Change Parameters in this Section". In case of many sheets, it will merge all sheets. If you don't want to merge all sheets but few sheets, just create two sheets named "Start"…
Challenge 65 – How Many Sundays on Last Date of Months
This time you need to work out the number of Sundays on last date of the months between two given dates. For example, between two dates of 1-Jan-2017 to 31-Dec-2020, total number of Sundays on last date of the months is 6 as highlighted below. The answer to the solution will be published after a…
Tips & Tricks 162 – Convert a Month Name to Month Number
Suppose, you have text denoting month in cell A1. Let's say A1 = "Sep" or A1="September", then you can use following formula to convert this to month number =MONTH(1&A1) =–TEXT(1&A1,"m") In case, cell A1 contains the partial month name say "Septe", then in place of A1 in the above formulas, you can write LEFT(A1,3).
Downloads 18 – Sample CSV Files / Data Sets for Testing (till 5 Million Records) – Sales
Disclaimer – The datasets are generated through random logic in VBA. These are not real sales data and should not be used for any other purpose other than testing. Other data sets – Human Resources Credit Card Bank Transactions HR Analytics Note – I have been approached for the permission to use data…
Downloads 17 – Sample CSV Files / Data Sets for Testing (5 Million Records) – Credit Card
Disclaimer – The datasets are generated through random logic in VBA. These are not real credit card data and should not be used for any other purpose other than testing. Other data sets – Human Resources Sales Bank Transactions HR Analytics Note – I have been approached for the permission to use data set…
Solution – Challenge 64 – Sum up the Range where a particular alphabet appears
Below is a possible solution to the Challenge 64 – Sum up the Range where a particular alphabet appears =SUMPRODUCT(ISNUMBER(SEARCH(" "&C2&","," "&A2:A13&","))*(B2:B13))