VBA HELP - Big table into a smaller table

[bobshah2010]

Hi Guys,

I would like some help in making a macro that pulls columns from a BIG table (e.g 40 rows x 50 Cols) to a smaller table (40 rows x 5 cols) into a new sheet, and those 5 columns that it converts the table to are based of identically matching headers in a list i've created on another sheet.

So essentially i will have 3 sheets -

• One with the list of headers of the columns i'd like to extract from the master table sheet
• Master table sheet (the big table with 40 rows x 50 cols)
• Macro derived table that when i push a button it will generate the new smaller table into this sheet with the same amount of rows, but different number of columns.

The reason I need this is to create a correlation matrix based on the smaller table as opposed to making a correlation matrix from the larger table. The smaller table will also be constantly updated i.e number columns from the Big table may be added to the end of the smaller table i.e today it might be 5 rows, but as the list from the first sheet increases so does the smaller table.

If it is possible could the step that involves generating the smaller table be improved by also performing a correlation matrix based on the size of the smaller table - and include the headers preferably in the matrix output.

Thanks a lot guys, your help is always much appreciated.

Kind regards

 

[Michael M]

Assuming Master sheet is called "Master"
List Sheet is called "Unique List"
3rd Sheet is called "NEW SHEET"

Sub MM1()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, lr As Long, lc As Integer
Dim c As Integer, c1 As Integer, r As Long, lc2 As Integer
Set ws1 = Sheets("Master")
Set ws2 = Sheets("Unique List")
Set ws3 = Sheets("NEW SHEET")
lr = ws2.Cells(Rows.Count, "A").End(xlUp).Row
lc = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
lc2 = ws3.Cells(1, Columns.Count).End(xlToLeft).Column
For c = 1 To lc
    For r = 1 To lr
        If ws2.Cells(r, 1).Value = ws1.Cells(1, c) Then
            ws1.Columns(c).Copy ws3.Cells(1, lc2)
        lc2 = lc2 + 1
        End If
    Next r
Next c
End Sub

 

[bobshah2010]

Thank you so much Michael - works like a charm!

I've added a line which clears the "NEW SHEET" page so that it refreshes the table each time.

Once again - thank you for this!

 

[Michael M]

glad it worked for you...

 

[bobshah2010]

Hi Michael,

Sub GenerateCorrMatrix()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, s1 As Worksheet, lr As Long, lc As Integer
Dim c As Integer, c1 As Integer, r As Long, lc2 As Integer, rng As Range
Set ws1 = Sheets("Data Table")
Set ws2 = Sheets("Unique Name List")
Set ws3 = Sheets("Correlation Matrix")
Set s1 = Sheets("Hypothesis")


s1.Range("M21:M1000").Copy ws2.Range("A1")
ws2.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo


On Error Resume Next
ws2.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete




Sheets("Correlation Matrix").Cells.Clear


lr = ws2.Cells(Rows.Count, "A").End(xlUp).Row
lc = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
lc2 = ws3.Cells(1, Columns.Count).End(xlToLeft).Column
For c = 1 To lc
    For r = 1 To lr
        If ws2.Cells(r, 1).Value = ws1.Cells(1, c) Then
            ws1.Columns(c).Copy ws3.Cells(1, lc2)
        lc2 = lc2 + 1
        End If
    Next r
Next c




rng = ws3.Range([a1], [a1].End(xlDown).End(xlToRight))
     Application.Run "ATPVBAEN.XLA!Mcorrel", rng, _
        ws3.Range("$A$60"), "C", True

I've amended the original code you made to work for me. I'm currently having difficulty creating the correlation matrix to be formed at the bottom of the table generated in the "Correlation matrix" sheet. Could you please help around that. From my understanding - the Macro should select all the columns in the "correlation Matrix" sheet and then perform the Correlation Matrix application and then paste the result into A60. However nothing is happening at the moment.

 

[bobshah2010]

Open question to anyone else as well - there is obviously a problem with my last few lines:

rng = ws3.Range([a1], [a1].End(xlDown).End(xlToRight))
Application.Run "ATPVBAEN.XLA!Mcorrel", rng, _
ws3.Range("$A$60"), "C", True

 

[Michael M]

Can you post what the matrix would look like based on the sample sheet we worked with, then upload again back to dropbox ??

 

[bobshah2010]

Hi Michael,

The correlation matrix will look like this:

http://imgur.com/a/RoWvK

Is this what you are after?

I should probably add - The correlation matrix is automatically created through the use of the data analysis pack in excel.

Also the picture doesn't show it but the first table starts from A1

 

[Michael M]

Hmmm, I don't have the analysis pack....I'm guessing it's an addin ???..so that would make it difficult to proceed any further.
I'd suggest posting a new thread with your matrix question, but please ensure you also include a link back to this thread, so others can see where you have progressed from.
In the meantime I will have a look at the addin AND how it relates.

 

[bobshah2010]

Hi Michael,

Thanks for all your help anyways - i'll create a thread tomorrow

Really appreciate the help you have provided!!

 

[BrianJN1]

@Michael,
yes, it is an Add-in and you should have it within your software.

Check:
File>Options>Add-ins.
I see Analysis ToolPak registered under a greyed "Inactive Application Add-ins"
I haven't tested how to make it active.