Array formula to find results based on multiple search criteria

[Pigmy]

Hi,

I'm trying to construct a table that shows what project was being undertaken at a particular time by an individual. My input table comes from a system that tells me the individual name, the project they were working on and the time they started and finished. So where an individual has been working on multiple projects they will have multiple entries against their name.

I've split the input data into 5 min slots and created a sheet called Calculator that now shows their projects in column A, their names in column B and the times along row 1 and populates the project they were working on in the appropriate cell. Where they weren't working on a project, these cells have a 0 in them (though could easily be changed to a "" or 1 if it helps). At this stage, a person still appears multiple times in column B and projects occur multiple times in column A, however a person cannot be working on 2 projects at the same time.

I then want to create a table called Re-Calc showing everyone who was working on a specific day and what they were working on (with a view to colour coding the different projects). I've used

{=IFERROR(INDEX(Calculator!$B$2:$B$500, MATCH(0,COUNTIF($A$1:A1, Calculator!$B$2:$B$500), 0)),"")}

to generate a unique list of people in column A and I have the times along the top in row 1.

So I want to write a formula to find where the person's name matches the values in Calculator!column B, the time in row 1 matches the times in Calculator!row 1 and then return the first non-blank entry from the corresponding table. However I'm struggling with the Match function as it only finds the 1st match with the person's name. I've being struggling with this for a while now so think I've started going off on a tangent and therefore need help!

The closest I've got is the below:-

=SMALL(
IF(
AND(MATCH('Re-calc'!$A3,Calculator!$B$2:$B$500,0),
INDEX(Calculator!BT$2:BT$500,MATCH(FALSE,ISBLANK(Calculator!BT$2:BT$500),0))),Calculator!$BT$1:$BT$500),1)

