How to convert a 1 one column matrix into a set of X column x Y rows matrix


My people ask me how to convert a one column matrix (which can have several hundreds of lines) into a set of X.Y matrixes..(usually 96 cells)
I am looking a way to do it with only usual excel functions. But of course if necessary we can use macros

They can eventually do it, one matrix after another..

I join a sample of a single transposed matrix, builded line after after line.. How to do the same, more automatically, with so many similar matrixes as requested by the size of the original column

looking forward hearing from you

best regards

Jacques WeissenburgerAss ProfAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

take a look at the new worksheet I added to your workbook.

I'm using the Offset() function to retrieve the values and the Row() and Column() functions for offset addressing.
Jacques WeissenburgerAss ProfAuthor Commented:
Thanks a lot
This is almost perfect..

To make it brillant, I would need a second step.. A way to skip a line after.. 96 CELLS OR 8 LINES something like this Or change teh background color very 8 lines...  

This is why I am a retired man, and the most this thing will be automated, the best it will be..

Nevertheless, if too complicated, I can handle it the way to proposed and once more, thanks for that quick answer

Best regards

I don't see any line skipping in the workbook you posted.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jacques WeissenburgerAss ProfAuthor Commented:
Oh! yes,
because I don't know how to do it automatically.

Normally I need 'n' boxes with 96 cases.. clearly separated..

Do you have a idea how to do this with a formula or using conditionnal formatting?
I'm looking for a visual representation of your verbal description.  If you copy/paste values, you will be able to manually arrange the data to match your description.

I am assuming that you weren't describing a skip of the column of values.  Your manual result will reveal the exact nature of your problem description.
Jacques WeissenburgerAss ProfAuthor Commented:
Yes here i tried to do it.. But I failed..
Of course, I inserted a line to separate the blocks, but doing that I offset also 12 case more, and this is not what I wanted..
Jacques WeissenburgerAss ProfAuthor Commented:
Sorry I forgot the excel spreatsheet
You forgot to copy/paste value the transposed cells before you started inserting the blank rows.  I added code to do that and to insert the blank rows for you in the attached file.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jacques WeissenburgerAss ProfAuthor Commented:
I don't undetstand
I need a blanck line after every 8 transposed lines
Mine shows that line. But doing this i produced an error because the blanck line is part of offset
What error did it produce and on what line?

I left your modified worksheet alone and made a copy.  The code only works on the copy of the worksheet.
Jacques WeissenburgerAss ProfAuthor Commented:
after the blanck line, Line 9  should show A97 and not A97+12=A109
After a blanck line I should reset the offset formula some 12 points less
Are you looking at the tRANSPOSE-SAMPLE-v3.xlsm file I posted?
Did you run the VBA macro routine, Q_28628919?
Are you looking at the results in worksheet Q_28628919 (2) ?
Jacques WeissenburgerAss ProfAuthor Commented:
Ok sorry
May be i didn't check the macro
I just looked the result

I am in a hurry
A lot of lectures to do

I will take more time in a few days

Sorry for the inconvenience
Jacques WeissenburgerAss ProfAuthor Commented:

AS told, I was a bit overwhelmed..

I checked and the macro works fine on Sheet Q_28628919(2) which contains brut data (just numbers, no formula)..
I open the sheet Q_28628919 which has the formula (extracted form the sheet "Original"), and launched the macro, but it modified the  Q_28628919(2)  sheet only
I tried to correct the macro, but did not find a tool to edit it..

Could you help me here again

Thanks for your time

Best regards

Jacques WeissenburgerAss ProfAuthor Commented:
Sorry, sorry  I didn't download the right version.

I found my mistake and checked the last one

Effectively, everything works fine now, and I could modify the macro as I wanted

Thanks a lot
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows XP

From novice to tech pro — start learning today.