+ Reply to Thread
Results 1 to 87 of 87

Find: How many Classes Each Student Has Attended Facultywise from a datasheet

  1. #1
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Here is Given an excel datasheet: Datasheet 1 (Attached)
    As per the datasheet each student has attended the Classes of Each Faculty like this: e.g [B]William Has Attended one class of the Faculty "A" while one Class also for the faculty "B".
    No of Faculties may be more than 100 and no of students may be more than 1000
    Please help to solve this problem
    Attached Images Attached Images
    Last edited by indraji2001; 04-06-2018 at 03:30 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Yes I tried to attach the file. since attachment icon did not work I could not attach the workbook. ok I am going to the post and trying to attach through "Go Advanced"

  4. #4
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Datasheet is attached herewith
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    In your sample, each student has attended two classes: is this representative of the real data? Will all students have attended just two classes?

    Why do you need to display the data in this format? Would you consider other layouts? Concatenation of data always complicates matters.

  6. #6
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    No it may be any number ...it is just a representative idea. ....

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    A formula as below will give the counts ..

    =SUMPRODUCT(($C$2:$C$7="A")*(ISNUMBER(SEARCH("Cateye",$G$2:$G$7))))

    so result could be :

    "Cateye:" & =SUMPRODUCT(($C$2:$C$7="A")*(ISNUMBER(SEARCH("Cateye",$G$2:$G$7)))) & "(A)"

    Better


    =SUMPRODUCT(($C$2:$C$7="A")*(ISNUMBER(SEARCH(B10,$G$2:$G$7))))

    where B10 is name e.g "Cateye"

    I do not envisage trying to concatenate a large number of faculties for a given student.

    I would reconsider how you want the results presented.

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    maybe with PowerQuery.....

    Power Query for

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Quote Originally Posted by indraji2001 View Post
    No it may be any number ...it is just a representative idea. ....
    Which means that your sample data is NOT truly representative.

    As John has said and I have implied, a reconsideration of the representation of your data may be in order.

  10. #10
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    I attached a more detailed datasheet : Datasheet 1_Edited.
    Here "?" the no of classes that corresponding student has attended for that faculty.
    The No of Faculty members may roughly 100 (in the datasheet it is 06). No of students per class (at each time slot) may be maximum 200
    So the actual list may be quite long department-wise.
    Yes I do not have enough knowledge in excel, therefore I have come to the help forum. It is not possible for everybody to learn everything in detail.
    I have my research expertise in Synthetic Organic Chemistry. If I expect that other person belonging to other domain will have
    the expertise like me then I am wrong, since now a days to become expert over any area needs lot of time and dedication.
    Thanks and Regards
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    I attached a more detailed datasheet : Datasheet 1_Edited.
    Here "?" the no of classes that corresponding student has attended for that faculty.
    The No of Faculty members may roughly 100 (in the datasheet it is 06). No of students per class (at each time slot) may be maximum 200
    So the actual list may be quite long department-wise.
    Yes I do not have enough knowledge in excel, therefore I have come to the help forum.
    Thanks and Regards
    Attached Files Attached Files

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    OK, so this proposed layout is going to be very difficult to achieve:

    Excel 2016 (Windows) 32 bit
    C
    14
    William = A(?) + B(?) + C(?) + D(?) + E(?) + F(?)
    15
    Tom = A(?) + B(?) + C(?) + D(?) + E(?) + F(?)
    16
    Smith = A(?) + B(?) + C(?) + D(?) + E(?) + F(?)
    17
    Rocky = A(?) + B(?) + C(?) + D(?) + E(?) + F(?)
    18
    Sticky = A(?) + B(?) + C(?) + D(?) + E(?) + F(?)
    19
    Cateye = A(?) + B(?) + C(?) + D(?) + E(?) + F(?)
    20
    John = A(?) + B(?) + C(?) + D(?) + E(?) + F(?)
    21
    Shiron = A(?) + B(?) + C(?) + D(?) + E(?) + F(?)
    Sheet: Sheet1

    I suggest you rethink it.

    Could you work with something like this, for example?

    Excel 2016 (Windows) 32 bit
    B
    C
    D
    E
    F
    G
    H
    14
    William A(?) B(?) C(?) D(?) E(?) F(?)
    15
    Tom A(?) B(?) C(?) D(?) E(?) F(?)
    16
    Smith A(?) B(?) C(?) D(?) E(?) F(?)
    17
    Rocky A(?) B(?) C(?) D(?) E(?) F(?)
    18
    Sticky A(?) B(?) C(?) D(?) E(?) F(?)
    19
    Cateye A(?) B(?) C(?) D(?) E(?) F(?)
    20
    John A(?) B(?) C(?) D(?) E(?) F(?)
    21
    Shiron A(?) B(?) C(?) D(?) E(?) F(?)
    Sheet: Sheet1
    Last edited by AliGW; 04-06-2018 at 04:18 AM.

  13. #13
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Actually Ali
    what I wanted to mean

    Each student has attended different classes at different time slots for a number of faculty members.

    Let us take the example of William. We have to calculate in the excel sheel How many classes William has done for Faculty "A", Faculty "B", Faculty "C", Faculty "D", Faculty "E", Faculty "F". Those numbers have to be placed in place of "?" [after calculation (by formula)] in the respective places.
    The same thing is applicable for other students also.
    Hope I could noe make it understood before you.

    As per your opinion it is very difficult by excel - right?
    Regards

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    I would suggest initially a matrix of Faculty in Columns (B to ...?? in row 1) and Students in Rows 2 onward (column A)

    Then a single repeatable formula could be used to fill in the numbers.

  15. #15
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    is that like this?

    btw, William is not the same as Wlliam and Cateye vs cateye so check your raw data
    Attached Files Attached Files

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Yes, I understand what you want. What I am saying is that the layout you are asking for will be very difficult to achieve.

    If it were me, I would start from scratch. Each student's attendance details would be made up of individual records:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    1
    Timestamp Teachers Subject Date Time Slot Value
    2
    02:48:08
    A Sub1
    21/05/2017
    10 -10:45 William
    3
    02:48:08
    A Sub1
    21/05/2017
    10 -10:45 Tom
    4
    02:48:08
    A Sub1
    21/05/2017
    10 -10:45 Smith
    5
    02:48:08
    A Sub1
    21/05/2017
    10 -10:45 Rocky
    6
    02:48:08
    A Sub1
    21/05/2017
    10 -10:45 Sticky
    7
    02:48:08
    A Sub1
    21/05/2017
    10 -10:45 Cateye
    8
    02:48:08
    A Sub1
    21/05/2017
    10 -10:45 John
    9
    02:48:08
    A Sub1
    21/05/2017
    10 -10:45 Shiron
    10
    03:02:16
    B Sub2
    21/05/2017
    10:45 - 11:30 Tom
    11
    03:02:16
    B Sub2
    21/05/2017
    10:45 - 11:30 Rocky
    12
    03:02:16
    B Sub2
    21/05/2017
    10:45 - 11:30 Sticky
    13
    21:28:37
    E Sub1
    21/06/2017
    10:45 - 11:30 Sticky
    14
    21:28:37
    E Sub1
    21/06/2017
    10:45 - 11:30 cateye
    15
    21:28:37
    E Sub1
    21/06/2017
    10:45 - 11:30 John
    16
    21:28:37
    E Sub1
    21/06/2017
    10:45 - 11:30 Rocky
    17
    21:33:09
    B Sub2
    21/06/2017
    11:30- 12:15 Rocky
    18
    21:33:09
    B Sub2
    21/06/2017
    11:30- 12:15 Sticky
    19
    21:33:09
    B Sub2
    21/06/2017
    11:30- 12:15 cateye
    20
    21:33:09
    B Sub2
    21/06/2017
    11:30- 12:15 John
    21
    21:33:09
    B Sub2
    21/06/2017
    11:30- 12:15 Smith
    22
    21:33:09
    B Sub2
    21/06/2017
    11:30- 12:15 Shiron
    23
    21:38:48
    C Sub3
    21/06/2017
    10:45 - 11:30 Tom
    24
    21:38:48
    C Sub3
    21/06/2017
    10:45 - 11:30 Smith
    25
    21:38:48
    C Sub3
    21/06/2017
    10:45 - 11:30 William
    26
    21:45:41
    D Sub2
    21/06/2017
    10:45 - 11:30 William
    27
    21:45:41
    D Sub2
    21/06/2017
    10:45 - 11:30 Tom
    28
    21:45:41
    D Sub2
    21/06/2017
    10:45 - 11:30 Rocky
    29
    21:45:41
    D Sub2
    21/06/2017
    10:45 - 11:30 Wlliam
    30
    21:33:09
    C Sub2
    21/06/2017
    11:30- 12:15 Rocky
    31
    21:33:09
    C Sub2
    21/06/2017
    11:30- 12:15 Sticky
    32
    21:33:09
    C Sub2
    21/06/2017
    11:30- 12:15 Rocky
    33
    21:33:09
    E Sub2
    21/06/2017
    11:30- 12:15 Rocky
    Sheet: Sheet2

    By normalising the data in this way, the analysis you want to do becomes much easier.

  17. #17
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Sandy you are 100% correct. Yes I accept my unintentional fault : it would be William and Cateye only everywhere.

    Now can it be done that is customization?
    I want to Mean If William attended total 04 classes then how many classes of Faculty A or B or C or D or E or F?
    Same is applicable for other students.

  18. #18
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    You mean:
    William = A(1) + B(0) + C(1) + D(2) + E(0) + F(1) ?

    Basic question was : how many not how many which...

    btw there is duplicate in D so it is mistake or ?

  19. #19
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Exactly. Since you have tremendous command over this subject you could express it technically and cryptically ..

    it would be like William = 5 = A(1) + B(0) + C(1) + D(2) + E(0) + F(1)

  20. #20
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    see the attached file A table is created for results but it is not looks like your desired results
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  21. #21
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Excellent, almost done. Purely on formula based. In fact your presentation is better than me.
    One thing can it be done:
    if any new student;s name and new Faculty Name (like A, B etc) is inserted in the left hand side table automatically it will be updated on the right hand side out put
    table?

  22. #22
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    It can be done, but it is better to have Student ID instead of Name, Students Names may be duplicate but not the Student ID

  23. #23
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    something like this ? choose one
    Attached Files Attached Files
    Last edited by sandy666; 04-06-2018 at 05:06 AM.

  24. #24
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666
    yes.. it is perfectly what I am looking for. But One thing has to be done since more students will be there in reality and also more number of faculty members.
    if any new student;s name and new Faculty Name (like A, B etc) is inserted in the main table automatically it will be updated in the outputput file after saving the file.
    Have you applied formula or by pivot table?
    Last edited by indraji2001; 04-06-2018 at 05:30 AM.

  25. #25
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @nflsales

    yes you are correct. In reality the Student's name will be linked with his roll no : so that will be unique id.
    Can you make that modification accordingly?
    Last edited by AliGW; 04-06-2018 at 05:32 AM. Reason: Name corrected.

  26. #26
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    use @[screenname] because I don't know to whom you are talking

  27. #27
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    ok @sandy666

  28. #28
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    post #24 @?
    post #25 @?

    use Edit option

  29. #29
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    post #24 @sandy666
    post #25 @nflsales

    I have already edited that as per your instruction

  30. #30
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Thank you

  31. #31
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Reattach a sample file with students id or role number

  32. #32
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @indraji2001

    The forum guru is nflsales - I have changed both posts where you have used this.

    Screen name = nflsales
    Forum status = Forum Guru

    Screen name = sandy666
    Forum status = Forum Expert

    Screen name = AliGW
    Forum status = Moderator

  33. #33
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    You can add more names or teachers to your raw data then refresh PivotTable(s)
    It was done with PowerQuery then from query table ==>> PivotTable

    about PowerQuery read post #8

    Edit:
    If you want use my solution you'll need to install this add-in
    if not - use nflsales solution
    Last edited by sandy666; 04-06-2018 at 05:42 AM.

  34. #34
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @nflsales

    Yes it is attached.....
    Attached Files Attached Files

  35. #35
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @Sandy 666

    I am using MS office 2016 ...so is it needed to install that addin?

    And in Excel 2016 after adding add more names or teachers to your raw data from where I will refresh the Pivot Table. Excuse me for my little knowledge in this domain....I am learning it since last 9 months
    Rgards
    Last edited by indraji2001; 04-06-2018 at 06:09 AM.

  36. #36
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Power Query is part of Excel 2016 - it's on the Data ribbon and is called Get & Transform (at the lefthand end).

  37. #37
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Update your profile about Excel version

  38. #38
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666 and @ali

    I am going to add this add-in in Excel 2016 but not getting ..under the com add-in section I am not gettting power add-in
    Excel.PNG

  39. #39
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @ nflsales

    Is the datasheet ok as you wanted?

  40. #40
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Quote Originally Posted by indraji2001 View Post
    @sandy666 and @ali

    I am going to add this add-in in Excel 2016 but not getting ..under the com add-in section I am not gettting power add-in
    Attachment 569045
    It's not an add-in - it's already there. Look at your screenshot - Get & Transform is on the left of the Data ribbon.

  41. #41
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @Ali
    Thanks now I got it ...please do not mind for my poor knowledge ...I am fighting a lot for last 8 months to learn excel slowly slowly..hope I will get you as my valuable tutors

  42. #42
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    There is no problem.

  43. #43
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @ Sandy666

    Ultimately I got it. I refreshed all and everything got updated after adding new entries.

    Can you give the tutorial how you made this project using power query ? I want to learn from you

    Since it is the golden opportunity for me to learn these things from the experts of this forum. This forum is for educational purpose
    not for mere solution providing.
    Last edited by indraji2001; 04-06-2018 at 06:42 AM.

  44. #44
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @nflsales
    I reattached a sample file as you desired
    Attached Files Attached Files

  45. #45
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Which solution do you want? The Power Query method or the formula method?

    This forum is for educational purpose not for mere solution providing.
    Absolutely correct! I hope that Sandy will provide you with a step-by-step.

  46. #46
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @Ali

    Frankly speaking I want to learn both of them, Power query is completely automatic and also fast. If I learn this I can make this type of table for different departments of my college. If you think it is too hard me to tackle by myself
    then Please help me to learn formula method- in that case I will request @nflsales to show me how the output table may be automatically updated when new faculties and new students will be added.

  47. #47
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Quote Originally Posted by indraji2001 View Post
    Can you give the tutorial how you made this project using power query ? I want to learn from you
    for which one ?

  48. #48
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666
    I want to learn how to make this file which you have made using power query?

  49. #49
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Look at post #23 - which one do you want Sandy to help you with?

  50. #50
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @Ali
    ok looking

  51. #51
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    See attached using this formula in K3

    =SUMPRODUCT(($C$2:$C$7=J$2)*(ISNUMBER(SEARCH($I3,$G$2:$G$7))))

    Copy across and down

    To add more students and/or Faculties, simply add to Rows and Columns and extend formula.

    If you need Student names I advise creating a table of Student ID/Student name: add Name column in "output" table and Use VLOOKUP or INDEX to get name.

    Having you data as per column G is not good practice: You should consider organising in format that Ali advised in previous post.
    Attached Files Attached Files

  52. #52
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @Ali and @sandy666

    for this one I need step by step tutoral
    https://www.excelforum.com/attachmen...asheet-v2.xlsx

  53. #53
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Sure....
    at first: PowerQuery is case sensitive, so Mark vs mark are treated like two different names
    second: you need prepare your raw data without errors/mistakes - no additional commas at the end, no double spaces etc.... best way is: name,name,name (no spaces)

    so far so good
    change your raw data from range to Excel Table [Ctrl+T] (will be easier if you do that. Of course PQ will do that for you but sometimes PQ read mistakataModel
    es and wont load table to PQ editor)
    click inside Table and from the ribbon select Data-From Table , it will load table to PQ editor
    with Ctrl select two columns (Students and teachers) then right click and select remove other columns so you'll stay with these two only
    select Students column and from menu select Split by delimiter (choose comma, PQ should do that automatically) then Advanced Options select Rows and then Ok
    From menu select close&load and choose : only create connection and add this data to the DataModel (DataModel will give you DistinctCount possiblity)
    OK

    In Excel sheet : Insert - PivotTable - Use an external DataSource - Choose connection (select Query....)
    choose place where you want your PivotTable and Ok

    how to create Pivot Table you know (I hope)
    you can choose between Count or Distinct Count in Values Area, it's up to you
    Last edited by sandy666; 04-06-2018 at 08:36 AM.

  54. #54
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    see the attached file, got the solution with the helper columns
    Attached Files Attached Files

  55. #55
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666
    Thanks for lucid language.
    I could follow upto "From menu select close&load and choose : only create connection and add this data to the DataModel (DataModel will give you DistinctCount possiblity)
    OK"

    No I do not know how to create pivot table. Just know little bit from you tube.
    If you want you can help me through team viewer.
    Regards

  56. #56
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    After you select : Insert - PivotTable - Use an external DataSource - Choose connection (select Query....)

    whatever, wait a few....

  57. #57
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    If you want you can help me through team viewer.
    No - for the benefit of ALL members, keep the discussion and help here, please.

  58. #58
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    TeamViewr is a good tool but this is too simple to use TV
    here is step-by-step movie about PivotTable
    Attached Files Attached Files

  59. #59
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666
    as the admin Ali advised not to go beyond this thread section, i respect her opinion. so I cannot go with tv. you have sent a file. I am going home and after checking that I will report u.
    Thanks for your guidance. here I have joined to learn from experts, not just for solution.

  60. #60
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    You are welcome
    You've few choices here so choose one and mark thread as solved (if solved )

  61. #61
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666

    Done everything.......For recording this movie which software did you use? Can you please tell me ..that will help for future communication

  62. #62
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    But this is not Excel help [FastStone Capture]

  63. #63
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    It doesn't matter - it's a perfectly legitimate question.

    FastStone Capture is a great little utility - comes in a portable format, too. I would highly recommend it. You will find it easily via Google.

  64. #64
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666

    hahaha..it is auxiliary help ....everyday we can learn from others.....

  65. #65
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Thanks for the rep.

    I'm glad we were able to help you AND teach you a few things today.

  66. #66
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    But it shouldn't be treated as your main tool, only if really necessary.
    For standard communication here is Post and attached Excel file.
    Did you read forum rules?

  67. #67
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @Ali
    Yes today I learned how the same problem can be done in two ways: Pivot Table Method and another by Formula Method : both are excellent. Same Concept in Organic Chemistry - you can achieve one molecule by designing two retro-synthetic approaches.

    However Pivot table method is more automatic.

  68. #68
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666
    Yes I know that. But sometime it becomes very necessary to present the problem.

  69. #69
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    on the end I can say: sometime

    I'm going back to watching a real movie

  70. #70
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666
    If I want to keep the Pivot table size intact below the main table while I will expand the cell widths of the man table, what I will do?

  71. #71
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    maybe PivotTable Options - Layout & Format - Autofit columns widths on update (unchecked)

    read here: PivotTable
    Last edited by sandy666; 04-06-2018 at 02:03 PM.

  72. #72
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666
    I did that. But even after that whenever I am expanding the column width of the main data table, the cell widths of the Pivot Table below it is also changing.
    When you will get time please look into this issue.

  73. #73
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    I don't see any attached excel file to look at .....

  74. #74
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    here it is
    Attached Files Attached Files

  75. #75
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    because you changing width of columns so it works for everything down. One column cannot be with two different widths
    move your PT to the right not under the raw table or to another sheet
    Attached Files Attached Files

  76. #76
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666
    Thanks. worked

  77. #77
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    You are welcome

  78. #78
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666

    thanks for your help which is awesome to me. Can you please go to this thread?
    I have no knowledge in VBA.
    https://www.excelforum.com/excel-pro...ck-symbol.html

    presently learning basic things posted in this forum
    https://www.excelforum.com/excel-pro...materials.html

  79. #79
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    Quote Originally Posted by indraji2001 View Post
    I have no knowledge in VBA
    .
    Me too

  80. #80
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666
    really a honest guy you are. Can this be done by formula method ? if so I will post it in this subforum officially

  81. #81
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    But what ?

    here are ppl much better (faster) than me with formulas

  82. #82
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666
    Regarding this post ..I really need a solution for my personal use, to be honest.

    https://www.excelforum.com/excel-pro...ck-symbol.html

  83. #83
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    VB is a BlackMagic to me
    (I dont know what are you tryin' to achieve and better way is wait for answer on VBA subforum)
    But look at this
    Attached Files Attached Files

  84. #84
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666

    actually what I want
    if I put the name of the symbols and exchanges => accordingly the name of corresponding companies will come under the "description" column , the name of the companies will be pulled from Google Finance Website

    e.g if I Put SBIN and exchange NSE, the name of the company "State Bank of India" will be appeared in the corresponding "Description" column . this name will be fetched from the google finance site: https://www.google.com/search?tbm=fi...vQSg4q-oCA_5:0

    similarly
    e.g if I Put ABAN and exchange NSE, the name of the company "Aban Offshore Ltd Fully Paid Ord. Shrs" will be appeared in the corresponding "Description" column . this name will be fetched from the google finance site:https://www.google.com/search?tbm=fi...vASgvruABQ_5:0

  85. #85
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    It seems like URL query with parameters from cells, but.... this is beyond of my help here
    you started your VBA thread at: Today, 04:32 PM, so be patient

    edit:
    or use Commercial Service

  86. #86
    Registered User
    Join Date
    05-05-2013
    Location
    Bhubaneswar, India
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    @sandy666

    no issue...I just wanted to know whether it is possible for you to tackle this problem or not.....

  87. #87
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find: How many Classes Each Student Has Attended Facultywise from a datasheet

    see edit in previous post

+ 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. [SOLVED] Count Age range formula ONLY for those who attended
    By prvnrk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2018, 02:19 PM
  2. [SOLVED] How to find name of top student for each subject?
    By mso3 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 08-17-2014, 12:03 PM
  3. VBA for display Various Datasheet data in Masterdatasheet when we choose datasheet name
    By santosh226001 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-08-2014, 10:21 AM
  4. Replies: 1
    Last Post: 02-06-2014, 08:07 AM
  5. Combing Student list and Classes they took to one sheet
    By minimacros in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-13-2010, 01:20 PM
  6. find value from the datasheet by matching two fields?
    By gaju_3112 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-15-2007, 01:47 PM
  7. Calls Attended
    By harishs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2007, 08:24 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