Attendance Analysis

Maybe :

Please Login or Register  to view this content.

To run the macro :
- Press Alt F8 on keyboard
- Click "Run" command button

I use the "Collection" object here.
Collection object acts as container, that holds other data in it.
You can imagine collection object like a box that holds other items inside it.
A collection object can put another collection objects in it, like a box that can put another smaller boxes inside it.

The macro loop for each data on column Q on each sheet, if the data is qualified (< 50 %) then look at column B on the same row.
If the "bigger box" with that name (like S1-A, S1-C, S1-D, etc) is not exists yet, then create it.
Then put the "littler boxes" inside right "bigger box", ex. put Stephanie, May, June inside S1-A box, put Snow and Mary inside S2-A box, etc.

When all data have been distributed, then recollect the data by looping big box -> small box, and store them in array.
Then the array is written to the new sheet.

You are welcome.

Don't forget to mark this thread as solved :
please select Thread Tools from the menu link above and mark this thread as SOLVED.

Regards

You better upload a sample workbook that describe your new case.

Your data layout is inconsistent, e.g. Last column (also known as % Attendance column) of :
Sheet1 = Column Y
Sheet2 = Column Y
Sheet3 = Column W
Sheet4 = Column T
Sheet5 = Column X
Sheet6 = Column U

You must change all the sheets layout to a uniformed layout first.

Should be :

Please Login or Register  to view this content.

The macro is to run on all worksheets (it means you can add as many sheets as needed without the need to re-modified the code) that qualified some constraints :

---> If Left$(v1.Range("A2"), 6) = "Absent" And Left$(v1.Range("A16"), 6) = "Name" Then
- First six characters of range A2 on that sheet must be "Absent"
- Range A16 on that sheet must be "Name"

Any sheet that meets these criteria will be processed by macro, so you can freely add another sheets (as long as the data layout is same).

It means that your other excel sheet has different layout, the code need to modified based on your new layout.
Please upload your file.

As expected, your data layout is different from first post, and your new data layout have so many inconsistencies :
- You add a new column (S/N --> column A), this makes all data is shifted to right, and of course, calculation will be wrong
- Data sometime start from row 2 (Sheet1 - Sheet5), sometime from row 1 (Sheet6 - Sheet20)
- % Attendance column location is always changed, for the last workbook it is located at column Y, for Sample1.xlsx it is at column J, for Sample2.xlsx is at column L
- Sample2.xlsx, this workbook has NO DATA entered yet !!! How can you expect macro run on empty tables ??!!

So :
- STOP keep changing your data layout, if you keep doing this, don't expect the macro will be worked, and don't expect me to remake the macro anytime you rechange the layout
- Don't do something silly like "the macro is not worked" on workbook WITH EMPTY TABLES like Samples2.xlsx, I don't understand what your motive by doing this

This macro will work on Sample1.xlsx, and Sample2.xlsx if Sample2.xlsx has already filled with some data, don't expect this can run if you rechange the data layout again.

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.