Help getting the data from other file

janu319

New Member
Joined
Feb 11, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hallo,

Hope someone can help me with this tricky question(at least tricky for me). I need to get the information from one excel to another (I can combine them into one file if that makes it easy) following certain conditions.

  • I must assign a “Volunteer_Group”(column-F) and “Volunteer ID”(column-G) if the “Manual_Operation” is set to “Yes” in the file “Rooms_Booths _list” from another list in the excel file “Volunteer_list”.
  • I need to assign a minimum of 4 “Volunteer_ID”s (from a Volunteer_Group) to each Room_Name. This means, if a Room_name has Zero, 1, 2 or 3 instances of Manual_Operation= Yes, then I must assign(or say reserve) 4 “Volunteer_ID”s. Does not have to be in sequential order.
  • I can assign a Volunteer_Group to a maximum of 4 continuous Room_Names.
  • I cannot assign more than one Volunteer_Group to a Room_Name.
I have added the sample data as mini sheets.

I tried to count the number of instances of YES, per each room_name based on the rule any instances less than 4 is counted as 4 and rest is actual value but I could not be able to get the data from other file.
I tried to CONCATENATE the columns Volunteer_Group and Volunteer_ID with a special character in-between to make it one column and want to use the Text to columns option later to make it two columns again, but I could not able to figure out how I can jump from one Volunteer_Group to other Volunteer_Group.

Any help or ideas are much appreciated.

