VBA trying to copy each unique combination of whole columns to a new sheet

P

PiPaPils

New Member
Joined
Feb 7, 2018
Messages
9
Hi all!

I usually lurk in these forums for all my VBA questions. There are several real nuggets of advice and solutions. I am always able to combine code and learn of the solutions offered. However, I can't get the following problem fixed... Basically I got a dynamic amount of columns in one sheet. I want to compare all possible combinations of columns in a separate sheet. So if I got Column A t/m F on the main sheet, I want a sheet for column A & B, one sheet for A & C, one sheet for A & D, etc... until the last sheet that consists of E & F.


[TABLE="width: 954"]
<tbody>[TR]
[TD="colspan: 2"]Main sheet:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet 1:[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet 2:[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet 3:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[TD]ColumnC[/TD]
[TD]ColumnD[/TD]
[TD]ColumnE[/TD]
[TD]ColumnF[/TD]
[TD][/TD]
[TD]ColumnA[/TD]
[TD]ColumnB[/TD]
[TD][/TD]
[TD]ColumnA[/TD]
[TD]ColumnC[/TD]
[TD][/TD]
[TD]ColumnA[/TD]
[TD]ColumnD[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

The issue is that the amount of columns is dynamic. So sometimes its 10, sometimes 15 and it can run up to 20. I am currently struggling to get it done with a double column loop, but I think I am thinking in the wrong direction.

Is there anyone who can give me a push in the right direction :) ?

Thanks in advance,

Koen
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
What is the first combination after you process the Columns A/F combination... ColumnB/ColumnC or ColumnB/ColumnA (the reverse of the first Column A processing)?
 
Upvote 0
Try:
Code: 
Sub CopyCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lColumn As Long
    lColumn = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
    Dim x As Long
    For x = 2 To lColumn
        Sheets.Add after:=Sheets(Sheets.Count)
        Sheets("Sheet1").Range("A1:A" & LastRow).Copy Cells(1, 1)
        Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, x), Sheets("Sheet1").Cells(LastRow, x)).Copy Cells(1, 2)
    Next x
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
mumps said:
Try:
Code: 
Sub CopyCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lColumn As Long
    lColumn = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
    Dim x As Long
    For x = 2 To lColumn
        Sheets.Add after:=Sheets(Sheets.Count)
        Sheets("Sheet1").Range("A1:A" & LastRow).Copy Cells(1, 1)
        Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, x), Sheets("Sheet1").Cells(LastRow, x)).Copy Cells(1, 2)
    Next x
    Application.ScreenUpdating = True
End Sub
Click to expand...

Hi Mumps!

Thats certainly a step in the right direction! It runs "only" from A & B to A & F however. I could added the blue part to make it run for all B combinations as well. Ideally there is a way to loop this as well ofcourse :)

Code: 
Sub CopyCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lColumn As Long
    lColumn = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
    Dim x As Long
    For x = 2 To lColumn
        Sheets.Add after:=Sheets(Sheets.Count)
        Sheets("Sheet1").Range("A1:A" & LastRow).Copy Cells(1, 1)
        Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, x), Sheets("Sheet1").Cells(LastRow, x)).Copy Cells(1, 2)
    Next x
    
    
[COLOR=#0000ff][I]    For x = 3 To lColumn[/I][/COLOR]
[COLOR=#0000ff][I]        Sheets.Add after:=Sheets(Sheets.Count)[/I][/COLOR]
[COLOR=#0000ff][I]        Sheets("Sheet1").Range("B1:B" & LastRow).Copy Cells(1, 1)[/I][/COLOR]
[COLOR=#0000ff][I]        Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, x), Sheets("Sheet1").Cells(LastRow, x)).Copy Cells(1, 2)[/I][/COLOR]
[COLOR=#0000ff][I]    Next x[/I][/COLOR]
    
    Application.ScreenUpdating = True
    
    
    
    
    
    
    
End Sub
@Rick, the combination is not important since I am planning to use a worksheet loop afterwards. I want to run several statistical tests on every possible combination, and then combine it again in a summary sheet. I do not necessarily need both A & B and B & A, but if that's the simplest solution, they are welcome :)
 
Upvote 0
PiPaPils said:
@Rick, the combination is not important since I am planning to use a worksheet loop afterwards. I want to run several statistical tests on every possible combination, and then combine it again in a summary sheet. I do not necessarily need both A & B and B & A, but if that's the simplest solution, they are welcome :)
Click to expand...
Okay, give this macro a try. The code assumes your data is on a sheet named "Main" and it will insert a new sheet for each column combination that you want and name that inserted sheet with the column designations its data came from.
Code: 
Sub ColumnsTwoByTwo()
  Dim R As Long, C As Long, Coff As Long, LastRow As Long, LastCol As Long, LastSht As String
  LastRow = Sheets("Main").Cells(Rows.Count, "A").End(xlUp).Row
  LastCol = Sheets("Main").Cells(1, Columns.Count).End(xlToLeft).Column
  LastSht = "Main"
  For C = 1 To LastCol - 1
    For Coff = C + 1 To LastCol
      With Sheets.Add(After:=Sheets(LastSht))
        .Name = Split(Columns(C).Address(0, 0), ":")(0) & "-" & Split(Columns(Coff).Address(0, 0), ":")(0)
        LastSht = .Name
        .Range("A1:B" & LastRow) = Application.Index(Sheets("Main").Cells, Evaluate("ROW(1:" & LastRow & ")"), Split(C & " " & Coff))
      End With
    Next
  Next
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
195
admvlad
A
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
91
Peter_SSs
Peter_SSs
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
283
etaf
etaf
H
  • Solved
First cell with a value of 0 after a certain row
2
Replies
10
Views
263
Fluff
Fluff
S
  • Solved
Individual Ranking
2
Replies
11
Views
455
sksanjeev786
S

Forum statistics

Threads
1,223,980
Messages
6,175,764
Members
452,668
Latest member
mrider123

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