Arrays and data types

Again, leverage the power of XL. Very few use more than a tiny fraction of XL's raw native capability. Sadly, too many resort to the all too familiar world of VBA too quickly.

Ignoring all the stuff about page numbers, suppose you have 65,000 entries in column A of Sheet1. Then, in Sheet2, select a range of 1,000 rows x 65 columns. Type the formula =INDIRECT("'Sheet1'!R"&((COLUMN()-1)*1000+MOD(ROW()-1,1000)+1)&"C1",FALSE) and complete data entry with CTRL+ENTER

Note that this is not an array formula, so do not use CTRL+SHIFT+ENTER.

On my computer this took the blink of an eye to complete.

parry said:

{snip}

On another forum an op asked to transfer data from one column into a matrix of 55 rows/9 columns per page for printing purposes. The eventual answer also included a page header as well. eg Page 1 of X in row 1 followed by 55 rows of data then Page 2 of X in row 57 etc. The matrix also has to read down 55 rows then onto the next column per page block.
{snip}

Click to expand...

Thanks Tushar, your quite right but I dont know a formula that could do it. Its not quite what the op wants though & because I dont understand your formula Im not sure how to amend it.

The op wants to get as many pages of 55 rows x 9 columns from source data in column A. To avoid errors of referencing cells off the sheet then lets say 7223 rows x 9 columns which is 65007 cells - close enough.

Also, your formula has the values listed all the way down the row until the bottom. He actually wants it to go down until row 55 then begin with the next value in the next column etc until at the bottom of column 9 it begins again.

Because of the constraints of the HTML maker I wont put all that but using an example of 5 rows x 3 columns then this is what it would look like. The names will not be filename1 etc.

Again, leverage the power of XL. Very few use more than a tiny fraction of XL's raw native capability. Sadly, too many resort to the all too familiar world of VBA too quickly.

Click to expand...

Yes, of course You got a good point

The lack of knowledge of the built-in capacity is usually compensated with VBA.

However, from my point of view different problems need different solutions.

Setting up a table with 2000 VLOOKUP-formulas is not a good solution or using array-formulas in a 20x20 matris.

Many good and highly respected XL-users tend to use formulas/functions in an exaggerated way as well due to the lack of VBA-knowledge.

Four mainapproaches exist:
* Use the built-in tools like Pivottable etc.
* Use Formulas/Functions
* Use VBA
* Use other programming-languages to access XL, like VB/VB.Net /C# etc.

For professional solutions we usually use a mix of the above approaches.

Another point is that nowdays more and more users just want to have the solutions and then move on, ie quick & dirty solutions. No attention is paid about performance, efficiency or stability. Here we can strongly argue about the above. However, since the life-cycles of solutions tend to decrease the need to considerate these aspects are not worth the money, i e the payoff-time for an investment is too short.

Kind regards,
Dennis

I don't know your math background, but the general rule for mapping a vector into a MxN matrix columnwise is that each matrix element (i,j) is given by the vector element (j-1)*M +((i-1) mod M+1), which can be simplified to (j-1)*M+i. That means that my original formula was overly complicated since it could be simplified to =INDIRECT("'Sheet1'!R"&((COLUMN()-1)*1000+ROW())&"C1",FALSE)

In the specific case of some number of MxN blocks, the rule becomes (j-1)*M+((i-1) mod M +1) + ((i-1) div M) * (M*N) Note that the first piece (j-1)*M+((i-1) mod M +1) is the same as that for creating a single block. So, select 7281 rows x 9 columns and enter the formula =INDIRECT("'Sheet1'!R"&((COLUMN()-1)*55+MOD((ROW()-1),55)+1+(INT((ROW()-1)/55)*(55*9)))&"C1",FALSE)

parry said:

Thanks Tushar, your quite right but I dont know a formula that could do it. Its not quite what the op wants though & because I dont understand your formula Im not sure how to amend it.

The op wants to get as many pages of 55 rows x 9 columns from source data in column A. To avoid errors of referencing cells off the sheet then lets say 7223 rows x 9 columns which is 65007 cells - close enough.

Also, your formula has the values listed all the way down the row until the bottom. He actually wants it to go down until row 55 then begin with the next value in the next column etc until at the bottom of column 9 it begins again.
{snip}

Click to expand...

Hi Dennis,

XL-Dennis said:

{snip}
Yes, of course You got a good point

The lack of knowledge of the built-in capacity is usually compensated with VBA.

However, from my point of view different problems need different solutions.

Setting up a table with 2000 VLOOKUP-formulas is not a good solution or using array-formulas in a 20x20 matris.

Many good and highly respected XL-users tend to use formulas/functions in an exaggerated way as well due to the lack of VBA-knowledge.
{snip}

Click to expand...

Without a doubt. One needs a balance between the many approaches available. My bias has always been towards those that make the intent the most transparent, with machine efficiency becoming crucial only in limited cases. So, I do advocate the use of UDFs / macros when XL-native formulas become overly burdensome. IMO, many who hang out in the microsoft.public.excel.worksheet.functions group tend to overuse XL formulas, especially array formulas. Similarly, most who hang out here tend to overuse VBA.

XL-Dennis said:

