Need Excel Formula to calculate number of group members previously worked with (includes diff. size groups over time)

E

ea2146

New Member
Joined
Apr 9, 2012
Messages
30
Hello,

Thank you very much in advance for any help you can provide. I REALLY appreciate it!

I am looking for a formula that will calculate the values that currently appear in column D below. Specifically, I need the formula to calculate the total number of current group members with whom each student previously worked. For example, there is a value of "3" next to Carol's name in group 5 because Carol had previously worked with Susan, Eric, and Ashley at different times in the past.

If anyone could help me create this formula I would be extremely grateful!!



<tbody>
[TD="class: xl63"]Date[/TD]
[TD="class: xl63, width: 84"]Group[/TD]
[TD="class: xl63, width: 84"]Student[/TD]
[TD="class: xl63, width: 389"]Number of Current Group Members Worked with Previously[/TD]

[TD="class: xl64"]2/10/2015[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]Susan[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]2/10/2015[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]Kayley[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]2/10/2015[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]Carol[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]5/15/2015[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]Kayley[/TD]
[TD="class: xl63"]1[/TD]

[TD="class: xl64"]5/15/2015[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]Carol[/TD]
[TD="class: xl63"]1[/TD]

[TD="class: xl64"]5/15/2015[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]Jeff[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]5/15/2015[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]Eric[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]5/15/2015[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]Clarissa[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]5/15/2015[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]Ashley[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]7/15/2015[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]Susan[/TD]
[TD="class: xl63"]2[/TD]

[TD="class: xl64"]7/15/2015[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]Kayley[/TD]
[TD="class: xl63"]3[/TD]

[TD="class: xl64"]7/15/2015[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]Carol[/TD]
[TD="class: xl63"]3[/TD]

[TD="class: xl64"]7/15/2015[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]Jeff[/TD]
[TD="class: xl63"]2[/TD]

[TD="class: xl64"]8/18/2015[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]Alice[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]8/18/2015[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]Drew[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]8/18/2015[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]James[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]8/18/2015[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]Carol[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]8/18/2015[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]John[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]9/22/2015[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]Susan[/TD]
[TD="class: xl63"]1[/TD]

[TD="class: xl64"]9/22/2015[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]Eric[/TD]
[TD="class: xl63"]2[/TD]

[TD="class: xl64"]9/22/2015[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]Ashley[/TD]
[TD="class: xl63"]2[/TD]

[TD="class: xl64"]9/22/2015[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]Carol[/TD]
[TD="class: xl63"]3[/TD]

[TD="class: xl64"]9/22/2015[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]Frank[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]9/22/2015[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]David[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]9/22/2015[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]Hannah[/TD]
[TD="class: xl63"]0[/TD]

[TD="class: xl64"]9/22/2015[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]Thomas[/TD]
[TD="class: xl63"]0[/TD]

</tbody>
 
Sort by date Sort by votes
Very tricky. I couldn't figure out a way to do it with native Excel functions, so I wrote a UDF. Even that was tricky. But here it is and it seems to work fine.

1) Open your workbook
2) Press Alt-F11 to open the VBA editor
3) From the menu, select Insert --> module
4) On the window that opens up, paste this code:
Code: 
Public Function PrevStudents(Grp As Range, Stdnt As Range)
Dim MyGroups(1000) As String
Dim r As Long, wk, i As Long, j As Long, ctr As Integer

    Erase MyGroups
    ctr = 0
    r = 2
    
    While Cells(r, Grp.Column) <> ""
        grpno = CInt(Cells(r, Grp.Column))
        MyGroups(grpno) = MyGroups(grpno) & "," & Cells(r, Stdnt.Column)
        r = r + 1
    Wend
    
    wk = Split(MyGroups(CInt(Grp.Value)), ",")
    For i = 1 To UBound(wk)
        For j = 1 To Grp - 1
            If InStr(MyGroups(j), wk(i)) > 0 And InStr(MyGroups(j), Stdnt.Value) > 0 Then
                ctr = ctr + 1
                GoTo NextI:
            End If
        Next j
NextI:
    Next i
    
    PrevStudents = IIf(ctr > 0, ctr - 1, 0)
            
End Function
5) Return to your Excel sheet.
6) In D2, enter this function:
=PrevStudents(B2,C2)
7) Copy cell D2 and paste it down the column.

Let me know how that works.
 
Upvote 0
Thank you SO MUCH, Eric W!!!!

I just tried this and it works perfectly! This is brilliant. I really appreciate the help!
 
Upvote 0
Hi again,

Quick question - I presented an example that was analogous, but not identical to the data I am working with. The script you provided works perfectly for the example I provided, but I receive the "#VALUE!" error when I try to run it using my actual data which looks like this (see below - I have tens of thousands of rows). I couldn't figure out what to alter in the script to make it work. Any suggestions? Thanks!!!


<colgroup><col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <col style="mso-width-source:userset;mso-width-alt:13165;width:270pt" width="360"> </colgroup><tbody>
[TD="class: xl64, width: 75"]bcdate[/TD]
[TD="class: xl65, width: 96"]expid_CODE[/TD]
[TD="class: xl63, width: 360"]Name[/TD]

