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?

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

<(E-Mail Removed)> wrote in message
news:d5b1284c-d8d2-41e6-9bbe-(E-Mail Removed)...
> 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?

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

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").Sort Key1:=Range("C2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("C").Delete
End Sub

Regards,
Peter T

<(E-Mail Removed)> wrote in message
news:5b0d194e-498d-4928-8fb7-(E-Mail Removed)...
> 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

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").Sort Key1:=Range("C2"), _
> * * * * * * * * * * * * Order1:=xlAscending, Header:=xlYes, _
> * * * * * * * * * * * * OrderCustom:=1, MatchCase:=False, _
> * * * * * * * * * * * * Orientation:=xlTopToBottom, _
> * * * * * * * * * * * * DataOption1:=xlSortNormal
>
> * * Range("C").Delete
> End Sub
>
> Regards,
> Peter T