+ Reply to Thread
Results 1 to 9 of 9

Formula to loop and combine days of the week from multiple rows?

  1. #1
    Registered User
    Join Date
    03-06-2014
    Location
    Valparaiso, FL
    MS-Off Ver
    Excel 2013
    Posts
    73

    Formula to loop and combine days of the week from multiple rows?

    Gurus please help!


    The FeePage sheet is a summary page that pulls data from the Master sheet using formulas. All I do is enter the CRN number into column A on the FeePage and the rest of the row is populated with the data. Some classes have more than 1 row in the master sheet. For CRN 20570 the class days are S, U & F. The formula I have just pulls the class day value from the first row and then stops.

    =IFERROR(VLOOKUP(TEXT($A3,"0"),Master!$1:$10001,COLUMN(),FALSE),IFERROR(VLOOKUP($A3,Master!$1:$10001,COLUMN(),FALSE),""))
    How can this formula be reworked to loop through the matching rows and give the desired result of "FSU"

    Many thanks!!!

    NeedAllDays.xlsm

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,956

    Re: Formula to loop and combine days of the week from multiple rows?

    Hi,

    By its nature a formula will only return one value. Have you considered using either a Pivot Table or even a data filter in a line or three of VBA code which could loop down your unique CRN Numbers and build the schedule on the fly.

    I'm attaching the pivot table option which I think will be far more flexible and useful. I've just filtered the CRN 20570 in B2 but of course you can use the drop down to select all or as many as necessary.
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,956

    Re: Formula to loop and combine days of the week from multiple rows?

    ... sorry missed the CRN # from the first column on the PT.

    If you do have Excel 2010 or later rather than 2003 you could also take advantage of the Pivot Table 'Slicer' functionality which gives you easier control over the filtering of the PT.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-06-2014
    Location
    Valparaiso, FL
    MS-Off Ver
    Excel 2013
    Posts
    73

    Re: Formula to loop and combine days of the week from multiple rows?

    Wow! What a cool solution! I haven't really played with pivot tables yet. The whole purpose of the sheet is to be able to quickly bring up class information as I work with my veteran students. The original fee page, I just type in the crn numbers and the data fills in. Is there a faster way to enter the crn numbers besides using the filter? Or is that what you meant by slicers? I have excel 2013 at work. I appreciate your thoughts. Thank you again for such a cool solution--awesome work!

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,956

    Re: Formula to loop and combine days of the week from multiple rows?

    Hi,

    The original workbook I attached had the CRN number in the Report Filter area slightly above the main area of the Pivot Table. With that layout then it would work in much the same way as you describe. You could filter all of the particular CRN values that you're interested in. With the second version I'd simply dragged and dropped the CRN field into the body of the PT

    Are the CRN numbers the 'class information' you mention or is the class something different. I don't see any Class reference in the data so I'm not entirely clear on that point.

    The beauty of a Pivot table is that you can freely drag and drop field column names around between the four areas of the PT definition and see different views of your data at a stroke. They combine the functionality of summing, grouping subtotalling and other analysis information without the need to create formulae or use VBA. They really are powerful and well worth investing a little time understanding them.

    Yes the slicers offer a more intuitive way of dicing and slicing your data.

  6. #6
    Registered User
    Join Date
    03-06-2014
    Location
    Valparaiso, FL
    MS-Off Ver
    Excel 2013
    Posts
    73

    Re: Formula to loop and combine days of the week from multiple rows?

    I have been tinkering with pivot tables and they are really cool! Thanks so much for your help and showing me a new direction! Ultimately I think I might need to use VBA to sort the days and I'll start another thread since it's slightly off topic here. Thanks so much!!!

  7. #7
    Registered User
    Join Date
    03-06-2014
    Location
    Valparaiso, FL
    MS-Off Ver
    Excel 2013
    Posts
    73

    Re: Formula to loop and combine days of the week from multiple rows?

    I just checked out your first version--that's incredible! I like that you can type in the CRN and the table updates.

    I didn't answer your question from before...What I meant regarding class information was the CRN number. When students come to see me to process their VA benefits I have to look up every single class they are taking and figure out what the tuition and fees are. Luckily each class has a unique id-the CRN number. Sadly our new system cannot calculate tuition and fees for us so I am doing this manually and I have over a thousand students for just 3 of us! Thus the need for my fee sheet. I can just type in each CRN and immediately I have all the class data.

    Do you know if its possible to enter multiple CRN numbers in your first example? My students often take more than 1 class. Thank you again for your feedback. I really appreciate your help!

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,956

    Re: Formula to loop and combine days of the week from multiple rows?

    Quote Originally Posted by hopegriffin View Post

    Do you know if its possible to enter multiple CRN numbers in your first example? My students often take more than 1 class. Thank you again for your feedback. I really appreciate your help!
    Yes, of course. When you select the drop down arrow pointer to select a CRN note the small tick box at the bottom which says 'Select multiple items'. Tick that and you can select as many as you need.

  9. #9
    Registered User
    Join Date
    03-06-2014
    Location
    Valparaiso, FL
    MS-Off Ver
    Excel 2013
    Posts
    73

    Re: Formula to loop and combine days of the week from multiple rows?

    HAHA! I am such an idiot! Pardon my blonde moment. That's fantastic! Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Overlap of Multiple Time Intervals in Days of the Week
    By Jayden933 in forum Excel General
    Replies: 0
    Last Post: 07-01-2015, 04:32 PM
  2. Replies: 7
    Last Post: 03-13-2015, 05:34 PM
  3. [SOLVED] Loop through date data and return with days per each week of the data
    By jeptha in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-05-2012, 09:18 AM
  4. formula for six days week explanation..:)
    By ajitexcel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2010, 11:24 AM
  5. Copy from one worksheet into rows (days of week) on second spreadsheet.
    By Sandy2976 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-13-2009, 12:36 PM
  6. formula for subsequent days of the week
    By John_G in forum Excel General
    Replies: 6
    Last Post: 10-16-2008, 03:14 PM
  7. How do I put days of the week into a multiple regression model?
    By Paula in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-06-2006, 05:34 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1