Fill out 'complete correlation table"

M

mikesal57

Board Regular
Joined
Jul 6, 2011
Messages
193
Office Version
  1. 2016
Platform
  1. Windows
Hi All...

I need the correlation table to be completely fill out so that I can ADD the full columns to do a few functions.
Is there a way to copy and transpose the numbers over?

Thxs
 
Last edited:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
This is the normal result you get after you compile info...

I need those blank boxes filled in ...its just the same numbers on the other side of the 1's

synchronisation-business-cycles
https://voxeu.org/article/synchronisation-business-cycles
 
Last edited:
Upvote 0
Not 100% sure I know what the end result needs to be, but here's a possibility:

Given a table like this:

ABCDE
AustraliaBelgiumFinlandFrance
Australia
Belgium
Finland
France

<tbody>
[TD="align: center"]50[/TD]
[TD="align: right"][/TD]

[TD="align: center"]51[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]52[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]53[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]54[/TD]

[TD="align: right"]0.93[/TD]
[TD="align: right"]0.95[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]

</tbody>
Summary​



Here's a table that completes the blank spaces along with formulas.
I created the formula in one cell and just pasted it down and across:

ABCDE
AustraliaBelgiumFinlandFrance
Australia
Belgium
Finland
France

<tbody>
[TD="align: center"]57[/TD]
[TD="align: right"][/TD]

[TD="align: center"]58[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]0.93[/TD]

[TD="align: center"]59[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]0.95[/TD]

