Challenge: Is there a way to "shuffle" two columns

W

Wulf

Active Member
Joined
Dec 1, 2004
Messages
395
Office Version
  1. 365
Platform
  1. Windows
One column of names, antoher column of names.

Some names are in both columns, some are not.

How does one shuffle both of the columns, with identical entries not entered twice, in the column between them both?[/b]
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
I think we need more explaining.
What does this mean?
with identical entries not entered twice
Click to expand...

To me identical entries are ones that are entered twice?????


Michael
 
Upvote 0
I think we need more explaining.
What does this mean?
with identical entries not entered twice
Click to expand...

To me identical entries are ones that are entered twice?????


Michael
Click to expand...

Column A.....................Column C
A.................................A
B.................................C
C.................................D
F.................................E
G.................................F
H.................................H
I.................................K
M.................................K


..........Column B
..........A
..........B
..........C
..........D
..........E
..........F
..........G
..........H
..........J
..........K
..........M

"Shuffled", alphabetically, with identical entries (the same entry in both columns) only entered once in the center.
 
Upvote 0
Column A.....................Column C
A.................................A
B.................................C
C.................................D
F.................................E
G.................................F
H.................................H
I.................................K
M.................................K


..........Column B
..........A
..........B
..........C
..........D
..........E
..........F
..........G
..........H
..........J
..........K
..........M

"Shuffled", alphabetically, with identical entries (the same entry in both columns) only entered once in the center.
Click to expand...
Should there be an "I" in column B?
 
Upvote 0
This bit of code will do it. It assumes the columns are not sorted before hand and that that each column can also have duplicate values in it..

Code: 
Option Explicit

Sub Shuffle()
    Dim llSource1 As Long
    Dim llSource2 As Long
    Dim llDestination As Long

    llSource1 = 2
    llSource2 = 2
    llDestination = 1

    wsList.Columns(1).Sort wsList.Cells(1, 1), , , , , , , xlYes
    wsList.Columns(3).Sort wsList.Cells(1, 3), , , , , , , xlYes

    Do While Len(wsList.Cells(llSource1, 1).Value & wsList.Cells(llSource2, 3).Value) > 0
        If Len(wsList.Cells(llSource1, 1).Value) = 0 Then
            llDestination = AddValue(wsList.Cells(llSource2, 3).Value, llDestination)
            llSource2 = llSource2 + 1
        ElseIf Len(wsList.Cells(llSource2, 3).Value) = 0 Then
            llDestination = AddValue(wsList.Cells(llSource1, 1).Value, llDestination)
            llSource1 = llSource1 + 1

        ElseIf wsList.Cells(llSource1, 1).Value < wsList.Cells(llSource2, 3).Value Then
            llDestination = AddValue(wsList.Cells(llSource1, 1).Value, llDestination)
            llSource1 = llSource1 + 1

        ElseIf wsList.Cells(llSource1, 1).Value > wsList.Cells(llSource2, 3).Value Then
            llDestination = AddValue(wsList.Cells(llSource2, 3).Value, llDestination)
            llSource2 = llSource2 + 1
        
        Else
            llDestination = AddValue(wsList.Cells(llSource1, 1).Value, llDestination)
            llSource1 = llSource1 + 1
            llSource2 = llSource2 + 1

        End If

    Loop

End Sub

Private Function AddValue(Value As String, Row As Long) As Long
    Dim llResult As Long

    llResult = Row
    If wsList.Cells(llResult, 2).Value <> Value Then
        llResult = llResult + 1
        wsList.Cells(llResult, 2).Value = Value

    End If

    AddValue = llResult

End Function
 
Upvote 0
Assuming column H is vacant, try this:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Shuffle()
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    Range("H1").Value = "Temp"
    Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)).Copy _
        Destination:=Range("H2")
    Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp)).Copy _
        Destination:=Range("H" & Rows.Count).End(xlUp).Offset(1)
    <SPAN style="color:#00007F">With</SPAN> Columns("H")
        .Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
        .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B1"), Unique:=<SPAN style="color:#00007F">True</SPAN>
        .EntireColumn.Delete
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Range("B1").Delete Shift:=xlUp
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
You must log in or register to reply here.

Similar threads

Data123
  • Solved
Symbol list in Spreadsheet
Replies
7
Views
241
Peter_SSs
Peter_SSs
M
  • Question
VBA search for specific file name in folders and their subfolders
Replies
7
Views
836
Candyman8019
C
Jovinchious
  • Solved
VLOOKUP challenge
Replies
3
Views
345
duggie33
D
G
  • Solved
How to filter a table with two email columns against a list of emails
Replies
7
Views
351
dreid1011
dreid1011
S
  • Question
Macro to add sum total to track percentages
Replies
0
Views
106
spenserallen7
S

Forum statistics

Threads
1,215,066
Messages
6,122,948
Members
449,095
Latest member
nmaske

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top