Return Multiple Result Two Dimensions

[zaki1979]

Dear Expert,

I am working in a small project. I have attached my simple data (Input & output)

Master Data Sheet looks like as follows:

Employee ID Client Name Site Name 01/02 02/02 03/02 04/02 05/02 06/02 07/02 08/02
201101 Client A Site X P P A
201101 Client B Site Y P P P
201101 Client C Site Z P A and till the end of the Month of February 2022 (Where P represents Present & A represents Absent)

I have another Sheets (Output Sheet)/Report which will display only unique employee Id I want the result to be displayed as follows:

Employee ID 01/02 02/02 03/02 04/02 05/02 06/02 07/02 08/02 .................................28/02
201101 P P A P P P P A.
201102
201103
etc...

I know that I have to use Index/Match but unfortunately I couldn't solve the task. Is there a way to use Index/Match with Offset
any solution will be highly appreciated.

Many thanks in advance for your precious time and consideration. Looking forward to hear from you as soon as possible.





Kindly note that I am using office 2019.

 

[zaki1979]

Dear Sir,
After copying the formula into Original Sheet I encountered some problems. Please need your help again because it works for some cells while for others it display error value in the cell.

 

[zaki1979]

Dear sir,

Your Formula works fine but it doesn't cover all cases of attendance can I resend you the sheets so you can find out the problem.

 

[zaki1979]

MASTER DATA

New Microsoft Excel Worksheet.xlsx
	AB
13	P
MASTER DATA

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I8:AJ16	Cell Value	="R"	text	NO
I8:AJ16	Cell Value	="M"	text	NO
I8:AJ16	Cell Value	="A"	text	NO
I8:AJ16	Cell Value	="P"	text	NO

OUTPUT DATA

New Microsoft Excel Worksheet.xlsx
	L
18
ATTENDANCE REPORT

 

[zaki1979]

MASTER DATA

MATRICULE

NOM

ÉQUIPE

FONCTION

CLIENT

SITE

REMARQUES

UNIQUE_ID

01

02

03

04

05

06

07

08

09

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

201101

MIDO MIWUMBU

A

GUARDE

CLIENT 1

SITE A

RAS

201101_8

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

201102

AKANGA ALONDO

A

GUARDE

CLIENT 2

SITE B

RAS

201102_9

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

M

M

M

M

P

P

201103

INGANGE EYENGA

B

GUARDE

CLIENT 3

SITE C

RAS

201103_10

P

P

P

P

P

P

P

A

A

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

P

201101

MIDO MIWUMBU

A

GUARDE

CLIENT 4

SITE D

TRANSFERT #101

201101_11

P

P

P

A

A

P

P

P

R

P

201104

TEST 1

AB

GUARDE

CLIENT 5

SITE E

201104_12

P

P

201105

TEST 2

A

GUARDE

CLIENT 1

SITE A

201105_13

P

P

201106

TEST 3

A

GUARDE

CLIENT 7

SITE F

201106_14

P

P

P

201107

TEST 4

A

GUARDE

CLIENT 8

SITE G

201107_15

P

P

P

A

A

A

P

P

P

P

201107

TEST 4

A

GUARDE

CLIENT 2

SITE B

201107_16

R

P

P

R

A

A

A

P

A

 

[DanteAmor]

Try again to give your examples because they are not complete.

 

[zaki1979]

output

MATRICULE	FONCTION	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
201101	GUARDE	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	A	A	P	P	P	R	P
201102	GUARDE	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	M	M	M	M	P	P
201103	GUARDE	P	P	P	P	P	P	P	A	A	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P
201104	GUARDE	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
201105	GUARDE	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
201106	GUARDE	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
201107	GUARDE	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

 

[zaki1979]

sorry sir I tried to do it like this because when i click in Mini sheet it is not working

 

[DanteAmor]

sorry sir I tried to do it like this because when i click in Mini sheet it is not working

Try again.

Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[zaki1979]

master data

my project.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
7	MATRICULE	NOM	ÉQUIPE	FONCTION	CLIENT	SITE	REMARQUES	UNIQUE_ID	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
8	201101	MIDO MIWUMBU	A	GUARDE	CLIENT 1	SITE A	RAS	201101_8	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P
9	201102	AKANGA ALONDO	A	GUARDE	CLIENT 2	SITE B	RAS	201102_9	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	M	M	M	M	P	P
10	201103	INGANGE EYENGA	B	GUARDE	CLIENT 3	SITE C	RAS	201103_10	P	P	P	P	P	P	P	A	A	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P	P
11	201101	MIDO MIWUMBU	A	GUARDE	CLIENT 4	SITE D	TRANSFERT #101	201101_11																			P	P	P	A	A	P	P	P	R	P
12	201104	TEST 1	AB	GUARDE	CLIENT 5	SITE E		201104_12																											P	P
13	201105	TEST 2	A	GUARDE	CLIENT 1	SITE A		201105_13									P											P
14	201106	TEST 3	A	GUARDE	CLIENT 7	SITE F		201106_14					P	P	P
15	201107	TEST 4	A	GUARDE	CLIENT 8	SITE G		201107_15	P	P	P	A	A	A	P	P															P					P
16	201107	TEST 4	A	GUARDE	CLIENT 2	SITE B		201107_16									R	P	P	R	A	A	A	P	A
MASTER DATA

Cell Formulas
Range	Formula
H8:H16	H8	=A8&"_"&ROW()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I8:AJ16	Cell Value	="R"	text	NO
I8:AJ16	Cell Value	="M"	text	NO
I8:AJ16	Cell Value	="A"	text	NO
I8:AJ16	Cell Value	="P"	text	NO

 

[zaki1979]

I am trying the output sheet but it still not working with minisheet