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

 

[BrianJN1]

I think an example of what you are after would make it clearly as to exactly your requirements.

Why not make your "Big" table say 5 x 5, your small table 5 x 2 and therefore your 3rd sheet would be 5 x 2.
Then explain what is supposed to be happening. It seems to me that data from BIG is being condensed into the small table, but I cannot understand the full relationship of the third table.

 

[bobshah2010]

I think an example of what you are after would make it clearly as to exactly your requirements.

Why not make your "Big" table say 5 x 5, your small table 5 x 2 and therefore your 3rd sheet would be 5 x 2.
Then explain what is supposed to be happening. It seems to me that data from BIG is being condensed into the small table, but I cannot understand the full relationship of the third table.

Hi Brian,

I guess I can explain the relationship with what I currently have and what I expect to happen.

Currently I have

• a big table say 5 x 5. (Say with column headers: Revenue, Growth, Size, Budget, Buildings)
• And I have another sheet with a list of the headers I prefer to have on the newly formed smaller table (Only want Revenue and Growth) - This list will be increasing but wont be as big as the original table i.e. tomorrow I might add Budget to the list

What I want is a new table to be created into another sheet that only has the headers for Revenue and Growth. I then want to create a correlation matrix based of this newly formed smaller table - Preferably also in another sheet

I hope this makes sense.

 

[Michael M]

What you want is doable, but you are not providing any information of content, location in each sheet & criteria for making said table !!

 

[BrianJN1]

I'm sorry but you have lost me. I can now understand the overall concept but your requirements are beyond me.
I trust someone else will rise to your occasion.

 

[Michael M]

@Brian
Nice drop of rain for the course ....

 

[bobshah2010]

What you want is doable, but you are not providing any information of content, location in each sheet & criteria for making said table !!

Apologies Michael,

I'll define the three sheets:
Sheet one contains list of headers under Column M row 22:100 - This list will not be unique so in order to aid the macro I'm willing to create a helper column which lists all the Unique column headers in say Column A - in the same sheet. So for the purpose of the macro assume the list of headers be located in Column A for Sheet one.

Sheet two:
Contains master table that currently ranges from A20:A60 to BV20:BV60 with row 20 containing the unique header

Sheet three:
Ideally will have a table that has been reduced based off the list of headers i want to extract from the master table.

Also beneath this table have the correlation matrix for the smaller table.

 

[Michael M]

Nope, I'm out of this thread..... !!
....the data you are using is obviously very private and can't be shared....

 

[bobshah2010]

A button on the first sheet to regenerate the smaller table based off any new unique headers will be great to have. As this will constantly be getting updated - and subsequently the correlation matrix.

I'm sorry if this isn't clear - willing to add more detail around this as well

 

[bobshah2010]

@Michael M
The data is just very big, i'll try again with examples / snippets of the data?

Master table Sheet

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Current Assets[/TD]
[TD]Non-current assets[/TD]
[TD]Revenue[/TD]
[TD]Investment Income[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]62,340[/TD]
[TD]12,300[/TD]
[TD]35,000[/TD]
[TD]23,430[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]63,400[/TD]
[TD]15,330[/TD]
[TD]40,000[/TD]
[TD]53,020[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]23,430[/TD]
[TD]12,554[/TD]
[TD]50,000[/TD]
[TD]30,232[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]30,400[/TD]
[TD]17,234[/TD]
[TD]60,320[/TD]
[TD]60,003[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Unique List Sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[/TR]
[TR]
[TD]Current Assets[/TD]
[/TR]
[TR]
[TD]Non-current assets[/TD]
[/TR]
</tbody>[/TABLE]

Ideal 3rd Sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Current Assets[/TD]
[TD]Non-current Assets[/TD]
[/TR]
[TR]
[TD]62,340[/TD]
[TD]12,300[/TD]
[/TR]
[TR]
[TD]63,400[/TD]
[TD]15,300[/TD]
[/TR]
[TR]
[TD]23,430[/TD]
[TD]12,554[/TD]
[/TR]
[TR]
[TD]30,400[/TD]
[TD]17,234[/TD]
[/TR]
</tbody>[/TABLE]

I hope this makes it clearer?