How to conditionally list a subset of data based on specific values? [SOLVED]

[MichaelO]

In a table, there are 30 columns, one of which is used as a "flag" to determine if data from that record should be included in a report on another tab. The flag will be Y/N in all cases. The report will include about 15 of the source table's 30 columns, and will be a straight copy (i.e. no calculations done) in all cases.


Today, I have been sorting by the flag, moving all the Y's to the new tab, removing the unwanted columns, and reorganizing them to match report specs. Is there a way of creating the report I need with less manual effort?


Every week when I do this, I think, "There has got to be a better way." I'm hoping someone here can point me in the right direction. Any help is appreciated, thank you.

 

[Sajan]

Hello Michael,

Have you considered a Pivot table for your needs? It sounds like it might do what you are looking for.

(A Pivot table would allow you to apply filters, such as "show only the Y values".)


Cheers,

Sajan.

 

[MichaelO]

Sajan, thank you for your response.


Yes, I have tried using a pivot, for exactly the reason you mention: the ease of filtering. Unfortunately, the pivot keeps trying to apply mathematical functions to the field values, such as counting non-numeric values of each record. I'd like a straight copy, without any calculations on the values at all.


If it will help, I posted a sanitized version of the file online. It's the first time I've done that, so please let me know if you have any challenges accessing the file. This is an Excel 2010 file.


https://docs.google.com/file/d/0B8mzv0t9vCLSZ0QyMjFhYmNWX0U/edit?usp=sharing


The indicator that determines if the record is to be included is in column AH, "On PSCP?" My apologies, I am reminded it uses "Yes/No" rather than "Y/N", if that makes a difference.

 

[Sajan]

Hi Michael,

The computer I am on does not have access to file sharing sites... As such, hopefully, the following instructions are useful to you.


1. Add a new Pivot table for your data range.


2. In the Pivot Table, select the "Flag" column as your Report Filter.


3. Then, add the desired columns as "Row Labels"


4. For each item in "Row Labels" go into the "Field Settings" screen, and set the "Show Item Labels in Tabular form" radio button in the "Layout and Print" tab.


5. Leave the "Column Labels" and "Values" sections empty.


That should give you something that looks like a table.


Hope this helps.


Cheers,

Sajan.

 

[SirJB7]

Hi, MichaelO!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/How%20to%20conditionally%20list%20a%20subset%20of%20data%20based%20on%20specific%20values_%20-%20Chandoo%20Sample%20%28for%20MichaelO%20at%20chandoo.org%29.xlsx


It uses 2 additional worksheets:


ColumnSelection: for selecting columns to be included, use 2 values that won't exist in your actual data (in the example, Y for yes and N for no, yes, I had to think a lot but I succeeded)

Filtered data: the output required data, format properly each column upon it actual contents


It uses 4 dynamic named ranges as follows:


ProjectDataTable: =DESREF(ProjectData!$A$2;;;CONTARA(ProjectData!$A:$A)-1;CONTARA(ProjectData!$1:$1)) -----> in english: =OFFSET(ProjectData!$A$2,,,COUNTA(ProjectData!$A:$A)-1,COUNTA(ProjectData!$1:$1))


ProjectDataTitles: =DESREF(ProjectDataTable;-1;;1 -----> in english: =OFFSET(ProjectDataTable,-1,,1,)


ProjectDataFilterCountList: =DESREF(ProjectDataTable;;40;;1) -----> in english: =OFFSET(ProjectDataTable,,40,,1)


ColumnSelectionCountList: =DESREF(ColumnSelection!$A$3;;;1;COLUMNAS(ProjectDataTable)) -----> in english: =OFFSET(ColumnSelection!$A$3,,,1,COLUMNS(ProjectDataTable))


It uses an additional helper column at the end of the data on worksheet ProjectData with this formulas:

AO1: PSCP Count (string value)

AO2: =CONTAR.SI(AH$2:AH2;"Yes") -----> in english: =COUNTIF(AH$2:AH2,"Yes")

Copy down AO2 as required.


Just relax, take it easy, it doesn't use anything else


Now in worksheet FilteredData just 2 formulas, one for titles and another for data:


A1: =SI.ERROR(INDICE(ProjectDataTitles;1;COINCIDIR(COLUMNA();ColumnSelectionCountList;0));"") -----> in english: =IFERROR(INDEX(ProjectDataTitles,1,MATCH(COLUMN(),ColumnSelectionCountList,0)),"")


A2: =SI.ERROR(INDICE(ProjectDataTable;COINCIDIR(FILA()-1;ProjectDataFilterCountList;0);COINCIDIR(COLUMNA();ColumnSelectionCountList;0));"") -----> in english: =IFERROR(INDEX(ProjectDataTable,MATCH(ROW()-1,ProjectDataFilterCountList,0),MATCH(COLUMN(),ColumnSelectionCountList,0)),"")


Copy across A1 as required, copy A2 down and across as required. In the sample file I used columns up to AZ (for both worksheets ColumnSelection and FilteredData) and rows up to 5 (for FilteredData), orange shaded.


Just advise if any issue.


Regards!

 

[MichaelO]

Thank you both for your very educational help. My apologies for the delay in getting back to you.


Sajan, as much as I play with Pivots, I had never used that function (show in tabular form) before. That is a wonderful feature that will make quite a bit of my work easier.


SirJB7, this idea interests me greatly, and I'll be exploring that today and tomorrow. It looks like that will take more time to implement.


thank you!

 

[MichaelO]

SirJB7, there's a lot there to think about. Applied your steps in the larger spreadsheet and it looks very nice. Thank you! Now I have two different but very effective ways of saving time on report creation


thank you,

MichaelO

 

[SirJB7]

Hi, MichaelO!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!