{snip}
Another point is that nowdays more and more users just want to have the solutions and then move on, ie quick & dirty solutions. No attention is paid about performance, efficiency or stability. Here we can strongly argue about the above. However, since the life-cycles of solutions tend to decrease the need to considerate these aspects are not worth the money, i e the payoff-time for an investment is too short.

Click to expand...

This is not a new phenomenon. Almost every manager I've dealt with in 20+ years knows that her/his review/bonus depends on *this* project *now*, not what happens to the project in the future. Consequently, no project is ever designed with maintenance in mind. Obviously, no one will say so, and the rationalization is that the project is 'short term.' Every one knows that that is not the case, but no better incentive system exists. Among the many projects I could discuss, here's one that comes to mind: In 1988 I was a consultant to this particular project team. I insisted that the project integrate Y2K considerations. The project manager swore on everything holy that that was a waste of resources because the project was designed to last no more than 5 years. I bailed out of the project and, subsequently, moved on to other endeavors. Talking with a friend a couple of years ago, I learnt that in 1999 the company made a multi-million acquisition of a new system that duplicated the functionality of the '5 year' project but was Y2K compatible!

Hi Tusharm,

I believe that the local terms of management around the world have becomed more similar nowdays then in the past and therefore there exist a "timelag" between our experiences

I´m not total convinced that the US-managementsystem fits in everywhere but it something we all have to accept and live with.

Kind regards,
Dennis

tusharm said:

I don't know your math background, but the general rule for mapping a vector into a MxN matrix columnwise is that each matrix element (i,j) is given by the vector element (j-1)*M +((i-1) mod M+1), which can be simplified to (j-1)*M+i. That means that my original formula was overly complicated since it could be simplified to =INDIRECT("'Sheet1'!R"&((COLUMN()-1)*1000+ROW())&"C1",FALSE)

In the specific case of some number of MxN blocks, the rule becomes (j-1)*M+((i-1) mod M +1) + ((i-1) div M) * (M*N) Note that the first piece (j-1)*M+((i-1) mod M +1) is the same as that for creating a single block. So, select 7281 rows x 9 columns and enter the formula =INDIRECT("'Sheet1'!R"&((COLUMN()-1)*55+MOD((ROW()-1),55)+1+(INT((ROW()-1)/55)*(55*9)))&"C1",FALSE)

Click to expand...

Thanks Tushar, at first I was confused by the Sheet1!R piece which appears to be looking in Column R but on reading help on the Indirect function I see you are using an R1C1 reference.

Given your comments then could this formula not be shortened from...
(j-1)*M+((i-1) mod M +1) + ((i-1) div M) * (M*N)
to
(j-1)*M+i + ((i-1) div M) * (M*N)

It appears not because when I tried the shortened version I got an error on the 'False' argument...
=INDIRECT("'Sheet1'!R"&((COLUMN()-1)*55+ROW())+(INT((ROW()-1)/55)*(55*9)))&"C1",FALSE)

No, the formula can be shortened only in the instance when there is a single block of M rows.

Edit: And, that is because for a single block the largest value of i -- which is the same as ROW() -- will be M, so (i-1) mod M will always equal i-1. Hence, (i-1) mod M + 1 will always equal (i-1)+1 or i.

That is not true when i can be > M.

parry said:

{snip}

Thanks Tushar, at first I was confused by the Sheet1!R piece which appears to be looking in Column R but on reading help on the Indirect function I see you are using an R1C1 reference.

Given your comments then could this formula not be shortened from...
(j-1)*M+((i-1) mod M +1) + ((i-1) div M) * (M*N)
to
(j-1)*M+i + ((i-1) div M) * (M*N)

It appears not because when I tried the shortened version I got an error on the 'False' argument...
=INDIRECT("'Sheet1'!R"&((COLUMN()-1)*55+ROW())+(INT((ROW()-1)/55)*(55*9)))&"C1",FALSE)

Click to expand...

Thanks again Tushar, bear with me once more please just so I am crystal clear on this.

Single MxN Matrix
(j-1)*M +((i-1) mod M+1) OR (j-1)*M+i

Repeated MxN Matrix
(j-1)*M+((i-1) mod M +1) + ((i-1) div M) * (M*N)

Where…
i = Source Row Number - i.e. Row()
j = Source Column Number - i.e. Column()
M = Number of rows in Matrix ( y axis for the Matrix )
N = Number of columns in the Matrix (x axis for the Matrix)

To convert this into a formula expression so its easier to understand...
Formula Expression of Single Matrix
INDIRECT(“R” & ((Column()-1)*M+Row())&”C”&Y,False)

Formula Expression of Repeated Matrix
=INDIRECT("R"&((COLUMN()-1)*M+MOD((ROW()-1),M)+1+(INT((ROW()-1)/M)*(M*N)))&"C”&Y,FALSE)

Where…
M = Number of rows in Matrix
Y = Number of columns in the source.
N = Number of columns in the Matrix

So I only need to change these arguments – correct?

cheers

Hi guys and especially parry

I found the URL i was looking for in which Bob Umlas discuss arrays:

[url]http://www.emailoffice.com/excel/arrays-bobumlas.html
[/url]

Kind regards,
Dennis