0

I'm stuck with a fairly finicky data report that I don't have the option of customizing at all, that I would like to prepare in such a way to make my life easier. I am using Excel 2011 on a Mac. (This is for student attendance)

I've been searching all over the web for days and tried multiple different methods, even the most promising of haven't worked. The closest approximation (Combining duplicate entries with unique data in Excel & the one linked in comments) runs into problems with Scripting.Dictionary not being a thing on Macs?

Right now I'm filtering the data, turning that into a pivot table and then manually cross-referencing the pivot table count and the filtered data dates & sessions and tracking new absences by pasting them into a "master list."
It doesn't work well. Its both time consuming and students fall through the cracks.

Information (Columns) I have:

  • Student,
  • Course,
  • Course start,
  • Course end,
  • Attendance date,
  • Session (AM or PM) and
  • attendance (Present, Absent, Late, Left Early, Excused, Explained)

We meet with students after a certain number of absences and lates - but if they are absent for both morning and evening session it counts as a single absence (lates both count)

At a minimum, I would like to consolidate the AM and PM row for "absent" and "explained absent" occurring for the same student on the same day into one row, instead of spread across two so I can just count rows (explained, absent, late, and left early) instead of count and then manually track and compare. (conveniently, the classes are all entire day classes, and there is no date overlap for any single student. Less conveniently not all attendance sessions are entered, so there is no pattern to base things on.)

Priority 1: Consolidate AM & PM into one row when appropriate
- Consolidate Rows so that Session becomes "AM, PM" instead of AM & PM across two rows for "Explained Absence" "Excused Absence" and "Absence" ONLY. (Student, Course, date and attendance need to all be identical)
- Where attendance is "late", "left early" or different for the morning and evening session, the lines should NOT be consolidated.

Goals
- minimal steps, every time I do this I have to generate a new report, and I'd like to change this from an all day task to something I can do more frequently. - End result needs a count as described above and a list of the days and sessions missed, so pivot tables are not ideal as they erase the specific data.

I am comfortable with inserting and light tweaking of VBA, but am not fluent in it by any stretch.

Example:
Student, Course, Course Start, Course End all consistent in this example. Not included below because it becomes too unwieldy.
...I give up on making this a table. Sorry.

Attendance Date Session Attendance Manual "strikes" count

17-12-04 PM Present 0
17-12-04 AM Late 1
17-12-06 AM Absent 2
17-12-06 PM Absent 2 (absent also above)
17-12-07 AM Late 3
17-12-07 PM Absent 4 (different types)
17-12-08 AM Late 1 (new course)
17-12-08 AM Late 2 (two lates)

Rowan
  • 1
  • 1
  • Can you show us what you have tried so far? – Dave Mar 27 '18 at 16:23
  • Why does your pivot table result in manual work? Have you attempted refreshing the pivot table? Or is it formatted in a way you don't like? – elliot svensson Mar 27 '18 at 17:01
  • oh geeze. I've tried a lot so far. – Rowan Mar 28 '18 at 17:20
  • Latest attempt crashed excel just trying to delete rows with VBA using this (yes, I know filters or formulas & manual is faster, but this is only 1 step and I can always set my computer to work and then go do other things): Sub rowdeltest() Dim x As Long, lastrow As Long lastrow = Cells(Rows.Count, 1).End(xlUp).Row For x = lastrow To 1 Step -1 If Cells(x, 9).Value = "Present" Then Rows(x).Delete End If Next x End Sub – Rowan Mar 28 '18 at 17:26
  • The pivot table is a very clunky way of doing what I need - its for counting, I need the dates (and I know there is a way to get it to show those, but its messy still) If I can consolidate rows I can probably get that down to something less manual b/c then each count will be only 1 "strike" – Rowan Mar 28 '18 at 17:27

2 Answers2

0

Have you tried using Data > Data Tools > Remove Duplicates? If you select the entire table, this method allows you to choose multiple columns for the "remove duplicates" function. Remove Duplicates

elliot svensson
  • 593
  • 3
  • 11
  • The problem with remove duplicates is that it destroys data (or does nothing) - I need to merge the AM/PM - but for absences, excused and explained only (when that also matches) – Rowan Mar 28 '18 at 17:19
  • Perhaps your data stream isn't the best place for your accounting... do you already have a structure on a different sheet somewhere with exactly one cell for absences, tardies, etc. per student per day? – elliot svensson Mar 28 '18 at 17:29
  • No. I have no way of changing the output. My boss has tried multiple times to get changes in the reports. Its not happening. – Rowan Mar 28 '18 at 19:38
  • Can you please detail what you are doing when you manually cross-reference the pivot table? Could this be solved by having the PivotTable automatically increase its data range, as it does when the data is formatted as a Table (Insert > Table)? – elliot svensson Mar 28 '18 at 19:46
  • I flag the totals that are 5 and above, then I go back into the sorted raw data and find out if any of the absence (differences and lates are counted twice if there) days are double counted, subtract those from the total. Copy all the days and their absent status to a master spreadsheet where we track when we've met with students about their absences, so we don't have more than one meeting for the same days. – Rowan Mar 28 '18 at 21:19
0

Try this. I used the PivotTable option PivotTable Tools > Options > Calculations > Show Values As > Index, which seems to remove duplicates during PivotTable reporting.My PivotTable

When I added a Row Labels PivotTable field that makes same-same rows unique, (such as "AM" or "PM" in your example), this "Show Values As Index" method did not remove duplicate rows.

Also, I'm using "Date Filter, This Week" on the Date dropdown-list to just show the dates I'm interested in.

Date Filter

Good luck!

elliot svensson
  • 593
  • 3
  • 11