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

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

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

3. ## 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.

4. ## 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. ## 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. ## 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. ## 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. ## Re: Formula to loop and combine days of the week from multiple rows?

Originally Posted by hopegriffin

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

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

#### 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