how automatically combine two column and sort the cells?

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

how automatically combine two column and sort the cells?

Post by siamandm »

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?
Screenshot 2021-09-24 084229.png
You do not have the required permissions to view the files attached to this post.

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: how automatically combine two column and sort the cells?

Post by YasserKhalil »

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

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: how automatically combine two column and sort the cells?

Post by siamandm »

So its not possible to use the commands on the interface!

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: how automatically combine two column and sort the cells?

Post by YasserKhalil »

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.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: how automatically combine two column and sort the cells?

Post by HansV »

siamandm wrote:
24 Sep 2021, 06:48
So its not possible to use the commands on the interface!
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

User avatar
p45cal
2StarLounger
Posts: 142
Joined: 11 Jun 2012, 20:37

Re: how automatically combine two column and sort the cells?

Post by p45cal »

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)

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: how automatically combine two column and sort the cells?

Post by siamandm »

YasserKhalil wrote:
24 Sep 2021, 06:31
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
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!!

Regards

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: how automatically combine two column and sort the cells?

Post by YasserKhalil »

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)

siamandm
5StarLounger
Posts: 1192
Joined: 01 May 2016, 09:58

Re: how automatically combine two column and sort the cells?

Post by siamandm »

Thank you very much for your support
Now is working as expected