• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fetch data based on rows' number

P

Pavel S

New Member
Hi,


What I have:

Two tables. The first one (Table 1) is the origin of data: from left to right it is monthly budgets, three columns per month; from top to bottom it is a list of unique items. Columns have unique titles.

[pre]
Code: 
Like this:
Items   Jan1 Jan2 Jan3 Feb1 Feb2 Feb3 ...
-----------------------------------------
Item1
Item2
Item3
...

The second table (Table 2) should be filled from the first one and is organized as such: from left to right it has one column per month, corresponding to the first column of the three monlthy ones from the first table; from top to bottom it has a list of groups of items from the first table.


Like this:
Groups   Jan1 Feb1 Mar1 ...
---------------------------
Group1
Group2
...
where Group1 consists of Items 1, 5, 7, etc
[/pre]
What I want:

Basically, each cell of the second table should contain a sum for a given group items for a given month. Normally this is done by adding a table with relation group->items and using SUMIFS. Unfortunately the spreadsheet is locked for changes and the only editable cells are those in the mentioned tables. No pivot, no additional columns, no new sheets etc.


At the time, this problem occured, I simply linked the cells manually as the second table is relatively small -- only 6 rows. But still, 6 rows X 12 months and each cell from the table 1 should be fit into. I'd like to have a more elegant solution.


What I tryed:

1. A new workbook. Link to data in the Table 1 - aggregate -- link back to the Table 2. Works. Not good -- I need to keep an eye on another file.

2. An approach with indirect referencing and using array functions. {=SUM(INDIRECT("R"&{21,23,27}&"C"&{15,15,15},FALSE))} sums only the first of the three needed cells.


Any thoughts?


Regards,

Pavel


PS Chandoo, post form needs more info on markup syntax. {[<?
 
Is there a consistent logic as to what items go into each group?

Implicitly, yes. There is a fixed group->items relation.
 
If I wrap SUM(INDIRECT("R"&{21,23,27}&"C"&{15,15,15},FALSE)) into SUMPRODUCT, the formula sums up all three. But I still have to manually enter column coordinates.
 
Hmm. Not sure then how to limit the function to only the rows you want. If it's all just manual, then best I can do is:

=SUMPRODUCT(INDEX($B$2:$E$8,,(COLUMN(A$1)-1)*3+1)*($A$2:$A$8={"Item1","Item5","Item7"}))


Where B2:E8 is the table of numbers, A2:A8 is the list of names. You then have to manually type which Items you want. Advantage is that formula can be copied to the right and it will automatically look at every 3rd column.
 
Thanks. Cannot test it now. Is it possible to change the second condition into something like ROW ($A$2:$A$8)={1,3,5} or somehow fix row numbers? Item names are too long for formula.


BTW do you know how can I get rid of SUM in SUMPRODUCT(SUM(INDIRECT("R"&{1,3,5}&"C"&((COLUMN(A$1)-1)*3+1)*{1,1,1}),FALSE)))? It would give another solution.
 
Luke,


Your formula produces #N/A, as Index part and item part are arrays of different dimentions.

I had to amend it into =SUMPRODUCT(INDEX($B$2:$E$8;;(COLUMN(A$1)-1)*3+1)*(($A$2:$A$8={"Item1"})+($A$2:$A$8={"Item4"})+($A$2:$A$8={"Item7"})))


Thank you for participation.


Meanwhile my solution worked with slight amendment =SUMPRODUCT(SUM(INDIRECT("R"&{2;3;5}&"C"&(((COLUMN(A$1)-1)*3+2)*{1;1;1});FALSE))). I still do not get necessity of SUM here.
 
Glad to hear you got it working. To answer your earlier question, yes, you could use the ROW function in a similar manner. The reason my formula failed was that our regional settings are different (I separate arguments with a comma, yours uses semicolon.) Try this:

=SUMPRODUCT(INDEX($B$2:$E$8;;(COLUMN(A$1)-1)*3+1)*(ROW($A$2:$A$8)={1;3;5}))


The different between a comma and a semicolon when building an array determines whether we are using a new row, or a new column.
 
Uhhm, I think it is not that easy. If you look closely, I changed all commas into semicolons before amending it. BTW in Russian locale it is colon that separates rows.


When I posted last time I just forgot to change delimeters back.


What I get from F9 with your formula:

=SUMPRODUCT(INDEX($B$2:$E$8;;(COLUMN(A$1)-1)*3+1)*($A$2:$A$8={"Item1";"Item5";"Item7"}))

=SUMPRODUCT(INDEX($B$2:$E$8;;{1})*({TRUE;FALSE;FALSE;#N/A;#N/A;#N/A;#N/A}))

=SUMPRODUCT({4;4;2;4;6;8;10}*({TRUE;FALSE;FALSE;#N/A;#N/A;#N/A;#N/A}))

#N/A


I think it has smth to do with array dimentions. Does the formula work OK at your location?
 
Correct, this formula:

=SUMPRODUCT(INDEX($B$2:$E$8,,(COLUMN(A$1)-1)*3+1)*(ROW($A$2:$A$8)={2,6,8}))


on my machine is working correctly. F9 the last bit gives this:

=SUMPRODUCT(INDEX($B$2:$E$8,,(COLUMN(A$1)-1)*3+1)*{TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,TRUE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,TRUE})


Just the first part looks like this:

=SUMPRODUCT({4;6;8;10;12;14;16}*(ROW($A$2:$A$8)={2,6,8}))


So, when multipled together:

=SUMPRODUCT({4,0,0;0,0,0;0,0,0;0,0,0;0,12,0;0,0,0;0,0,16})

=32


Note that I was just using a dummy formula of

=ROW()*COLUMN()

to generate some data.


Perhaps we just need to fiddle with the separators somehow...
 
Yep, I was checking it and got it working! The correct separator is a backslash! While at work it is a colon %-) Sorry, my bad


For the record, here is a recipe to check correct delimeters:


In VBA IDE go to the immediate window and execute one by one

[pre]
Code: 
?Application.International(xlColumnSeparator)
?Application.International(xlRowSeparator)
[/pre]

So, thanks again Luke
 
Hurrah, glad you got it figured out. Was getting frustrated that we had a solution that I knew would work, but wasn't!

Thanks for the tip on checking what the separators are for international versions.
 
You must log in or register to reply here.
Back
Top