entered as an array formula, but it just finds the top line of data from Calculator. I guess this is logical as
(MATCH('Re-calc'!$A3,Calculator!$B$2:$B$500,0) its finding the presence of the person's name, not a specific row so I'm now thinking I've gone about this in the wrong way!

Any help appreciated!

 

[James006]

Hello,

If I understand correctly your sheet's structure ...instead of (MATCH('Re-calc'!$A3,Calculator!$B$2:$B$500,0) ...

you should be using SMALL(IF(Calculator!$B$2:$B$500='Re-calc'!$A3,Row($B$2:$B$500)),ROWS($1:1))

Hope this will help

 

[Pigmy]

Hi James,

Ive tried that - the whole string now loks like:-

=SMALL(
IF(
AND(
SMALL(IF(Calculator!$B$2:$B$500='Re-calc'!$A3,ROW($B$2:$B$500)),ROWS($1:1)),
INDEX(Calculator!BT$2:BT$500,MATCH(FALSE,ISBLANK(Calculator!BT$2:BT$500),0))),Calculator!$BT$1:$BT$500),1)

However its still just picking up the top line from he Calculator sheet.

Does that make sense?
Thanks

 

[James006]

Hi again,

Do you mind listing the conditions :

1. Condition 1
2. Condition 2
3. Condition 3

and confirming the data to be returned is located in range : Calculator!BT$2:BT$500

As mentioned before ... you should not use Match() but instead use If() ...

 

[Pigmy]

Hi,

The data is in the table from columns H through to HH (I think). However the first instance I've found where the first person has been working on a second project is in column BT. So yes, the first cell that should give a positive result is in column BT.

The logic I'm trying to write is:-
1 - find all the occurrences in Calculator where the employee whose number is shown in Re-Calc A2 appears in column B. They usually work on 3-4 projects a day so if you image me filtering Calculator based on 1 persons employee number, I'd get about 4 lines showing
2 - Summarise these 4 lines into 1 row with no gaps. So if employee 12345 worked on project X from 09:00 to 10:00 then project Y from 10:30 to 12:00, the top line in Calculator would currently show as a string of 0's up to 09:00 then would have X in each cell until 10:00 and then a further string of 0s. The next line would show a string of 0's up to 10:30, then show Y until 12:00 then a further string of 0's. I'm trying to get these into 1 row. I've tried to show this below:-

Current Calculator sheet

Employee	Project	08:00	08:30	09:00	09:30	10:00	10:30	11:00	11:30	12:00	12:30	13:00
12345	X	0	0	X	X	0	0	0	0	0	0	0
12345	Y	0	0	0	0	0	Y	Y	Y	0	0	0
12345	Z	0	0	0	0	0	0	0	0	0	Z	Z

<colgroup><col width="64" span="13" style="width: 48pt;"></colgroup><tbody>
</tbody>

Desired output in Re-Calc

Employee

08:00

08:30

09:00

09:30

10:00

10:30

11:00

11:30

12:00

12:30

13:00

12345

X

X

Y

Y

Y

Z

Z

<colgroup><col width="64" span="13" style="width: 48pt;"></colgroup><tbody>
</tbody>

If guessing the second set of criteria I'm using to find the blank cells uses MATCH and therefore should be IF as you suggest

Best regards

 

[James006]

Hi,

Based on your example above ...

If your Current Calculator is located in Sheet1 in range A1:M4

and your desired output in Sheet2 in range A1:M2 ...

You can test following Array formula in Sheet2 cell C2 :

 =IF(MAX(ROW(Sheet1!C2:C4)*(Sheet1!C2:C4<>0))=0,"",INDEX(Sheet1!C1:C4,MAX(ROW(Sheet1!C2:C4)*(Sheet1!C2:C4<>0))))

Hope this will help

 

[Pigmy]

Hi

That worked for 1 emloyee but the data inclues several employees like the table below (sorry for not making that clear)

CALCULATOR SHEET

Employee	Project	08:00	08:30	09:00	09:30	10:00	10:30	11:00	11:30	12:00	12:30	13:00
12345	X	0	0	X	X	0	0	0	0	0	0	0
22222	X	X	X	X	X	X	X	0	0	0	0	0
12345	Y	0	0	0	0	0	Y	Y	Y	0	0	0
33333	Y	0	0	Y	Y	Y	Y	Y	Y	0	0	0
12345	Z	0	0	0	0	0	0	0	0	0	Z	Z
33333	Z	0	0	0	0	0	0	0	0	Z	Z	Z
44444	F	0	0	F	F	F	F	F	F	F	F	F

<colgroup><col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <col width="64" style="width: 48pt;" span="12"> <tbody> </tbody>

DESIRED OUTPUT

Employee

Project

08:00

08:30

09:00

09:30

10:00

10:30

11:00

11:30

12:00

12:30

13:00

12345

X

X

Y

Y

Y

Z

Z

22222

X

X

X

X

X

X

33333

Y

Y

Y

Y

Y

Y

Z

Z

Z

44444

F

F

F

F

F

F

F

F

F

<colgroup><col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <col width="64" style="width: 48pt;" span="12"> <tbody> </tbody>

Hopefully that makes more sense. Thanks for helping

 

[James006]

Hi again,

Based on the same assumptions as earlier ...

In cell A2

=IFERROR(INDEX(Sheet1!$A$2:$A$8,MATCH(0,COUNTIF($A$1:A1,Sheet1!$A$2:$A$8),0)),"")

and in cell C2

=IF(MAX(ROW(Sheet1!C$2:C$8)*(Sheet1!C$2:C$8<>0)*(Sheet1!$A$2:$A$8=Sheet2!$A2))=0,"",INDEX(Sheet1!C$1:C$8,MAX(ROW(Sheet1!C$2:C$8)*(Sheet1!C$2:C$8<>0)*(Sheet1!$A$2:$A$8=Sheet2!$A2))))

Hope this will help

 

[Pigmy]

That's brilliant, thank you for helping me, it works a treat!

 

[James006]

Pleased to hear it is all sorted out ...

Thanks for your Thanks ...