Find value on two sheets and copy in correct order on master list

Hi danfullwood,
.
. The solution i have done for you is somewhat longer and more complicated then it needs to be because
.
. a) It helps me see what is going on
. b) I am learning by answering these sort of Threads and wanted to try out a new idea.
.
. Depending on your feedback i can simplify, modify , correct as necessary.
. The code appears to work with your sample data, and I expect with no, or minor, modification it should work on bigger files with lots more sheets and data.
.
. I assume for now, headings are already there..
.
. So , assuming you have this to start with ..

Using Excel 2007

Row\Col	A	B	C	D
2
3	order	forename	surname	age
4
5

master list

... and for example data similar to what you gave :


Using Excel 2007

Row\Col	A	B	C	D
2
3	order	forename	surname	age
4	2	adam	smith	11
5	16	hayley	smith	25
6	3	hayley	smith	12
7	4	jacob	smith	13
8	5	hayley	smith	14
9	17	jacob	smith	26
10	18	josh	smith	27
11	19	dan	smith	28
12	1	dan	smith	10
13	20	hayley	smith	29
14	21	jacob	smith	30
15

daniel

Using Excel 2007

Row\Col	A	B	C	D
2
3	order	forename	surname	age
4	7	dan	smith	16
5	8	hayley	smith	17
6	10	josh	smith	19
7	11	hayley	smith	20
8	12	jacob	smith	21
9	13	josh	smith	22
10	6	dan	smith	15
11	14	lewis	smith	23
12	15	dan	smith	24
13	22	josh	smith	31
14	9	jacob	smith	18
15	23	dan	smith	32
16	24	lewis	smith	33
17

josh

......
. Then after running the code, ( Given in the next Post ( Post # 3 ) due to Post size limitations ), you achieve this

Using Excel 2007

Row\Col	A	B	C	D
3	order	forename	surname	age
4	1	dan	smith	10
5	2	adam	smith	11
6	3	hayley	smith	12
7	4	jacob	smith	13
8	5	hayley	smith	14
9	6	dan	smith	15
10	7	dan	smith	16
11	8	hayley	smith	17
12	9	jacob	smith	18
13	10	josh	smith	19
14	11	hayley	smith	20
15	12	jacob	smith	21
16	13	josh	smith	22
17	14	lewis	smith	23
18	15	dan	smith	24
19	16	hayley	smith	25
20	17	jacob	smith	26
21	18	josh	smith	27
22	19	dan	smith	28
23	20	hayley	smith	29
24	21	jacob	smith	30
25	22	josh	smith	31
26	23	dan	smith	32
27	24	lewis	smith	33

master list

...
Please let me know how you get on

Alan

Code for post # 2

Please Login or Register  to view this content.

Brief Description of code:

. – A 1 dimension “Mega” Array is made of size equal to the number of data sheets ( excluding the main sheet )
. – The Array elements of this Array are themselves Arrays. Each of these Arrays contain the data for a sheet
. – An output Array is made equal in “row” size to the total number of data rows in all data sheets
. – The output Array is then filled with its “rows” in the appropriate order.
. – Finally the output Array is pasted out to the master list in one go.