[TD="class: xl64"]6/26/1921[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl63"]Alexander Frederick RichmondWollaston1875UK[/TD]

[TD="class: xl64"]6/26/1921[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl63"]Alexander MacMillanHeron1884UK[/TD]

[TD="class: xl64"]6/26/1921[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl63"]Alexander MitchellKellas1868UK[/TD]

[TD="class: xl64"]6/26/1921[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl63"]Ang PasangSherpaNepal[/TD]

[TD="class: xl64"]6/26/1921[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl63"]Charles KennethHoward-Bury1883UK[/TD]

[TD="class: xl64"]6/26/1921[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl63"]Edward OliverWheeler1890Canada[/TD]

[TD="class: xl64"]6/26/1921[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl63"]GarongSherpaNepal[/TD]

[TD="class: xl64"]6/26/1921[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl63"]George Herbert LeighMallory1886UK[/TD]

[TD="class: xl64"]6/26/1921[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl63"]Guy HenryBullock1887UK[/TD]

[TD="class: xl64"]6/26/1921[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl63"]Harold AndrewRaeburn1865UK[/TD]

[TD="class: xl64"]6/26/1921[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl63"]Henry TreiseMorshead1882UK[/TD]

[TD="class: xl64"]6/26/1921[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl63"]LagaySherpaNepal[/TD]

[TD="class: xl64"]6/26/1921[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl63"]UnknownIndia[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]Arthur WilliamWakefield1876UK[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]Charles GranvilleBruce1866UK[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]Charles JohnMorris1895UK[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]ChettanSherpaNepal[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]Colin Grant (Ferdie)Crawford1890UK[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]DorjeSherpaNepal[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]Edward FelixNorton1884UK[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]Edward Lisle (Bill)Strutt1874UK[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]George Herbert LeighMallory1886UK[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]George IngleFinch1888Australia[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]Henry TreiseMorshead1882UK[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]John Baptist LuciusNoel1890UK[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]John GeoffreyBruce1896UK[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]LhakpaSherpaNepal[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]NorbuSherpaNepal[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]PasangSherpaNepal[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]PemaSherpaNepal[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]SangeSherpaNepal[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]TejbirBuraNepal[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]TembaSherpaNepal[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]Theodore HowardSomervell1890UK[/TD]

[TD="class: xl64"]5/1/1922[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl63"]Thomas GeorgeLongstaff1875UK[/TD]

[TD="class: xl64"]4/28/1924[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl63"]Andrew Comyn (Sandy)Irvine1902UK[/TD]

[TD="class: xl64"]4/28/1924[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl63"]BentleyBeetham1886UK[/TD]

[TD="class: xl64"]4/28/1924[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl63"]Edward FelixNorton1884UK[/TD]

[TD="class: xl64"]4/28/1924[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl63"]Edward OswaldShebbeare1884UK[/TD]

[TD="class: xl64"]4/28/1924[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl63"]George Herbert LeighMallory1886UK[/TD]

[TD="class: xl64"]4/28/1924[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl63"]John Baptist LuciusNoel1890UK[/TD]

[TD="class: xl64"]4/28/1924[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl63"]John de VereHazard1885UK[/TD]

[TD="class: xl64"]4/28/1924[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl63"]John GeoffreyBruce1896UK[/TD]

[TD="class: xl64"]4/28/1924[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl63"]Man BahadurNepal[/TD]

[TD="class: xl64"]4/28/1924[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl63"]Noel EwartOdell1890UK[/TD]

[TD="class: xl64"]4/28/1924[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl63"]Richard William GeorgeHingston1887UK[/TD]

[TD="class: xl64"]4/28/1924[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl63"]ShamsherPunNepal[/TD]

[TD="class: xl64"]4/28/1924[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl63"]Theodore HowardSomervell1890UK[/TD]

</tbody>
 
Upvote 0
I just copied the latest sample set, and it worked fine. My best guess is that you're using different columns. If the above data is in Columns A-C, then
=PrevStudents(B2,C2)

is OK. The first value (B2) should be the group column, and the second value (C2) should be the name. If your data is in columns B-D, then you should change the formula to:
=PrevStudents(C2,D2)

When I get the column references off, I get the #VALUE error too. Try that, and let me know if it works.
 
Upvote 0
Thank you again!!! I got it to work by changing

"Dim MyGroups(1000) As String"

to
"Dim MyGroups(10000) As String"

I assume the number in parentheses here refers to the max number of groups? The reason I think I was getting the error is because I have more than 1000 groups in my data which is why I changed that value to 10000.

Thanks for writing the UDF - it's really helpful :-)
 
Upvote 0
Good catch! I meant to mention that, but you caught it.

Glad you got it working.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Median with multiple conditions and date
Replies
3
Views
61
Peter_SSs
Peter_SSs
R
  • Solved
Read through XML file
Replies
1
Views
438
rjtaylor
R
J
  • Solved
Formula to find score by counting numbers in a cell
Replies
8
Views
49
Scott Huish
Scott Huish
V
  • Solved
I need help with this sumif formula:
Replies
4
Views
144
valmir
V
R
  • Question Question
Looking for help w Excel formula
Replies
2
Views
257
Dave Patton
D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top