• 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.

Pivot without using pivots

edbyford

New Member
Hi all,


I'm building a dashboard which needs to update every week. I want to build a nice, pretty table which pulls in rows of data based on certain criteria.


For example, I might want to show in the dashboard rows where the item is owned by Bill, and is red, and is Critical, but only show the owner, the unique number, and the 'irrelevant data'.


I do not want to pivot this information as I need these tables to be reproducible and copiable, I do not want to set up a pivot table each time. I also need them to look pretty - I can't customise a pivot table as much as I'd like to.


Example here: https://dl.dropbox.com/u/84751033/Example.xlsx
 
If you have more 2000 rows of data. Pivot table is probably your only real robust option.


Try out Advanced Filter, This will get what you need, but is a somewhat manual process
 
Hi edbyford,


I agree with Montrey, Pivt table is a very flexable tool, besides you can look at this file:


http://dl.dropbox.com/u/60644346/Example%20(1).xlsx


Regards,

Faseeh
 
I'm not positive, but is this what you are looking for? See Sheet 2 on file.


http://www.speedyshare.com/zGg4p/Example-with-dashboard.xlsx


If so, just use an array formula..make sure to hit CTRL-SHIFT-ENTER when you enter the formula:


Code:
{=INDEX(Table1[Owner],MATCH(1,(Table1[Owner]=$A$3)*(Table1[Colour]=$B$3)*(Table1[Critical?]=$C$3),0))}
 
Hi edbyford,


I have not fully understood your criteria to pull the data, and moreover I can not provide you the probable solution with an workbook uploaded as I am posting this from my office network:)...So I will try to post my understanding and / or probable solution here only...(thank you for your co-operation in advance:))


So according to your data I am assuming you have two criteria (Color and Critical) based on which you want to fetch data from A3 to E20 and fill the cells under the column of K to N.


If my understanding is right (to some extent), then plz follow the below instructions:


1)At H3 write Red and at I3 write Y


Now, according to my understanding, based on this criteria (Colr = Red, Critical = y) you want to fetch data from you table (Col A to E).


2)Formula at K3:

=IFERROR(INDEX(Table1[Unique Number],SMALL(IF((Table1[Colour]=$H$3)*(Table1[Critical?]=$I$3),ROW(Table1[Colour])-2),ROW(A1))),"") (Press ctrl+shift+enter from keyboard)...not just enter,....drag it down as much as you want.


3)Formula at L3:

=IFERROR(INDEX(Table1[Owner],SMALL(IF((Table1[Colour]=$H$3)*(Table1[Critical?]=$I$3),ROW(Table1[Colour])-2),ROW(B1))),"")(Press ctrl+shift+enter from keyboard)...not just enter,....drag it down as much as you want.


4)Formula at M3:

=IFERROR(INDEX(Table1[Colour],SMALL(IF((Table1[Colour]=$H$3)*(Table1[Critical?]=$I$3),ROW(Table1[Colour])-2),ROW(C1))),"")Press ctrl+shift+enter from keyboard)...not just enter,....drag it down as much as you want.


5)Formula at N3:

=IFERROR(INDEX(Table1[Irrelevant Data],SMALL(IF((Table1[Colour]=$H$3)*(Table1[Critical?]=$I$3),ROW(Table1[Colour])-2),ROW(D1))),"")Press ctrl+shift+enter from keyboard)...not just enter,....drag it down as much as you want.


Now, this will give you all the data points when Color= red and Critical = y


Now if your number of criteria increases; say for e.g., Owner = helen; then we need to adjust the formula at the "logical test" part of IF formula.


Please do the same and let us know if this is what something you are looking for..


Regards,

Kaushik
 
Guys, thank you so much for this. You all gave me a good way to achieve this.


I am glad that I formatted the data as a table as it gives me the human language field names, and makes it easier to understand.


However, my data is not in a table format - it is actually in a normal data range with named ranges to represent the columns of data I am interested in - can I just use the named ranges as opposed to the "Table1[Colour]"?
 
Ok, I tried it and named ranges seem to work too.


@Kaushik, thank you, this worked well for me.


Can someone explain please the reason for the "ROW(Table1[Colour])-2),ROW(D1)" section of his formula? And why the minus 2?
 
It seems to work without changing the ROW(A1) on the end of the formula. - not sure why?


Thanks for your continuing help guys, it'd be great to understand this so I can troubleshoot this in future!
 
I managed to break it! Somehow it is not working, although I can't see how it is different from my formula...


Here is my formula:

{=INDEX(InventoryNum,SMALL(IF((Status=$C$47)*(Category=$D$47)*(Stream=$E$47),ROW(Status)-2),ROW(A1)))}


Here is Kaushik's formula:

{=IFERROR(INDEX(IrrelevantData,SMALL(IF((Colour=G26)*(Critical=H26)*(Owner=F26),ROW(Colour)-2),ROW(A1))),"")}
 
I'm sorry you've reached your maximum limit for help today. just kidding.


try:


Code:
{=IFERROR(INDEX(InventoryNum,SMALL(IF((Status=$C$47)*(Category=$D$47)*(Stream=$E$47),ROW(Status)-2),ROW(A1))),"")}
 
Hi edbyford,


Glad that it worked for you.


The main reason is, since your data starts from A3(leaving two rows above), I have adjusted the rows by subtracting 2 [ROW(Colour)-2].


For deeper understanding regarding how such formula works...you can visit the below website where each and every step of such formulae are very clearly described.


http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/


Please let us know in case of any doubts.


Kaushik
 
Back
Top