Extracting two values out of a table based on the same index number

[dkjonesau]

Hi all,

I'm trying to cross reference initials into a roster. The organisation uses the same roster line number for a pair of employees on the same shift.

An example table is below.

	A	B	C	D	E	F	G	H
1	1234	SMITH BILL	MGR	OWLS	1	83/ROAD	BILL SMITH	BS
2	5678	JONES FRED	2IC	OWL/AD	1	83/OPS	FRED JONES	FJ
3	9123	HOLLY JANE	ALS	OWLS	8	83/OPS	JANE HOLLY	JH
4	4567	BELL SARAH	ALS	OWL/AD	8	83/OPS	SARAH BELL	SB
5	8910	HOUSE INDIA	ICP	OWLS	15	83/OPS	INDIA HOUSE	IH
6	2345	TOWN VERONICA	ALS	OWLS/AD	15	83/OPS	VERONICA TOWN	VT
7	6789	TWIST BARRY	ALS	OWLS	22	83/OPS	BARRY TWIST	BT
8	1235	READ ALLISTAIR	ICP	OWLS	22	83/OPS	ALLISTAIR READ	AR

I'm looking for a formula to take the initals for both employees from column H to another pair of cells based on the roster line in column E.

The result would end up looking like this:

	1	2	3
A	Line #	Date
B		Emp 1	Emp 2
C	1	BS	FJ
D	8	JH	SB
E	15	IH	VT
F	22	BT	AR

Both employees found in E1 & E2 (BS & FJ) initials end up in adjacent cells at B2 & B3 against roster line 1 in an alternative summary sheet.

I can't use VLOOKUP as the index for both employees is the same. ie. 1 for line 1, 8 for line 8 etc.

It just returns BS in both Emp 1 & Emp 2.

What is the best formula top put in C2 & C3 in the bottom table to fetch the initials from the pair working line 1 in the top table (ie initials from H1 & H2)

The data coming in is from an external source and is being used for thousands of employees. It can't be customised to make the job easier.

Thanks

Dave

 

[jimrward]

Ok, you have got me thinking now about the stragglers here is an idea which may work for all scenarios
if you are able on your data feed sheet is create a helper column in column I as follows
=E2&”_”&COUNTIF($E$2:E2,E2). My double quotes are iPad ones and not windows ones so change accordingly
this will create a value of shift_n where n is 1 or 2 And these can be anywhere on your list

next change you MATCH to look for A2&”_”&”1” and likewise same but with 2 at the end again change the double quotes so you are looking for instance 22_1 and 22_2 as in your last example

this will find the pairs from anywhere in the list

this is one option rather than using array formulas to return Nth matching strings

 

[dkjonesau]

Ok, you have got me thinking now about the stragglers here is an idea which may work for all scenarios
if you are able on your data feed sheet is create a helper column in column I as follows
=E2&”_”&COUNTIF($E$2:E2,E2). My double quotes are iPad ones and not windows ones so change accordingly
this will create a value of shift_n where n is 1 or 2 And these can be anywhere on your list

next change you MATCH to look for A2&”_”&”1” and likewise same but with 2 at the end again change the double quotes so you are looking for instance 22_1 and 22_2 as in your last example

this will find the pairs from anywhere in the list

this is one option rather than using array formulas to return Nth matching strings

Hi Jim,

Probably easiest if I deidentify the spreadsheet & include here.

You can see the team member at F19 - the second instance of 22 - is on LSL (leave) in column H. The team member at F27 - third instance of 22 - will cover that line as they duplicate it.

Its an unusual circumstance. Normally the first instances of 22 in the top bracket from 1 - 36 would be in the body of the roster.

I can use a helper column or two if needs be. The text in red is helper columns reversing surname first name to first name surname in column I, then using a GetFirstLetter function to make initials. (Didn't like the names with "prefix surname first name" so the SLL should be LLS but I'll live.)

In this instance i could probably establish a way of determining that the 22 on leave in the body of the roster needs to be swapped with Line 27 by searching the top cells for leave types in col H, but it's messy.

Dave

 

[jimrward]

It should be ok to factor in the leave criteria when building the helper column. I should be able to get to my pc in the next hour or so and see what I can come up with

 

[jimrward]

Ok finally on my computer, there are probably slicker ways of doing this but I prefer the easy step by step as its easier to understand and hopefully explains my thinking and or techniques

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	A	B	C	D	E	F		G	H	Helper Able to work	Helper OnShift_EmployeeCount
2	1234	SMITH BILL	MGR	OWLS	1	83/ROAD	A/L	BILL SMITH	BS	1	1_1
3	5678	JONES FRED	2IC	OWL/AD	1	83/OPS	RDO	FRED JONES	FJ	1	1_2
4	9123	HOLLY JANE	ALS	OWLS	8	83/OPS	RDO	JANE HOLLY	JH	8	8_1
5	4567	BELL SARAH	ALS	OWL/AD	8	83/OPS	RDO	SARAH BELL	SB	8	8_2
6	8910	HOUSE INDIA	ICP	OWLS	15	83/OPS	3/LCK	INDIA HOUSE	IH	15	15_1
7	2345	TOWN VERONICA	ALS	OWLS/AD	15	83/OPS	3/LCK	VERONICA TOWN	VT	15	15_2
8	6789	TWIST BARRY	ALS	OWLS	22	83/OPS	LSLH	BARRY TWIST	BT	on leave
9	1235	READ ALLISTAIR	ICP	OWLS	22	83/OPS	700	ALLISTAIR READ	AR	22	22_1
10	1236	TIME JUSTIN	ICP	OWLS	22	83/OPS	COSQ	JUSTIN TIME	JT	22	22_2
Sheet1

Cell Formulas
Range	Formula
J2:J10	J2	=IF(LEFT(G2,3)<>"LSL",E2,"on leave")
K2:K10	K2	=IF(J2 <> "on leave",J2&"_"&COUNTIF($J$2:J2,J2),"")

I have created 2 help columns the first one looks for the string LSL (substitute in what you need) in column G if it finds it sets the helper column value to on_leave, else it copies across the shift number
so in cell J2 enter the following
=IF(LEFT(G2,3)<>"LSL",F2,"on leave")

In column K is our second helper column where we set the ShiftNumber_EmployeeCount to use later on for sheet2
in cell K2 enter the following
=IF(J2 <> "on leave",J2&"_"&COUNTIF($J$2:J2,J2),"")

Now for sheet 2

Book1.xlsx
	A	B	C	D
1	A	B	C
2	1	Line #	Date
3	2		Emp 1	Emp 2
4	3	1	BS	FJ
5	4	8	JH	SB
6	5	15	IH	VT
7	6	22	AR	JT
Sheet2

Cell Formulas
Range	Formula
C4:C7	C4	=INDEX(Sheet1!$I$2:$I$10,MATCH(B4&"_1",Sheet1!$K$2:$K$10,0))
D4:D7	D4	=INDEX(Sheet1!$I$2:$I$10,MATCH(B4&"_2",Sheet1!$K$2:$K$10,0))

for our INDEX MATCH combination
=INDEX(sheet1!$I$2:$I$10,MATCH(B4&"_1",sheet1!$K$2:$K$10,0))
=INDEX(sheet1!$I$2:$I$10,MATCH(B4&"_2",sheet1!$K$2:$K$10,0))

 

[dkjonesau]

[
Hey thanks Jim

I’ll have a play with that over the next few days. Let you know how I go. On days off now so will probably be home isolating with the welder in the shed rather than on the computer. Much appreciated!!

Dave