Multiple Fault Code Mapping Based on Time Slot Ranges

[Surendra Bhilare]

Dear Team,

We have two sheets, Sheet1 and Sheet3.

Our requirement is to extract multiple fault codes from Sheet3 and display them in Sheet1, based on the following parameters:

• Posting Date
• Work Center
• Time Slot

Key Requirement:

• If the time slot in Sheet3 is mentioned as 07-15, the corresponding fault codes should be distributed across the time slots 07:00-08:00 to 14:00-15:00 in Sheet1.
• The fault codes should be correctly placed within the respective time slot ranges.
• Multiple codes added with "," separator.
• We are using excel version 2013.
• Please take for midnight range slot.

Please let us know the best way to achieve this.

Sheet 1 - Data require in sheet 1.

Posting Date	Work Center	Department	Bay	07-08	08-09	09-10	10-11	11-12	12-13	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-00	00-01	01-02	02-03	03-04	04-05	05-06	06-07
01-03-2025	SCMSCS02	SOD Cutting	0

Sheet 3 - Data to be pick from Sheet 3

Start Time	End Time	Time Slot	Work center	Fault Code Descr	Down time date.
07:00:00	07:30:00	07-08	SCMSCS02	MECH BREAK DOWN	01-03-2025
07:30:00	08:00:00	07-08	SCMSCS02	NO MANPOWER	01-03-2025
08:00:00	08:30:00	08-09	SCMSCS02	NO PLANNING	01-03-2025
08:30:00	09:00:00	08-09	SCMSCS02	DIE SETUP	01-03-2025
09:00:00	10:00:00	09-10	SCMSCS02	HEAT ISSUE	01-03-2025
10:00:00	11:00:00	10-11	SCMSCS02	MECH BREAK DOWN	01-03-2025
11:00:00	12:00:00	11-12	SCMSCS02	LINE CHANGE	01-03-2025
12:00:00	13:00:00	12-13	SCMSCS02	TEA BREAK	01-03-2025
18:00:00	19:00:00	18-19	SCMSCS02	RING PANA CHANGE	01-03-2025
19:00:00	19:30:00	19-20	SCMSCS02	NO MANPOWER	01-03-2025
20:00:00	20:30:00	20-21	SCMSCS02	TEA BREAK	01-03-2025
23:00:00	07:00:00	23-07	SCMSCS02	ELECTRICAL BREAKDOWN	01-03-2025

Thanks & Regards,
Surendra Bhilare

 

[Sam_D_Ben]

Something like this,

Book1
	A	B	C	D	E	F
1	Start Time	End Time	Time Slot	Work center	Fault Code Descr	Down time date
2	7:00:00	7:30:00	07-08	SCMSCS02	MECH BREAK DOWN	01/03/25
3	7:30:00	8:00:00	07-08	SCMSCS02	NO MANPOWER	01/03/25
4	8:00:00	8:30:00	08-09	SCMSCS02	NO PLANNING	01/03/25
5	8:30:00	9:00:00	08-09	SCMSCS02	DIE SETUP	01/03/25
6	9:00:00	10:00:00	09-10	SCMSCS02	HEAT ISSUE	01/03/25
7	10:00:00	11:00:00	10-11	SCMSCS02	MECH BREAK DOWN	01/03/25
8	11:00:00	12:00:00	11-12	SCMSCS02	LINE CHANGE	01/03/25
9	12:00:00	13:00:00	12-13	SCMSCS02	TEA BREAK	01/03/25
10	18:00:00	19:00:00	18-19	SCMSCS02	RING PANA CHANGE	01/03/25
11	19:00:00	19:30:00	19-20	SCMSCS02	NO MANPOWER	01/03/25
12	20:00:00	20:30:00	20-21	SCMSCS02	TEA BREAK	01/03/25
13	23:00:00	7:00:00	23-07	SCMSCS02	ELECTRICAL BREAKDOWN	01/03/25
14	7:00:00	7:30:00	07-15	SCMSCS02	MECH BREAK DOWN	02/03/25
15	7:30:00	8:00:00	07-08	SCMSCS02	NO MANPOWER	02/03/25
16	8:00:00	8:30:00	08-09	SCMSCS02	NO PLANNING	02/03/25
17	8:30:00	9:00:00	08-09	SCMSCS02	DIE SETUP	02/03/25
18	9:00:00	10:00:00	09-10	SCMSCS02	HEAT ISSUE	02/03/25
19	10:00:00	11:00:00	10-11	SCMSCS02	MECH BREAK DOWN	02/03/25
20	11:00:00	12:00:00	11-12	SCMSCS02	LINE CHANGE	02/03/25
21	12:00:00	13:00:00	12-13	SCMSCS02	TEA BREAK	02/03/25
22	18:00:00	19:00:00	18-19	SCMSCS02	RING PANA CHANGE	02/03/25
23	19:00:00	19:30:00	19-20	SCMSCS02	NO MANPOWER	02/03/25
24	20:00:00	20:30:00	20-21	SCMSCS02	TEA BREAK	02/03/25
25	23:00:00	7:00:00	23-07	SCMSCS02	ELECTRICAL BREAKDOWN	02/03/25
Sheet3

