Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating xla file to clear junk and sort reqd data
Hello.
I am doing a manual task everyday to remove columns which are not required and sort required columns after seggregating alphabets from number. I am seeking help to create an .xla file (Addin) which can allow me to do this task in one shortcut key (CTRL+ALT+G) My data is as follows A1:F13 Junk1 Code Junk2 Remark Junk3 Junk4 Junk L Junk 2-F Junk Junk Junk K Junk 2-E Junk Junk Junk F Junk 1-F Junk Junk Junk A Junk 1-A Junk Junk Junk I Junk 2-C Junk Junk Junk J Junk 2-D Junk Junk Junk C Junk 1-C Junk Junk Junk B Junk 1-B Junk Junk Junk H Junk 2-B Junk Junk Junk D Junk 1-D Junk Junk Junk G Junk 2-A Junk Junk Junk E Junk 1-E Junk Junk I want an output which would look like as follow (A1:B13) Code Remark A 1-A B 1-B C 1-C D 1-D E 1-E F 1-F G 2-A H 2-B I 2-C J 2-D K 2-E L 2-F Above is just a sample data. The rows and columns could be more. Code and Remark will always be in column B and D respectively. Can somebody help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating xla file to clear junk and sort reqd data
I recorded a macro which gave me this....
Sub Macro1() ' ' Macro1 Macro ' Macro recorded 11-10-2008 by PT ' ' Range("A:A,C:C,E:E,F:F").Select Range("F1").Activate Selection.Delete Shift:=xlToLeft Columns("A:B").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub ... then I removed unnecessary Select's, Activate and Selection and ended up with this Sub DelAndSort() Range("A:A,C:C,E:F").Delete Columns("A:B").Sort Key1:=Range("A2"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub I would have though a little macro like this would fit into your Personal.xls. Look at alt-F8 to set a shortcut, or look at Application.OnKey in help. Regards, Peter T wrote in message ... Hello. I am doing a manual task everyday to remove columns which are not required and sort required columns after seggregating alphabets from number. I am seeking help to create an .xla file (Addin) which can allow me to do this task in one shortcut key (CTRL+ALT+G) My data is as follows A1:F13 Junk1 Code Junk2 Remark Junk3 Junk4 Junk L Junk 2-F Junk Junk Junk K Junk 2-E Junk Junk Junk F Junk 1-F Junk Junk Junk A Junk 1-A Junk Junk Junk I Junk 2-C Junk Junk Junk J Junk 2-D Junk Junk Junk C Junk 1-C Junk Junk Junk B Junk 1-B Junk Junk Junk H Junk 2-B Junk Junk Junk D Junk 1-D Junk Junk Junk G Junk 2-A Junk Junk Junk E Junk 1-E Junk Junk I want an output which would look like as follow (A1:B13) Code Remark A 1-A B 1-B C 1-C D 1-D E 1-E F 1-F G 2-A H 2-B I 2-C J 2-D K 2-E L 2-F Above is just a sample data. The rows and columns could be more. Code and Remark will always be in column B and D respectively. Can somebody help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating xla file to clear junk and sort reqd data
The below code that you gave works well
Sub DelAndSort() Range("A:A,C:C,E:F").Delete Columns("A:B").Sort Key1:=Range("A2"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub But it does not satisfy my condition. I want my first sort on column B. I changed the line .Sort Key1:=Range("A2") to "B2" and it still works fine but if I add few more lines to my data, it does not give desired output. For instance, If I add the following to my data Junk O Junk 10-C Junk Junk Junk M Junk 10-A Junk Junk Junk N Junk 10-B Junk Junk Junk R Junk 10-F Junk Junk Junk Q Junk 10-E Junk Junk Junk P Junk 10-D Junk Junk and make my data range bigger A1:F19 it sorts alphabetically. It does not recognize numbers and text seperately. If I run your code changing the sort key1 from A to B it give me the following result Code Remark M 10-A N 10-B O 10-C P 10-D Q 10-E R 10-F A 1-A B 1-B C 1-C D 1-D E 1-E F 1-F G 2-A H 2-B I 2-C J 2-D K 2-E L 2-F But I want it in the following way (column A should not get sorted first - first sort should always be on column B). Code Remark A 1-A B 1-B C 1-C D 1-D E 1-E F 1-F G 2-A H 2-B I 2-C J 2-D K 2-E L 2-F M 10-A N 10-B O 10-C P 10-D Q 10-E R 10-F I hope I made my question clear |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating xla file to clear junk and sort reqd data
Have a go with this
Sub DelAndSort2() Range("A:A,C:C,E:F").Delete ' copy values in col-B to col-C Range("C:C").Value = Range("B:B").Value ' text to columns, "-" separator Range("C:C").TextToColumns Destination:=Range("C1"), _ DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _ Comma:=False, Space:=False, Other:=True, OtherChar:="-", _ FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True Range("C1") = "Temp" ' ensure there's a header cell ' sort on col C Columns("A:D").Sort Key1:=Range("C2"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("C:D").Delete End Sub Regards, Peter T wrote in message ... The below code that you gave works well Sub DelAndSort() Range("A:A,C:C,E:F").Delete Columns("A:B").Sort Key1:=Range("A2"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub But it does not satisfy my condition. I want my first sort on column B. I changed the line .Sort Key1:=Range("A2") to "B2" and it still works fine but if I add few more lines to my data, it does not give desired output. For instance, If I add the following to my data Junk O Junk 10-C Junk Junk Junk M Junk 10-A Junk Junk Junk N Junk 10-B Junk Junk Junk R Junk 10-F Junk Junk Junk Q Junk 10-E Junk Junk Junk P Junk 10-D Junk Junk and make my data range bigger A1:F19 it sorts alphabetically. It does not recognize numbers and text seperately. If I run your code changing the sort key1 from A to B it give me the following result Code Remark M 10-A N 10-B O 10-C P 10-D Q 10-E R 10-F A 1-A B 1-B C 1-C D 1-D E 1-E F 1-F G 2-A H 2-B I 2-C J 2-D K 2-E L 2-F But I want it in the following way (column A should not get sorted first - first sort should always be on column B). Code Remark A 1-A B 1-B C 1-C D 1-D E 1-E F 1-F G 2-A H 2-B I 2-C J 2-D K 2-E L 2-F M 10-A N 10-B O 10-C P 10-D Q 10-E R 10-F I hope I made my question clear |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating xla file to clear junk and sort reqd data
Thanx... it worked !!!
I was getting warning to replace rows while the system was performing text to column. I set the warning to FALSE in the beginning and TRUE at the end. Thank you very much On Oct 12, 1:19*am, "Peter T" <peter_t@discussions wrote: Have a go with this Sub DelAndSort2() * * Range("A:A,C:C,E:F").Delete * * ' copy values in col-B to col-C * * Range("C:C").Value = Range("B:B").Value * * ' text to columns, "-" separator * * Range("C:C").TextToColumns Destination:=Range("C1"), _ * * * * * * DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ * * * * * * ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _ * * * * * * Comma:=False, Space:=False, Other:=True, OtherChar:="-", _ * * * * * * FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True * * Range("C1") = "Temp" ' ensure there's a header cell * * ' sort on col C * * Columns("A:D").Sort Key1:=Range("C2"), _ * * * * * * * * * * * * Order1:=xlAscending, Header:=xlYes, _ * * * * * * * * * * * * OrderCustom:=1, MatchCase:=False, _ * * * * * * * * * * * * Orientation:=xlTopToBottom, _ * * * * * * * * * * * * DataOption1:=xlSortNormal * * Range("C:D").Delete End Sub Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i AM CREATING A DATA AND WANT TO SORT, BUT KEEP DATA TOGETHER | Excel Worksheet Functions | |||
Email downloads into a single Excel cell - data separation reqd. | Excel Programming | |||
Junk Characters in Excel Documents | Excel Discussion (Misc queries) | |||
Creating a graph with existing data...clear data after save | Excel Programming | |||
delete blocked senders messages on receipt why junk mail them? | Excel Discussion (Misc queries) |