Below is a possible solution to the challenge – Challenge 49 – Whether a Word is Isogram or Not Use following formula – =IF(SUMPRODUCT(–(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW($65:$90)),""))>1)) =1,"Not Isogram","Isogram")
Challenge 51 – Another Word Challenge – Whether Given Words are Anagram or not
Given a word, an anagram is made by using all letters of the given word exactly once. For example, For example, my name "vijay" can be written as "jaivy", "yajvi" and so on….Some well known anagrams are Lead – Flea Admirer – Married Listen – Silent Mother in Law – Woman Hitler Eleven Plus Two…
Tips & Tricks 144 – Enter the Last Save Date and Time
1. Open your workbook and ALT+F11 2. Locate your Workbook name in Project Explorer Window 3. Right click on your workbook name > Insert > Module 4. Copy paste the Macro code given 5. Save your file as .xlsm To get Last Save Date, enter following in a cell =LastSaveDate() To get Last Save Time,…
Solution – Challenge 48 – Whether a Sentence is Pangram or Not
Below is a possible solution to the challenge – Challenge 48 – Whether a Sentence is Pangram or Not =IF(ISNUMBER(SUMPRODUCT(MATCH(ROW($65:$90),INDEX(CODE(MID(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(UPPER(A1)," ",""),".",""),",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(A1," ",""),".",""),",","")))),1)),,),0))),"Pangram","Not Pangram") Update on 10-Aug-21: The above can be done with a simpler formula =IF(ISNUMBER(MATCH(FALSE,ISNUMBER(SEARCH(CHAR(ROW(97:122)),A2)),0)), "Not Pangram","Pangram") You will have to confirm the above formula with CTRL+SHIFT+ENTER i.e. as an array formula…
Challenge 50 – Cryptography Challenge 3 – Generate Tabula Recta
Tabula Recta is supposed to be one of the foundation stones of cryptography. This was one of the earliest forms of encryption. The more about this can be read at https://en.wikipedia.org/wiki/Tabula_recta One look at below and you will understand what it is. In upcoming challenges in future, Tabula Recta will be used. The first row…
Downloads 06 – Template 06 – Risk Log based on PMBOK 5
< The template can be downloaded from Template 06 – Risk Log > This time, this is a unique kind of Risk Log. This covers all 5 phases of Risk Management and is based on PMI PMBOK 5. The template is highly customizable and Risk Score and PI Matrix coloring will be done automatically.
Tips and Tricks 143 – Increment a Number when Workbook is Opened (Invoice or PO Number)
1. Make a backup of your workbook. 2. Open your workbook and ALT+F11 3. Locate your Workbook name in Project Explorer Window 4. Double Click on "This Workbook" 5. Copy paste the Macro code given – Replace Sheet1 and B1 as per your need 6. Save your file as .xlsm if you intend to reuse…
Solution – Challenge 47 – Generate Pentagonal Series
Below is a possible solution to the problem – Challenge 47 – Generate Pentagonal Series Put following formula and drag down – =ROWS($1:1)*(3*ROWS($1:1)-1)/2
Article 42 – Generating an Odd Order Magic Square in Excel (VBA)
A magic square needs no introduction and we come across it many times. A magic square is a square grid and the minimum size of a magic square is 3×3. The whole numbers in magic square appear only once and all cells are filled. The horizontal rows, vertical columns and main and secondary diagonals all…
Challenge 49 – Whether a Word is Isogram or Not
This time, continuing my effort to make challenge on English language, the challenge is on Isogram. An Isogram is that word in English which doesn't repeat any alphabet. For example: "Botany" is an Isogram but "Britain" is not as "i" repeats. Hence, this time, you need to make a formula which returns "Isogram" if the…