Book1
	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
1	Posting Date	Work Center	Department	Bay	07-08	08-09	09-10	10-11	11-12	12-13	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-00	00-01	01-02	02-03	03-04	04-05	05-06	06-07
2	01/03/25	SCMSCS02	SOD Cutting	0	MECH BREAK DOWN NO MANPOWER	NO PLANNING DIE SETUP	HEAT ISSUE	MECH BREAK DOWN	LINE CHANGE	TEA BREAK						RING PANA CHANGE	NO MANPOWER	TEA BREAK			ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN
3	02/03/25	SCMSCS02	SOD Cutting	0	MECH BREAK DOWN NO MANPOWER	MECH BREAK DOWN NO PLANNING DIE SETUP	MECH BREAK DOWN HEAT ISSUE	MECH BREAK DOWN	MECH BREAK DOWN LINE CHANGE	MECH BREAK DOWN TEA BREAK	MECH BREAK DOWN	MECH BREAK DOWN				RING PANA CHANGE	NO MANPOWER	TEA BREAK			ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN
Sheet1

Sub testMapping()
    Dim a%, b%, c%, d%, e%, f%, g$, h$, i$, j$, k$, l$, m As Worksheet, n As Worksheet
    Dim o&, p&, q&, r&, s$, t As Range, u As Range, v As String

    Set m = Worksheets("Sheet3")
    Set n = Worksheets("Sheet1")

    a = m.Cells(m.Rows.Count, 1).End(xlUp).Row
    b = n.Cells(n.Rows.Count, 1).End(xlUp).Row
    Set t = n.Rows(1)

    For c = 2 To a
        g = Format(m.Cells(c, 6).Value, "d/m/yyyy")
        h = Trim(m.Cells(c, 4).Value)
        i = Trim(m.Cells(c, 3).Value)
        j = Trim(m.Cells(c, 5).Value)

        f = 0
        For d = 2 To b
            If Format(n.Cells(d, 1).Value, "d/m/yyyy") = g And Trim(n.Cells(d, 2).Value) = h Then
                f = d
                Exit For
            End If
        Next d
        If f = 0 Then GoTo y

        If InStr(i, "-") > 0 Then
            o = Val(Split(i, "-")(0))
            p = Val(Split(i, "-")(1))
            q = o
            Do
                r = (q + 1) Mod 24
                s = Format(q, "00") & "-" & Format(r, "00")
                Set u = t.Find(What:=s, LookIn:=xlValues, LookAt:=xlWhole)
                If Not u Is Nothing Then
                    With n.Cells(f, u.Column)
                        If .Value = "" Then
                            .Value = j
                        ElseIf InStr(1, .Value, j, vbTextCompare) = 0 Then
                            .Value = .Value & Chr(10) & j
                        End If
                        .WrapText = True
                    End With
                End If
                q = (q + 1) Mod 24
            Loop While q <> p
        End If
y:
    Next c
    
End Sub

 

[Surendra Bhilare]

Dear Sam,

Can you provide me the excel formula (Version of excel is 2013) instead of VBA.

 

[Surendra Bhilare]

Dear Sam,

Also please confirm if we use the VBA code then where we copy this code in under objects as per attached screenshot.

As per provided VBA code the column names are different then how can we assign the correct coloumn number in vba code.

Please check and share the revise code with all correct coloumn names which are showing in screenshots which we have provided earlier..

 

[kvsrinivasamurthy]

Book1
	A	B	C	D	E	F	G
1	Start Time	End Time	Time Slot	Work center	Fault Code Descr	Down time date
2	07:00:00	07:30:00	7-8	SCMSCS02	MECH BREAK DOWN	03-01-2025
3	07:30:00	08:00:00	8-9	SCMSCS02	NO MANPOWER	03-01-2025
4	08:00:00	08:30:00	9-10	SCMSCS02	NO PLANNING	03-01-2025
5	08:30:00	09:00:00	10-11	SCMSCS02	DIE SETUP	03-01-2025
6	09:00:00	10:00:00	11-12	SCMSCS02	HEAT ISSUE	03-01-2025
7	10:00:00	11:00:00	12-13	SCMSCS02	MECH BREAK DOWN	03-01-2025
8	11:00:00	12:00:00	13-14	SCMSCS02	LINE CHANGE	03-01-2025
9	12:00:00	13:00:00	14-15	SCMSCS02	TEA BREAK	03-01-2025
10	18:00:00	19:00:00	15-16	SCMSCS02	RING PANA CHANGE	03-01-2025
11	19:00:00	19:30:00	16-17	SCMSCS02	NO MANPOWER	03-01-2025
12	20:00:00	20:30:00	17-18	SCMSCS02	TEA BREAK	03-01-2025
13	23:00:00	07:00:00	18-19	SCMSCS02	ELECTRICAL BREAKDOWN	03-01-2025
14	23:00:00	07:00:00	19-20	SCMSCS02	MECH BREAK DOWN	03-02-2025
15	23:00:00	07:00:00	20-21	SCMSCS02	NO MANPOWER	03-02-2025
16	23:00:00	07:00:00	21-22	SCMSCS02	NO PLANNING	03-02-2025
17	23:00:00	07:00:00	22-23	SCMSCS02	DIE SETUP	03-02-2025
18	23:00:00	07:00:00	23-24	SCMSCS02	HEAT ISSUE	03-02-2025
19	23:00:00	07:00:00	0-1	SCMSCS02	MECH BREAK DOWN	03-02-2025
20	23:00:00	07:00:00	1-2	SCMSCS02	LINE CHANGE	03-02-2025
21	23:00:00	07:00:00	2-3	SCMSCS02	TEA BREAK	03-02-2025
22	23:00:00	07:00:00	3-4	SCMSCS02	RING PANA CHANGE	03-02-2025
23	23:00:00	07:00:00	4-5	SCMSCS02	NO MANPOWER	03-02-2025
24	23:00:00	07:00:00	5-6	SCMSCS02	TEA BREAK	03-02-2025
25	23:00:00	07:00:00	6-7	SCMSCS02	ELECTRICAL BREAKDOWN	03-02-2025
Sheet3