Rooms_Booths _list.xlsx
ABCDEFG
1City_CodeBooth_NoRoom_nameBoxManual_Operation Volunteer_GroupVolunteer_ID
233451A1Yes3345_VL011
333451A2No
433451A3No
533451A4Yes3345_VL012
633451A5No
733451A6No
833451A7No
933451A8No
1033451A9No
1133451A10No
1233451A11No
1333451A12No
1433451A13No
1533451A14No
1633451A15Yes3345_VL013
1733451A16No
1833451A17No
1933451A18No
2033451A19No
2133451A20No
2233451A21No
2333451A22No
2433451A23No
2533451A24No
2633451B1No
2733451B2No
2833451B3No
2933451B4No
3033451B5No
3133451B6No
3233451B7Yes3345_VL015
3333451B8Yes3345_VL016
3433451B9Yes3345_VL017
3533451B10Yes3345_VL018
3633451B11Yes3345_VL019
3733451B12No
3833451B13No
3933451B14No
4033451B15No
4133451B16No
4233451B17No
4333451B18No
4433451B19No
4533451B20No
4633451B21No
4733451B22No
4833451B23No
4933451B24No
5033451C1No
5133451C2No
5233451C3No
5333451C4No
5433451C5No
5533451C6No
5633451C7No
5733451C8No
5833451C9No
5933451C10No
6033451C11No
6133451C12No
6233451C13No
6333451C14No
6433451C15No
6533451C16No
6633451C17No
6733451C18No
6833451C19No
6933451C20No
7033451C21No
7133451C22No
7233451C23No
7333451C24No
7433451D1Yes3345_VL021
7533451D2Yes3345_VL022
7633451D3Yes3345_VL023
7733451D4Yes3345_VL024
7833451D5Yes3345_VL025
7933451D6Yes3345_VL026
8033451D7Yes3345_VL027
8133451D8Yes3345_VL028
8233451D9Yes3345_VL029
8333451D10Yes3345_VL0210
8433451D11Yes3345_VL0211
8533451D12Yes3345_VL0212
8633451D13Yes3345_VL0213
8733451D14Yes3345_VL0214
8833451D15Yes3345_VL0215
8933451D16Yes3345_VL0216
9033451D17Yes3345_VL0217
9133451D18Yes3345_VL0218
9233451D19Yes3345_VL0219
9333451D20Yes3345_VL0220
9433451D21Yes3345_VL0221
9533451D22Yes3345_VL0222
9633451D23Yes3345_VL0223
9733451D24Yes3345_VL0224
9833452A1No
9933452A2No
10033452A3No
10133452A4No
10233452A5No
10333452A6No
10433452A7No
10533452A8No
10633452A9No
10733452A10Yes3345_VL021
10833452A11Yes
10933452A12Yes
11033452A13Yes
11133452A14Yes
11233452A15Yes
11333452A16Yes
11433452A17Yes
11533452A18No
11633452A19No
11733452A20No
11833452A21No
11933452A22Yes
12033452A23Yes
12133452A24Yes
12233452B1No
12333452B2No
12433452B3Yes
12533452B4Yes
12633452B5Yes
12733452B6Yes
12833452B7No
12933452B8No
13033452B9Yes
13133452B10No
13233452B11Yes
13333452B12No
13433452B13No
13533452B14Yes
13633452B15No
13733452B16Yes
13833452B17No
13933452B18No
14033452B19Yes
14133452B20No
14233452B21Yes
14333452B22No
14433452B23No
14533452B24Yes
14633452C1No
14733452C2Yes
14833452C3No
14933452C4No
15033452C5Yes
15133452C6No
15233452C7Yes
15333452C8No
15433452C9No
15533452C10Yes
15633452C11No
15733452C12Yes
15833452C13No
15933452C14No
16033452C15Yes
16133452C16No
16233452C17Yes
16333452C18No
16433452C19No
16533452C20Yes
16633452C21No
16733452C22Yes
16833452C23No
16933452C24No
17033452D1Yes
17133452D2No
17233452D3Yes
17333452D4No
17433452D5No
17533452D6Yes
17633452D7No
17733452D8Yes
17833452D9No
17933452D10Yes
18033452D11No
18133452D12No
18233452D13No
18333452D14No
18433452D15Yes
18533452D16No
18633452D17No
18733452D18No
18833452D19No
18933452D20No
19033452D21No
19133452D22No
19233452D23No
19333452D24No
19433453A1No
19533453A2Yes
19633453A3No
19733453A4Yes
19833453A5No
19933453A6No
20033453A7Yes
20133453A8No
20233453A9Yes
20333453A10No
20433453A11No
20533453A12Yes
20633453A13No
20733453A14Yes
20833453A15No
20933453A16No
21033453A17Yes
21133453A18No
21233453A19Yes
21333453A20No
21433453A21No
21533453A22Yes
21633453A23No
21733453A24Yes
21833453B1No
21933453B2No
22033453B3No
22133453B4No
22233453B5No
22333453B6No
22433453B7No
22533453B8No
22633453B9No
22733453B10No
22833453B11No
22933453B12No
23033453B13No
23133453B14No
23233453B15No
23333453B16No
23433453B17No
23533453B18Yes
23633453B19Yes
23733453B20Yes
23833453B21Yes
23933453B22Yes
24033453B23Yes
24133453B24Yes
24233453C1No
24333453C2Yes
24433453C3Yes
24533453C4No
24633453C5No
24733453C6No
24833453C7No
24933453C8No
25033453C9No
25133453C10No
25233453C11No
25333453C12No
25433453C13No
25533453C14No
25633453C15No
25733453C16Yes
25833453C17No
25933453C18No
26033453C19No
26133453C20No
26233453C21No
26333453C22No
26433453C23No
26533453C24No
26633453D1No
26733453D2No
26833453D3No
26933453D4No
27033453D5No
27133453D6No
27233453D7No
27333453D8No
27433453D9No
27533453D10No
27633453D11Yes
27733453D12No
27833453D13No
27933453D14No
28033453D15No
28133453D16No
28233453D17No
28333453D18No
28433453D19No
28533453D20No
28633453D21Yes
28733453D22Yes
28833453D23Yes
28933453D24Yes
29033454A1Yes
29133454A2No
29233454A3No
29333454A4No
29433454A5No
29533454A6No
29633454A7No
29733454A8No
29833454A9No
29933454A10No
30033454A11No
30133454A12No
30233454A13No
30333454A14No
30433454A15No
30533454A16No
30633454A17No
30733454A18No
30833454A19No
30933454A20No
31033454A21No
31133454A22No
31233454A23No
31333454A24No
31433454B1No
31533454B2No
31633454B3No
31733454B4No
31833454B5No
31933454B6No
32033454B7No
32133454B8No
32233454B9No
32333454B10No
32433454B11No
32533454B12No
32633454B13No
32733454B14No
32833454B15No
32933454B16No
33033454B17No
33133454B18No
33233454B19Yes
33333454B20Yes
33433454B21Yes
33533454B22Yes
33633454B23Yes
33733454B24No
33833454C1No
33933454C2No
34033454C3No
34133454C4No
34233454C5No
34333454C6No
34433454C7No
34533454C8Yes
34633454C9Yes
34733454C10No
34833454C11No
34933454C12Yes
35033454C13No
35133454C14No
35233454C15Yes
35333454C16Yes
35433454C17No
35533454C18No
35633454C19No
35733454C20No
35833454C21No
35933454C22No
36033454C23No
36133454C24No
36233454D1No
36333454D2No
36433454D3Yes
36533454D4Yes
36633454D5Yes
36733454D6Yes
36833454D7Yes
36933454D8Yes
37033454D9Yes
37133454D10Yes
37233454D11Yes
37333454D12Yes
37433454D13Yes
37533454D14No
37633454D15No
37733454D16No
37833454D17No
37933454D18No
38033454D19No
38133454D20No
38233454D21No
38333454D22No
38433454D23No
38533454D24No
Sheet1



