Fetching user IDs

[Jonam711]

Hello - I have base data were there are 2 columns, A - list of services which are handled by employees. B - emp IDs. one employee may have multiple services. Now I have another sheet were I have only the services and I need to get the emp IDs in a row.

 

[awoohaw]

assuming services and employee lists are on Worksheet1!A2:B200 , and the services without IDs listed on Worksheet2 starting at Worksheet2!A2.
AND
There is no duplication of services on worksheet1

In cell Worksheet2!b2 put this formula

=VLOOKUP(A2,Worksheet1!$A$1:$B$200,2,0)

other similar Functions you can use are INDEX-Match, and XLOOKUP.
and copy down.

 

[Jonam711]

assuming services and employee lists are on Worksheet1!A2:B200 , and the services without IDs listed on Worksheet2 starting at Worksheet2!A2.
AND
There is no duplication of services on worksheet1

In cell Worksheet2!b2 put this formula

=VLOOKUP(A2,Worksheet1!$A$1:$B$200,2,0)

other similar Functions you can use are INDEX-Match, and XLOOKUP.
and copy down.

Thank you mate but worksheet 1 services have duplicates that is one employee can have multiple services and vice versa.

 

[awoohaw]

So, I'm confused. How do you know that which combo of AA E10001, AA E10002 is to be matched with AA on Worksheet2? How do you do it in your head?
Or maybe my example was inaccurate. Why don't you post some data and expectations?

Mr. Excel has a tool that allows the forum to post mini worksheets. It is called xl2bb add in (link below). If you cannot use that, then please post as a copyable TABLE, not an image.

Thanks.

 

[Jonam711]

So, I'm confused. How do you know that which combo of AA E10001, AA E10002 is to be matched with AA on Worksheet2? How do you do it in your head?
Or maybe my example was inaccurate. Why don't you post some data and expectations?

Mr. Excel has a tool that allows the forum to post mini worksheets. It is called xl2bb add in (link below). If you cannot use that, then please post as a copyable TABLE, not an image.

Thanks.

Sorry for the confusion mate, let me put differently say worksheet 1, column A is the services- A1 & A2 are Bloomberg share market A3 Verizon north data. Their respective employee column B, B1 E1001 B2 E1002 B3 E1003. in worksheet 2 we have column A services- A1 Bloomberg share market, A2 Verizon north data. Now we need to pull employees E1001 & E1002 in B1 as they both have Bloomberg, B2 only E1003

 

[AhoyNC]

Maybe use the FILTER function.

Sheet1

Book1
	A	B
1	Services	ID
2	A	200
3	A	45
4	B	60
5	C	45
6	D	62
7	B	78
8	R	75
9	T	200
10	G	60
11	B	400
12	C	55
Sheet1

Sheet2

Book1
	A	B	C	D	E	F	G	H	I
1	Services	A	B	C	D	R	T	G	X
2		200	60	45	62	75	200	60	No Record
3		45	78	55
4			400
Sheet2

Cell Formulas
Range	Formula
B2:B3,E2:I2,D2:D3,C2:C4	B2	=FILTER(Sheet1!$B$2:$B$12,Sheet1!$A$2:$A$12=B$1,"No Record")
Dynamic array formulas.

 

[awoohaw]

then maybe this:

Mr Excel Questions 71.xlsm
	A	B	C	D	E