Book1
	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
1	Bay	7-8	8-9	9-10	10-11	11-12	12-13	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-24	0-1	1-2	2-3	3-4	4-5	5-6	6-7
2	1	MECH BREAK DOWN	NO MANPOWER	NO PLANNING	DIE SETUP	HEAT ISSUE	MECH BREAK DOWN	LINE CHANGE	TEA BREAK	RING PANA CHANGE	NO MANPOWER	TEA BREAK	ELECTRICAL BREAKDOWN
3	0													MECH BREAK DOWN	NO MANPOWER	NO PLANNING	DIE SETUP	HEAT ISSUE	MECH BREAK DOWN	LINE CHANGE	TEA BREAK	RING PANA CHANGE	NO MANPOWER	TEA BREAK	ELECTRICAL BREAKDOWN
Sheet1

Cell Formulas
Range	Formula
E2:AB3	E2	=CONCAT(IF((Sheet3!$F$2:$F$25=$A2)*(Sheet3!$D$2:$D$25=$B2)*(Sheet3!$C$2:$C$25=E$1),Sheet3!$E$2:$E$25,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

ARRAY formula in E2 copied up to AB3

=CONCAT(IF((Sheet3!$F$2:$F$25=$A2)*(Sheet3!$D$2:$D$25=$B2)*(Sheet3!$C$2:$C$25=E$1),Sheet3!$E$2:$E$25,""))

 

[Surendra Bhilare]

Dear Team,

I am using the formula that was shared, but I am encountering a "#NAME?" error in the result. Could you please check the formula and confirm if there are any issues?

Additionally, the current formula fetches only a single fault code per time slot. However, we require multiple fault codes where available in the given time slot. Please suggest a solution to accommodate this requirement.

Looking forward to your support.

 

[Surendra Bhilare]

Require result output as below.

e	Work Center	Department	Bay	07-08	08-09	09-10	10-11	11-12	12-13	13-14	14-15	15-16	16-17	17-18	18-19	19-20	20-21	21-22	22-23	23-00	00-01	01-02	02-03	03-04	04-05	05-06	06-07
2	01/03/25	SCMSCS02	SOD Cutting	0	MECH BREAK DOWN NO MANPOWER	NO PLANNING DIE SETUP	HEAT ISSUE	MECH BREAK DOWN	LINE CHANGE	TEA BREAK	RING PANA CHANGE	NO MANPOWER	TEA BREAK	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN
3	02/03/25	SCMSCS02	SOD Cutting	0	MECH BREAK DOWN NO MANPOWER	MECH BREAK DOWN NO PLANNING DIE SETUP	MECH BREAK DOWN HEAT ISSUE	MECH BREAK DOWN	MECH BREAK DOWN LINE CHANGE	MECH BREAK DOWN TEA BREAK	MECH BREAK DOWN	MECH BREAK DOWN	RING PANA CHANGE	NO MANPOWER	TEA BREAK	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN	ELECTRICAL BREAKDOWN

 

[kvsrinivasamurthy]

CONCAT function is not available in 2013.
Pl use this ARRAY formula in E2 copied to full range.

=IFERROR(INDEX(Sheet3!$E$2:$E$25,SMALL(IF((Sheet3!$F$2:$F$25=$A2)*(Sheet3!$D$2:$D$25=$B2)*(Sheet3!$C$2:$C$25=E$1),ROW(Sheet3!$E$2:$E$25),""),1)-ROW($E$1)),"")

 

[kvsrinivasamurthy]

Formula given is for data in Post#5.

 

[Surendra Bhilare]

Dear Team,


I have used the provided formula, but it only displays fault codes for the 07:00-08:00 time slot. For all other time slots, the result is blank.


Additionally, we need the formula to fetch multiple fault codes available in a given time slot. There may also be cases where the time slot is different, such as 07:00-15:00. Please provide a solution that accommodates these requirements.


Looking forward to your support.