[TD="align: center"]60[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.97[/TD]

[TD="align: center"]61[/TD]

[TD="align: right"]0.93[/TD]
[TD="align: right"]0.95[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]

</tbody>
Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B58[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(B51)),B51,OFFSET($B$51,COLUMNS($B$51:B51)-1,ROWS($B$51:B51)-1))[/TD]
[/TR]
[TR]
[TH]C58[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(C51)),C51,OFFSET($B$51,COLUMNS($B$51:C51)-1,ROWS($B$51:C51)-1))[/TD]
[/TR]
[TR]
[TH]D58[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(D51)),D51,OFFSET($B$51,COLUMNS($B$51:D51)-1,ROWS($B$51:D51)-1))[/TD]
[/TR]
[TR]
[TH]E58[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(E51)),E51,OFFSET($B$51,COLUMNS($B$51:E51)-1,ROWS($B$51:E51)-1))[/TD]
[/TR]
[TR]
[TH]B59[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(B52)),B52,OFFSET($B$51,COLUMNS($B$51:B52)-1,ROWS($B$51:B52)-1))[/TD]
[/TR]
[TR]
[TH]C59[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(C52)),C52,OFFSET($B$51,COLUMNS($B$51:C52)-1,ROWS($B$51:C52)-1))[/TD]
[/TR]
[TR]
[TH]D59[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(D52)),D52,OFFSET($B$51,COLUMNS($B$51:D52)-1,ROWS($B$51:D52)-1))[/TD]
[/TR]
[TR]
[TH]E59[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(E52)),E52,OFFSET($B$51,COLUMNS($B$51:E52)-1,ROWS($B$51:E52)-1))[/TD]
[/TR]
[TR]
[TH]B60[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(B53)),B53,OFFSET($B$51,COLUMNS($B$51:B53)-1,ROWS($B$51:B53)-1))[/TD]
[/TR]
[TR]
[TH]C60[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(C53)),C53,OFFSET($B$51,COLUMNS($B$51:C53)-1,ROWS($B$51:C53)-1))[/TD]
[/TR]
[TR]
[TH]D60[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(D53)),D53,OFFSET($B$51,COLUMNS($B$51:D53)-1,ROWS($B$51:D53)-1))[/TD]
[/TR]
[TR]
[TH]E60[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(E53)),E53,OFFSET($B$51,COLUMNS($B$51:E53)-1,ROWS($B$51:E53)-1))[/TD]
[/TR]
[TR]
[TH]B61[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(B54)),B54,OFFSET($B$51,COLUMNS($B$51:B54)-1,ROWS($B$51:B54)-1))[/TD]
[/TR]
[TR]
[TH]C61[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(C54)),C54,OFFSET($B$51,COLUMNS($B$51:C54)-1,ROWS($B$51:C54)-1))[/TD]
[/TR]
[TR]
[TH]D61[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(D54)),D54,OFFSET($B$51,COLUMNS($B$51:D54)-1,ROWS($B$51:D54)-1))[/TD]
[/TR]
[TR]
[TH]E61[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(E54)),E54,OFFSET($B$51,COLUMNS($B$51:E54)-1,ROWS($B$51:E54)-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




The formula pattern is:
If a cell has data in it, carry it forward
If not, reverse the columns and rows to get the new answer


What do you think?


BrianGGG
 
Last edited:
Upvote 0
BrianGGG said:
Not 100% sure I know what the end result needs to be, but here's a possibility:

Given a table like this:

ABCDE
AustraliaBelgiumFinlandFrance
Australia
Belgium
Finland
France

<tbody>
[TD="align: center"]50[/TD]
[TD="align: right"][/TD]

[TD="align: center"]51[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]52[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]53[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]54[/TD]

[TD="align: right"]0.93[/TD]
[TD="align: right"]0.95[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]

</tbody>
Summary​



Here's a table that completes the blank spaces along with formulas.
I created the formula in one cell and just pasted it down and across:

ABCDE
AustraliaBelgiumFinlandFrance
Australia
Belgium
Finland
France

<tbody>
[TD="align: center"]57[/TD]
[TD="align: right"][/TD]

[TD="align: center"]58[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]0.93[/TD]

[TD="align: center"]59[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]0.95[/TD]

[TD="align: center"]60[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.97[/TD]

[TD="align: center"]61[/TD]

[TD="align: right"]0.93[/TD]
[TD="align: right"]0.95[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]

</tbody>
Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B58[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(B51)),B51,OFFSET($B$51,COLUMNS($B$51:B51)-1,ROWS($B$51:B51)-1))[/TD]
[/TR]
[TR]
[TH]C58[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(C51)),C51,OFFSET($B$51,COLUMNS($B$51:C51)-1,ROWS($B$51:C51)-1))[/TD]
[/TR]
[TR]
[TH]D58[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(D51)),D51,OFFSET($B$51,COLUMNS($B$51:D51)-1,ROWS($B$51:D51)-1))[/TD]
[/TR]
[TR]
[TH]E58[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(E51)),E51,OFFSET($B$51,COLUMNS($B$51:E51)-1,ROWS($B$51:E51)-1))[/TD]
[/TR]
[TR]
[TH]B59[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(B52)),B52,OFFSET($B$51,COLUMNS($B$51:B52)-1,ROWS($B$51:B52)-1))[/TD]
[/TR]
[TR]
[TH]C59[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(C52)),C52,OFFSET($B$51,COLUMNS($B$51:C52)-1,ROWS($B$51:C52)-1))[/TD]
[/TR]
[TR]
[TH]D59[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(D52)),D52,OFFSET($B$51,COLUMNS($B$51:D52)-1,ROWS($B$51:D52)-1))[/TD]
[/TR]
[TR]
[TH]E59[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(E52)),E52,OFFSET($B$51,COLUMNS($B$51:E52)-1,ROWS($B$51:E52)-1))[/TD]
[/TR]
[TR]
[TH]B60[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(B53)),B53,OFFSET($B$51,COLUMNS($B$51:B53)-1,ROWS($B$51:B53)-1))[/TD]
[/TR]
[TR]
[TH]C60[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(C53)),C53,OFFSET($B$51,COLUMNS($B$51:C53)-1,ROWS($B$51:C53)-1))[/TD]
[/TR]
[TR]
[TH]D60[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(D53)),D53,OFFSET($B$51,COLUMNS($B$51:D53)-1,ROWS($B$51:D53)-1))[/TD]
[/TR]
[TR]
[TH]E60[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(E53)),E53,OFFSET($B$51,COLUMNS($B$51:E53)-1,ROWS($B$51:E53)-1))[/TD]
[/TR]
[TR]
[TH]B61[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(B54)),B54,OFFSET($B$51,COLUMNS($B$51:B54)-1,ROWS($B$51:B54)-1))[/TD]
[/TR]
[TR]
[TH]C61[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(C54)),C54,OFFSET($B$51,COLUMNS($B$51:C54)-1,ROWS($B$51:C54)-1))[/TD]
[/TR]
[TR]
[TH]D61[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(D54)),D54,OFFSET($B$51,COLUMNS($B$51:D54)-1,ROWS($B$51:D54)-1))[/TD]
[/TR]
[TR]
[TH]E61[/TH]
[TD="align: left"]=IF(NOT(ISBLANK(E54)),E54,OFFSET($B$51,COLUMNS($B$51:E54)-1,ROWS($B$51:E54)-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




The formula pattern is:
If a cell has data in it, carry it forward
If not, reverse the columns and rows to get the new answer


What do you think?


BrianGGG
Click to expand...


Thxs sooooooo much....worked like a charm...

Mike
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Conditional calculation of added columns - seeking a cleaver solution.
Replies
9
Views
275
dreid1011
dreid1011
R
  • Question Question
Need help with random number generating
Replies
2
Views
568
royland27
R
C
  • Question Question
Copy, paste, change name of file.
Replies
2
Views
119
Skyybot
Skyybot
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
402
sijpie
sijpie
G
  • Question Question
Macro or VBA code for repeating a task but with different cell information each time
Replies
2
Views
154
gregg777
G

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

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