Please help with generating this matrix from an array

%3CLINGO-SUB%20id%3D%22lingo-sub-2382046%22%20slang%3D%22en-US%22%3EPlease%20help%20with%20generating%20this%20matrix%20from%20an%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2382046%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EHello%3CSPAN%3E%26nbsp%3BExcel%20Forum%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3ESay%20I%20have%20an%20array%20(see%20below%20example)%20that%20is%201%20x%208%20matrix.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EI%20would%20like%20to%20convert%20this%20to%20a%208%20x%208%20matrix%20(see%20below%20example)%20by%20shifting%20the%20array%20one%20column%20to%20the%20right.%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EI%20would%20like%20to%20generate%20this%20matrix%20by%20using%20one%20formula%20(aka%20dynamic%20array).%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EThank%20you!%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2382046%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2382076%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help%20with%20generating%20this%20matrix%20from%20an%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2382076%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037339%22%20target%3D%22_blank%22%3E%40PapaAustin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20your%20array%20is%20in%20B2%3AI2%2C%20and%20your%20matrix%20should%20start%20in%20B4.%20Enter%20the%20following%20formula%20in%20B4%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(ROW(B4%3AI11)-ROW(%24B%244)%26gt%3BCOLUMN(B4%3AI11)-COLUMN(%24B%244)%2C0%2CINDEX(%24B%242%3A%24I%242%2CCOLUMN(B4%3AI11)-COLUMN(%24B%244)-(ROW(B4%3AI11)-ROW(%24B%244))%2B1))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20not%20using%20Excel%20in%20Microsoft%20365%2C%20select%20B4%3AI11%20and%20enter%20that%20formula%20confirmed%20with%20Ctrl%2BShift%2BEnter.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello Excel Forum,

Say I have an array (see below example) that is 1 x 8 matrix.

I would like to convert this to a 8 x 8 matrix (see below example) by shifting the array one column to the right.

I would like to generate this matrix by using one formula (aka dynamic array).

Thank you!

 

 

1 Reply

@PapaAustin 

Let's say your array is in B2:I2, and your matrix should start in B4. Enter the following formula in B4:

 

=IF(ROW(B4:I11)-ROW($B$4)>COLUMN(B4:I11)-COLUMN($B$4),0,INDEX($B$2:$I$2,COLUMN(B4:I11)-COLUMN($B$4)-(ROW(B4:I11)-ROW($B$4))+1))

 

If you are not using Excel in Microsoft 365, select B4:I11 and enter that formula confirmed with Ctrl+Shift+Enter.