Merge 3 lists into 1

E

ExcelRoy

Well-known Member
Joined
Oct 2, 2006
Messages
2,540
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Here's one way:

ABCDEFGHIJKLMNOP
IDNameIDNameIDNameIDName
AlDebbieFrankAl
Bob 7 2
CalHankCal
5IrisDebbie
Erica
Frank
Gayle
Hank
Iris

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]Erica[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]Gayle[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]Bob[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M2[/TH]
[TD="align: left"]=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)),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O2[/TH]
[TD="align: left"]=ROWS($A$2:$A$10)-COUNTBLANK($A$2:$A$10)+O1[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O3[/TH]
[TD="align: left"]=ROWS($E$2:$E$10)-COUNTBLANK($E$2:$E$10)+O2[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O4[/TH]
[TD="align: left"]=ROWS($I$2:$I$10)-COUNTBLANK($I$2:$I$10)+O3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
Hi Eric W, That is just amazing, how do you do it!

Many thanks for your help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
How to use If Statement or Vlookup when comparing lists of names
2
Replies
13
Views
475
Peter_SSs
Peter_SSs
D
  • Solved
Using Vlookup, If statements??
Replies
3
Views
228
Dprobasco
D
L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
210
Lux Aeterna
L
T
  • Solved
How can I filter duplicates from a worksheet column out of a data validation list drop-down box?
Replies
5
Views
241
Peter_SSs
Peter_SSs
T
  • Question Question
Moving address list to columns from rows
Replies
3
Views
96
Sufiyan97
Sufiyan97

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top