Trick : Sort the IDs List

Trick : Sort the IDs List.

If you have the IDs column list as in our example A1, A2 … A100..A200 (Column C ) and you need to sort the list accordingly. Then, simple sorting wouldn’t helpful you anyways.

For that, you need to convert the list as A001, A002, A003…A100..A200 (Column D).

Create the list with this formula in Column D =LEFT(C3,1)&RIGHT("000"&RIGHT(C3,LEN(C3)-1),3)

 And then , Sort it with help of new list .

And Here you get the desired result.

Thank You !! :)

Trick : Replica of Exact Set of Formula Range.

Replica of Exact Set of Formula Range.

If you want the Exact Range Set Formulas. Then, here we have the simple trick for it.

Steps:

a) Select a Range of Formulas. In our Example it's D9:E11

b) After selecting the range, Group the Sheet1 and Sheet2(Sheet2 is blank sheet)-Press Ctrl and Select Sheet2. From Home tab menu, Select Fill >> Across Workbook >> Select Fill option from dialogue box. Click OK.

c) In Sheet2, you can see the Formula Range Set. Ungroup Sheets. In sheet2 Range of Formulas Selected. Press Ctrl+X to cut the range.

d) Then, Activate the cell D12 in Sheet2. And Press Enter. You can see Formulas set shift downward.
e) Again, Group Sheet1 and Sheet2-Press Ctrl and Select Sheet1. From Home tab menu, Select Fill >> Across Workbook >> Select Fill option from dialogue box. Click OK.

And it is done here. You can see the Replica of Exact set of Formula range (sheet1~D9:E11) in sheet1~D12:E14.

Thank You !!

Trick : Repeat Entries in a List - Fill the blank Cell With Repeated Text or Number

Trick to Repeat Entries in a List .

Fill the Blank Cells With Repeated Text or Number.

Steps:
a) Select a B:B column.

 b) Press Ctrl+G - Go To Dialogue Box will open.

c) Click on Special and Select "Blanks" option. Click OK.

d) In formula bar write =B3. And press Ctrl+Enter.

And here, you get the desired result. 

Now, Copy the column B:B and paste special it as a values.

Thank You!!

AutoCorrect Options

AutoCorrect Options: 

Repeat Complicated term on worksheets or workbooks, by just assigning short symbol for it.

If you are tired of entering complicated word many times , then the best way to make your work easy is to use AutoCorrect Options.
AutoCorrect Options, will replace the input text with corrected text. For that assign the Input text and Corrected text. As many times as you enter the input text , it will replace it with corrected text.

Steps:
a) Click on Office >> Excel Options >> Proofing >> AutoCorrect Option
b) On replace enter the corrected text (original text), in our example it is - Worcester, Massachusetts. And on With: option enter the short symbol , in our eg. it is- W, MA
c) Click on Add button, and close.

Now , as many times as you enter W, MA in a cell or in statement. It will automatically replace it with Worcester, Massachusetts.

If you, want to delete the assign symbol. Follow the same steps a) & b) above. Click on the symbol and then click delete button

Thank you !!

Near Number From the List of Numbers

Near Number From the List of Numbers

To find the very near value from the list of numbers in a row,

Here, we have array formula
AC3= '{=IFERROR(INDEX($C3:$Y3,MATCH(MIN(ABS($C3:$Y3-$AA3)),ABS($C3:$Y3-$AA3),0)),"")} . For curly brackets , use Ctrl+shift+Enter

If Header , you want to show , just change the Index range with dollar sign(F4) to make it fixed for other cells when you drag down the formula.
AD3 = {=IFERROR(INDEX($C$2:$Y$2,MATCH(MIN(ABS($C3:$Y3-$AA3)),ABS($C3:$Y3-$AA3),0)),"")} . For curly brackets , use Ctrl+shift+Enter

Let's understand the formula here:

Formula: = {=IFERROR(INDEX($C3:$Y3,MATCH(MIN(ABS($C3:$Y3-$AA3)),ABS($C3:$Y3-$AA3),0)),"")}

What we are doing here, 
a) is to get the Difference of every value $C3:$Y3-$AA3. 
b) Converted every difference into all positive value (as difference can be negative too)with ABS function: ABS($C3:$Y3-$AA3).

c) Then, exact Match the minimum value value of above with the range of absolute difference values. MATCH(MIN(ABS($C3:$Y3-$AA3)),ABS($C3:$Y3-$AA3),0))
d) Match will find the position of the value ,  get the value or header with INDEX function
{=IFERROR(INDEX($C3:$Y3,MATCH(MIN(ABS($C3:$Y3-$AA3)),ABS($C3:$Y3-$AA3),0)),"")}

Press Ctrl+shift+Enter for curly brackets as it is array function.

Have a Nice Day!!

Ratio

Ratio:

Ratio : Number1:Number2

G column , we used GCD function =D4/GCD(D4,E4)&":"&E4/GCD(D4,E4)

H column , we used Text & Substitute Function =SUBSTITUTE(TEXT(D4/E4,"#/######"),"/",":")

Text function is suitable for negative number also, whereas GCD function is Not.

GCD function stands Greater common factor.
syntax : GCD(number1, [number2], ...)

Example: GCD(100,200) = 100
When you divide 100 by 100, its 1
& 200 by 100, its 2.

Since we need the ratio, by concatenate the results by using concatenate function or &":"&.

So, our formula returns as =D4/GCD(D4,E4)&":"&E4/GCD(D4,E4)

For H column Formula,  Using Text function TEXT(D4/E4,"#/######"), returns the result of maximum divided into the format "number1/number2". Example for if 100 is divided by 200, then text format will return 1/2 which is the maximum possible divide.
Since we need the result in 1:2 insteaed of 1/2, so we are using SUBSTITUTE function for it to substitute"/" by ":".

Our formula will be then,
=SUBSTITUTE(TEXT(D4/E4,"#/######"),"/",":")

Thanks

Split a number into integer and decimal portions.

Split a Number into Integer and Decimal portions.

For integer, you could also use formula =INT(D4). But since INT function is for only positive numbers. So, we are using here TRUNC function, =TRUNC(D4,0)

And for decimal , it is =MOD(D4,SIGN(D4))

Sequence of Months parallel to sequence of number.

Get Sequence of Months parallel to sequence of number.

In previous blog :
For sequence of number we used MOD function.
C4  =MOD(C3,12)+1

For, Sequence Month:
Formula: =TEXT(DATE(2000,C4,1),"mmm") in E4 cell.

Drag down the formula, you will get Months in series.
From Jan to Dec , then start jan to Dec and so on.

Repeate a Sequence of Number

To Repeat a sequence of number, we could use MOD function for it.
For Example , We need a column of sequence 1,2,3,4,5 and then start from 1,2,3,4,5 and so on..

For that use formula :
C4 = MOD(C3,5)+1, Where C3 is blank .
Drag down the formula ..

MOD function (MOD( number , divisor ) >> returns the remainder after a number is divided by a divisor. 
In our example 5 is the divisor, when number reaches to 5 in C9 cell  , it make it zero and since we are adding it with 1, our series again started it with one.

Thanks