Macro for adding formula and sorting and extracting

[paradise]

Hi,

I have a data from A:Y5000 with heading.Now,here in Z column I want to to add a formula= left((trim(D2)) and copied down as long as A:Y range has data.

Secondly,sort Z column alphabetically and extract in new worksheet that contains'P'

Thanx all of u in advance.

 

[AlanSidman]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.

 

[paradise]

I have modified above range for minimising.Before & after has been mentioned.
I hope this would suffice info.

 

[Marc L]

Hi !​

Sub Demo1()
    Range("E2:E" & ActiveSheet.UsedRange.Rows.Count).Formula = "=LEFT((TRIM(B2)),1)"
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Marc L]

Secondly,sort Z column alphabetically and extract in new worksheet that contains'P'

No needs any formula neither a code,
just directly use an easy advanced filter !
​

 

[paradise]

Hi !​

Sub Demo1()
    Range("E2:E" & ActiveSheet.UsedRange.Rows.Count).Formula = "=LEFT((TRIM(B2)),1)"
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

Have u seen my ultimate required results in worksheet-'After-Final Result'
That results I require thru VBA code.

 

[AlanSidman]

Secondly,sort Z column alphabetically and extract in new worksheet that contains'P'

What does contains'P' mean? See no P to sort on? Your final result does not sort, but filters for West. Why do you need column E in your After Working Sheet if it is not used in the final output. Your request other than finding the last row is at best confusing. Please clarify what you are trying to do? Why do you need VBA to filter if this is a built in function of Excel?

 

[paradise]

What does contains'P' mean? See no P to sort on? Your final result does not sort, but filters for West. Why do you need column E in your After Working Sheet if it is not used in the final output. Your request other than finding the last row is at best confusing. Please clarify what you are trying to do? Why do you need VBA to filter if this is a built in function of Excel?

I have modified the post #1 due to confidential data.I have attached in Post#4 and accordingly criteria.In that file worksheet "After-Final Result" is my ultimate result which I want to extract from worksheet 'Before' which is my raw data & worksheet between these two is one kind of helper.Hence my ultimate goal is "After-Final Result" worksheet.

I hope this would ease.

 

[paradise]

Hi !​

Sub Demo1()
    Range("E2:E" & ActiveSheet.UsedRange.Rows.Count).Formula = "=LEFT((TRIM(B2)),1)"
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

This formula is awesome but it should be as long as the data are there and not more than that in either side.No more no less

 

[AlanSidman]

The easiest way to do this is to use Power Query/Get and Transpose. Here is the MCode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Region", type text}, {"Product", type text}, {"Quantity", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Region] = "West"))    #"Filtered Rows"

Highlight your table. Click on Power Query and select Get Data from Range or Table. Click on the Filter and uncheck all but West. Go to Home and Close and Load.