Create one list of names combining three lists

%3CLINGO-SUB%20id%3D%22lingo-sub-80221%22%20slang%3D%22en-US%22%3ECreate%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80221%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20am%20sure%20I%20have%20done%20this%20before%20but%20I%20can't%20find%20it%20now.%20%26nbsp%3BI%20have%20a%20spreadsheet%20with%20three%20lists.%20%26nbsp%3BI%20want%20to%20combine%20the%20lists%20into%20one.%20%26nbsp%3BOn%20the%20sheet%20attached%20I%20have%20three%20columns%20of%20names%20A%2C%20B%20and%20C.%20%26nbsp%3BIn%20D%2C%20I%20would%20like%20to%20list%20all%20the%20same%20names%20in%20one%20long%20list.%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-80221%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETips%20and%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352335%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352335%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22p1%22%3E%3DIF%3C%2FP%3E%3CP%20class%3D%22p1%22%3E(%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22s1%22%3EH4%3C%2FSPAN%3E%3D%22Bernal%20ms%22%2CNeff!C6%2C%3C%2FP%3E%3CP%20class%3D%22p1%22%3EIF%3CSPAN%20class%3D%22s2%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s1%22%3EH4%3C%2FSPAN%3E%3D%22Neff%20MS%22%2CConnally!C6%2C%3C%2FP%3E%3CP%20class%3D%22p1%22%3EIF%3CSPAN%20class%3D%22s3%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s1%22%3EH4%3C%2FSPAN%3E%3D%22Connally%20Ms%22%2CJefferson!C6%2C%3C%2FP%3E%3CP%20class%3D%22p1%22%3EIF%3CSPAN%20class%3D%22s4%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s1%22%3EH4%3C%2FSPAN%3E%3D%22Jefferson%20Ms%22%2CJordan!C6%2C0%3CSPAN%20class%3D%22s4%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22s3%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E)%3C%2FSPAN%3E)%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3EThe%20function%20above%20allows%20me%20to%20pull%20students%20from%20lists%20in%20the%20tabs%20across%20the%20bottom%20of%20the%20spreadsheet%20ensuring%20the%20function%20changes%20schools%20each%20time%2C%20but%20the%20problems%20is%20making%20sure%20I%20pull%20form%20the%20top%20of%20the%20directed%20lists%20each%20time%20the%20formula%20returns%20a%20hit%20for%20a%20school.%20I%20am%20not%20familiar%20enough%20with%20how%20to%20write%20a%20script%20using%20the%20%22index%20feature.%20Making%20the%20index%20function%20pull%20from%20a%20list%20until%20the%20list%20is%20exhausted.%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352309%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352309%22%20slang%3D%22en-US%22%3E%3CP%3ESergei%20I%20saw%20your%20script%20in%20the%20post%20above%20and%20was%20wondering%20if%20you%20could%20help%20derive%20a%20script%20that%20has%20been%20perplexing%20me%20for%20days%20now.%20I%20am%20trying%20to%20create%20teams%20for%20an%20upcoming%20function%20in%20my%20school%20district%20and%20need%20to%20set%20teams%20by%20two%20criteria%20a%20random%20number%20that%20has%20been%20assigned%20as%20a%20student%20ID%20and%20then%20make%20sure%20that%20a%20team%20doesn't%20have%20more%20than%20two%20team%20members%20from%20the%20same%20school.%20I%20need%20a%20script%20because%20the%20day%20of%20the%20event%20we%20will%20have%20students%20missing%20due%20to%20absence%20and%20will%20need%20to%20quickly%20rearrange%20the%20teams.%20I%20have%20excel%202010%20and%20can%20access%202013.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-82406%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-82406%22%20slang%3D%22en-US%22%3E%3CP%3EIan%2C%20one%20more%20modification.%20If%20you%20updated%20on%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DIFERROR(%0A%20%20%20INDEX(%0A%20%20%20%20%20%20OFFSET(%24B%243%2C0%2C0%2CCOUNTA(B%243%3A%24B%24100))%2C%0A%20%20%20%20%20%20%20%20%20ROWS(%24L%242%3A%24L2)%0A%20%20%20)%2C%0A%20%20%20%20%20%20IFERROR(%0A%20%20%20%20%20%20%20%20%20INDEX(%0A%20%20%20%20%20%20%20%20%20%20%20%20OFFSET(%24D%243%2C0%2C0%2CCOUNTA(D%243%3A%24D%24100))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20ROWS(%24L%242%3A%24L2)-IFERROR(COUNTIF(B%243%3A%24B%24100%2C%22%3F*%22)%2C0)%0A%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20IFERROR(%0A%20%20%20%20%20%20%20%20%20%20%20%20INDEX(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20OFFSET(%24G%243%2C0%2C0%2CCOUNTA(G%243%3A%24G%24100))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ROWS(%24L%242%3A%24L2)-IFERROR(COUNTIF(B%243%3A%24B%24100%2C%22%3F*%22)%2C0)-IFERROR(COUNTIF(D%243%3A%24D%24100%2C%22%3F*%22)%2C0)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20IFERROR(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20INDEX(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20OFFSET(%24J%243%2C0%2C0%2CCOUNTA(J%243%3A%24J%24100))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ROWS(%24L%242%3A%24L2)-IFERROR(COUNTIF(B%243%3A%24B%24100%2C%22%3F*%22)%2C0)-IFERROR(COUNTIF(D%243%3A%24D%24100%2C%22%3F*%22)%2C0)-IFERROR(COUNTIF(G%243%3A%24G%24100%2C%22%3F*%22)%2C0)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22%22%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20)%0A)%3C%2FPRE%3E%3CP%3Ei.e%20change%20COUNTA(range)%20on%20IFERROR(COUNTIF(range%2C%22%3F*%22))%2C%20but%20not%20within%20OFFSET%2C%20when%20you%20may%20delete%20any%20column(s)%20except%20first%20one.%20After%20deleting%20formula%20will%20look%20like%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DIFERROR(%0A%20%20%20INDEX(%0A%20%20%20%20%20%20OFFSET(%24B%243%2C0%2C0%2CCOUNTA(B%243%3A%24B%24100))%2C%0A%20%20%20%20%20%20%20%20%20ROWS(%24K%242%3A%24K2)%0A%20%20%20)%2C%0A%20%20%20%20%20%20IFERROR(%0A%20%20%20%20%20%20%20%20%20INDEX(%0A%20%20%20%20%20%20%20%20%20%20%20%20OFFSET(%24D%243%2C0%2C0%2CCOUNTA(D%243%3A%24D%24100))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20ROWS(%24K%242%3A%24K2)-IFERROR(COUNTIF(B%243%3A%24B%24100%2C%22%3F*%22)%2C0)%0A%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20IFERROR(%0A%20%20%20%20%20%20%20%20%20%20%20%20INDEX(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20OFFSET(%23REF!%2C0%2C0%2CCOUNTA(%23REF!))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ROWS(%24K%242%3A%24K2)-IFERROR(COUNTIF(B%243%3A%24B%24100%2C%22%3F*%22)%2C0)-IFERROR(COUNTIF(D%243%3A%24D%24100%2C%22%3F*%22)%2C0)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20IFERROR(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20INDEX(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20OFFSET(%24I%243%2C0%2C0%2CCOUNTA(I%243%3A%24I%24100))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ROWS(%24K%242%3A%24K2)-IFERROR(COUNTIF(B%243%3A%24B%24100%2C%22%3F*%22)%2C0)-IFERROR(COUNTIF(D%243%3A%24D%24100%2C%22%3F*%22)%2C0)-IFERROR(%3CSTRONG%3ECOUNTIF(%23REF!%2C%3C%2FSTRONG%3E%22%3F*%22)%2C0)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22%22%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20)%0A)%3C%2FPRE%3E%3CP%3Ebut%20IFERROR()%20in%20counting%20of%20rows%20numbers%20return%20correct%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20not%20COUNTA%20doesn't%20work%20here%20since%20COUNTA(%23REF!)%20always%20returns%201%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-82396%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-82396%22%20slang%3D%22en-US%22%3E%3CP%3EForgot%20the%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-82394%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-82394%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20such%20complex%20formulas%20better%20to%20use%20formatting%20-%20there%20were%20few%20misprints%20in%20your%20variant.%20This%20one%20shall%20work%3C%2FP%3E%3CPRE%3E%3DIFERROR(%0A%20%20%20INDEX(%0A%20%20%20%20%20%20OFFSET(%24B%243%2C0%2C0%2CCOUNTA(B%243%3A%24B%24100))%2C%0A%20%20%20%20%20%20%20%20%20ROWS(%24L%242%3A%24L2)%0A%20%20%20)%2C%0A%20%20%20%20%20%20IFERROR(%0A%20%20%20%20%20%20%20%20%20INDEX(%0A%20%20%20%20%20%20%20%20%20%20%20%20OFFSET(%24D%243%2C0%2C0%2CCOUNTA(D%243%3A%24D%24100))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20ROWS(%24L%242%3A%24L2)-COUNTA(B%243%3A%24B%24100)%0A%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20IFERROR(%0A%20%20%20%20%20%20%20%20%20%20%20%20INDEX(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20OFFSET(%24G%243%2C0%2C0%2CCOUNTA(G%243%3A%24G%24100))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ROWS(%24L%242%3A%24L2)-COUNTA(B%243%3A%24B%24100)-COUNTA(D%243%3A%24D%24100)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20IFERROR(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20INDEX(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20OFFSET(%24J%243%2C0%2C0%2CCOUNTA(J%243%3A%24J%24100))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ROWS(%24L%242%3A%24L2)-COUNTA(B%243%3A%24B%24100)-COUNTA(D%243%3A%24D%24100)-COUNTA(G%243%3A%24G%24100)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22%22%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20)%0A)%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20924px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F16461i59103DCE8DD3AF52%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22ListN.JPG%22%20title%3D%22ListN.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-82389%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-82389%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20attaced%20an%20example%20on%20sheet%201.%20%26nbsp%3BI%20have%20taken%20the%20%3D%20out%20of%20the%20beginning%20as%20I%20couldnt%20do%20it%20otherwise.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-82388%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-82388%22%20slang%3D%22en-US%22%3E%3CP%3EYes%20but%20I%20keep%20getting%20'You've%20entered%20too%20many%20arguments%20dfor%20this%20function'%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-82383%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-82383%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20you%20initial%20lists%20are%20in%20B%2C%20D%2C%20G%2C%20J%20and%20you%20put%20combined%20list%20into%20L%2C%20correct%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-82381%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-82381%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20tried%20the%20following%20formula%20to%20do%20four%20columns%20but%20Im%20guessing%20i%20am%20missing%20something%20as%20it%20doesnt%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(OFFSET(%24B%243%2C0%2C0%2CCOUNTA(B%243%3A%24B%24100))%2CROWS(%24L%242%3A%24L2))%2CIFERROR(INDEX(OFFSET(%24D%243%2C0%2C0%2CCOUNTA(D%243%3A%24D%24100))%2CROWS(%24L%242%3A%24L2)COUNTA(B%243%3A%24B%24100))%2CIFERROR(INDEX(OFFSET(%24G%243%2C0%2C0%2CCOUNTA(G%243%3A%24G%24100))%2CROWS(%24L%242%3A%24L2)COUNTA(B%243%3A%24B%24100)COUNTA(D%243%3A%24D%24100))%2CIFERROR(INDEX(OFFSET(%24J%243%2C0%2C0%2CCONTA(J%243%3A%24J%24100))%2CROWS(%24L%242%3A%24L2)COUNTA(B%243%3A%24B%24100)COUNTA(D%243%3A%24D%24100)-COUNTA(G%243%3A%24G%24100))%2C%22%22))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20actually%20dont%20need%20more%20than%20four%20so%20if%20i%20can%20get%20this%20to%20work%20ive%20cracked%20it%20i%20think.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%3C%2FP%3E%3CP%3EIan%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-82371%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-82371%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ian%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20guess%20so%2C%20will%20think%20how%20to%20automate%20on%20defined%20number%20of%20columns.%20Do%20you%20have%20any%20logic%20in%20how%20your%20lists%20are%20placed%2C%20e.g.%20every%20third%20column%20or%20what%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-82369%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-82369%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%3C%2FP%3E%3CP%3EIs%20there%20a%20mximum%20amount%20of%20arrangements%20I%20can%20fit%20into%20this%20formula%3F%20%26nbsp%3BShould%20I%20be%20able%20to%20use%20the%20formula%20for%20four%20columns%3F%20Or%20five%3F%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3EIan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-82366%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-82366%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ian%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20update.%20Yes%2C%20your%20formula%20is%20fine%20to%20exclude%20%22%22.%20Just%20in%20case%2C%20please%20take%20into%20account%20it%20works%20with%20texts%20only%20(but%20that's%20your%20case)%2C%20more%20universal%20will%20be%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DSUMPRODUCT(--(LEN(range)%26gt%3B0))%3C%2FPRE%3E%3CP%3Eif%20your%20list%20is%20the%20mix%20of%20texts%20and%20numbers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-82357%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-82357%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%3C%2FP%3E%3CP%3EIgnore%20my%20last%20message%20I%20have%20found%20a%20way%20that%20works.%20%26nbsp%3BRather%20than%20having%20COUNTA%20in%20the%20formula%20I%20have%20changed%20it%20COUNTIF%20and%20used%20%22%3F*%22%20as%20the%20criteria.%3C%2FP%3E%3CP%3EThank%20you%20for%20all%20your%20help%20on%20this%3C%2FP%3E%3CP%3EIan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-82349%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-82349%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%3C%2FP%3E%3CP%3EYou%20have%20done%20everything%20I%20have%20asked%20so%20far%2C%20thank%20you%20for%20that%2C%20but%20I%20still%20cannot%20get%20it%20to%20work%20in%20the%20actual%20sheet.%3C%2FP%3E%3CP%3EI%20think%20my%20problem%20is%20that%20the%20names%20in%20the%20list%20are%20not%20manually%20inputted%20but%20are%20pulled%20through%20from%20a%20formula%20so%20no%20cell%20in%20the%20list%20is%20really%20blank.%3C%2FP%3E%3CP%3EThe%20lists%20are%20made%20from%20a%20formula%20that%20matches%20two%20other%20lists%20and%20pulls%20out%20the%20same%20names.%20it%20looks%20like%20this%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(Maths!%24C%2418%3A%24C%2443%2CMATCH(0%2CCOUNTIF(%24G%242%3AG2%2CMaths!%24C%2418%3A%24C%2443)%2BIF(IF(COUNTIF(Maths!%24M%246%3A%24M%2417%2CMaths!%24C%2418%3A%24C%2443)%26gt%3B0%2C1%2C0)%3D1%2C0%2C1)%2C0))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20then%20want%20to%20use%20the%20lists%20made%20from%20the%20above%20formulas%20to%20make%20the%20lists%20we%20have%20been%20talking%20about.%20%26nbsp%3BI%20can%20make%20it%20work%20in%20a%20test%20sheet%20like%20the%20one%20we%20have%20been%20talking%20about%20but%20not%20where%20the%20formulas%20are.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20attach%20the%20actual%20sheet%20as%20there%20is%20lots%20of%20information%20on%20there.%20%26nbsp%3BDo%20you%20have%20any%20ideas%3F%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3EIan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-81188%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-81188%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ian%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20updated%20the%20formula%20a%20bit%20to%20make%20it%20more%20flexible.%20If%20you%20wand%20generate%20combined%20list%20starting%20from%20cell%20C3%20in%20second%20sheet%2C%20enter%20into%20this%20cell%20and%20when%20copy%20down%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DIFERROR(%0A%20%20%20INDEX(%0A%20%20%20%20%20%20OFFSET(Sheet1!%24A%242%2C0%2C0%2CCOUNTA(Sheet1!A%242%3A%24A%24100))%2C%0A%20%20%20%20%20%20ROWS(%24C%243%3A%24C3)%0A%20%20%20)%2C%0A%20%20%20IFERROR(%0A%20%20%20%20%20%20INDEX(%0A%20%20%20%20%20%20%20%20%20OFFSET(Sheet1!%24D%242%2C0%2C0%2CCOUNTA(Sheet1!D%242%3A%24D%24100))%2C%0A%20%20%20%20%20%20%20%20%20ROWS(%24C%243%3A%24C3)-COUNTA(Sheet1!A%242%3A%24A%24100)%0A%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20IFERROR(%0A%20%20%20%20%20%20%20%20%20INDEX(%0A%20%20%20%20%20%20%20%20%20%20%20%20OFFSET(Sheet1!%24H%242%2C0%2C0%2CCOUNTA(Sheet1!H%242%3A%24H%24100))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20ROWS(%24C%243%3A%24C3)-COUNTA(Sheet1!A%242%3A%24A%24100)-COUNTA(Sheet1!D%242%3A%24D%24100)%0A%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%22%22%0A%20%20%20%20%20%20)%0A%20%20%20)%0A)%3C%2FPRE%3E%3CP%3EIn%20ROWS()%20shall%20be%20starting%20cell%20of%20your%20list%20as%20above%2C%20it%20returns%20current%20position%20within%20combined%20list%20when%20copied%20down.%3C%2FP%3E%3CP%3EEach%20COUNTA()%20returns%20number%20of%20elements%20in%20your%20source%20list.%3C%2FP%3E%3CP%3EEach%20OFFSET()%20returns%20your%20source%20list.%20It%20is%20assumed%20there%20are%20no%20blank%20cells%20in%20the%20middle%20of%20the%20list.%3C%2FP%3E%3CP%3EFinally%20INDEX()%20return%20element%20of%20source%20list%20of%20current%20row%20is%20within%20the%20range%20of%20this%20list%2C%20otherwise%20error.%3C%2FP%3E%3CP%3EWith%20IFERROR()%20we%20are%20switching%20from%20one%20source%20list%20to%20next%20one%20and%20return%20empty%20cell%20if%20no%20more%20source%20lists.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course%20you%20may%20add%20some%20parameters%20into%20the%20cell(s)%20within%20your%20file%20not%20to%20keep%20all%20refrences%20within%20the%20formula%20and%20use%20them%20in%20formula%2C%20but%20that's%20another%20story.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20it%20works%20is%20in%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80989%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80989%22%20slang%3D%22en-US%22%3E%3CP%3EBy%20the%20way%2C%20about%20Power%20Query%20-%20if%20your%20are%20using%20Excel%20more%20or%20less%20regular%20it's%20worth%20to%20check%20at%20least%20basic%20things.%20It%20could%20be%20quite%20powerful%26nbsp%3Band%20simple%2C%20even%20without%20any%20coding.%20From%20this%20point%20of%20view%20Episode%202%20of%20Excel%20Table%20Talks%20on%20community%26nbsp%3Bhome%20page%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fct-p%2FExcel_Cat%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fct-p%2FExcel_Cat%3C%2FA%3E%20illustrates%20the%20approach%20quite%20good.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80987%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80987%22%20slang%3D%22en-US%22%3E%3CP%3EIan%2C%20that%20formula%20is%20better%20to%20consider%20as%20prototype.%20It%20assumes%20in%20in%20Sheet1%20you%20have%20empty%20columns%20between%20columns%20with%20your%20names%3B%20combine%20column%20in%20Sheet2%20starts%20from%20the%20same%20second%20row%20as%20lists%20in%20Sheet1%2C%20etc.%20It%20could%20be%20updated%20using%2C%20for%20example%2C%20OFFSET()%20and%2For%20some%20helper%20cells%2C%20some%20time%20is%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20current%20formula%20we%20compare%20row%20number%20in%20resulting%20list%20(taking%20into%20account%20above%20assumptions)%20with%20number%20of%20non-empty%20cells%20in%20colums%20with%20list%20of%20Sheet1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%3C%2FP%3E%3CPRE%3E%3DIF(ROW()%26lt%3B%3DCOUNTA(Sheet1!A%3AA)%2C%3C%2FPRE%3E%3CP%3Eif%20it%20is%20less%20than%20row%20number%20with%20first%20blank%20cell%2C%20we%20take%20names%20from%20first%20list.%20As%20soon%20as%20it%20is%20more%20we%20compare%20with%20total%20number%20of%20non-blank%20cells%20in%20columns%26nbsp%3Bfrom%20A%20to%20D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EIF(ROW()%26gt%3BCOUNTA(Sheet1!A%3AD)%3CSTRONG%3E-(4-1)%3C%2FSTRONG%3E%2C%3C%2FPRE%3E%3CP%3EHere%20we%20deduct%20number%20of%20non-blank%20cells%20in%20header%20row%20(first%20one)%20in%20above%26nbsp%3Bcolumns%2C%20i.e%20in%20A1%3AD1.%20That's%204.%20Minus%20one%20since%20we%20compare%20as%20%22more%20than%22%2C%20could%20be%20missed%20with%20%22equal%20or%20more%20than%22.%3C%2FP%3E%3CP%3EAfter%20that%20similar%26nbsp%3Bway%20compare%20row%20number%20in%20combined%20list%20in%20Sheet2%20with%20total%20number%20of%20non-blank%20cells%20in%20all%20columns%20from%20A%20to%20H%20excluding%20headers%20cells%3C%2FP%3E%3CPRE%3EIF(ROW()%26lt%3B%3D%20COUNTA(Sheet1!A%3AH)-%3CSTRONG%3E(8-1)%3C%2FSTRONG%3E%2C%3C%2FPRE%3E%3CP%3EIf%20less%20return%20third%20list%2C%20otherwise%20blanks.%20And%20if%20previous%20condition%20is%20not%20met%20return%20values%20from%20second%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80954%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80954%22%20slang%3D%22en-US%22%3EHi%20Ian%2C%3CBR%20%2F%3EOkay%2C%20I%20see.%20Will%20answer%20some%20later.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80944%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80944%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%3C%2FP%3E%3CP%3EI%20definitly%20prefer%20the%20formula%20as%20i%20dont%20really%20understand%20what%20is%20happening%20with%20the%20Query.%20%26nbsp%3BCan%20you%20tell%20me%20what%20the%20numbers%20in%20the%20brackets%20are%20for%20on%20the%20formula%20you%20sent%20me%20on%20the%20workbook%20with%20the%20two%20sheets%3F%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3EIan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80554%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80554%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ian%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20solution%20with%20PowerQuery%2C%20that's%20Excel%20tool%20which%20is%20most%20suitable%20for%20such%20tasks.%20If%20you%20are%20on%20Excel%202013%20is%20shall%20be%20downloaded%20and%20installed%20(fast%20and%20stright%20forward%20process)%2C%20in%20Excel%202016%20it's%20built-in%20(named%20as%20Get%20%26amp%3B%20Transform).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20know%20if%20you%20are%20familiar%20with%20it%2C%20here%20are%20step%20by%20step%20instructions%2C%20same%20is%20in%20attached%20file%20with%20screenshots.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E1)%20Select%20all%20columns%20with%20source%20lists%20and%20name%20them%20somehow%0A2)%20Ribbon-%26gt%3BData%20click%20on%20From%20Table%2FRange%0A3)%20In%20appeared%20Query%20Editor%20remove%20Change%20Types%20step%0A4)%20Select%20(with%20Ctrl)%20columns%20with%20your%20names%2C%20right%20click%20on%20any%20and%20Remove%20Other%20Columns%3A%0A5)%20In%20Ribbon%20click%20Use%20First%20Row%20as%20Header%0A6)%20Select%20all%20columns%20and%20in%20Ribbon-%26gt%3BTransform%20click%20Unpivot%20Columns%0A7)%20Select%20new%20column%20%22Attributes%22%2C%20Right%20click%20and%20Remove%0A8)%20Double%20click%20on%20%22Value%22%20column%20name%2C%20enter%20any%20name%20you%20want%2C%20Enter%0A9)%20In%20Ribbon%20select%20Close%20%26amp%3B%20Load%20To%20(it's%20disabled%20for%20the%20existing%20query%2C%20but%20available%0Afor%20new%20one)%2C%20after%20that%20select%20Table%2C%20Existing%20Sheet%2C%20and%20any%20place%20in%20your%20Sheet2%3C%2FPRE%3E%3CP%3EYou%20will%20have%20in%20Sheet2%20the%20table%20with%20combined%20list%20of%20names.%20If%20you%20make%20any%20changes%20in%20source%20data%20in%20Ribbon-%26gt%3BData%20click%20on%20Refresh%20All.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20still%20prefer%20formulas%20let%20us%20know%2C%20will%20update%20then.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80497%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80497%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ian%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVery%20fast%20modification%20of%20the%20formula%20is%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DIF(%0A%20ROW()%26lt%3B%3DCOUNTA(Sheet1!A%3AA)%2C%0A%20%20INDEX(Sheet1!A%3AA%2CROW())%2C%0A%20%20IF(ROW()%26gt%3BCOUNTA(Sheet1!A%3AD)-(4-1)%2C%0A%20%20%20IF(ROW()%26lt%3B%3D%20COUNTA(Sheet1!A%3AH)-(8-1)%2CINDEX(Sheet1!H%3AH%2CROW()-COUNTA(Sheet1!A%3AD)%2B4)%2C%22%22%20)%2C%0A%20%20%20INDEX(Sheet1!D%3AD%2CROW()-COUNTA(Sheet1!A%3AA)%2B1)%0A%20%20)%0A)%3C%2FPRE%3E%3CP%3Ebut%20above%20is%20very%20unflexible.%20Another%20approach%20could%20be%20using%20of%20Power%20Query.%3C%2FP%3E%3CP%3EI'll%20modify%20a%20bit%20some%20later%20to%20make%20the%20formula%20more%20human%20friendly%2C%20so%20far%20the%20result%20is%20in%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80476%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80476%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20the%20reply%20Sergei%2C%20however%20I%20think%20I%20have%20tried%20to%20be%20too%20clever.%20%26nbsp%3BI%20thought%20I%20would%20be%20able%20to%20change%20the%20formula%20to%20the%20exact%20specifications%20I%20was%20wanting%20but%20I%20dont%20seem%20to%20be%20able%20to%20do%20this.%20%26nbsp%3BThe%20list%20I%20need%20to%20make%20is%20on%20a%20different%20sheet%20and%20there%20are%20gaps%20between%20the%20columns%20on%20the%20first%20sheet.%20%26nbsp%3BI%20have%20atached%20a%20workbook%20that%20looks%20more%20like%20what%20I%20really%20need.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20three%20columns%20that%20have%20data%20in%20on%20the%20first%20sheet%20need%20combining%20in%20a%20column%20on%20the%20second%20sheet%20if%20that%20is%20possible%20please%3F%3C%2FP%3E%3CP%3ESorry%20for%20not%20being%20specific%20enough%20in%20the%20origional%20post.%3C%2FP%3E%3CP%3EIan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-80237%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20one%20list%20of%20names%20combining%20three%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-80237%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ian%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20formula%20starting%20from%20D1%20and%20copy%20it%20down%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DIF(%0A%20ROW()%26lt%3B%3DCOUNTA(A%3AA)%2C%0A%20%20INDEX(A%3AA%2CROW())%2C%0A%20%20IF(ROW()%26gt%3BCOUNTA(A%3AB)%2C%0A%20%20%20IF(ROW()%26lt%3B%3D%20COUNTA(A%3AC)%2CINDEX(C%3AC%2CROW()-COUNTA(A%3AB))%2C%22%22%20)%2C%0A%20%20%20INDEX(B%3AB%2CROW()-COUNTA(A%3AA))%0A%20%20)%0A)%0A%0A%3C%2FPRE%3E%3CP%3EFile%20is%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi

I am sure I have done this before but I can't find it now.  I have a spreadsheet with three lists.  I want to combine the lists into one.  On the sheet attached I have three columns of names A, B and C.  In D, I would like to list all the same names in one long list.

Thank you

23 Replies
Highlighted

Forgot the file

Highlighted

Ian, one more modification. If you updated on

 

 

=IFERROR(
   INDEX(
      OFFSET($B$3,0,0,COUNTA(B$3:$B$100)),
         ROWS($L$2:$L2)
   ),
      IFERROR(
         INDEX(
            OFFSET($D$3,0,0,COUNTA(D$3:$D$100)),
            ROWS($L$2:$L2)-IFERROR(COUNTIF(B$3:$B$100,"?*"),0)
         ),
         IFERROR(
            INDEX(
               OFFSET($G$3,0,0,COUNTA(G$3:$G$100)),
               ROWS($L$2:$L2)-IFERROR(COUNTIF(B$3:$B$100,"?*"),0)-IFERROR(COUNTIF(D$3:$D$100,"?*"),0)
            ),
            IFERROR(
               INDEX(
                  OFFSET($J$3,0,0,COUNTA(J$3:$J$100)),
                  ROWS($L$2:$L2)-IFERROR(COUNTIF(B$3:$B$100,"?*"),0)-IFERROR(COUNTIF(D$3:$D$100,"?*"),0)-IFERROR(COUNTIF(G$3:$G$100,"?*"),0)
               ),
               ""
            )
         )
      )
)

i.e change COUNTA(range) on IFERROR(COUNTIF(range,"?*")), but not within OFFSET, when you may delete any column(s) except first one. After deleting formula will look like

 

 

 

=IFERROR(
   INDEX(
      OFFSET($B$3,0,0,COUNTA(B$3:$B$100)),
         ROWS($K$2:$K2)
   ),
      IFERROR(
         INDEX(
            OFFSET($D$3,0,0,COUNTA(D$3:$D$100)),
            ROWS($K$2:$K2)-IFERROR(COUNTIF(B$3:$B$100,"?*"),0)
         ),
         IFERROR(
            INDEX(
               OFFSET(#REF!,0,0,COUNTA(#REF!)),
               ROWS($K$2:$K2)-IFERROR(COUNTIF(B$3:$B$100,"?*"),0)-IFERROR(COUNTIF(D$3:$D$100,"?*"),0)
            ),
            IFERROR(
               INDEX(
                  OFFSET($I$3,0,0,COUNTA(I$3:$I$100)),
                  ROWS($K$2:$K2)-IFERROR(COUNTIF(B$3:$B$100,"?*"),0)-IFERROR(COUNTIF(D$3:$D$100,"?*"),0)-IFERROR(COUNTIF(#REF!,"?*"),0)
               ),
               ""
            )
         )
      )
)

but IFERROR() in counting of rows numbers return correct result.

 

Please not COUNTA doesn't work here since COUNTA(#REF!) always returns 1

 

 

 

Highlighted

Sergei I saw your script in the post above and was wondering if you could help derive a script that has been perplexing me for days now. I am trying to create teams for an upcoming function in my school district and need to set teams by two criteria a random number that has been assigned as a student ID and then make sure that a team doesn't have more than two team members from the same school. I need a script because the day of the event we will have students missing due to absence and will need to quickly rearrange the teams. I have excel 2010 and can access 2013. 

Highlighted

=IF

(

H4="Bernal ms",Neff!C6,

IF(H4="Neff MS",Connally!C6,

IF(H4="Connally Ms",Jefferson!C6,

IF(H4="Jefferson Ms",Jordan!C6,0)

)))

 

The function above allows me to pull students from lists in the tabs across the bottom of the spreadsheet ensuring the function changes schools each time, but the problems is making sure I pull form the top of the directed lists each time the formula returns a hit for a school. I am not familiar enough with how to write a script using the "index feature. Making the index function pull from a list until the list is exhausted.