FIND TOP FIVE VALUES FROM A LIST OF DUPLICATE VALUES

Generally if we use Large function to find out top 5 (any number) values from a list of duplicates it will give
67,65,65,40,40. But if you want to find out the large unique values use below mentioned formula.


Select range from B2:B6 and enter the following formula in the cell B2

=TRANSPOSE(LARGE(IF(FREQUENCY(A2:A11,A2:A11)>0,A2:A11,""),{1,2,3,4,5}))

Press Ctrl+Shift+Enter

GANTT CHART BY VBA

Paste the below code in the module:


Sub gantt_chart()
Dim lastrow, lastcol As Long

lastrow = Range("A65536").End(xlUp).Row
lastcol = Range("A:A").End(xlToRight).Column

Dim i, j As Integer

For j = 5 To lastcol
For i = 2 To lastrow

If Cells(1, j).Value >= Cells(i, 3).Value And Cells(1, j).Value <= Cells(i, 4).Value Then
Cells(i, j).Value = 1
Cells(i, j).Interior.Color = vbRed
Cells(i, j).NumberFormat = ";;;"
Else
Cells(i, j).Value = 0
Cells(i, j).NumberFormat = ";;;"
End If
Next
Next
End Sub

COUNT UNIQUE ENTRIES FROM A LIST OF DUPLICATE DATA

Formula in B2:

=SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11))

FORMULA TO SUM ALL ODD NUMBERS BETWEEN 1 TO 100

Formulas:

=SUM(2*ROW(OFFSET($A$1,,,100/2))-1) 

Press Ctrl+Shft+Entr

=SUM(ROW(1:100)*MOD(ROW(1:100),2))

Press Ctrl+Shft+Entr

=SUM(ROW(1:100)*ISODD(ROW(1:100)))

Press Ctrl+Shft+Entr

All these formulas can be used for the same purpose

FORMULA TO SUM ALL EVEN NUMBERS BETWEEN 1 TO 100

 Array Formula:

=SUM(ROW(1:100)*ISEVEN(ROW(1:100)))

Press   Ctrl+Shft+Entr.

ADD NUMBERS HAVING TEXT WITH IT

3 Formulas to do this

Formula1 in B2:  =SUMPRODUCT(--SUBSTITUTE(UPPER(G33:G37),"K",""))
Formula2 in B3:  =SUM(--LEFT(G33:G37,LEN(G33:G37)-1))        Press Ctrl+Shft+Enter
Formula3 in B4:  =SUMPRODUCT(--(LEFT(G33:G37,LEN(G33:G37)-1)))    Press Ctrl+Shft+Enter

EXTRACT NUMBERS FROM AN ALPHANUMERIC STRING WHEN NUMBERS ARE NOT CLUSTERED

Alphanumeric String	Numbers
RED458ELT9	4589
s324rake8ete	3248
drk77dFTT563j	77563
FKD5RR	5
4tryr351	4351
3fdffd4f5	345
Rkknk4656s	4656
9c5vcv933`90923]]	9593390923
À87&‰ð$ 45§§Ø7çz24[	8745724
~9¯œ»~ô855‹eg87Û©37	98558737

Array Formula:

=NPV(-0.9,,IFERROR(MID(A58,LEN(A58)-ROW(INDIRECT("1:"&LEN(A58)))+1,1)%,""))

Press Ctrl+Shft+Enter