1	Services	Employee
2	AA	G1892		AE	I1430
3	AB	C1436		AJ	F1109
4	AC	A1983		AO	H1984
5	AD	G1909		AT	B1437,F1395
6	AE	I1430		AY	G1967,C1720
7	AF	I1138		BD	F1314,E1790
8	AG	E1194		BI	I1430
9	AH	I1163		CC	F1270
10	AI	A1315		CH	C1942
11	AJ	F1109
12	AK	F1395
13	AL	E1334
14	AM	I1059
15	AN	B1108
16	AO	H1984
17	AP	C1720
18	AQ	C1983
19	AR	C1577
20	AS	E1051
21	AT	B1437
22	AU	E1790
23	AV	B1795
24	AW	C1820
25	AX	F1286
26	AY	G1967
27	AZ	J1581
28	BA	G1626
29	BB	H1996
30	BC	F1698
31	BD	F1314
32	BE	G1892
33	BF	C1436
34	BG	A1983
35	BH	G1909
36	BI	I1430
37	BJ	I1138
38	BK	E1194
39	AQ	I1163
40	AR	A1315
41	AS	F1109
42	AT	F1395
43	AU	E1334
44	AV	I1059
45	AW	B1108
46	AX	H1984
47	AY	C1720
48	AZ	C1983
49	BA	C1577
50	BB	E1051
51	BC	B1437
52	BD	E1790
53	BE	G1645
54	CA	D1056
55	CB	C1114
56	CC	F1270
57	CD	C1164
58	CE	H1192
59	CF	B1040
60	CG	F1870
61	CH	C1942
62	CI	B1819
63	CJ	G1625
64	CK	J1912
65	CL	A1436
66	CM	F1323
67	CN	F1201
68	CO	F1536
69	CP	E1857
70	CQ	J1297
71	CR	F1242
72	CS	E1905
73	CT	D1371
74	CU	F1867
75	CV	I1102
76	CW	J1781
77	CX	G1381
78	CY	G1476
79	CZ	I1845
80	DA	I1444
81	DB	H1295
82	DC	J1421
83	DD	D1541
84	DE	B1855
85	DF	E1567
86	DG	H1841
87	DH	J1088
88	DI	D1047
89	DJ	A1588
90	DK	I1819
91	DL	C1882
92	DM	E1607
93	DN	E1226
94	DO	F1009
95	DP	G1264
96	DQ	A1303
97	DR	F1258
98	DS	E1157
99	DT	G1347
100	DU	E1328
101	DV	D1893
102	DW	D1273
103	DX	B1044
104	DY	C1619
105	DZ	H1227
106	EA	I1469
107	EB	G1198
108	EC	E1012
109	ED	B1734
110	EE	A1715
111	EF	B1576
112	EG	D1128
113	EH	E1917
114	EI	J1225
115	EJ	C1904
116	EK	A1340
117	EL	J1013
118	EM	J1342
119	EN	F1458
120	EO	E1131
121	EP	H1560
122	EQ	H1811
123	ER	I1118
124	ES	C1354
125	ET	F1479
126	EU	H1834
127	EV	I1618
128	EW	B1020
129	EX	J1391
130	EY	J1308
131	EZ	E1239
132	FA	D1907
133	FB	J1685
134	FC	A1109
135	FD	I1559
136	FE	H1347
137	FF	E1776
138	FG	G1616
139	FH	J1379
140	FI	C1372
141	FJ	B1785
142	FK	E1308
143	FL	C1339
144	FM	C1585
145	FN	F1440
146	FO	F1585
147	FP	F1123
148	FQ	F1552
149	FR	B1202
150	FS	D1169
151	FT	F1644
152	FU	A1724
153	FV	D1378
154	FW	D1551
155	FX	E1951
156	FY	E1817
157	FZ	G1932
158	GA	B1352
159	GB	D1201
160	GC	D1920
161	GD	A1651
162	GE	D1894
163	GF	D1120
164	GG	F1585
165	GH	A1473
166	GI	G1839
167	GJ	A1462
168	GK	D1782
169	GL	C1513
170	GM	C1252
171	GN	G1506
172	GO	C1517
173	GP	F1240
174	GQ	B1719
175	GR	E1059
176	GS	I1879
177	GT	J1082
178	GU	E1723
179	GV	D1776
180	GW	I1076
181	GX	F1095
182	GY	A1471
183	GZ	D1724
184	HA	I1592
185	HB	G1149
186	HC	G1920
187	HD	C1247
188	HE	F1104
189	HF	A1733
190	HG	B1554
191	HH	E1958
192	HI	D1865
193	HJ	H1445
194	HK	F1949
195	HL	G1909
196	HM	H1324
197	HN	E1691
198	HO	I1635
199	HP	C1826
200	HQ	J1353
201
Jonam711

Cell Formulas
Range	Formula
E2:E10	E2	=TEXTJOIN(",",,TRANSPOSE(TAKE(FILTER($A$2:$B$200,$A$2:$A$200=D2),,-1)))

 

[Jonam711]

Maybe use the FILTER function.

Sheet1

Book1
	A	B
1	Services	ID
2	A	200
3	A	45
4	B	60
5	C	45
6	D	62
7	B	78
8	R	75
9	T	200
10	G	60
11	B	400
12	C	55
Sheet1

Sheet2

Book1
	A	B	C	D	E	F	G	H	I
1	Services	A	B	C	D	R	T	G	X
2		200	60	45	62	75	200	60	No Record
3		45	78	55
4			400
Sheet2

Cell Formulas
Range	Formula
B2:B3,E2:I2,D2:D3,C2:C4	B2	=FILTER(Sheet1!$B$2:$B$12,Sheet1!$A$2:$A$12=B$1,"No Record")
Dynamic array formulas.

 

[Jonam711]

then maybe this:

Mr Excel Questions 71.xlsm
	A	B	C	D	E
