Combining same ID from multiple rows into single row

%3CLINGO-SUB%20id%3D%22lingo-sub-3301709%22%20slang%3D%22en-US%22%3ECombining%20same%20ID%20from%20multiple%20rows%20into%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301709%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20pretty%20substantial%20data%20set%20that%20resembles%20the%20following%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3E%3CSTRONG%3EID%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E%3CSTRONG%3ECity%26nbsp%3B%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E%3CSTRONG%3EState%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E%3CSTRONG%3ERace%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E%3CSTRONG%3EGraduation%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E%3CSTRONG%3ECollege%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E%3CSTRONG%3EEnroll_Began%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E%3CSTRONG%3EEnroll_End%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E%3CSTRONG%3EClass_Level%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E%3CSTRONG%3EMajor%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3E12345%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3ECincinatti%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EOH%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EWhite%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E2015%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EVanderbilt%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E08%2F15%2F2015%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E12%2F15%2F2015%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EFreshman%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EElectrical%20Engineering%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3E12345%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3ECincinatti%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EOH%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EWhite%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E2015%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EVanderbilt%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E01%2F15%2F2016%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E05%2F15%2F2016%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EFreshman%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EElectrical%20Engineering%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3E12345%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3ECincinatti%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EOH%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EWhite%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E2015%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EOhio%20State%20University%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E08%2F15%2F2016%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E12%2F15%2F2016%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3ESophomore%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EElectrical%20Engineering%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3E12345%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3ECincinatti%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EOH%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EWhite%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E2015%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EOhio%20State%20University%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E01%2F15%2F2017%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E05%2F15%2F2017%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3ESophomore%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EElectrical%20Engineering%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3E67890%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3ESt.%20Louis%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EMO%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EAsian%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E2016%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EUniversity%20of%20Missouri%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E08%2F15%2F2016%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E12%2F15%2F2016%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EFreshman%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EArt%20History%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3E67890%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3ESt.%20Louis%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EMO%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EAsian%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E2016%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EUniversity%20of%20Missouri%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E01%2F15%2F2017%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E05%2F15%2F2017%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EFreshman%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EArt%20History%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3E67890%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3ESt.%20Louis%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EMO%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EAsian%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E2016%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EUniversity%20of%20Missouri%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E08%2F15%2F2017%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E12%2F15%2F2017%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3ESophomore%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EBiology%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3E67890%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3ESt.%20Louis%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EMO%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EAsian%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E2016%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EUniversity%20of%20Missouri%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E01%2F15%2F2018%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E05%2F15%2F2018%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3ESophomore%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EBiology%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2210%25%22%3E67890%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3ESt.%20Louis%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EMO%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EAsian%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E2016%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EUniversity%20of%20Missouri%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E08%2F15%2F2018%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3E12%2F15%2F2018%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EJunior%3C%2FTD%3E%3CTD%20width%3D%2210%25%22%3EBiology%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%2C%20I%20have%20multiple%20rows%20for%20one%20individual.%20There%20are%20pieces%20of%20information%20that%20will%20remain%20the%20same%20in%20each%20row%20with%20that%20ID%2C%20but%20there%20are%20also%20parts%20that%20may%20differ%20(such%20as%20College%2C%20Beginning%20and%20End%20Dates%2C%20Class%20Level%2C%20and%20Major).%20In%20these%20examples%20I%20included%20someone%20who%20changed%20from%20one%20college%20to%20another%20(12345)%2C%20and%20another%20individual%20who%20changed%20their%20major%20(67890).%20My%20goal%20is%20to%20get%20all%20of%20this%20information%20into%20a%20single%20row%20per%20ID.%20I%20only%20want%20to%20have%20the%20static%20pieces%20of%20information%20to%20be%20shown%20once%2C%20but%20then%20I%20want%20a%20new%20column%20for%20each%20new%20piece%20of%20information.%20Does%20anyone%20have%20any%20idea%20how%20to%20go%20about%20this%20in%20Excel%3F%20I%20am%20suspecting%20that%20it%20may%20be%20too%20complex%20for%20Excel%20and%20I%20will%20have%20to%20go%20to%20code%2C%20but%20any%20feedback%20would%20be%20helpful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3301709%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302450%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20same%20ID%20from%20multiple%20rows%20into%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302450%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20I%20do%20appreciate%20your%20input%20and%20recognize%20the%20need%20to%20see%20data%20overtime%20as%20an%20individual%20row%2C%20the%20current%20goal%20for%20this%20project%20is%20to%20identify%20an%20individual's%20first%20enrollment%20in%20a%20post-secondary%20institution.%20All%20of%20the%20original%20data%20will%20be%20retained.%20After%20identifying%20an%20individual's%20first%20enrollment%2C%20we%20are%20going%20to%20be%20doing%20things%20that%20include%20frequencies%20for%20m%2Ff%20and%20race%2Fethnicity%20for%202%20and%204-year%20institutions%2C%20as%20well%20as%20frequencies%20of%20STEM%20majors%20by%20demographic%20breakdown.%20The%20organization%20we%20are%20working%20with%20is%20tracking%20students%20after%20completion%20of%20a%20high%20school%20program%2C%20and%20at%20this%20moment%20in%20time%20they%20only%20want%20to%20have%20information%20on%20the%20very%20first%20post-secondary%20enrollment%20that%20students%20of%20their%20program%20have.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3301822%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20same%20ID%20from%20multiple%20rows%20into%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1273596%22%20target%3D%22_blank%22%3E%40cma123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20a%20person%20who%20was%20at%20one%20time%20(I'm%20retired%20now%20nearly%20two%20decades)%20the%20director%20of%20the%20HR%2FPayroll%20database%20of%20a%20major%20corporation%2C%20I%20have%20to%20ask%20you%20to%20step%20back%20and%20consider%20whether%20you%20really%20want%20to%20do%20this.%20What%20you%20currently%20have%20could%20be%20described%20as%20an%20accurate%20history%20of%20these%20students'%20academic%20life%2C%20including%20the%20dates%20when%20changes%20were%20made.%20History%20is%20better%20kept%20as%20a%20single%20row%20per%20transaction%20or%20per%20status%20change.%20You'd%20be%20potentially%20messing%20up%20that%20history%20by%20getting%20everything%20for%20a%20given%20student%20into%20a%20single%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20unless%20there's%20a%20compelling%20reason%20for%20doing%20that%20(and%20as%20you%20might%20guess%2C%20I'm%20skeptical%20about%20any%20such%20reason)%2C%20%3CU%3EI%20think%20you'd%20be%20better%20served%20leaving%20the%20history%20as%20it%20is%2C%20looking%20for%20ways%20to%20create%20a%20dashboard%20where%20you%20can%20see%20the%20history%20of%20any%20given%20individual%2C%20school%20changes%2C%20major%20changes%2C%20degrees%20accomplished%2C%20etc.%26nbsp%3B%3C%2FU%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3311251%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20same%20ID%20from%20multiple%20rows%20into%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3311251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1273596%22%20target%3D%22_blank%22%3E%40cma123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20wrote%3A%3C%2FP%3E%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CEM%3EThe%20organization%20we%20are%20working%20with%20is%20tracking%20students%20after%20completion%20of%20a%20high%20school%20program%2C%20and%20%3CU%3Eat%20this%20moment%20in%20time%20they%20%3CFONT%20color%3D%22%23DF0000%22%3E%3CSTRONG%3Eonly%3C%2FSTRONG%3E%20%3C%2FFONT%3Ewant%20to%20have%20information%20on%20the%20very%20first%20post-secondary%20enrollment%20that%20students%20of%20their%20program%20have%3C%2FU%3E.%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESo%20here's%20a%20way%20to%20get%2C%20very%20specifically%2C%20at%20that%20last.%26nbsp%3B%3C%2FSPAN%3EThank%20you%20for%20a%20specific%20goal%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EMy%20formula%20sifts%20through%20your%20database%20to%20get%20the%20first%20enrollment%20of%20each%20student%2C%20and%20does%20so%20primarily%20using%20the%20FILTER%20function%2C%20first%20to%20get%20the%20earliest%20enrollment%20date%20for%26nbsp%3Beach%20student%2C%20then%20to%20get%20the%20rest%20of%20that%20row%20for%20each%20student%2C%20showing%20the%20institution%20and%20major%20associated%20with%20each%20student's%20first%20enrollment%20date.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20formula%20looks%20like%20this%3A%3C%2FSPAN%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DFILTER(Table1%5B%5BCity%20%5D%3A%5BMajor%5D%5D%2C(Table1%5BID%5D%3D'First%20Enrollment'!A2)*(Table1%5BEnroll_Began%5D%3DMIN(FILTER(Table1%5BEnroll_Began%5D%2CTable1%5BID%5D%3D'First%20Enrollment'!A2))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20the%20sample%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3311834%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20same%20ID%20from%20multiple%20rows%20into%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3311834%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1273596%22%20target%3D%22_blank%22%3E%40cma123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20if%20you%20like%20shorter%20formulas%2C%20I%20was%20able%20to%20make%20it%20a%20bit%20shorter%20using%20the%20LET%20function%20to%20eliminate%20a%20couple%20of%20redundancies.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DLET(Iden%2CTable1%5BID%5D%3D'First%20Enrollment'!A2%2CFiEn%2CTable1%5BEnroll_Began%5D%2CFILTER(Table1%5B%5BCity%20%5D%3A%5BMajor%5D%5D%2CIden*(FiEn%3DMIN(FILTER(FiEn%2CIden)))))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi there, 

 

I have a pretty substantial data set that resembles the following: 

 

IDCity StateRaceGraduationCollegeEnroll_BeganEnroll_EndClass_LevelMajor
12345CincinattiOHWhite2015Vanderbilt08/15/201512/15/2015FreshmanElectrical Engineering
12345CincinattiOHWhite2015Vanderbilt01/15/201605/15/2016FreshmanElectrical Engineering
12345CincinattiOHWhite2015Ohio State University08/15/201612/15/2016SophomoreElectrical Engineering
12345CincinattiOHWhite2015Ohio State University01/15/201705/15/2017SophomoreElectrical Engineering
67890St. LouisMOAsian2016University of Missouri08/15/201612/15/2016FreshmanArt History
67890St. LouisMOAsian2016University of Missouri01/15/201705/15/2017FreshmanArt History
67890St. LouisMOAsian2016University of Missouri08/15/201712/15/2017SophomoreBiology
67890St. LouisMOAsian2016University of Missouri01/15/201805/15/2018SophomoreBiology
67890St. LouisMOAsian2016University of Missouri08/15/201812/15/2018JuniorBiology

 

As you can see, I have multiple rows for one individual. There are pieces of information that will remain the same in each row with that ID, but there are also parts that may differ (such as College, Beginning and End Dates, Class Level, and Major). In these examples I included someone who changed from one college to another (12345), and another individual who changed their major (67890). My goal is to get all of this information into a single row per ID. I only want to have the static pieces of information to be shown once, but then I want a new column for each new piece of information. Does anyone have any idea how to go about this in Excel? I am suspecting that it may be too complex for Excel and I will have to go to code, but any feedback would be helpful.

 

Thank you!

4 Replies

@cma123 

 

As a person who was at one time (I'm retired now nearly two decades) the director of the HR/Payroll database of a major corporation, I have to ask you to step back and consider whether you really want to do this. What you currently have could be described as an accurate history of these students' academic life, including the dates when changes were made. History is better kept as a single row per transaction or per status change. You'd be potentially messing up that history by getting everything for a given student into a single row.

 

So unless there's a compelling reason for doing that (and as you might guess, I'm skeptical about any such reason), I think you'd be better served leaving the history as it is, looking for ways to create a dashboard where you can see the history of any given individual, school changes, major changes, degrees accomplished, etc. 

@mathetes 

While I do appreciate your input and recognize the need to see data overtime as an individual row, the current goal for this project is to identify an individual's first enrollment in a post-secondary institution. All of the original data will be retained. After identifying an individual's first enrollment, we are going to be doing things that include frequencies for m/f and race/ethnicity for 2 and 4-year institutions, as well as frequencies of STEM majors by demographic breakdown. The organization we are working with is tracking students after completion of a high school program, and at this moment in time they only want to have information on the very first post-secondary enrollment that students of their program have. 

@cma123 

 

You wrote:

The organization we are working with is tracking students after completion of a high school program, and at this moment in time they only want to have information on the very first post-secondary enrollment that students of their program have

 

So here's a way to get, very specifically, at that last. Thank you for a specific goal to achieve.

 

My formula sifts through your database to get the first enrollment of each student, and does so primarily using the FILTER function, first to get the earliest enrollment date for each student, then to get the rest of that row for each student, showing the institution and major associated with each student's first enrollment date.

 

The formula looks like this:

=FILTER(Table1[[City ]:[Major]],(Table1[ID]='First Enrollment'!A2)*(Table1[Enroll_Began]=MIN(FILTER(Table1[Enroll_Began],Table1[ID]='First Enrollment'!A2))))

 

I've attached the sample spreadsheet.

 

@cma123 

 

And if you like shorter formulas, I was able to make it a bit shorter using the LET function to eliminate a couple of redundancies.

=LET(Iden,Table1[ID]='First Enrollment'!A2,FiEn,Table1[Enroll_Began],FILTER(Table1[[City ]:[Major]],Iden*(FiEn=MIN(FILTER(FiEn,Iden)))))