Return Multiple Result Two Dimensions

zaki1979 · Jun 7, 2022

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.

DanteAmor · Jun 7, 2022

Hi and welcome to MrExcel!

It would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are.

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 · Jun 8, 2022

Dear Expert,

Many thanks for your reply. Kindly note that here below is my Master Data Sheet (Input Data):

New Microsoft Excel Worksheet.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

1

2

P

PRESENT

3

A

ABSENCE

4

M

MALADE

5

R

REPOT

6

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

9

201102

AKANGA ALONDO

A

GUARDE

CLIENT 2

SITE B

RAS

201102_9

P

M

P

10

201103

INGANGE EYENGA

B

GUARDE

CLIENT 3

SITE C

RAS

201103_10

P

A

P

11

201101

MIDO MIWUMBU

A

GUARDE

CLIENT 4

SITE D

TRANSFERT #101

201101_11

P

A

P

R

P

MASTER DATA

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

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

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

New Microsoft Excel Worksheet.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

3

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

TOTAL PRESENCE

TOTAL ABSENCE

4

201101

GUARDE

P

A

P

R

P

25

2

5

201102

GUARDE

P

M

P

24

0

6

201103

GUARDE

P

A

P

26

2

ATTENDANCE REPORT

Cell Formulas
Range	Formula
AE4:AE6	AE4	=COUNTIF(C4:AD4,"P")
AF4:AF6	AF4	=COUNTIF(C4:AD4,"A")

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.

DanteAmor · Jun 8, 2022

In the sheet "Master data" you have 2 times the MATRICULE "201101", it may be that on the same day it has 2 letters, or always only one letter will exist?
If there are 2 letters which of them should I put?

zaki1979 · Jun 8, 2022

Yes sir, of course it must be like that because it means employee 201101 worked from 01/02 till 18/02 in site A
then he worked from 19/02 till 28/02 in Site D.

In the Master Sheet the Attendance has been registered based on their Location, but when I want to create my report I
wanted to be displayed without caring about their locations. It's about continuous attendance.

I tried to solve my problem using VBA as follows and it works fine till now. I will prefer to do it using a Formula
hope you will resolve my task.

Here's VBA code tell me what you think about it:

Sub GetAttendance()

On Error Resume Next

Dim mSheet As Worksheet: Set mSheet = ThisWorkbook.Sheets("ATTENDANCE")
Dim cSheet As Worksheet: Set cSheet = ThisWorkbook.Sheets("RP03")
Dim m_lRow As Long: m_lRow = mSheet.Range("A" & Rows.Count).End(xlUp).Row
Dim c_lRow As Long: c_lRow = cSheet.Range("A" & Rows.Count).End(xlUp).Row
Dim c, rng As Range
Dim i, j, k As Long

j = 2
k = 6

Do While j <= c_lRow
'loop through Master Sheet....
For Each c In mSheet.Range("A12:AR" & m_lRow)
'Assign values from Report Sheet to check within Master sheet....
If c.Value = cSheet.Range("A" & j).Value Then
'If exists then read values from column N till the last column
For i = 13 To 43
'If cell is empty don't execute...
If c.Offset(0, i).Value <> "" Then
'loop through columns in Sheet Report/Output
For k = 6 To 36
If (i - k) = 7 Then
'Write back the result into Report sheet in their place....
cSheet.Cells(j, k).Value = c.Offset(0, i).Value
Exit For
End If
Next k
End If
Next i
End If
Next c
'increment the index to move 1 row in the report sheet
j = j + 1
Loop

End Sub

DanteAmor · Jun 8, 2022

zaki1979 said:
I will prefer to do it using a Formula

Try this:

Dante Amor

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

1

2

3

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

TOTAL PRESENCE

TOTAL ABSENCE

4

201101

GUARDE

5

201102

GUARDE

6

201103

GUARDE

Atrtendance report

Cell Formulas
Range	Formula
C4:AD6	C4	=INDEX('Master data'!$A$1:$AJ$11,MAX(IF('Master data'!$A$8:$A$11=$A4,IF('Master data'!$I$8:$AJ$11<>"",IF('Master data'!$I$7:$AJ$7=C$3,ROW('Master data'!$A$8:$A$11))))),MATCH(C$3,'Master data'!$A$7:$AJ$7,0))
AE4:AE6	AE4	=COUNTIF(C4:AD4,"P")
AF4:AF6	AF4	=COUNTIF(C4:AD4,"A")
Press CTRL+SHIFT+ENTER to enter array formulas.

Note:
In future please use code tags when posting code. How to Post Your VBA Code it makes your code easier to read andcopy and it also maintains VBA formatting.

zaki1979 · Jun 9, 2022

Dear Sir, I don't what code tag means. Actually it's my 1st time I use a sit like that. please explain to me what do you mean by Code Tags.
I will try the your formula then I'll refer back to you. Really thanks a lot for all the efforts you did.

zaki1979 · Jun 9, 2022

Dear Sir,

I really can't thank you enough because you make my life easier. You are amazing
May GOD bless you since you are helping people.

DanteAmor · Jun 9, 2022

zaki1979 said:
Dear Sir, I don't what code tag means. Actually it's my 1st time I use a sit like that. please explain to me what do you mean by Code Tags.
I will try the your formula then I'll refer back to you. Really thanks a lot for all the efforts you did.

I mean you put this code without using code tags.
Sub GetAttendance()
On Error Resume Next
Dim mSheet As Worksheet: Set mSheet = ThisWorkbook.Sheets("ATTENDANCE")
Dim cSheet As Worksheet: Set cSheet = ThisWorkbook.Sheets("RP03")
'...etc
End Sub

When you use code tags it looks like this:

VBA Code:

Sub GetAttendance()
On Error Resume Next
Dim mSheet As Worksheet: Set mSheet = ThisWorkbook.Sheets("ATTENDANCE")
Dim cSheet As Worksheet: Set cSheet = ThisWorkbook.Sheets("RP03")
'...etc
End Sub

To put the code tags click on the VBA-code icon, you find it in the icon bar:

After pressing the icon, in the panel the code tags appear, between the tags you must put your code.

DanteAmor · Jun 9, 2022

zaki1979 said:
Dear Sir,

I really can't thank you enough because you make my life easier. You are amazing
May GOD bless you since you are helping people.

I'm glad to help you. Thanks for the feedback.

Return Multiple Result Two Dimensions

zaki1979

New Member

Attachments

Excel Facts

DanteAmor

Well-known Member

zaki1979

New Member

DanteAmor

Well-known Member

zaki1979

New Member

DanteAmor

Well-known Member

zaki1979

New Member

zaki1979

New Member

DanteAmor

Well-known Member

DanteAmor

Well-known Member

Similar threads

Forum statistics

Share this page

Return Multiple Result Two Dimensions

New Member

Attachments

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock