This time challenge is to generate unique characters string through a formula. Suppose, you have following following string in cell A2 Letters Now the output should be after extracting characters occurring once only. Letrs Some sample data to be be used Source String Result String adequate adequt stagnant stagn phenomenon phenom effective efctiv benevolent benvolt…
Author: Vijay A. Verma
Tips & Tricks 179 – Retrieve Sheet Name in Excel Online
We can easily retrieve sheet name by following – Tips & Tricks 34 – Get Sheet Name through Formula But the formula depends upon CELL function. Excel Online i.e. web version of Excel doesn't support CELL function. It doesn't support VBA as well. Hence, we need to find an alternative to extract sheet name through…
Test for a Number – Power Query – Tips & Tricks 5
Test for a Number is a very important topic in Power Query Suppose, you have a column of values and you need to test whether the value contained is a number or not.
Solution – Challenge 71 – Determine the Rank
Below is a proposed solution to the Challenge 71 – Determine the Rank In E2, use following formula and drag down =SUMPRODUCT(–($B$2:$B$6+$C$2:$C$6+$D$2:$D$6>SUM(B2:D2))) +SUMPRODUCT(–($B$2:$B2+$C$2:$C2+$D$2:$D2=SUM(B2:D2)))
Change Index at discontinuity – Power Query – Tips & Tricks 4
Power Query – Change Index at each discontinuity. Sometimes, you need to insert index but change is needed at when value in left column changes
Challenge 71 – Determine the Rank
Column A has players and there are 3 rounds of a game. Columns B to D contain the points achieved by the players. The challenge is to determine the rank of these players on the basis of sum of points in various rounds without a helper column. 1 would mean the highest rank. Column E…
Excel Quiz 52
Inserting a Sequence 1,2,3,1,2,3,1,2,3…..- Power Query – Tips & Tricks 3 –
Inserting a sequence saves a lot of headache when mashing up queries. A common task is to insert a sequential query which repeats like 1,2,3,4,1,2,3,4,1,2,3,4
Tips & Tricks 178 – Extract last 2 words OR last n words
To extract last 2 words, use below formula =IF(ISNUMBER(FIND(" ",A2)),TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),2*LEN(A2))),"") To make a generic formula, to extract last 2 words =IF(COUNTIF(A2,"* *")>0,TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),2*LEN(A2))),"") Now, you would need to change * * and 2 only which are marked in Red. If you need to replace last 3 words, then * * would be…
Downloads 22 – Sample CSV Files / Data Sets for Testing (till 5 Million Records) – IBM HR Analytics for Attrition
On Kaggle there is a data set published named "IBM HR Analytics Employee Attrition & Performance" to predict attrition of your valuable employees. This is a very popular dataset and has usability index of 8.8. https://www.kaggle.com/pavansubhasht/ibm-hr-analytics-attrition-dataset But this data set has only 1470 rows whereas we need, sometimes, a large data set for testing. So,…