Consolidate 30 arrays

[BRISBANEBOB]

I have up to 30 arrays of data. The 'x' consists of the same 8 products but the 'y' - the customers - can be up to 20 from a customer list of a couple of hundred. I've tried =sumproduct, =sumif, =index but not getting anywhere.

Any help appreciated - sample of data attached.

[Pete_UK]

In Sheet2, list A to G in cells B6:B12, then you can put this formula in cell C5:

=INDEX(Sheet1!$1:$292,5+(ROWS($1:1)-1)+INT((COLUMN()-3)/20)*9,3+MOD(COLUMN()-3,20))

Copy this down to C12, then copy C5:C12 across as far as you need to.

Hope this helps.

Pete

[BRISBANEBOB]

Hi Pete

I've followed your instructions but not getting the result I expected. I've attached the spreadsheet for further clarification if you's be so good.

[Pete_UK]

The formula you used wasn't exactly what I posted. I've applied the formula in Sheet2 of the attached file, and copied it across a few columns. In Sheet3 I've applied a different formula, which transposes the data so that you have the products going across and the customer numbers going down - just copy the row of formulae down as far as you need to (until you get #REF errors).

Hope this helps.

Pete

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED. Also, you can show your appreciation by clicking the "star" icon.

[BRISBANEBOB]

Pete - I am awestruck! I have added to your kudos, and I am very appreciative of the time you have spent on it. Any chance you could give me a clue as to how the formula works?

[Pete_UK]

I will do later on, but I'm just about to go out now.

Pete

[Pete_UK]

Hi Bob,

I said I would return with an explanation, so here goes:

The formula is of the type:

=INDEX(range, row, column)

which will return the value from one cell in the range corresponding to the values of the row and column parameters. You can see that the range is defined as:

Sheet1!$1:$292

which covers all your data on Sheet1. Bear in mind that the data actually starts on row 5 and in column 3 in Sheet1, and that you have 9 rows between identically-formatted tables. The column parameter is given by this term:

3+MOD(COLUMN()-3,20))

and as the formula is in column C, the initial value of COLUMN() is 3. However, as this is copied across, so that term will result in 4, 5, 6 etc. in subsequent columns. However, 3 is subtracted from that, giving values of 0, 1, 2, 3 etc. in successive columns, and those values are inside a MOD( ... ,20) function, which will give the remainder after dividing by 20, i.e. 0 to 19 then 0 to 19 again etc. in the columns to the right. However, your data starts in column 3 of Sheet1, so that is added on and the result of the column expression is to return the values 3, 4, 5, 6 etc. up to 22 (i.e. your column V), and then to reset itself and return the same sequence of numbers as the formula is copied further to the right.

The row expression is given by the following:

5+(ROWS($1:1)-1)+INT((COLUMN()-3)/20)*9

Remember that this is the row in Sheet1 where we want to get the data from (and it starts on row 5). The ROWS($1:1) term will initially return 1, but as the formula is copied down this will return 2, 3, 4 etc. on successive rows, and we add 5 onto this because that is the start row of your data - so, for now, this will return the values 5, 6, 7 etc. However, there is another term which is added on to this which is dependent on the column in which the formula resides, i.e.:

INT((COLUMN()-3)/20)*9

COLUMN() will initially return 3, but as 3 is subtracted then this will return the numbers 0,1,2,3 etc. in subsequent columns to the right, as discussed above. BUT, we take the integer value of this after it has been divided by 20, so that results in a series of twenty zeroes, followed by twenty ones, then twenty twos etc. as the formula is copied across. This in turn is multiplied by 9, which is the number of rows between your different tables on Sheet1. So this term will have no effect on the values of the row discussed above for the first 20 columns, but then it will add 9 onto the next twenty values to the right, and then it will add 18 onto the next 20 values, and so on. Thus the row that we want to get the data from will be 5, 6, 7 etc. on successive rows for the first 20 columns that the formula is copied across to, and then it will be 14, 15, 16 etc. for the next 20 columns, and so on.

Thus, we will get the data from your table in the sequence expected.

Note that the formula in Sheet 3 has different expressions for the row and column parameters. Maybe with the above explanation in mind, you might be able to explain to yourself how those work.

Hope this helps.

Pete

[BRISBANEBOB]

Hi Pete.

Again my thanks. I am at work at the moment and will work my way through your explanantion. Much appreciated.

