VBA OR FORMULA

[Vijeshkv]

Hello I have below data , need row wise for the dates any formula or VBA

DTP

Territory

Tasks/Recon

Country Name

Team

Major Process

Activity/ Task

Frequency

BU SME Name

Preparer Name(Enterprise ID)

Preparer/Reviewer

Level

Criticality

Dependency on BU

AHT (Hrs)

Avg Time per task (Hrs)

Total per month (Hrs)

3/1

3/2

3/3

3/4

3/5

3/6

3/7

3/8

3/9

3/10

3/11

3/12

3/13

3/14

3/15

3/16

3/17

3/18

3/19

3/20

3/21

3/22

3/23

3/24

3/25

3/26

3/27

3/28

3/29

3/30

3/31

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12​

NA

No

11.00

0.5

11.0

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

0.50

 

[Vijeshkv]

Result should be as below ... date wise

DTP

Territory

Tasks/Recon

Country Name

Team

Major Process

Activity/ Task

Frequency

BU SME Name

Preparer Name(Enterprise ID)

Preparer/Reviewer

Level

Criticality

Dependency on BU

AHT (Hrs)

Avg Time per task (Hrs)

Total per month (Hrs)

Date

AHT_Daily

 

[Vijeshkv]

DTP

Territory

Tasks/Recon

Country Name

Team

Major Process

Activity/ Task

Frequency

BU SME Name

Preparer Name(Enterprise ID)

Preparer/Reviewer

Level

Criticality

Dependency on BU

AHT (Hrs)

Avg Time per task (Hrs)

Total per month (Hrs)

Date

AHT_Daily

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/1/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/2/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/3/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/6/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/7/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/8/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/9/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/10/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/13/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/14/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/15/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/16/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/17/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/20/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/21/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/22/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/23/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/24/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/27/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/28/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/29/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/30/2023​

0.5​

SSS

N/A

KK

SS

MAMA

KK

huddle

Daily

NA

VIJESH.K

Preparer

12

NA

No

11​

0.5​

11​

3/31/2023​

0.5​

 

[RudRud]

Hi, Im new in VBA, Kindly try the code below

P.S. : Need add a new sheet first (Sheet2)

Sub test()
    
    With Sheet1
    
        lcol = .Cells(1, Columns.Count).End(xlToLeft).Column
        a = .Range(Cells(1, "a"), Cells(2, lcol)).Value
        ReDim b(1 To UBound(a, 2), 1 To 2)
        
        For j = 1 To UBound(a, 2)
         If IsDate(a(1, j)) And a(2, j) <> "" Then
            k = k + 1
            b(k, 1) = a(1, j)
            b(k, 2) = a(2, j)
         End If
        
        Next j
    
    End With

     With Sheet2
        .Range("r2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
        .Range("a1:q1").Value = Sheet1.Range("a1:q1").Value
        .Range("a2: Q" & .Cells(Rows.Count, "r").End(xlUp).Row).Value = Sheet1.Range("a2:q2").Value

     End With
    

End Sub