INDEX MATCH or VLOOKUP?

[pvtjoker77]

I have been racking my brain on this one and can't find what I'm looking for to line up names and positions from one sheet to another and to add to the difficulty if there were duplicates with the same time and position that the 2nd entry for that time and position would go onto the TRN line of the position. (the highlighted one in the example below would go on the G2 TRN line)

So to set it up:

On Sheet1 I have a daily schedule lineup with times and positions to the corresponding cells to try and work with INDEX MATCH.

On Sheet2 I have the daily Start times, Positions, and Names that I would copy and paste from a downloadable csv file so the names and amount of positions are always changing.

Thank you in advance for any help!!

Book1
	A	B	C	D	E
1	Time	Position		Name
2				Tue May 3, 2022
3			Desk	xxx	Extension
4	0600 - 1430	Desk G1	REG XXX East		+0409
5	0600 - 1430	Desk G1	REG XXX East TRN		+0409
6	0600 - 1430	Desk G2	REG XXX Central		+0424
7	0600 - 1430	Desk G2	REG XXX Central TRN		+0424
8	600 - 1430	Desk G3	REG XXX West		+0403
9	0600 - 1430	Desk G3	REG XXX West TRN		+0403
10	0600 - 1430	Desk G4	XXX		+0423
11	0600 - 1430	Desk G4	XXX TRN		+0423
12	0600 - 1430	Desk S1	Sector 1		+0416
13	0600 - 1430	Desk S1	Sector 1 TRN		+0416
14	0600 - 1430	Desk S2	Sector 2		+0417
15	0600 - 1430	Desk S2	Sector 2 TRN		+0417
16	0600 - 1430	Desk S3	Sector 3		+0418
17	0600 - 1430	Desk S3	Sector 3 TRN		+0418
18	0600 - 1430	Desk S4	Sector 4		+0419
19	0600 - 1430	Desk S4	Sector 4 TRN		+0419
20	0700 - 1530	Desk S5	Sector 5		+0420
21	0700 - 1530	Desk S5	Sector 5 TRN		+0420
22	0700 - 1530	Desk S6	Sector 6		+0421
23	0700 - 1530	Desk S6	Sector 6 TRN		+0421
24	0700 - 1530	Desk R1	Router 1		+0413
25	0700 - 1530	Desk R1	Router 1 TRN		+0413
26	0700 - 1530	Desk R2	Router 2		+0414
27	0700 - 1530	Desk R2	Router 2 TRN		+0414
28
29			Desk	xxx	Extension
30	1400 - 2230	Desk G1	REG xxx East		+0409
31	1400 - 2230	Desk G1	REG xxx East TRN		+0409
32	1400 - 2230	Desk G2	REG central		+0424
33	1400 - 2230	Desk G2	REG SOD Central TRN		+0424
34	1400 - 2230	Desk G3	REGxxx West		+0403
35	1400 - 2230	Desk G3	REG SOD West TRN		+0403
36	1400 - 2230	Desk G4	xxx		+0423
37	1400 - 2230	Desk G4	xxxTRN		+0423
38	1400 - 2230	Desk S1	Sector 1		+0416
39	1400 - 2230	Desk S1	Sector1 TRN		+0416
40	1400 - 2230	Desk S2	Sector 2		+0417
41	1400 - 2230	Desk S2	Sector 2 TRN		+0417
Sheet1

Book1
	A	B	C
1	Time	Position	Name
2	0600 - 1430	Desk G1	Marvin
3	1400 - 2230	Desk G1	Kush
4	2200 - 0630	Desk G1	Chris
5	0600 - 1430	Desk G2	Spencer
6	0600 - 1430	Desk G2	Judd
7	1400 - 2230	Desk G2	Leian
8	0600 - 1430	Desk G3	David
9	1400 - 2230	Desk G3	David
10	0600 - 1430	Desk G4	Courtney
11	1400 - 2230	Desk G4	Anthony
12	0700 - 1530	Desk R1	Jeff
13	1500 - 2330	Desk R1	Rick
14	0700 - 1530	Desk R2	Luis
15	1500 - 2330	Desk R2	Ryan
16	0600 - 1430	Desk S1	Bill
17	1400 - 2230	Desk S1	Cathy
18	2200 - 0630	Desk S1	Cale
19	0600 - 1430	Desk S2	Bruce
20	1400 - 2230	Desk S2	Roger
21	2200 - 0630	Desk S2	James
22	0600 - 1430	Desk S3	Charles
23	1400 - 2230	Desk S3	Todd
24	0600 - 1430	Desk S4	John
25	1400 - 2230	Desk S4	John
26	0700 - 1530	Desk S5	Thom
27	1500 - 2330	Desk S5	Lucas
28	0700 - 1530	Desk S6	Paul
29	1500 - 2330	Desk S6	Andrew
Sheet2

 

[Fraser1989]

Had the same issue recently. Resolved it with a nested vlookup to prove that i wasn't in the wrong (=iferror(vlookup(A1,*named range*,1,false),"no issue)) first to prove it and didn't pick up what was needed. Index match resolved it.


*name your ranges doing a vlookup to cut out human error

 

[Fraser1989]

Or you could hide multiple cells and use the trim(right/left) function then do the Vlookup? Although that would only be worth the time if you were using it a lot and creating a template.

 

[pvtjoker77]

