Problem 1 :- Filter Data and paste to other sheet

Dear Sir / madam,

Please see the attachment.  

I have a problem I want to copy data base on the Person name which i highlighted with yellow colour.

suppose if the person name "JKM" a sheet will created with the name "JKM" and the entire rows data relating to Jkm WILL copy to the new sheets of JKM.  if the person name is HM the the same rule will be follow.

Attachment

1. Filter Data and Make new Sheet with name




Sub Test()
Dim J As Integer
Dim sh As Worksheet
Sheet1.Range("M:M").Copy Sheet1.Range("Z:Z")
Sheet1.Range("Z:Z").RemoveDuplicates Columns:=1, Header:=xlNo
'Set sh = Worksheets
For J = Sheet1.Range("Z" & Rows.Count).End(xlUp).Row To 3 Step -1

    ActiveSheet.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).AutoFilter Field:=13, Criteria1:=Sheet1.Cells(J, 26)


Sheet1.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).Select
Selection.SpecialCells(xlCellTypeVisible).Copy
Set sh = Worksheets.Add
sh.Range("A1").PasteSpecial xlPasteAll
sh.Name = Sheet1.Cells(J, 26)
Sheet1.Activate
ActiveSheet.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).AutoFilter
Next J
Sheet1.Range("Z:Z").Delete

MsgBox "Thanks"

End Sub

2. Delete Old Sheet and Make new worksheet with updated Data

Sub Test()
Dim ws As Worksheet
Dim J As Integer
Dim sh As Worksheet
Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
Sheet1.Range("M:M").Copy Sheet1.Range("Z:Z")
Sheet1.Range("Z:Z").RemoveDuplicates Columns:=1, Header:=xlNo
'Set sh = Worksheets

For Each ws In Application.ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
            ws.Delete
        End If
    Next

For J = Sheet1.Range("Z" & Rows.Count).End(xlUp).Row To 3 Step -1
Sheet1.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).AutoFilter Field:=13, Criteria1:=Sheet1.Cells(J, 26)
Sheet1.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).Select
Selection.SpecialCells(xlCellTypeVisible).Copy
Set sh = Worksheets.Add
sh.Range("A1").PasteSpecial xlPasteAll
sh.Name = Sheet1.Cells(J, 26)
Sheet1.Activate
ActiveSheet.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).AutoFilter
Next J
Sheet1.Range("Z:Z").Delete
Sheet1.Range("A1").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox "Thanks"

End Sub

End Function in Excel

This function tests two or more conditions to see if they are all true.

It can be used to test that a series of numbers meet certain conditions.

It can be used to test that a number or a date falls between an upper and lower limit.

Note :- 

1. Returns TRUE if all its arguments evaluate to TRUE.

2. Returns FALSE if one or more arguments evaluate to FALSE

=And(Logical1,Logical2,.......)

=AND(TRUE,TRUE)       =      TRUE
=AND(TRUE,FALSE)      =      FALSE

Age Calculation

You can calculate age based on their birthday and today's date.

1 :- Datedif Function :-  Calculates the number of days, months, or years between two dates

                            =datedif(start date, End Date, Unit)

Unit:-

1. Number of completed year :- Y

2. Number of completed Month :- M

3. Number of completed Days :- D

4. The difference between the days. The months and years of the dates are ignored :- MD

5. The difference between the months. The days and years of the dates are ignored :- YM

6. The difference between the days. The years of the dates are ignored :- YD

2 :- Today Date Function :- Returns the current date.

                           =today()

Age Calculation :- (C1 = 10-Mar-1980)

Number of Years :-  DATEDIF(C1,TODAY(),"y")

Number of Months:-DATEDIF(C1,TODAY(),"ym")

Number of Day's :- DATEDIF(C1,TODAY(),"md")

Birth date :	10-Mar-1980
Number of Years :	37
Number of Months :	2
Number of Days :	1

Filename formula

There may be times when you need to insert the name of the current workbook or worksheet in to a cell.

 =CELL("filename")

The problem with this is that it gives the complete path including drive letter and folders.

Pick the Excel Path.

 =MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)

Pick the Workbook name.

 =MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

Pick the Worksheet name.

 =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

Time Calculation

1. :- Excel can work with time very easily.

2:- Time can be entered in various different formats and calculations performed.

3:- There are one or two oddities, but nothing which should put you off working with it.

Time Formats :- HH:MM:SS

When time is entered into worksheet it should be entered with a colon between the Hour, Minute and Seconds.

Example 1 :- 10:20:22 , 13:30:01, 23:00:00

                            OR

Example 2 :- 10:20 AM , 01:30 PM, 11:00 PM

Excel can either the 24 hour or the am/pm system.

Difference between two times

End Time - Start Time  = difference

You may need to reformat the answer.

Adding time

End Time + Start Time  = Answer

=Sum(start Time + End Time)

How To Apply Custom Formatting

 1. Click on the cell which needs the format.

2. Choose the Format menu.

3. Choose Cells.

4. Click the Number tag at the top right.

5. Choose Custom.

6. Click inside the Type: box.

7. Type [hh]:mm as the format.

8. Click Enter or OK to confirm.

Return the characters after Nth character in a Cell

Return the characters after the nth ","|"."

Use of SUBSTITUTE function when you are replacing text based on its content.

SUBSTITUTE find and replaces your old text with new text in a text string.

SUBSTITUTE(text, old_text, new_text, [instance_number]) The SUBSTITUTE function syntax has the following arguments Text  Required. The text or the reference to a cell containing text for which you want to substitute characters. Old text  Required. The text you want to replace. New text  Required. The text you want to replace old text with. Instance number  Optional. Specifies which occurrence of old text you want to replace with new text. If you specify instance number, only that instance of old text is replaced. Otherwise, every occurrence of old text in text is changed to new text.

REPT :- Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.

REPT(text, numbertimes)

Text  Required. The text you want to repeat.
Number times  Required. A positive number specifying the number of times to repeat text.

TRIM :-

TRIM(text)

Text  Required. The text from which you want spaces removed.

Return the characters after 10th character 

=TRIM(RIGHT(SUBSTITUTE(G2,"|",REPT(" ",11255),10),11255))

=TRIM(RIGHT(SUBSTITUTE(G3,".",REPT(" ",11255),10),11255))

=TRIM(RIGHT(SUBSTITUTE(G4,",",REPT(" ",11255),10),11255))

If Function with Example

IF function is one of the most valuable functions in Excel. you can make logical comparisons.

IF(Something is True, then do something, otherwise do something else)

IF Examples :- 

If value is Equals to "RAM" Then return "OK" otherwise return "Not Ok"

=IF(A1="RAM","OK","Not Ok")

If value is Grater then to 5 Then return "OK" otherwise return "Not Ok"

=IF(A4>5,"OK","Not Ok")

If F1 is grater then 3 then (F1*2) cell value is multiple 2 and the condition of first condition false result is 0

=IF(F1>3,F1*2,0)

Microsoft Excel Basic

*The  Max function will return the largest (max) value in the selected range. 

*The Min function will display the smallest value in a selected set of cells.

*The SUM function adds all the numbers that are contained in cells.

*The COUNT function counts the number of cells that contain numbers

*The COUNTA function counts the number of cells that are not empty in a range.