Volunteer_list.xlsx
AB
1Volunteer_GroupVolunteer_ID
23345_VL011
33345_VL012
43345_VL013
53345_VL014
63345_VL015
73345_VL016
83345_VL017
93345_VL018
103345_VL019
113345_VL0110
123345_VL0111
133345_VL0112
143345_VL0113
153345_VL0114
163345_VL0115
173345_VL0116
183345_VL0117
193345_VL0118
203345_VL0119
213345_VL0120
223345_VL0121
233345_VL0122
243345_VL0123
253345_VL0124
263345_VL021
273345_VL022
283345_VL023
293345_VL024
303345_VL025
313345_VL026
323345_VL027
333345_VL028
343345_VL029
353345_VL0210
363345_VL0211
373345_VL0212
383345_VL0213
393345_VL0214
403345_VL0215
413345_VL0216
423345_VL0217
433345_VL0218
443345_VL0219
453345_VL0220
463345_VL0221
473345_VL0222
483345_VL0223
493345_VL0224
503345_VL031
513345_VL032
523345_VL033
533345_VL034
543345_VL035
553345_VL036
563345_VL037
573345_VL038
583345_VL039
593345_VL0310
603345_VL0311
613345_VL0312
623345_VL0313
633345_VL0314
643345_VL0315
653345_VL0316
663345_VL0317
673345_VL0318
683345_VL0319
693345_VL0320
703345_VL0321
713345_VL0322
723345_VL0323
733345_VL0324
743345_VL041
753345_VL042
763345_VL043
773345_VL044
783345_VL045
793345_VL046
803345_VL047
813345_VL048
823345_VL049
833345_VL0410
843345_VL0411
853345_VL0412
863345_VL0413
873345_VL0414
883345_VL0415
893345_VL0416
903345_VL0417
913345_VL0418
923345_VL0419
933345_VL0420
943345_VL0421
953345_VL0422
963345_VL0423
973345_VL0424
983345_VL051
993345_VL052
1003345_VL053
1013345_VL054
1023345_VL055
1033345_VL056
1043345_VL057
1053345_VL058
1063345_VL059
1073345_VL0510
1083345_VL0511
1093345_VL0512
1103345_VL0513
1113345_VL0514
1123345_VL0515
1133345_VL0516
1143345_VL0517
1153345_VL0518
1163345_VL0519
1173345_VL0520
1183345_VL0521
1193345_VL0522
1203345_VL0523
1213345_VL0524
1223345_VL061
1233345_VL062
1243345_VL063
1253345_VL064
1263345_VL065
1273345_VL066
1283345_VL067
1293345_VL068
1303345_VL069
1313345_VL0610
1323345_VL0611
1333345_VL0612
1343345_VL0613
1353345_VL0614
1363345_VL0615
1373345_VL0616
1383345_VL0617
1393345_VL0618
1403345_VL0619
1413345_VL0620
1423345_VL0621
1433345_VL0622
1443345_VL0623
1453345_VL0624
1463345_VL071
1473345_VL072
1483345_VL073
1493345_VL074
1503345_VL075
1513345_VL076
1523345_VL077
1533345_VL078
1543345_VL079
1553345_VL0710
1563345_VL0711
1573345_VL0712
1583345_VL0713
1593345_VL0714
1603345_VL0715
1613345_VL0716
1623345_VL0717
1633345_VL0718
1643345_VL0719
1653345_VL0720
1663345_VL0721
1673345_VL0722
1683345_VL0723
1693345_VL0724
1703345_VL081
1713345_VL082
1723345_VL083
1733345_VL084
1743345_VL085
1753345_VL086
1763345_VL087
1773345_VL088
1783345_VL089
1793345_VL0810
1803345_VL0811
1813345_VL0812
1823345_VL0813
1833345_VL0814
1843345_VL0815
1853345_VL0816
1863345_VL0817
1873345_VL0818
1883345_VL0819
1893345_VL0820
1903345_VL0821
1913345_VL0822
1923345_VL0823
1933345_VL0824
1943345_VL091
1953345_VL092
1963345_VL093
1973345_VL094
1983345_VL095
1993345_VL096
2003345_VL097
2013345_VL098
2023345_VL099
2033345_VL0910
2043345_VL0911
2053345_VL0912
2063345_VL0913
2073345_VL0914
2083345_VL0915
2093345_VL0916
2103345_VL0917
2113345_VL0918
2123345_VL0919
2133345_VL0920
2143345_VL0921
2153345_VL0922
2163345_VL0923
2173345_VL0924
2183345_VL101
2193345_VL102
2203345_VL103
2213345_VL104
2223345_VL105
2233345_VL106
2243345_VL107
2253345_VL108
2263345_VL109
2273345_VL1010
2283345_VL1011
2293345_VL1012
2303345_VL1013
2313345_VL1014
2323345_VL1015
2333345_VL1016
2343345_VL1017
2353345_VL1018
2363345_VL1019
2373345_VL1020
2383345_VL1021
2393345_VL1022
2403345_VL1023
2413345_VL1024
2423345_VL111
2433345_VL112
2443345_VL113
2453345_VL114
2463345_VL115
2473345_VL116
2483345_VL117
2493345_VL118
2503345_VL119
2513345_VL1110
2523345_VL1111
2533345_VL1112
2543345_VL1113
2553345_VL1114
2563345_VL1115
2573345_VL1116
2583345_VL1117
2593345_VL1118
2603345_VL1119
2613345_VL1120
2623345_VL1121
2633345_VL1122
2643345_VL1123
2653345_VL1124
2663345_VL121
2673345_VL122
2683345_VL123
2693345_VL124
2703345_VL125
2713345_VL126
2723345_VL127
2733345_VL128
2743345_VL129
2753345_VL1210
2763345_VL1211
2773345_VL1212
2783345_VL1213
2793345_VL1214
2803345_VL1215
2813345_VL1216
2823345_VL1217
2833345_VL1218
2843345_VL1219
2853345_VL1220
2863345_VL1221
2873345_VL1222
2883345_VL1223
2893345_VL1224
Sheet1



Thanks in advance for your time.

Kind regards,
Janu
 

Attachments

  • Scenarios.PNG
    Scenarios.PNG
    43.4 KB · Views: 25

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

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