Results 1 to 7 of 7

Thread: fill rows

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    fill rows

    Hi All,

    A separate thread named "find which cell to use" asked for suggestions on how to find the next non-blank cell in a column (if the next row is blank in that col, the entire row is blank) so that its contents could be used in a calculation for the current row.

    The way to do that is to delete any blank rows so that the current cell's reference to the next row is always valid. That is usually done by VBA (thanks RG). However I didn't want to use VBA for this project - at least not for that if there was a way around it.

    So necessity being the mother of invention, I came up with a way of doing the same thing. That is, create a new sheet with only those rows with a "Y" in an "Include" col. This actually is more in line with the way this spreadsheet will be used anyway, so the approach is perfect for my purposes.

    I've attached a sample spreadsheet showing the approach if it's of use to anyone (given all the help I've gotten on this project).

    Instead of deleting rows on the "Data Base" sheet, I've created a new sheet called "Selected Rows" which only has those rows with a "Y" in the "Include" col C of the "Data Base" sheet. The value in col D of the "Data Base sheet provides a value that's used in the RANK function in cols A and B of the "Selected Rows" sheet. It takes advantage of the fact that RANK will assign the same ranking value to all entries with the same value. So as you go down the rows of the "Selected Rows" sheet, the formula in A2 will look for a ranking in col D of the "Data Base" sheet that has a ranking of 1, 2, 3, etc. The formula uses ROW()-1 (-1 bcs there is 1 header row in "Selected Rows" so row 2 is filled with the information that had a Search value of 1, row 3 is filled with the info that had a Search value of 2, etc). Similarly for col B of the "Selected Rows" sheet.

    The #N/A errors come about bcs, for this quick sample, I didn't embed the OFFSET in an IF(test,"",OFFSET(...)) statement. Since there are only 3 "Y"s to be included from the "Data Base" sheet, an attempt to include a 4th row will fail.

    Any suggestions welcome for improving this.

    Thanks.

    Fred
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am not a proponent of trying to maintain multiple copies of the same data. Autofilter can achieve essentially the same results. Also the formula in D2:
    =COUNTIF($C$2:C2,"Y")

    Yields the same results more efficiently and does not require an explicitly enterred Array-based formula. The SUBTOTAL function can be used with the autofilter (if desired) to do calculations (averages, counts, min, max, stdev, etc) on the filtered data excluding the data that is filtered out.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Steve,

    While I agree with you about maintaining multiple copies of data, this is for someone who barely knows Excel. So I'm not sure that Autofilter is an option. Could create a button that creates the filtered results (actually 3 versions of filtered results are needed) but I also didn't want to create any VBA code (this is that project for the MAC user that you and I exchanged posts on a different thread).

    As to the COUNTIF, you're right. I had adapted the search from something I had for finding the n-th occurrence of a char in a string. Definitely overkill for this. That wasn't the "useful" part of this exercise anyway; it was using the RANK function to fill the 2nd sheet without blank rows (autofilter aside). Will change to COUNTIF.

    Thanks for the comments.

    Fred

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Steve,

    Another thought crossed my mind about copying the data rather than filtering.

    You may recall a thread I started called Compare Groups that you helped on. That has evolved since that thread. A key component of that work was figuring out how many people in one class were also in the class that followed it. That was calculated by comparing the 2 rosters for the classes in consecuitve rows.

    In the attachment to my post starting this thread (a very simplified version of the attachment in the Compare Groups thread), the Data Base sheet would also have a column for the number of people in one class who were also in the following class. That is still needed.

    But once you filter on the Include field (col C), you need to recompute the value of people in one class who are also in the next class but with both classes having a Y in the Include field. So if the class following a class in the Data Base sheet is NOT included, then the value of # People in 1 class also in the next class is no longer correct.

    I'd welcome any thoughts on how to have the correct value with Filtering.

    Fred

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    While I agree with you about maintaining multiple copies of data, this is for someone who barely knows Excel.
    IMO, All the more reason not to create redundant lists

    So I'm not sure that Autofilter is an option.
    IMO, it seems the preferred option. It is easy to maintain, it is built-in feature, it is intuitive to people with little excel skills...

    Could create a button that creates the filtered results (actually 3 versions of filtered results are needed) but I also didn't want to create any VBA code (this is that project for the MAC user that you and I exchanged posts on a different thread).
    All the more reason to use a built-in feature. Filtered lists are easy to maintain, you could have dozens without too much issues. If you don't want to have them create the filtered list from multiple columns, you can create a formula to allow the creation of the list from 1 particular list.

    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I'd welcome any thoughts on how to have the correct value with Filtering.
    Perhaps I am a little obtuse, but I don't see how the 2 questions relate (filtered lists and comparing groups). Perhaps an example combining both aspects to detail the problem would be helpful to me. Focus on what you have and what the goal is, not on how you think it needs to be done: I may have alternative means to accomplish it. If you are particular in how you want to solve the problem, I can only work with that and it may ignore the true goal.

    Steve

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    The application is for a dance school.

    To prepare for the final recitals in June, they put together an ordering of classes for the rehearsal (1 row per class) in a Rehearsal spreadsheet. Even during the rehearsal, the time for students to change costumes between numbers is important. So it becomes important to know if successive numbers have the same students (hence the comparing groups problem that I started with in a different thread). Think of this as the Data Base sheet in the attachment in this thread.

    But not every class participates in every recital (there are 3 of them spread over a weekend) when the parents, grandparents, etc. come to see their kids perform. There are various criteria the school uses to decide which classes go into which Recital but that's not important for me (at least not yet). So the Data Base has a col for each recital where the user marks whether that class (a row in the Data Base/Rehearsal sheet) is to be included in each of the 3 recitals - Y or N.

    The school needs to be able to not only see which classes are in a recital (yes, a filtered list) but again needs to see how many people are in successive classes for the changing time. This info in the Rehearsal sheet is of no use since classes may have been omitted for a particular recital. So filtering the original Data Base/Rehearsal sheet is no good for this very reason - as far as I can tell. Hence the need for a separate sheet that I called "Selected Rows" (think of this as the Recital list - and I have 3 sheets, one for each Recital).

    As was explained to me, if it takes even just one minute to change costumes - you have one minute of dead time between numbers in the recital. With 50-60 classes in a recital, this is adding one hour to the length of a 3-4 hour recital if just adding the dance time for each class. If a class coming on has no one in the previous class, you can have this next class waiting in the wings until the previous class ends. As that previous class is going out stage right, the next class is coming on stage left. Almost 0 dead time.

    Another piece of information that is "dynamic" (can change as you put together a recital ordering by omitting classes from the reheasal) is the class type: tap, ballet, jazz, hip-hop, etc. If the rehearsal has 2 classes of tap followed by a jazz class followed by a tap class, no problem. But they want to avoid 3 of the same class type in a roww (becomes boring to audience). In putting together the recital ordering, suppose the jazz class is omitted for whatever reason. Now the recital has 3 tap classes in a row - BORING! I've used Conditional Formatting to highlight the 3rd class if it makes 3 of the same Class Type in a row. In the Rehearsal, no highlighting would take place in this case bcs of the jazz class. But in the Recital, with 3 tap classes selected, Conditional Formatting comes into play to indicate 3 tap classes in a row.

    So I'd like to understand how the filtering would highlight the 3rd tap class (or all 3 tap classes) in a filtered list.

    Autofilter:
    it is built-in feature, it is intuitive to people with little excel skills
    I'm not going to count on that as far as this user goes (and I've already spoken to them). Autofilter was never in the beginning Excel classes I've seen/taught; always in the next (Intermediate?) class. The people using this are dance instructors.

    If you don't want to have them create the filtered list from multiple columns, you can create a formula to allow the creation of the list from 1 particular list.
    Not sure what you're suggesting in terms of a formula. Would like to understand this better - particularly on the key issue of the Number of People in Following Number. If it wasn't for this, I would probably agree with you 110% - you'd be filtering rows with static information (class name).

    I hope this clarifies things.

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •