TOCOL() and VLOOKUP() help needed

[realtoast]

I have a large table (smaller sample here), there I have an Employee ID, Employee Name, and Dependent ID and Dependent Name, all on the same row. There are instances where there are more than one dependent, where subsequent dependents are on an additional row that also includes another instance of the Employee ID and Employee Name. My challenge: I need to have each individual in their own row. In other words, move that first dependent down to their own row. The Employee ID will also show on each row to bind dependents to their respective employee.

I figure I can use TOCOL() to create a dynamic list of unique IDs, each in their own respect row. It seems that VLOOKUP() would be the next step to put names adjacent to their unique IDs in the new table, but I am failing at this. Do you have a different solution, or the proper way to do this with VLOOKUP()?

Thanks.

EEID	DEPID	NAME	DEPENDENT NAME		TOCOL()	How?
5388Y8M8		Smith, Steven D			5388Y8M8	=VLOOKUP(BF4,Census34[[EEID]:[DEPID]],3,FALSE)​
Y92Q22339		Jones, Marcas A			Y92Q22339	#REF!​
Y553QQ3A4	2M	King,Timothy	Raymond Stephen King		Y553QQ3A4	#REF!​
Y553QQ3A4	Y78QM786	King,Timothy	Jenny King		2M
Y553QQ3A4	QY8398970	King,Timothy	Chandra King		Y553QQ3A4
YAM844958		Klien, Maisa			Y78QM786	#N/A​
MAA323A88		Burrows, Charles E			Y553QQ3A4	#REF!​
5AM33QYM8		Kling, Kevin W			QY8398970	#N/A​
83883A5M		Mount, Meghan			YAM844958	#REF!​
Q33Y2839A		Turley, Bushra			MAA323A88	#REF!​
MA59M9MQ5	MA3QAA439	Bond, Jacqueline S	David Bond		5AM33QYM8	#REF!​
239Q3YA54		Parsley, Eric A			83883A5M	#REF!​
55MM95988		Campbell, James			Q33Y2839A	#REF!​
22A538Q54		Wilson, Annabelle M			MA59M9MQ5	#REF!​
MY33MAQ58	M2345M89M	Terry, Janee	John Terry, Jr.		MA3QAA439	#N/A​
84AMMYQM		Chen, Ming			239Q3YA54	#REF!​
2Y5459Q4A		Chen, Xi			55MM95988	#REF!​
2Q28454YM		Chen, Ma			22A538Q54	#REF!​
MYMA2A5A4	8YM35QMY2	Compton, Nimrah	Muhammad Compton		MY33MAQ58	#REF!​
					M2345M89M
					84AMMYQM
					2Y5459Q4A
					2Q28454YM
					MYMA2A5A4
					8YM35QMY2

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	EEID	DEPID	NAME	DEPENDENT NAME
2	5388Y8M8		Smith, Steven D			5388Y8M8	Smith, Steven D
3	Y92Q22339		Jones, Marcas A			Y92Q22339	Jones, Marcas A
4	Y553QQ3A4	2M	King,Timothy	Raymond Stephen King		Y553QQ3A4	King,Timothy
5	Y553QQ3A4	Y78QM786	King,Timothy	Jenny King		2M	Raymond Stephen King
6	Y553QQ3A4	QY8398970	King,Timothy	Chandra King		Y553QQ3A4	King,Timothy
7	YAM844958		Klien, Maisa			Y78QM786	Jenny King
8	MAA323A88		Burrows, Charles E			Y553QQ3A4	King,Timothy
9	5AM33QYM8		Kling, Kevin W			QY8398970	Chandra King
10	83883A5M		Mount, Meghan			YAM844958	Klien, Maisa
11	Q33Y2839A		Turley, Bushra			MAA323A88	Burrows, Charles E
12	MA59M9MQ5	MA3QAA439	Bond, Jacqueline S	David Bond		5AM33QYM8	Kling, Kevin W
13	239Q3YA54		Parsley, Eric A			83883A5M	Mount, Meghan
14	55MM95988		Campbell, James			Q33Y2839A	Turley, Bushra
15	22A538Q54		Wilson, Annabelle M			MA59M9MQ5	Bond, Jacqueline S
16	MY33MAQ58	M2345M89M	Terry, Janee	John Terry, Jr.		MA3QAA439	David Bond
17	84AMMYQM		Chen, Ming			239Q3YA54	Parsley, Eric A
18	2Y5459Q4A		Chen, Xi			55MM95988	Campbell, James
19	2Q28454YM		Chen, Ma			22A538Q54	Wilson, Annabelle M
20	MYMA2A5A4	8YM35QMY2	Compton, Nimrah	Muhammad Compton		MY33MAQ58	Terry, Janee
21						M2345M89M	John Terry, Jr.
22						84AMMYQM	Chen, Ming
23						2Y5459Q4A	Chen, Xi
24						2Q28454YM	Chen, Ma
25						MYMA2A5A4	Compton, Nimrah
26						8YM35QMY2	Muhammad Compton
Sheet6

Cell Formulas
Range	Formula
F2:G26	F2	=WRAPROWS(TOCOL(CHOOSECOLS(A2:D23,1,3,2,4),1),2)
Dynamic array formulas.

 

[realtoast]

BAM! Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.