I have tried Helper columns of A&B and with IF & Vllokup but trying to take into account when there are not any Duplicates (someone Training) is what is tripping me up.
For example, in the above example cells, A5:B6 are currently duplicates but tomorrow the training person currently in A6:B6 is off tomorrow so cells A5:B5 will have 0600-1430 Desk G2 and Cells A6:B6 will have 1400-2230 Desk G2.

Thanks though

 

[pvtjoker77]

I have even tried IF statements, and as I was going along, I realized that All the main Cells (the ones without TRN people associated next to them) all could be 1 cell off. Not that there would be 10 people training at once,This is because we could have up to 4 or 5 on any given day.

I have tried it with helpers and sorting. I have even added formulas for duplicates. This is because, If there are duplicates of the same time and position, the bottom one of the duplicates is the TRN person.

Just an FYI Sheet1 Columns A, B & G are just Helpers I made to try and figure this out.
Also Sheet2 the Highlighted rows are to sow the duplicates and that the 2nd person is the person Training (TRN)

Thanks in advance

Book1.xlsx
	A	B	C	D	E	F	G	H
1	Time	Position		Name
2				Tue May 3, 2022
3			Desk	xxx	Extension
4	0600 - 1430	Desk G1	REG XXX East	Marvin	+0409		0600 - 1430 Desk G1
5	0600 - 1430	Desk G1	REG XXX East TRN		+0409		0600 - 1430 Desk G1
6	0600 - 1430	Desk G2	REG XXX Central	Spencer	+0424		0600 - 1430 Desk G2
7	0600 - 1430	Desk G2	REG XXX Central TRN	Judd	+0424		0600 - 1430 Desk G2
8	0600 - 1430	Desk G3	REG XXX West	David	+0403		0600 - 1430 Desk G3
9	0600 - 1430	Desk G3	REG XXX West TRN		+0403		0600 - 1430 Desk G3
10	0600 - 1430	Desk G4	XXX	Courtney	+0423		0600 - 1430 Desk G4
11	0600 - 1430	Desk G4	XXX TRN		+0423		0600 - 1430 Desk G4
12	0600 - 1430	Desk S1	Sector 1		+0416		0600 - 1430 Desk S1
13	0600 - 1430	Desk S1	Sector 1 TRN		+0416		0600 - 1430 Desk S1
14	0600 - 1430	Desk S2	Sector 2		+0417		0600 - 1430 Desk S2
15	0600 - 1430	Desk S2	Sector 2 TRN		+0417		0600 - 1430 Desk S2
Sheet1

Cell Formulas
Range	Formula
D4	D4	=Sheet2!D2
D5	D5	=IF(Sheet2!E3=2,Sheet2!D3,"")
D6,D8	D6	=IF(Sheet2!E3=1,Sheet2!D3,"")
D7,D9	D7	=IF(Sheet2!E4=2,Sheet2!D4,"")
D10	D10	=IF(Sheet2!E6=1,Sheet2!D6,"")
D11	D11	=IF(Sheet2!E7=2,Sheet2!D7,"")
G4:G15	G4	=A4&""&B4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:D27	Expression	=FIND("TRN",C4:C61)	text	NO
C10	Expression	=$F$31="open"	text	NO
E10	Expression	=$F$31="open"	text	NO
E12	Expression	=$F$21="open"	text	NO
C6	Expression	=$F$17="open"	text	NO
C4	Expression	=$F$15="open"	text	NO
E4	Expression	=$F$15="open"	text	NO
E6	Expression	=$F$17="open"	text	NO
C8	Expression	=$F$19="open"	text	NO
C14	Expression	=$F$23="open"	text	NO
E14	Expression	=$F$23="open"	text	NO
E8	Expression	=$F$19="open"	text	NO
D4:D27	Cell Value	contains "open"	text	NO
C4:E4,D5:D27	Cell Value	contains "Open"	text	NO

Book1.xlsx
	A	B	C	D	E
1	Time	Position		Name
2	0600 - 1430	Desk G1	0600 - 1430 Desk G1	Marvin
3	0600 - 1430	Desk G2	0600 - 1430 Desk G2	Spencer	1
4	0600 - 1430	Desk G2	0600 - 1430 Desk G2	Judd	2
5	0600 - 1430	Desk G3	0600 - 1430 Desk G3	David	1
6	0600 - 1430	Desk G4	0600 - 1430 Desk G4	Courtney	1
7	0600 - 1430	Desk S1	0600 - 1430 Desk S1	Bill	1
8	0600 - 1430	Desk S2	0600 - 1430 Desk S2	Bruce	1
9	0600 - 1430	Desk S3	0600 - 1430 Desk S3	Charles	1
10	0600 - 1430	Desk S4	0600 - 1430 Desk S4	John	1
11	0700 - 1530	Desk R1	0700 - 1530 Desk R1	Jeff	1
12	0700 - 1530	Desk R2	0700 - 1530 Desk R2	Luis	1
13	0700 - 1530	Desk S5	0700 - 1530 Desk S5	Thom	1
14	0700 - 1530	Desk S6	0700 - 1530 Desk S6	Paul	1
15	1400 - 2230	Desk G1	1400 - 2230 Desk G1	Kush	1
Sheet2

Cell Formulas
Range	Formula
C2:C15	C2	=A2&""&B2
E3:E15	E3	=IF(D2>"",COUNTIF(C2:C3, C2),"")