If you dont want to/cant use VBA, then try this using 2 helper columns (I used I and J...

G
H
I
J
36
 Designator Box Serials
37
 1 KVZV, XNHM, QJFW, GFVN, KVZV, XNHM, QJFW, GFVN,
0
38
 1 PBMG, BXQD, LLRZ, CGJNL, KVZV, XNHM, QJFW, GFVN, PBMG, BXQD, LLRZ, CGJNL,
0
39
 2 ZDTN, WPLS, MZGC, HKDT, KVZV, XNHM, QJFW, GFVN, PBMG, BXQD, LLRZ, CGJNL, 0 ZDTN, WPLS, MZGC, HKDT,
40
 1 QFXB, BMVV, VGHF, VXPK, KVZV, XNHM, QJFW, GFVN, PBMG, BXQD, LLRZ, CGJNL, QFXB, BMVV, VGHF, VXPK, 0 ZDTN, WPLS, MZGC, HKDT,
41
 2 ZFTN, RCJD, DCFV, GXRX, KVZV, XNHM, QJFW, GFVN, PBMG, BXQD, LLRZ, CGJNL, QFXB, BMVV, VGHF, VXPK, 0 ZDTN, WPLS, MZGC, HKDT, ZFTN, RCJD, DCFV, GXRX,
42
 1 SXDX, CPSM, KXTK, PHCX, KVZV, XNHM, QJFW, GFVN, PBMG, BXQD, LLRZ, CGJNL, QFXB, BMVV, VGHF, VXPK, SXDX, CPSM, KXTK, PHCX, 0 ZDTN, WPLS, MZGC, HKDT, ZFTN, RCJD, DCFV, GXRX,
43
 2 QVJT, LWCK, TRWL, LPZQ, KVZV, XNHM, QJFW, GFVN, PBMG, BXQD, LLRZ, CGJNL, QFXB, BMVV, VGHF, VXPK, SXDX, CPSM, KXTK, PHCX, 0 ZDTN, WPLS, MZGC, HKDT, ZFTN, RCJD, DCFV, GXRX, QVJT, LWCK, TRWL, LPZQ,
44
 1 PTRX, CPKD, LXJC, KTNV, KVZV, XNHM, QJFW, GFVN, PBMG, BXQD, LLRZ, CGJNL, QFXB, BMVV, VGHF, VXPK, SXDX, CPSM, KXTK, PHCX, PTRX, CPKD, LXJC, KTNV, 0 ZDTN, WPLS, MZGC, HKDT, ZFTN, RCJD, DCFV, GXRX, QVJT, LWCK, TRWL, LPZQ,


I37=IF(G37=1,I36&" "&H37,I36)
J37=IF(G37=2,J36&" "&H37,J36)

both copied down. You would then reference the last entry in each column for your answer