none
Custom filtering over grouped rows RRS feed

  • Question

  • Hi All,

    my sheet consists of data records, where each record fills ca 20 grouped (via Data->Outline->Group) rows in which some of the cells are static text (labels), some are actual data, many of them are merged cells. I'd like to employ a custom filter over these non-adjacent cells; would any of the local famed Excel masters know of a sample of such implementation?

    Many thanks,

    Martin

    Wednesday, October 14, 2015 4:39 PM

Answers

  • So this is a bit of a philosophic conversation so sorry.

    If you want to work with data structures, create structures that are useful. What you show is some kind of 'human friendly, data death' construct; the merged columns, splitting data for 'one record' across many rows is just bad data design. It looks pretty but makes analysis a total pita and often impossible. In your case, there would appear to be NO way (*) to just use simple filters to get what you want because your columns are not consistent across the data set.

    If you want dead bang simple, establish one row as one record. It's going to be wide but then build a form to display the results for one record. The form can employ filters to get you to exactly what you want.

    If you want a more technically correct data design, you may need to establish multiple tables and join them just as you would in a normalized database (in fact, Access might be preferred for this task).

    (*) I think to use your current design you'd need to add one or more columns that can be set as flags to say 'use this row' and then figure out a way to set the flag for each of the relevant rows. This would most likely take VBA but then again, if the rows that represent a record contain something you can use as a flag, you might be able to set one field somewhere and have all of the other rows magically get set to display. Not knowing the actual data makes it hard to be more specific. In any case, I'd recommend you go flat with your data and walk away from the current design if at all possible.

    Thursday, October 15, 2015 2:19 PM

All replies

  • Hi Martin,

    >> I'd like to employ a custom filter over these non-adjacent cells
    Did you want vba or excel built-in function to achieve your requirement?

    If you want vba code, could your requirement be achieved manually? If you could, I suggest you manually filter the rows, and record the steps, you could get the code with recording the steps.

    For Filter Object, you could refer the link below:
    # Filter Object (Excel)
    https://msdn.microsoft.com/en-us/library/office/ff836198.aspx

    If you have specific issue about this, please feel free to let us know.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, October 15, 2015 5:33 AM
  • @Edward thanks for your response.

    A picture's worth a thousand words .. so I have prepared one. I tried to reveal as much as possible while keeping any private details ..well, private.:-)

    Please, see below ..

    On the left you can see the groupings. The whole picture presents one record (depicted by the '1' in blue at the bottom left); there'll be dozens of such records in the sheet. I'd like to set up filtering for some (not all!) of the fields of a record, let's say Internal/External, Source Name, Target Name, Data Category, Data Class for starters. I can imagine a panel (saving a few rows by Freeze Panes at the top) where you'd enter (selecting from dropdowns of available vlaues is preferred) values to filter by, then only those records would appear .. 

    If something like that could be achievable without VBA, that would be great; no problem with VBA approach either.

    Any ideas/samples, please?


    Thursday, October 15, 2015 8:03 AM
  • So this is a bit of a philosophic conversation so sorry.

    If you want to work with data structures, create structures that are useful. What you show is some kind of 'human friendly, data death' construct; the merged columns, splitting data for 'one record' across many rows is just bad data design. It looks pretty but makes analysis a total pita and often impossible. In your case, there would appear to be NO way (*) to just use simple filters to get what you want because your columns are not consistent across the data set.

    If you want dead bang simple, establish one row as one record. It's going to be wide but then build a form to display the results for one record. The form can employ filters to get you to exactly what you want.

    If you want a more technically correct data design, you may need to establish multiple tables and join them just as you would in a normalized database (in fact, Access might be preferred for this task).

    (*) I think to use your current design you'd need to add one or more columns that can be set as flags to say 'use this row' and then figure out a way to set the flag for each of the relevant rows. This would most likely take VBA but then again, if the rows that represent a record contain something you can use as a flag, you might be able to set one field somewhere and have all of the other rows magically get set to display. Not knowing the actual data makes it hard to be more specific. In any case, I'd recommend you go flat with your data and walk away from the current design if at all possible.

    Thursday, October 15, 2015 2:19 PM
  • Thanks Dogubob,

    yours was the last straw that made me stop wanting to stick to that desgin.:-) Intuitively I have felt this is not what I had intended, but you know how it goes after you have spent some time evolving what originally seemed as a brilliant idea ...

    However I still want to refrain fro Access as I'd like this to be usable for the broadest audience possible ans I am not sure how many of these people even have Access installed ..

    But what I do like is the idea of going flat completely. In that case the User Data Form might serve as the filtering agent .. can I somehow enforce its being used (have it 'turned on' by default) .. ?

    Friday, October 16, 2015 2:03 PM
  • Yeah, a convert to good data design! ;-)

    I know how it goes...starts out good, gets complex, turns out bad...I only know this from years of beating my face against bad data structures (mine and others). Once I learned to do design backward things got easier; start with 'what do I need out of this system', then work back to 'what goes in'; if you understand the outputs, the inputs become a no brainer.

    I don't use data forms so I can't help, sorry.

    Doug

    Friday, October 16, 2015 2:11 PM
  • Hi Martinigue,

    >> If something like that could be achievable without VBA, that would be great; no problem with VBA approach either

    With this requirement, I agree with Dogubob. We could filter the records which is common rows and cells. We could not filter the rows like your sheet design. I suggest you redesign your sheet and data structure.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, October 19, 2015 7:14 AM