Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Automatically constructing text strings

    I am a professor and I keep my grades in an Excel worksheet. On one tab, I track attendance. Row 1 has dates, column A has names, and column B is a count of absences. For each student, I place an "X" in a column when they are absent on that date. The count column then counts the absences for including in their grade. For example:

    Name_____Count 3/1 3/3 3/8 3/11
    Bob________0
    Sally______1________X
    Ted________2____X____________X

    (Sorry for the underlining. I tried to0 format the table with Courier but the forum just stripped out all the extra spaces and there is no table function so I could not figure out any other way to get the columns to line up.)

    Is it possible to write a formula to construct a text variable for each student giving the dates of their absences? In the above example, Bob would have "", Sally would have "3/3", and Ted would have "3/1,3/11".

    I ask because I upload all this information to an online course management system and it would be nice to show the students the dates I have them down as absence, not just the count.

    Ronny
    Last edited by Ronny; 2011-04-11 at 20:15. Reason: Editor screwed up my column spacing.
    Ronny Richardson

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by Ronny View Post
    ........ On one tab, I track attendance. Row 1 has dates,
    How is it determined what dates are in Row 1? There were no absences on 3/8.
    How many possible dates for Row 1?
    Can you attach a sample of the attendance Sheet (tab)?

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You would need to use code I think - is that OK?
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    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
    If the labesl are in row 1 and the names in Col A, Count in Col B, dates in C-F, in G1 you can enter the formula:
    =IF(B2=0,"",LEFT(IF(C2="","",C$1&", ")&IF(D2="","",D$1&", ")&IF(E2="","",E$1&", ")&IF(F2="","",F$1&", "),LEN(IF(C2="","",C$1&", ")&IF(D2="","",D$1&", ")&IF(E2="","",E$1&", ")&IF(F2="","",F$1&", "))-2))

    And copy it down the column...

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post
    Quote Originally Posted by sdckapr View Post
    =IF(B2=0,"",LEFT(IF(C2="","",C$1&", ")&IF(D2="","",D$1&", ")&IF(E2="","",E$1&", ")&IF(F2="","",F$1&", "),LEN(IF(C2="","",C$1&", ")&IF(D2="","",D$1&", ")&IF(E2="","",E$1&", ")&IF(F2="","",F$1&", "))-2))
    It works perfectly, thanks!

    Ronny
    Ronny Richardson

  6. #6
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Using above formula, I cannot get it to format with "date" in Column G. I just get raw number [40605]. If I put 40605 in another cell [J2] and format with "date", I get appropriate date, as expected. Why not with this formula return?
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post
    The formula works just fine, you just have to enter the dates as text, e.g. enter March 4th as '3/4 with a quote mark in front of it.


    Ronny
    Ronny Richardson

  8. #8
    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
    If your headings are actual dates (and not text) instead of using:

    C$1&

    Use:
    Text(c$1, "m/d")&

    Replace the "format" with whatever format you want and use this for D1, E1, F1, etc...

    The Text function converts the number to a text of a particular format...
    Steve

  9. #9
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    This last option seems to be a better choice in that LEN seems to have less effect and more flexibility is possible. An example is the trailing -2 and you can remove the commas in C$1&", " and D,E,F [in LEN]. Both seem to be unnecessary here. Plus you can enter the dates as normal, as dates not text.
    I do not understand why without the LEN portion, it only returns 3.

  10. #10
    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
    What do you have if there is no Len Portion? You need a len portion the formula does not know how much of a substring to extract.

    Steve

Posting Permissions

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