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

[ea2146]

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!!

Date	Group	Student	Number of Current Group Members Worked with Previously
2/10/2015	1	Susan	0
2/10/2015	1	Kayley	0
2/10/2015	1	Carol	0
5/15/2015	2	Kayley	1
5/15/2015	2	Carol	1
5/15/2015	2	Jeff	0
5/15/2015	2	Eric	0
5/15/2015	2	Clarissa	0
5/15/2015	2	Ashley	0
7/15/2015	3	Susan	2
7/15/2015	3	Kayley	3
7/15/2015	3	Carol	3
7/15/2015	3	Jeff	2
8/18/2015	4	Alice	0
8/18/2015	4	Drew	0
8/18/2015	4	James	0
8/18/2015	4	Carol	0
8/18/2015	4	John	0
9/22/2015	5	Susan	1
9/22/2015	5	Eric	2
9/22/2015	5	Ashley	2
9/22/2015	5	Carol	3
9/22/2015	5	Frank	0
9/22/2015	5	David	0
9/22/2015	5	Hannah	0
9/22/2015	5	Thomas	0

<tbody>
</tbody>

 

[ea2146]

Please let me know if anyone can offer any suggestions. Thanks!!!

 

[Eric W]

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:

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.

 

[ea2146]

Thank you SO MUCH, Eric W!!!!

I just tried this and it works perfectly! This is brilliant. I really appreciate the help!

 

[ea2146]

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!!!

bcdate	expid_CODE	Name
6/26/1921	1	Alexander Frederick RichmondWollaston1875UK
6/26/1921	1	Alexander MacMillanHeron1884UK
6/26/1921	1	Alexander MitchellKellas1868UK
6/26/1921	1	Ang PasangSherpaNepal
6/26/1921	1	Charles KennethHoward-Bury1883UK
6/26/1921	1	Edward OliverWheeler1890Canada
6/26/1921	1	GarongSherpaNepal
6/26/1921	1	George Herbert LeighMallory1886UK
6/26/1921	1	Guy HenryBullock1887UK
6/26/1921	1	Harold AndrewRaeburn1865UK
6/26/1921	1	Henry TreiseMorshead1882UK
6/26/1921	1	LagaySherpaNepal
6/26/1921	1	UnknownIndia
5/1/1922	2	Arthur WilliamWakefield1876UK
5/1/1922	2	Charles GranvilleBruce1866UK
5/1/1922	2	Charles JohnMorris1895UK
5/1/1922	2	ChettanSherpaNepal
5/1/1922	2	Colin Grant (Ferdie)Crawford1890UK
5/1/1922	2	DorjeSherpaNepal
5/1/1922	2	Edward FelixNorton1884UK
5/1/1922	2	Edward Lisle (Bill)Strutt1874UK
5/1/1922	2	George Herbert LeighMallory1886UK
5/1/1922	2	George IngleFinch1888Australia
5/1/1922	2	Henry TreiseMorshead1882UK
5/1/1922	2	John Baptist LuciusNoel1890UK
5/1/1922	2	John GeoffreyBruce1896UK
5/1/1922	2	LhakpaSherpaNepal
5/1/1922	2	NorbuSherpaNepal
5/1/1922	2	PasangSherpaNepal
5/1/1922	2	PemaSherpaNepal
5/1/1922	2	SangeSherpaNepal
5/1/1922	2	TejbirBuraNepal
5/1/1922	2	TembaSherpaNepal
5/1/1922	2	Theodore HowardSomervell1890UK
5/1/1922	2	Thomas GeorgeLongstaff1875UK
4/28/1924	3	Andrew Comyn (Sandy)Irvine1902UK
4/28/1924	3	BentleyBeetham1886UK
4/28/1924	3	Edward FelixNorton1884UK
4/28/1924	3	Edward OswaldShebbeare1884UK
4/28/1924	3	George Herbert LeighMallory1886UK
4/28/1924	3	John Baptist LuciusNoel1890UK
4/28/1924	3	John de VereHazard1885UK
4/28/1924	3	John GeoffreyBruce1896UK
4/28/1924	3	Man BahadurNepal
4/28/1924	3	Noel EwartOdell1890UK
4/28/1924	3	Richard William GeorgeHingston1887UK
4/28/1924	3	ShamsherPunNepal
4/28/1924	3	Theodore HowardSomervell1890UK

<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>
</tbody>

 

[Eric W]

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.

 

[ea2146]

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

 

[Eric W]

Good catch! I meant to mention that, but you caught it.

Glad you got it working.