Merge 3 lists into 1

[ExcelRoy]

Hi,

I have 3 different lists that can vary in rows dependant on formulas an I am looking to have one list with the contents of the first list, then straight after that, the second list, the straight after the third list

Each list has

Column A, ID number
Column B, Name

Column E, ID number
Column F, Name

Column I, ID number
Column J, Name

Complete list being in M and N

Many thanks

 

[Eric W]

Here's one way:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	ID	Name			ID	Name			ID	Name			ID	Name	0
2	1	Al			4	Debbie			6	Frank			1	Al	3
3	2	Bob			5	Erica			7	Gayle			2	Bob	5
4	3	Cal							8	Hank			3	Cal	9
5									9	Iris			4	Debbie
6													5	Erica
7													6	Frank
8													7	Gayle
9													8	Hank
10													9	Iris
11

<tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula M2 =IFERROR(INDEX((A$2:A$10,E$2:E$10,I$2:I$10),ROWS($M$2:$M2)-LOOKUP(ROWS($M$2:$M2)-1,$O$1:$O$4),1,MATCH(ROWS($M$2:$M2)-1,$O$1:$O$4)),"") O2 =ROWS($A$2:$A$10)-COUNTBLANK($A$2:$A$10)+O1 O3 =ROWS($E$2:$E$10)-COUNTBLANK($E$2:$E$10)+O2 O4 =ROWS($I$2:$I$10)-COUNTBLANK($I$2:$I$10)+O3 <tbody> </tbody>

<tbody>
</tbody>

To keep the formula from getting too big, I made a helper column in O. Put 0 in O1, then put the formulas in O2:O4. These are the number of entries in each column. Once those are in place, you can put the formula in M2, copy to N2, then copy down the column as needed.

 

[ExcelRoy]

Hi Eric W, That is just amazing, how do you do it!

Many thanks for your help!