Kind regards

[BRISBANEBOB]

Hi Pete

Is it possible to produce 1 array out of the thirty, with every customer number who purchased along the top, and with the total of the products purchased by each of those customers against the product values (A - G)? For instance, customer 195 may have purchased products A, B, C, E, and G, and the total purchases for each of those product lines summed below his number? The total of all the sales in all thirty arrays would be in the one, consolidated array.

[Pete_UK]

Do you mean like in Sheet4 of the attached file?

Hope this helps.

Pete

[BRISBANEBOB]

Is there anything you can't do in Excel! Thanks - I presume the formula works as per the others? i will discect them when I get home tonight.

The problem with Excel is the more you learn you realise the less you know...thanks again.

[BRISBANEBOB]

Hi Pete

I am trying (and am failing!)to use the forumula to also sum different arrays. I've included a sample in sheet 4. How do I modify the formula to do that? Or is it a different formula?

[Pete_UK]

... I presume the formula works as per the others? ...

The formula is of the type:

=SUM(INDEX(...):INDEX(...))

where the expressions within the INDEX parts are similar to those described above. However, INDEX can be used in two ways - the first use allows it to return a value from a cell, but in this formula it returns the address of the cell, so that the two INDEX functions used with a colon will return the cell references of the range to be summed. Note that I have left the ROWS($1:2) and ROWS($1:8) terms in there so that you can more easily relate the formula to the previous description, but these could be replaced by numbers, as the formula does not get copied down rows.

As for your other request, I'm not sure what you are trying to do - please elaborate (or start another thread if it is a different topic).

Hope this helps.

Pete

[BRISBANEBOB]

HI Pete

I get about twenty or thirty arrays where the customer numbers are the header on the top row and the products (A - G) on the left most column. The value of the sale is in the array. So customer A bought $256 or product A, $456 of product B, $267 of product F, etc. On sheet 4 I have set out a couple of sample arrays and the top array is the summing of the lower arrays. The top array has all the customers who bought over the period and the total value of the products they bought. It is the sum of all the lower arrays.

I hope I'm making sense.

[Pete_UK]

Ah, right - I see what you are trying to do. I'm just about to go out now, but I'll take a more-detailed look later on. I'll probably set it up in Sheet5, or even a new workbook, as it is a different approach to what has gone before. Would you like to see that cumulative table in the same sheet as the data (as you have shown with the sample), or in a different sheet?

Pete

[BRISBANEBOB]

HI Pete


Seeing the answer on the same sheet is probably useful.

I have been trying an =index approach and I'm sure that must be part of the answer but I still can't get it.

[Pete_UK]

In your data in Sheet1, the tables extend to 20 columns, but in your sample data they are only 4 columns wide. How many columns are you likely to encounter with your real data?

Pete

[BRISBANEBOB]

Hi Pete

Any thing up to 25 columns with up to eight products but usually less.

[Pete_UK]

Hi Bob,

sorry for the delay - out again visiting !!

I've copied your latest sample data into Sheet5 of the attached workbook, and put this formula in cell D6:

=SUMPRODUCT(($D$15:$G$42=D$5)*(MOD(ROW(15:42)-14-ROWS($1:1),10)=0),$D16:$G43)

I've also applied a custom format to the cell of 0;; - this has the effect of hiding zeros. The formula is then copied across to I6, and then those formulae copied down to row 12 to give the summary table (pale blue cells). I hope you can relate the cell references and numbers used in the formula to the sample data cells, and thus apply the formula to your real data.

The formula assumes that the product codes are always listed and in the same order in each smaller table, and that you list the customer numbers across row 5.

Surely this is the end of this thread now !!

Hope this helps.

Pete

[BRISBANEBOB]

Very close to the end!

I have enjoyed very much experimenting with the formula.

Purely as an exercie in logic (assuming you need any more mental exercise!), assume you had new customers whose name (or number as we have been using in this case) you did not have when you started the consolidation, and you need to populate the top row of the consolidation formulaically. How would you do it? I can do it with a cumbersome =countif formula but is there a 'better' way?

[Pete_UK]

The following article:

http://www.get-digital-help.com/2009...om-one-column/

shows how you can extract a list of unique values from a column that contains duplicates and blanks using a formula - perhaps you can adapt this to your situation.

Hope this helps.

Pete

[BRISBANEBOB]

Thanks for your assistance.