Hello All
i have a column of alphabet and column of numbers hot to make one column from them and sort the like shown in the screen shot below?
how automatically combine two column and sort the cells?
-
- BronzeLounger
- Posts: 1234
- Joined: 01 May 2016, 09:58
how automatically combine two column and sort the cells?
You do not have the required permissions to view the files attached to this post.
-
- PlatinumLounger
- Posts: 4930
- Joined: 31 Aug 2016, 09:02
Re: how automatically combine two column and sort the cells?
Try
Code: Select all
Sub Test()
Dim a, x, m As Long, i As Long, k As Long
Columns(3).ClearContents
m = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A2:G" & m).Value
ReDim b(1 To UBound(a, 1) * 3, 1 To 1)
For i = LBound(a) To UBound(a)
For Each x In Array(1, 5, 7)
k = k + 1
b(k, 1) = a(i, x)
Next x
Next i
Range("C2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub
-
- BronzeLounger
- Posts: 1234
- Joined: 01 May 2016, 09:58
Re: how automatically combine two column and sort the cells?
So its not possible to use the commands on the interface!
-
- PlatinumLounger
- Posts: 4930
- Joined: 31 Aug 2016, 09:02
Re: how automatically combine two column and sort the cells?
I didn't get what you mean exactly.
If you mean to draw a shape or button to run the code, I think this is easy to be done.
If you mean to draw a shape or button to run the code, I think this is easy to be done.
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: how automatically combine two column and sort the cells?
The sort order that you want is not how Excel would sort the data, so you either have to drag the cells into place manually, or use a macro as suggested by Yasser.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 150
- Joined: 11 Jun 2012, 20:37
Re: how automatically combine two column and sort the cells?
If you have the SEQUENCE function on a worksheet available to you, in cell C1:
=INDEX((A2:A6,E2:E6,G2:G6),INT(SEQUENCE(15,1,1,1/3)),0,MOD(SEQUENCE(15)-1,3)+1)
or:
=INDEX((A2:A6,E2:E6,G2:G6),INT(SEQUENCE(15,,,1/3)),,MOD(SEQUENCE(15)-1,3)+1)
=INDEX((A2:A6,E2:E6,G2:G6),INT(SEQUENCE(15,1,1,1/3)),0,MOD(SEQUENCE(15)-1,3)+1)
or:
=INDEX((A2:A6,E2:E6,G2:G6),INT(SEQUENCE(15,,,1/3)),,MOD(SEQUENCE(15)-1,3)+1)
-
- BronzeLounger
- Posts: 1234
- Joined: 01 May 2016, 09:58
Re: how automatically combine two column and sort the cells?
thank you for the code, how to use this code please ? i have created a button and copied your code into it but didnt work!!YasserKhalil wrote: ↑24 Sep 2021, 06:31TryCode: Select all
Sub Test() Dim a, x, m As Long, i As Long, k As Long Columns(3).ClearContents m = Cells(Rows.Count, 1).End(xlUp).Row a = Range("A2:G" & m).Value ReDim b(1 To UBound(a, 1) * 3, 1 To 1) For i = LBound(a) To UBound(a) For Each x In Array(1, 5, 7) k = k + 1 b(k, 1) = a(i, x) Next x Next i Range("C2").Resize(UBound(b, 1), UBound(b, 2)).Value = b End Sub
Regards
-
- PlatinumLounger
- Posts: 4930
- Joined: 31 Aug 2016, 09:02
Re: how automatically combine two column and sort the cells?
Press Alt + F11 to open the VBE editor then from Insert menu select Module and finally paste the code in the standard module
Back to the worksheet draw a shape or a button > right-click on it then assign macro and select the macro name (Test in that case and you can change the macro name)
Back to the worksheet draw a shape or a button > right-click on it then assign macro and select the macro name (Test in that case and you can change the macro name)
-
- BronzeLounger
- Posts: 1234
- Joined: 01 May 2016, 09:58
Re: how automatically combine two column and sort the cells?
Thank you very much for your support
Now is working as expected
Now is working as expected