Pull data from sheet to new sheet based on multiple criteria from another sheet in excel

[sallam Qasem]

Hi Everyone.

I am seeking your assistance with a task involving two Excel sheets that I am currently working on.

The first sheet contains general household data, including a column that indicates the number of family members for each household. On the other hand, the second sheet contains detailed household member data. However, I am facing a challenge as the second sheet has more family members than the number specified in the first sheet.

I am in need VBA code that can help me extract member data to a new sheet based on the number of family members indicated in the first sheet. This would greatly streamline my workflow and ensure that I have accurate and organized data.

The below attached is the two sheets with few households and family members. But, kindly note that number of rows for both sheets I'm working for each file are hundreds thousands rows.

I would greatly appreciate any guidance, suggestions, or assistance you can provide in helping me achieve this task.

Household Information 3.xlsx
	A	B
1	Household ID	Family Member
2	36808383	3
3	91108509	2
4	23138382	4
5	55018385	6
6	94548509	5
7	92598509	1
8	47958374	3
9	36938383	4
10	90258509	4
11	92048509	2
12	93028509	4
13	93118509	5
14	90288509	7
15	90418509	4
16	90598509	2
17	94938509	4
18	96788509	2
19	99888509	5
20	54728385	6
21	89928508	4
Sheet1

Household Information 3.xlsx
	A	B	C	D
1	Household ID	Age	Gender	Recipient Type
2	47958374	46	Female	Recipient 1
3	47958374	45	Male	Recipient 2
4	47958374	82	Female	Family Member
5	47958374	5	Female	Family Member
6	47958374	42	Female	Family Member
7	47958374	9	Female	Family Member
8	47958374	10	Male	Family Member
9	23138382	68	Male	Recipient 1
10	23138382	36	Male	Recipient 2
11	23138382	8	Female	Family Member
12	23138382	8	Male	Family Member
13	23138382	63	Female	Family Member
14	36808383	28	Male	Recipient 1
15	36808383	29	Female	Recipient 2
16	36808383	7	Female	Family Member
17	36808383	14	Male	Family Member
18	36808383	14	Female	Family Member
19	36808383	67	Female	Family Member
20	36938383	42	Male	Recipient 1
21	36938383	39	Female	Recipient 2
22	36938383	10	Male	Family Member
23	36938383	13	Male	Family Member
24	36938383	78	Male	Family Member
25	36938383	3	Female	Family Member
26	54728385	62	Male	Recipient 1
27	54728385	59	Female	Recipient 2
28	54728385	19	Female	Family Member
29	54728385	22	Female	Family Member
30	54728385	15	Female	Family Member
31	54728385	24	Female	Family Member
32	54728385	27	Female	Family Member
33	54728385	28	Male	Family Member
34	55018385	50	Male	Recipient 1
35	55018385	46	Female	Recipient 2
36	55018385	22	Female	Family Member
37	55018385	23	Female	Family Member
38	55018385	19	Male	Family Member
39	55018385	18	Male	Family Member
40	55018385	3	Male	Family Member
41	89928508	44	Female	Recipient 1
42	89928508	38	Male	Recipient 2
43	89928508	22	Female	Family Member
44	89928508	23	Female	Family Member
45	89928508	15	Male	Family Member
46	89928508	24	Male	Family Member
47	89928508	15	Female	Family Member
48	90258509	38	Female	Recipient 1
49	90258509	48	Male	Recipient 2
50	90258509	15	Female	Family Member
51	90258509	8	Male	Family Member
52	90258509	1	Female	Family Member
53	90258509	4	Female	Family Member
54	90288509	66	Male	Recipient 1
55	90288509	19	Male	Family Member
56	90288509	15	Female	Family Member
57	90288509	22	Female	Family Member
58	90288509	17	Male	Family Member
59	90288509	20	Female	Family Member
60	90288509	16	Male	Family Member
61	90288509	49	Female	Family Member
62	90418509	46	Male	Recipient 1
63	90418509	9	Female	Family Member
64	90418509	7	Male	Family Member
65	90418509	7	Male	Family Member
66	90418509	33	Female	Family Member
67	90418509	1	Female	Family Member
68	90418509	1	Female	Family Member
69	90418509	3	Female	Family Member
70	90598509	82	Female	Recipient 1
71	90598509	46	Male	Recipient 2
72	90598509	6	Male	Family Member
73	90598509	5	Male	Family Member
74	90598509	40	Female	Family Member
75	90598509	49	Female	Family Member
76	90598509	3	Male	Family Member
77	91108509	37	Male	Recipient 1
78	91108509	27	Male	Recipient 2
79	91108509	6	Female	Family Member
80	91108509	28	Female	Family Member
81	92048509	56	Female	Recipient 1
82	92048509	37	Male	Recipient 2
83	92048509	35	Female	Family Member
84	92048509	24	Female	Family Member
85	92598509	58	Male	Recipient 1
86	92598509	55	Female	Recipient 2
87	92598509	21	Male	Family Member
88	93028509	31	Male	Recipient 1
89	93028509	40	Male	Recipient 2
90	93028509	28	Female	Family Member
91	93028509	56	Female	Family Member
92	93028509	5	Female	Family Member
93	93028509	2	Male	Family Member
94	93028509	6	Female	Family Member
95	93028509	22	Female	Family Member
96	93118509	34	Male	Recipient 1
97	93118509	58	Male	Recipient 2
98	93118509	23	Male	Family Member
99	93118509	55	Female	Family Member
100	93118509	26	Male	Family Member
101	93118509	30	Female	Family Member
102	93118509	5	Female	Family Member
103	94548509	34	Male	Recipient 1
104	94548509	35	Female	Recipient 2
105	94548509	7	Female	Family Member
106	94548509	11	Female	Family Member
107	94548509	12	Male	Family Member
108	94548509	3	Female	Family Member
109	94938509	48	Male	Recipient 1
110	94938509	16	Female	Family Member
111	94938509	39	Female	Family Member
112	94938509	12	Male	Family Member
113	94938509	4	Female	Family Member
114	96788509	61	Male	Recipient 1
115	96788509	26	Male	Recipient 2
116	96788509	19	Male	Family Member
117	96788509	31	Male	Family Member
118	96788509	23	Female	Family Member
119	96788509	54	Female	Family Member
120	99888509	45	Female	Recipient 1
121	99888509	12	Female	Family Member
122	99888509	7	Male	Family Member
123	99888509	16	Female	Family Member
124	99888509	14	Female	Family Member
125	99888509	51	Male	Family Member
Sheet2

 