1	Services	Employee
2	AA	G1892		AE	I1430
3	AB	C1436		AJ	F1109
4	AC	A1983		AO	H1984
5	AD	G1909		AT	B1437,F1395
6	AE	I1430		AY	G1967,C1720
7	AF	I1138		BD	F1314,E1790
8	AG	E1194		BI	I1430
9	AH	I1163		CC	F1270
10	AI	A1315		CH	C1942
11	AJ	F1109
12	AK	F1395
13	AL	E1334
14	AM	I1059
15	AN	B1108
16	AO	H1984
17	AP	C1720
18	AQ	C1983
19	AR	C1577
20	AS	E1051
21	AT	B1437
22	AU	E1790
23	AV	B1795
24	AW	C1820
25	AX	F1286
26	AY	G1967
27	AZ	J1581
28	BA	G1626
29	BB	H1996
30	BC	F1698
31	BD	F1314
32	BE	G1892
33	BF	C1436
34	BG	A1983
35	BH	G1909
36	BI	I1430
37	BJ	I1138
38	BK	E1194
39	AQ	I1163
40	AR	A1315
41	AS	F1109
42	AT	F1395
43	AU	E1334
44	AV	I1059
45	AW	B1108
46	AX	H1984
47	AY	C1720
48	AZ	C1983
49	BA	C1577
50	BB	E1051
51	BC	B1437
52	BD	E1790
53	BE	G1645
54	CA	D1056
55	CB	C1114
56	CC	F1270
57	CD	C1164
58	CE	H1192
59	CF	B1040
60	CG	F1870
61	CH	C1942
62	CI	B1819
63	CJ	G1625
64	CK	J1912
65	CL	A1436
66	CM	F1323
67	CN	F1201
68	CO	F1536
69	CP	E1857
70	CQ	J1297
71	CR	F1242
72	CS	E1905
73	CT	D1371
74	CU	F1867
75	CV	I1102
76	CW	J1781
77	CX	G1381
78	CY	G1476
79	CZ	I1845
80	DA	I1444
81	DB	H1295
82	DC	J1421
83	DD	D1541
84	DE	B1855
85	DF	E1567
86	DG	H1841
87	DH	J1088
88	DI	D1047
89	DJ	A1588
90	DK	I1819
91	DL	C1882
92	DM	E1607
93	DN	E1226
94	DO	F1009
95	DP	G1264
96	DQ	A1303
97	DR	F1258
98	DS	E1157
99	DT	G1347
100	DU	E1328
101	DV	D1893
102	DW	D1273
103	DX	B1044
104	DY	C1619
105	DZ	H1227
106	EA	I1469
107	EB	G1198
108	EC	E1012
109	ED	B1734
110	EE	A1715
111	EF	B1576
112	EG	D1128
113	EH	E1917
114	EI	J1225
115	EJ	C1904
116	EK	A1340
117	EL	J1013
118	EM	J1342
119	EN	F1458
120	EO	E1131
121	EP	H1560
122	EQ	H1811
123	ER	I1118
124	ES	C1354
125	ET	F1479
126	EU	H1834
127	EV	I1618
128	EW	B1020
129	EX	J1391
130	EY	J1308
131	EZ	E1239
132	FA	D1907
133	FB	J1685
134	FC	A1109
135	FD	I1559
136	FE	H1347
137	FF	E1776
138	FG	G1616
139	FH	J1379
140	FI	C1372
141	FJ	B1785
142	FK	E1308
143	FL	C1339
144	FM	C1585
145	FN	F1440
146	FO	F1585
147	FP	F1123
148	FQ	F1552
149	FR	B1202
150	FS	D1169
151	FT	F1644
152	FU	A1724
153	FV	D1378
154	FW	D1551
155	FX	E1951
156	FY	E1817
157	FZ	G1932
158	GA	B1352
159	GB	D1201
160	GC	D1920
161	GD	A1651
162	GE	D1894
163	GF	D1120
164	GG	F1585
165	GH	A1473
166	GI	G1839
167	GJ	A1462
168	GK	D1782
169	GL	C1513
170	GM	C1252
171	GN	G1506
172	GO	C1517
173	GP	F1240
174	GQ	B1719
175	GR	E1059
176	GS	I1879
177	GT	J1082
178	GU	E1723
179	GV	D1776
180	GW	I1076
181	GX	F1095
182	GY	A1471
183	GZ	D1724
184	HA	I1592
185	HB	G1149
186	HC	G1920
187	HD	C1247
188	HE	F1104
189	HF	A1733
190	HG	B1554
191	HH	E1958
192	HI	D1865
193	HJ	H1445
194	HK	F1949
195	HL	G1909
196	HM	H1324
197	HN	E1691
198	HO	I1635
199	HP	C1826
200	HQ	J1353
201
Jonam711

Cell Formulas
Range	Formula
E2:E10	E2	=TEXTJOIN(",",,TRANSPOSE(TAKE(FILTER($A$2:$B$200,$A$2:$A$200=D2),,-1)))

Thank you so much mate that worked

 

[Jonam711]

T

Maybe use the FILTER function.

Sheet1

Book1
	A	B
1	Services	ID
2	A	200
3	A	45
4	B	60
5	C	45
6	D	62
7	B	78
8	R	75
9	T	200
10	G	60
11	B	400
12	C	55
Sheet1

Sheet2

Book1
	A	B	C	D	E	F	G	H	I
1	Services	A	B	C	D	R	T	G	X
2		200	60	45	62	75	200	60	No Record
3		45	78	55
4			400
Sheet2

Cell Formulas
Range	Formula
B2:B3,E2:I2,D2:D3,C2:C4	B2	=FILTER(Sheet1!$B$2:$B$12,Sheet1!$A$2:$A$12=B$1,"No Record")
Dynamic array formulas.

Thank you bro that helped perfect!