Lookup formula

[njenkins]

I have a worksheet with dates in the first column and header rows are jobs to do. Each row has initials of people set to do those jobs. There may be more than one job per person - shown as that persons initial being in more than one column in any particular row.

The sheet is not easy to read for an individual person. I’d like a formula to create an individualised sheet for each person showing their jobs each day.

The worksheet would probably start with dates down the initial column - then a lookup formula pointing to the original worksheet and the row of the corresponding date. It would then look for cells with that persons initials in, and show the corresponding header row to that cell. It would need to cope with there potentially being several or no instances of the initials in any row.

 

[FormR]

Re: Lookup formula help

Hi, see if you can adapt something like this to your actual set-up - the formula in B2 can be copied down and across.

Excel 2013/2016
	A	B	C	D	E
1	Initial >	FR
2	01-Jan	Job 1	Job 3	Job 6
3	02-Jan	Job 2
4	03-Jan
5	04-Jan	Job 3	Job 4
6	05-Jan
7	06-Jan
8	07-Jan
9	08-Jan
10	09-Jan
11	10-Jan
12	11-Jan
13	12-Jan
14	13-Jan
Sheet2

Cell Formulas
Range	Formula
B2	=IFERROR(INDEX(Sheet1!$B$1:$L$1,AGGREGATE(15,6,(COLUMN(Sheet1!$B$1:$L$1)-COLUMN(Sheet1!$B$1)+1)/(INDEX(Sheet1!$B$2:$L$14,MATCH($A2,Sheet1!$A$2:$A$14,0),0)=$B$1),COLUMNS($B2:B2))),"")

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Date	Job 1	Job 2	Job 3	Job 4	Job 5	Job 6	Job 7	Job 8
2	01-Jan	FR	RB	FR	RB	XX	FR
3	02-Jan		FR		XX
4	03-Jan
5	04-Jan			FR	FR		CC
6	05-Jan
7	06-Jan
8	07-Jan
9	08-Jan
10	09-Jan
11	10-Jan
12	11-Jan
13	12-Jan
14	13-Jan
Sheet1

 

[njenkins]

Re: Lookup formula help

thats not working
this is a copy of my worksheet

joba

jobb

jobc

jobd

jobe

jobf

jobg

jobh

jobi

jobj

jobk

jobl

jobm

hobn

jobm

jobo

jobp

jobq

jobr

27-Feb

JS

JScr

EM

NJ

NJ

JS

28-Feb

JS

JScr

EM

NJ

EM

no clinic

01-Mar

EM/JScr

JScr

EM

NJ

JScr

NJ

02-Mar

JS

JScr

EM

NJ

Bal

MD

03-Mar

JS

JScr

EM

NJ

04-Mar

05-Mar

06-Mar

JS

JScr

AW

NJ

NJ

JS

07-Mar

JS

EM

EM

NJ

EM

no clinic

08-Mar

EM

JScr

AW

NJ

AW

NJ

09-Mar

JS

JScr

AW

NJ

AW

EM

NJ

10-Mar

EM for JS

JScr

AW

NJ

11-Mar

JScr

12-Mar

JScr

13-Mar

EM

JScr

AW

NJ

NJ

JS

14-Mar

EM

JScr

AW

NJ

JS

no clinic

15-Mar

AM

JScr

AW

NJ

JScr

NJ

16-Mar

JS

JScr

AW

NJ

JScr

NJ

17-Mar

JS

JScr

AW

NJ

18-Mar

19-Mar

20-Mar

EM

JScr

AW

NJ

JScr

no clinic

21-Mar

EM

JScr

AW

NJ

AW

no clinic

22-Mar

EM

JScr

AW

NJ

EM

NJ

JScr

23-Mar

EM

JScr

AW

NJ

EM

MD

AW

24-Mar

EM

JScr

AW

NJ

25-Mar

26-Mar

27-Mar

AW

JScr

AW

NJ

JScr

no clinic

28-Mar

JScr

JScr

AW

NJ

AW

no clinic

29-Mar

AM

JScr

AW

NJ

AM

NJ

30-Mar

MD

JScr

AW

NJ

AW

JScr

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="3"><col><col><col><col></colgroup><tbody>
</tbody>

 

[FormR]

Re: Lookup formula help

thats not working

Hi, if you are not able to adapt the suggestion to your layout then you will need to either describe the ranges involved or post sample data that demonstrates what the ranges involved are. i.e. the column and row references. We will also need to see the layout and expected results for the "Individual" sheet.

 

[njenkins]

Re: Lookup formula help

Any ideas how to do this as what I’ve done you presumably cannot see

 

[FormR]

Re: Lookup formula help

as what I’ve done you presumably cannot see

I can see your data, I can't tell though from that what columns and rows are what and I can't see sample data of the expected results sheet.

I posted my tables in post 2 using the "MrExcel HTML Maker", details can be found here along with some other options.

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Note, "Not working" does not give us much of a hint to help you diagnose what is going wrong. When you re-post your sample data (including the expected results), also post the formula you tried and describe in what way it didn't work.

 

[njenkins]

Re: Lookup formula help

I’ll try that.
I cut/pasted the formulae from your cell and it returned no value “”

 

[njenkins]

Re: Lookup formula help

i started again from scratch and did get it to work. many thanks
presumably for a wider and longer worksheet i just adjust the 14 to the number of rows i have and the L to the number of columns i have
i am not sure of the relevance of the 15,6 (num_function, options) to an enlarged worksheet

 

[FormR]

Re: Lookup formula help

Great, thanks for letting us know

presumably for a wider and longer worksheet i just adjust the 14 to the number of rows i have and the L to the number of columns i have

Yes, that's correct.

i am not sure of the relevance of the 15,6 (num_function, options) to an enlarged worksheet

These tell the Aggregate() function what to do, they do not need to be changed.

 

[njenkins]

Re: Lookup formula help

btw is there any way it could be adapted to included a cell in the initial worksheet which contains the two sets of initials eg. "FR and NJ" rather than just included those with just "FR"