[alansidman]

What is the criteria for selecting the correct number of participants from the second table? How about showing us a mocked up solution based upon the data presented.

 

[sallam Qasem]

The criteria is Family member column in Sheet1. Both sheets have household IDs. For example, the first household in Sheet 1 consists of 4 family members. However, in Sheet 2, you will find that this same household has 5 members per rows. My goal is to extract data for any 4 members from the same household based on Family member column in Sheet1 that indicates the number of family members and ignore the remaining 1 member, repeating this process for the other households.

The below is a sample data with mocked up solution.

Household Information 3.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
9
10		Sheet1 "Household Data"			Sheet2 "Family member Data"					Sheet3 "Result"
11		Household ID	Family Member		Household ID	Age	Gender	Recipient Type		Household ID	Age	Gender	Recipient Type
12		23138382	4		23138382	36	Male	Recipient 2		23138382	36	Male	Recipient 2
13		36808383	3		23138382	68	Male	Recipient 1		23138382	68	Male	Recipient 1
14		55018385	6		23138382	8	Female	Family Member		23138382	8	Female	Family Member
15		91108509	2		23138382	8	Male	Family Member		23138382	8	Male	Family Member
16		94548509	5		23138382	63	Female	Family Member		36808383	29	Female	Recipient 2
17					36808383	29	Female	Recipient 2		36808383	28	Male	Recipient 1
18					36808383	28	Male	Recipient 1		36808383	7	Female	Family Member
19					36808383	7	Female	Family Member		55018385	46	Female	Recipient 2
20					36808383	14	Male	Family Member		55018385	50	Male	Recipient 1
21					36808383	14	Female	Family Member		55018385	22	Female	Family Member
22					36808383	67	Female	Family Member		55018385	23	Female	Family Member
23					55018385	46	Female	Recipient 2		55018385	19	Male	Family Member
24					55018385	50	Male	Recipient 1		55018385	18	Male	Family Member
25					55018385	22	Female	Family Member		91108509	27	Male	Recipient 2
26					55018385	23	Female	Family Member		91108509	37	Male	Recipient 1
27					55018385	19	Male	Family Member		94548509	35	Female	Recipient 2
28					55018385	18	Male	Family Member		94548509	34	Male	Recipient 1
29					55018385	3	Male	Family Member		94548509	7	Female	Family Member
30					91108509	27	Male	Recipient 2		94548509	11	Female	Family Member
31					91108509	37	Male	Recipient 1		94548509	12	Male	Family Member
32					91108509	6	Female	Family Member
33					91108509	28	Female	Family Member
34					94548509	35	Female	Recipient 2
35					94548509	34	Male	Recipient 1
36					94548509	7	Female	Family Member
37					94548509	11	Female	Family Member
38					94548509	12	Male	Family Member
39					94548509	3	Female	Family Member
40
41
Sheet3