VBA Use dynamic array to create custom report

DFlem · Jul 20, 2022

Please assist if you can! I have an exported Excel report, each row is a new customer, but the rows stop at 137. There are about 250 columns with customer details, but only 5 columns belong to 1 customer in each row. My boss wants me to format this report to reflect each customer in individual rows. I have attempted stacking, transposing, unpivoting, dynamic arrays and index. However, when creating my 2 dimensional dynamic array for the dynamic report, my output is listing everything except my customer columns into rows. Is there a way for me to custom output my rows and columns into specific rows or is there an easier way.

Also another catch is that the first few columns in my worksheet includes data that must be applied to everyone individual customer in that corresponding row. I have attached an image for additional reference.

Any assistance is greatly appreciated!

igold · Jul 20, 2022

Welcome to the MrExcel Forum. I have a couple of questions.
1) From your attachment, it appears that for each of the 137 rows there are minimally five customers per row and probably more like 20 customers per row with each customer having 7 columns of common information and 5 columns of customer specific information - does that sound correct.
2) From your attachment it is impossible to know what character(s) are used to separate (delimit) each item of information from the Raw Data Input. In my below code example I used a double-space, I doubt that is what your generated report is using. One way to find out would be to use this formula on whatever cell (I used Cell A1) that contains the line from you Raw Data Input:

Excel Formula:

=CODE(MID(A1,10,1))

This character is the delimiter and is the single most import part of the code. Remember, I used a double-space as the delimiter.
In my code I used "Sheet1" as the sheet with the Raw Data and outputted Required Output to "Sheet2", starting in Row 2, leaving Row 1 for a Header Row.

Split text.xlsm

A

1

Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 1 Ready/permit 1 Type 1 S M 1 Quantity 1 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 2 Ready/permit 2 Type 2 S M 2 Quantity 2 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 3 Ready/permit 3 Type 3 S M 3 Quantity 3 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 4 Ready/permit 4 Type 4 S M 4 Quantity 4 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 5 Ready/permit 5 Type 5 S M 5 Quantity 5

2

Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 6 Ready/permit 1 Type 1 S M 1 Quantity 1 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 7 Ready/permit 2 Type 2 S M 2 Quantity 2 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 8 Ready/permit 3 Type 3 S M 3 Quantity 3 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 9 Ready/permit 4 Type 4 S M 4 Quantity 4 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 10 Ready/permit 5 Type 5 S M 5 Quantity 5

3

Sheet1

VBA Code:

Sub SplitRaw()

    Dim inp As String, del As String
    Dim lines, ct As Long, i As Long, rpt As Long, lRow As Long, ct2 As Long
    Dim ws1 As Worksheet: Set ws1 = Worksheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2")
    
    lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 1 To lRow
        inp = inp & ws1.Range("A" & i) & "  "
    Next
    del = "  "  '**** This is where the Delimiter goes ****
    lines = Split(inp, del)
    rpt = 0: ct = 2
    For ct2 = 0 To 10
    If rpt > UBound(lines) - 2 Then Exit Sub
        ws2.Cells(ct, 1) = lines(0 + rpt)
        ws2.Cells(ct, 2) = lines(1 + rpt)
        ws2.Cells(ct, 3) = lines(2 + rpt)
        ws2.Cells(ct, 4) = lines(3 + rpt)
        ws2.Cells(ct, 5) = lines(4 + rpt)
        ws2.Cells(ct, 6) = lines(5 + rpt)
        ws2.Cells(ct, 7) = lines(6 + rpt)
        ws2.Cells(ct, 8) = lines(7 + rpt)
        ws2.Cells(ct, 9) = lines(8 + rpt)
        ws2.Cells(ct, 10) = lines(9 + rpt)
        ws2.Cells(ct, 11) = lines(10 + rpt)
        rpt = rpt + 11
        ct = ct + 1
    Next
  
End Sub

Split text.xlsm

A

B

C

D

E

F

G

H

I

J

K

1

Header Row

2

Work Type

OW.O.#

MP.O.#

CW.O.#

P Number

Ticket Number

Customer 1

Ready/permit 1

Type 1

S M 1

Quantity 1

3

Work Type

OW.O.#

MP.O.#

CW.O.#

P Number

Ticket Number

Customer 2

Ready/permit 2

Type 2

S M 2

Quantity 2

4

Work Type

OW.O.#

MP.O.#

CW.O.#

P Number

Ticket Number

Customer 3

Ready/permit 3

Type 3

S M 3

Quantity 3

5

Work Type

OW.O.#

MP.O.#

CW.O.#

P Number

Ticket Number

Customer 4

Ready/permit 4

Type 4

S M 4

Quantity 4

6

Work Type

OW.O.#

MP.O.#

CW.O.#

P Number

Ticket Number

Customer 5

Ready/permit 5

Type 5

S M 5

Quantity 5

7

Work Type

OW.O.#

MP.O.#

CW.O.#

P Number

Ticket Number

Customer 6

Ready/permit 1

Type 1

S M 1

Quantity 1

8

Work Type

OW.O.#

MP.O.#

CW.O.#

P Number

Ticket Number

Customer 7

Ready/permit 2

Type 2

S M 2

Quantity 2

9

Work Type

OW.O.#

MP.O.#

CW.O.#

P Number

Ticket Number

Customer 8

Ready/permit 3

Type 3

S M 3

Quantity 3

10

Work Type

OW.O.#

MP.O.#

CW.O.#

P Number

Ticket Number

Customer 9

Ready/permit 4

Type 4

S M 4

Quantity 4

11

Work Type

OW.O.#

MP.O.#

CW.O.#

P Number

Ticket Number

Customer 10

Ready/permit 5

Type 5

S M 5

Quantity 5

12

13

Sheet2

DFlem · Jul 20, 2022

igold said:
Welcome to the MrExcel Forum. I have a couple of questions.
1) From your attachment, it appears that for each of the 137 rows there are minimally five customers per row and probably more like 20 customers per row with each customer having 7 columns of common information and 5 columns of customer specific information - does that sound correct.
2) From your attachment it is impossible to know what character(s) are used to separate (delimit) each item of information from the Raw Data Input. In my below code example I used a double-space, I doubt that is what your generated report is using. One way to find out would be to use this formula on whatever cell (I used Cell A1) that contains the line from you Raw Data Input:

Excel Formula:

=CODE(MID(A1,10,1))

This character is the delimiter and is the single most import part of the code. Remember, I used a double-space as the delimiter.
In my code I used "Sheet1" as the sheet with the Raw Data and outputted Required Output to "Sheet2", starting in Row 2, leaving Row 1 for a Header Row.

Split text.xlsm
A
1 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 1 Ready/permit 1 Type 1 S M 1 Quantity 1 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 2 Ready/permit 2 Type 2 S M 2 Quantity 2 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 3 Ready/permit 3 Type 3 S M 3 Quantity 3 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 4 Ready/permit 4 Type 4 S M 4 Quantity 4 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 5 Ready/permit 5 Type 5 S M 5 Quantity 5
2 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 6 Ready/permit 1 Type 1 S M 1 Quantity 1 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 7 Ready/permit 2 Type 2 S M 2 Quantity 2 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 8 Ready/permit 3 Type 3 S M 3 Quantity 3 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 9 Ready/permit 4 Type 4 S M 4 Quantity 4 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 10 Ready/permit 5 Type 5 S M 5 Quantity 5
3
Sheet1

VBA Code:

Sub SplitRaw() Dim inp As String, del As String Dim lines, ct As Long, i As Long, rpt As Long, lRow As Long, ct2 As Long Dim ws1 As Worksheet: Set ws1 = Worksheets("Sheet1") Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2") lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To lRow inp = inp & ws1.Range("A" & i) & " " Next del = " " '**** This is where the Delimiter goes **** lines = Split(inp, del) rpt = 0: ct = 2 For ct2 = 0 To 10 If rpt > UBound(lines) - 2 Then Exit Sub ws2.Cells(ct, 1) = lines(0 + rpt) ws2.Cells(ct, 2) = lines(1 + rpt) ws2.Cells(ct, 3) = lines(2 + rpt) ws2.Cells(ct, 4) = lines(3 + rpt) ws2.Cells(ct, 5) = lines(4 + rpt) ws2.Cells(ct, 6) = lines(5 + rpt) ws2.Cells(ct, 7) = lines(6 + rpt) ws2.Cells(ct, 8) = lines(7 + rpt) ws2.Cells(ct, 9) = lines(8 + rpt) ws2.Cells(ct, 10) = lines(9 + rpt) ws2.Cells(ct, 11) = lines(10 + rpt) rpt = rpt + 11 ct = ct + 1 Next End Sub

Split text.xlsm
A B C D E F G H I J K
1 Header Row
2 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 1 Ready/permit 1 Type 1 S M 1 Quantity 1
3 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 2 Ready/permit 2 Type 2 S M 2 Quantity 2
4 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 3 Ready/permit 3 Type 3 S M 3 Quantity 3
5 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 4 Ready/permit 4 Type 4 S M 4 Quantity 4
6 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 5 Ready/permit 5 Type 5 S M 5 Quantity 5
7 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 6 Ready/permit 1 Type 1 S M 1 Quantity 1
8 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 7 Ready/permit 2 Type 2 S M 2 Quantity 2
9 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 8 Ready/permit 3 Type 3 S M 3 Quantity 3
10 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 9 Ready/permit 4 Type 4 S M 4 Quantity 4
11 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 10 Ready/permit 5 Type 5 S M 5 Quantity 5
12
13
Sheet2

Hi.
Thank you for the warm welcome and your willingness to assist. There are actually 137 rows in my raw data report. Every row is a new customer, but not all rows are filled. That is just the format of the report and instead of the rows continuing in one column, after 137 rows the customers data continues in a new column and so on. The raw report has a total of 40 customers with adjacent data in into 5 columns. I apologize if that is confusing. I am very new to VBA.

How can I edit your sample data to reflect the 137 rows and adjacent customer data columns while separating the continued new customer list in columns?

DFlem · Jul 21, 2022

DFlem said:
Please assist if you can! I have an exported Excel report, each row is a new customer, but the rows stop at 137. There are about 250 columns with customer details, but only 5 columns belong to 1 customer in each row. My boss wants me to format this report to reflect each customer in individual rows. I have attempted stacking, transposing, unpivoting, dynamic arrays and index. However, when creating my 2 dimensional dynamic array for the dynamic report, my output is listing everything except my customer columns into rows. Is there a way for me to custom output my rows and columns into specific rows or is there an easier way.

Also another catch is that the first few columns in my worksheet includes data that must be applied to everyone individual customer in that corresponding row. I have attached an image for additional reference.

Any assistance is greatly appreciated!

igold said:
Welcome to the MrExcel Forum. I have a couple of questions.
1) From your attachment, it appears that for each of the 137 rows there are minimally five customers per row and probably more like 20 customers per row with each customer having 7 columns of common information and 5 columns of customer specific information - does that sound correct.
2) From your attachment it is impossible to know what character(s) are used to separate (delimit) each item of information from the Raw Data Input. In my below code example I used a double-space, I doubt that is what your generated report is using. One way to find out would be to use this formula on whatever cell (I used Cell A1) that contains the line from you Raw Data Input:

Excel Formula:

=CODE(MID(A1,10,1))

This character is the delimiter and is the single most import part of the code. Remember, I used a double-space as the delimiter.
In my code I used "Sheet1" as the sheet with the Raw Data and outputted Required Output to "Sheet2", starting in Row 2, leaving Row 1 for a Header Row.

Split text.xlsm
A
1 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 1 Ready/permit 1 Type 1 S M 1 Quantity 1 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 2 Ready/permit 2 Type 2 S M 2 Quantity 2 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 3 Ready/permit 3 Type 3 S M 3 Quantity 3 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 4 Ready/permit 4 Type 4 S M 4 Quantity 4 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 5 Ready/permit 5 Type 5 S M 5 Quantity 5
2 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 6 Ready/permit 1 Type 1 S M 1 Quantity 1 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 7 Ready/permit 2 Type 2 S M 2 Quantity 2 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 8 Ready/permit 3 Type 3 S M 3 Quantity 3 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 9 Ready/permit 4 Type 4 S M 4 Quantity 4 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 10 Ready/permit 5 Type 5 S M 5 Quantity 5
3
Sheet1

VBA Code:

Sub SplitRaw() Dim inp As String, del As String Dim lines, ct As Long, i As Long, rpt As Long, lRow As Long, ct2 As Long Dim ws1 As Worksheet: Set ws1 = Worksheets("Sheet1") Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2") lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To lRow inp = inp & ws1.Range("A" & i) & " " Next del = " " '**** This is where the Delimiter goes **** lines = Split(inp, del) rpt = 0: ct = 2 For ct2 = 0 To 10 If rpt > UBound(lines) - 2 Then Exit Sub ws2.Cells(ct, 1) = lines(0 + rpt) ws2.Cells(ct, 2) = lines(1 + rpt) ws2.Cells(ct, 3) = lines(2 + rpt) ws2.Cells(ct, 4) = lines(3 + rpt) ws2.Cells(ct, 5) = lines(4 + rpt) ws2.Cells(ct, 6) = lines(5 + rpt) ws2.Cells(ct, 7) = lines(6 + rpt) ws2.Cells(ct, 8) = lines(7 + rpt) ws2.Cells(ct, 9) = lines(8 + rpt) ws2.Cells(ct, 10) = lines(9 + rpt) ws2.Cells(ct, 11) = lines(10 + rpt) rpt = rpt + 11 ct = ct + 1 Next End Sub

Split text.xlsm
A B C D E F G H I J K
1 Header Row
2 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 1 Ready/permit 1 Type 1 S M 1 Quantity 1
3 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 2 Ready/permit 2 Type 2 S M 2 Quantity 2
4 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 3 Ready/permit 3 Type 3 S M 3 Quantity 3
5 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 4 Ready/permit 4 Type 4 S M 4 Quantity 4
6 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 5 Ready/permit 5 Type 5 S M 5 Quantity 5
7 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 6 Ready/permit 1 Type 1 S M 1 Quantity 1
8 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 7 Ready/permit 2 Type 2 S M 2 Quantity 2
9 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 8 Ready/permit 3 Type 3 S M 3 Quantity 3
10 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 9 Ready/permit 4 Type 4 S M 4 Quantity 4
11 Work Type OW.O.# MP.O.# CW.O.# P Number Ticket Number Customer 10 Ready/permit 5 Type 5 S M 5 Quantity 5
12
13
Sheet2

This may better an better view for reference. Thanks again!

RAW DATA REPORT

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

1

Work Type

M W 0#

M P O#

C WO#

CPO3

P NUM

TICKET NUM

CUSTOMER COL 1

READY/PERMIT 1

TYPE 1

SM 1

QUANTITY 1

CUSTOMER COL 2

READY/PERMIT 2

TYPE 2

SM 2

QUANTITY 2

CUSTOMER COL 3

READY/PERMIT 3

TYPE 3

SM3

QUANTITY 3

CUSTOMER 4

READY/PERMIT 4

TYPE 4

SM 4

2

XXXX

123

1111

0

123

234567

344547764

CAST

READY

TRANSFER

122348

1

JIM

PERMIT

PULL

35674

1

WIRELESS

READY

TYPE

23566

4562

WIRELESS

PERMIT

TYPE

2353

3

XXXX

1234

1111

0

123

234567

344547764

SUE

PERMIT

TRANSFER

122348

23

JOE

PERMIT

TRANSFER

53634

23

CABLE

READY

TYPE

4654

3

WIRELESS

PERMIT

WORK

46

4

XXXX

123

1111

0

123

234567

344547764

MARY

PERMIT

TRANSFER

122348

2

JACK

PERMIT

TRACK

533

TRACKS

READY

TYPE

63643

WIRELESS

PERMIT

PULL

346

5

XXXX

1234

1111

0

123

234567

344547764

JIM

READY

TRANSFER

122348

3

CABLE

PERMIT

TRANSFER

676

4

TRAIN

READY

TYPE

36346

43

WIRELESS

PERMIT

POLE

457

6

XXXX

123

1111

0

123

234567

344547764

MOBILE

PERMIT

TRANSFER

122348

4

TRAP

PERMIT

PULL

567567

5

SIM

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

346

7

XXXX

1234

1111

0

123

234567

344547764

NULL

SHOE

PERMIT

PULL

654

4

CABLE

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

3

8

XXXX

1234

1111

0

123

234567

344547764

UNKNOWN

READY

TRANSFER

122348

25

PHONE

PERMIT

PULL

654

CODE

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

6

9

XXXX

1234

1111

0

123

234567

344547764

CABLE

READY

TRANSFER

122348

56

ELECTRIC

PERMIT

PULL

654

EXCEL

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

6

10

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

44

WIRELESS

PERMIT

PULL

654

65

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

11

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

65

12

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

4.00E+05

13

XXXX

1234

1111

0

123

234567

344547764

NULL

WIRELESS

PERMIT

PULL

654

65

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

45

14

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

15

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

16

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

WIRELESS

PERMIT

TYPE

7

17

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

5

18

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

6

19

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

3

20

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

7

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

21

XXXX

1234

1111

0

123

234567

344547764

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

567

22

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

8

23

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

566

24

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

25

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

566

26

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

566

27

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

566

28

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

566

29

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

566

30

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

566

31

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

566

32

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

566

33

XXXX

1234

1111

0

123

234567

344547764

WIRELESS

READY

TRANSFER

122348

1

WIRELESS

PERMIT

PULL

654

7

VBA

READY

TYPE

36346

43

WIRELESS

PERMIT

TYPE

566

Sheet1

REQUIRED OUTPUT REPORT

Book1

A

B

C

D

E

F

G

H

I

J

K

L

1

Work Type

M W 0#

M P O#

C WO#

CPO3

P NUM

TICKET NUM

CUSTOMER COL 1

READY/PERMIT 1

TYPE 1

SM 1

QUANTITY 1

2

Work Type

M W 0#

M P O#

C WO#

CPO4

P NUM

TICKET NUM

CUSTOMER COL 2

READY/PERMIT 2

TYPE 2

SM 2

QUANTITY 2

3

Work Type

M W 0#

M P O#

C WO#

CPO5

P NUM

TICKET NUM

CUSTOMER COL 3

READY/PERMIT 3

TYPE 3

SM 3

QUANTITY 3

4

Work Type

M W 0#

M P O#

C WO#

CPO6

P NUM

TICKET NUM

CUSTOMER COL 4

READY/PERMIT 4

TYPE 4

SM 4

QUANTITY 4

5

Work Type

M W 0#

M P O#

C WO#

CPO7

P NUM

TICKET NUM

CUSTOMER COL 5

READY/PERMIT 5

TYPE 5

SM 5

QUANTITY 5

6

Work Type

M W 0#

M P O#

C WO#

CPO8

P NUM

TICKET NUM

CUSTOMER COL 6

READY/PERMIT 6

TYPE 6

SM 6

QUANTITY 6

7

Work Type

M W 0#

M P O#

C WO#

CPO9

P NUM

TICKET NUM

CUSTOMER COL 7

READY/PERMIT 7

TYPE 7

SM 7

QUANTITY 7

8

Work Type

M W 0#

M P O#

C WO#

CPO10

P NUM

TICKET NUM

CUSTOMER COL 8

READY/PERMIT 8

TYPE 8

SM 8

QUANTITY 8

9

Work Type

M W 0#

M P O#

C WO#

CPO11

P NUM

TICKET NUM

CUSTOMER COL 9

READY/PERMIT 9

TYPE 9

SM 9

QUANTITY 9

10

Work Type

M W 0#

M P O#

C WO#

CPO12

P NUM

TICKET NUM

CUSTOMER COL 10

READY/PERMIT 10

TYPE 10

SM 10

QUANTITY 10

11

Work Type

M W 0#

M P O#

C WO#

CPO13

P NUM

TICKET NUM

CUSTOMER COL 11

READY/PERMIT 11

TYPE 11

SM 11

QUANTITY 11

12

Work Type

M W 0#

M P O#

C WO#

CPO14

P NUM

TICKET NUM

CUSTOMER COL 12

READY/PERMIT 12

TYPE 12

SM 12

QUANTITY 12

13

Work Type

M W 0#

M P O#

C WO#

CPO15

P NUM

TICKET NUM

CUSTOMER COL 13

READY/PERMIT 13

TYPE 13

SM 13

QUANTITY 13

14

Work Type

M W 0#

M P O#

C WO#

CPO16

P NUM

TICKET NUM

CUSTOMER COL 14

READY/PERMIT 14

TYPE 14

SM 14

QUANTITY 14

15

Work Type

M W 0#

M P O#

C WO#

CPO17

P NUM

TICKET NUM

CUSTOMER COL 15

READY/PERMIT 15

TYPE 15

SM 15

QUANTITY 15

16

Work Type

M W 0#

M P O#

C WO#

CPO18

P NUM

TICKET NUM

CUSTOMER COL 16

READY/PERMIT 16

TYPE 16

SM 16

QUANTITY 16

17

Work Type

M W 0#

M P O#

C WO#

CPO19

P NUM

TICKET NUM

CUSTOMER COL 17

READY/PERMIT 17

TYPE 17

SM 17

QUANTITY 17

18

Work Type

M W 0#

M P O#

C WO#

CPO20

P NUM

TICKET NUM

CUSTOMER COL 18

READY/PERMIT 18

TYPE 18

SM 18

QUANTITY 18

19

Work Type

M W 0#

M P O#

C WO#

CPO21

P NUM

TICKET NUM

CUSTOMER COL 19

READY/PERMIT 19

TYPE 19

SM 19

QUANTITY 19

20

Work Type

M W 0#

M P O#

C WO#

CPO22

P NUM

TICKET NUM

CUSTOMER COL 20

READY/PERMIT 20

TYPE 20

SM 20

QUANTITY 20

21

Work Type

M W 0#

M P O#

C WO#

CPO23

P NUM

TICKET NUM

CUSTOMER COL 21

READY/PERMIT 21

TYPE 21

SM 21

QUANTITY 21

22

Work Type

M W 0#

M P O#

C WO#

CPO24

P NUM

TICKET NUM

CUSTOMER COL 22

READY/PERMIT 22

TYPE 22

SM 22

QUANTITY 22

23

Work Type

M W 0#

M P O#

C WO#

CPO25

P NUM

TICKET NUM

CUSTOMER COL 23

READY/PERMIT 23

TYPE 23

SM 23

QUANTITY 23

24

Work Type

M W 0#

M P O#

C WO#

CPO26

P NUM

TICKET NUM

CUSTOMER COL 24

READY/PERMIT 24

TYPE 24

SM 24

QUANTITY 24

25

Work Type

M W 0#

M P O#

C WO#

CPO27

P NUM

TICKET NUM

CUSTOMER COL 25

READY/PERMIT 25

TYPE 25

SM 25

QUANTITY 25

26

Work Type

M W 0#

M P O#

C WO#

CPO28

P NUM

TICKET NUM

CUSTOMER COL 26

READY/PERMIT 26

TYPE 26

SM 26

QUANTITY 26

27

Work Type

M W 0#

M P O#

C WO#

CPO29

P NUM

TICKET NUM

CUSTOMER COL 27

READY/PERMIT 27

TYPE 27

SM 27

QUANTITY 27

28

Work Type

M W 0#

M P O#

C WO#

CPO30

P NUM

TICKET NUM

CUSTOMER COL 28

READY/PERMIT 28

TYPE 28

SM 28

QUANTITY 28

29

Work Type

M W 0#

M P O#

C WO#

CPO31

P NUM

TICKET NUM

CUSTOMER COL 29

READY/PERMIT 29

TYPE 29

SM 29

QUANTITY 29

Sheet2

bebo021999 · Jul 21, 2022

With your output, only row 1 of raw data (header) is repeated
How about row 2 to 33 of raw data?
I doubt that you want to dupplicate row 2 for n customers ( n * 5 rows), then row 3 , then row 4,...
Is it correct?

DFlem · Jul 21, 2022

bebo021999 said:
With your output, only row 1 of raw data (header) is repeated
How about row 2 to 33 of raw data?
I doubt that you want to dupplicate row 2 for n customers ( n * 5 rows), then row 3 , then row 4,...
Is it correct?

Partially correct, in the raw data report columns A-G must remain in tact to go along with all customers in that same row. Except every 5 columns, there is new customer and every column in the worksheet contains new customer data. The data in A-G will need to be duplicated 40 times(40 new customer columns) to convert the columns into rows but remain in tact.

For some reason the raw report expands to new columns instead of continuing continuing down in rows.

Did that answer your question?

igold · Jul 21, 2022

I too, am a little confused. Given the raw data that you posted in your Post #4, and looking only at Row 2 of that data, is this what your expected output would be. Also, the Header Row is a little confusing...

Split text.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

1

Work Type

M W 0#

M P O#

C WO#

CPO3

P NUM

TICKET NUM

QUANTITY 1

CUSTOMER COL 2

READY/PERMIT 2

TYPE 2

SM 2

2

XXXX

123

1111

0

123

234567

344547764

1

JIM

PERMIT

PULL

35674

3

XXXX

123

1111

0

123

234567

344547764

1

WIRELESS

READY

TYPE

23566

4

XXXX

123

1111

0

123

234567

344547764

4562

WIRELESS

PERMIT

TYPE

2353

Output

Also, what would you have done with the rows where blank cells are entwined in the Raw Data.

DFlem · Jul 21, 2022

igold said:
I too, am a little confused. Given the raw data that you posted in your Post #4, and looking only at Row 2 of that data, is this what your expected output would be. Also, the Header Row is a little confusing...

Split text.xlsm
A B C D E F G H I J K L
1 Work Type M W 0# M P O# C WO# CPO3 P NUM TICKET NUM QUANTITY 1 CUSTOMER COL 2 READY/PERMIT 2 TYPE 2 SM 2
2 XXXX 123 1111 0 123 234567 344547764 1 JIM PERMIT PULL 35674
3 XXXX 123 1111 0 123 234567 344547764 1 WIRELESS READY TYPE 23566
4 XXXX 123 1111 0 123 234567 344547764 4562 WIRELESS PERMIT TYPE 2353
Output

Also, what would you have done with the rows where blank cells are entwined in the Raw Data.

Yes! This is exactly how I need to structure every row and customer in my worksheet! Any advice on how I achieve that? At the moment I am attempting to create dynamic arrays to add to an dictionary, but my dictionary output is outputting correctly.

igold · Jul 21, 2022

Can you tell me what the exact Header Row should look like. I would think that you would be dropping the numerical ending in at least Columns ("H", "I", "J") and possibly "K" and "L" as well.

DFlem · Jul 21, 2022

igold said:
Can you tell me what the exact Header Row should look like. I would think that you would be dropping the numerical ending in at least Columns ("H", "I", "J") and possibly "K" and "L" as well.

Yes, here they are. Columns A-AP & IE-JG must apply to the customer's data in the adjacent columns in that row.

esri raw report.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

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

BE

BF

BG

BH

BI

BJ

BK

BL

BM

BN

BO

BP

BQ

BR

BS

BT

BU

BV

BW

BX

BY

BZ

CA

CB

CC

CD

CE

CF

CG

CH

CI

CJ

CK

CL

CM

CN

CO

CP

CQ

CR

CS

CT

CU

CV

CW

CX

CY

CZ

DA

DB

DC

DD

DE

DF

DG

DH

DI

DJ

DK

DL

DM

DN

DO

DP

DQ

DR

DS

DT

DU

DV

DW

DX

DY

DZ

EA

EB

EC

ED

EE

EF

EG

EH

EI

EJ

EK

EL

EM

EN

EO

EP

EQ

ER

ES

ET

EU

EV

EW

EX

EY

EZ

FA

FB

FC

FD

FE

FF

FG

FH

FI

FJ

FK

FL

FM

FN

FO

FP

FQ

FR

FS

FT

FU

FV

FW

FX

FY

FZ

GA

GB

GC

GD

GE

GF

GG

GH

GI

GJ

GK

GL

GM

GN

GO

GP

GQ

GR

GS

GT

GU

GV

GW

GX

GY

GZ

HA

HB

HC

HD

HE

HF

HG

HH

HI

HJ

HK

HL

HM

HN

HO

HP

HQ

HR

HS

HT

HU

HV

HW

HX

HY

HZ

IA

IB

IC

ID

IE

IF

IG

IH

II

IJ

IK

IL

IM

IN

IO

IP

IQ

IR

IS

IT

IU

IV

IW

IX

IY

IZ

JA

JB

JC

JD

JE

JF

JG

1

OBJECTID

Received Date

Work Type

Work Reason

Office Comments

Service Center

Location

Circuit Name

Map Number

City / County

DTE Originator

O&M W.O. #

O&M P.O. #

Capital W.O. #

Capital P.O. #

GLN X

GLN Y

Pole Number

NJUNS Ticket Number

Preflight Assigned To

Field Crew Assigned To

Job Status

Latitude

Longitude

Planner Name

Pole Owner

Pole Owner (if 'Other')

Available Access

Mis Dig

Mis Dig Notes

Tree Trim

Tree Trim Notes

Pole Tag ID - Old

Pole Tag ID - New

DTE Power Only on Pole

Preflight Notes

Is Job Workable?

Field Observations

Comms to Complete Own Notes

Work Found Complete

Preflight Inspection Complete

Preflight Date

Customer 1

Make Ready / Violation 1

Work Type 1

Service Master 1

Quantity 1

Customer 2

Make Ready / Violation 2

Work Type 2

Service Master 2

Quantity 2

Customer 3

Make Ready / Violation 3

Work Type 3

Service Master 3

Quantity 3

Customer 4

Make Ready / Violation 4

Work Type 4

Service Master 4

Quantity 4

Customer 5

Make Ready / Violation 5

Work Type 5

Service Master 5

Quantity 5

Customer 6

Make Ready / Violation 6

Work Type 6

Service Master 6

Quantity 6

Customer 7

Make Ready / Violation 7

Work Type 7

Service Master 7

Quantity 7

Customer 8

Make Ready / Violation 8

Work Type 8

Service Master 8

Quantity 8

Customer 9

Make Ready / Violation 9

Work Type 9

Service Master 9

Quantity 9

Customer 10

Make Ready / Violation 10

Work Type 10

Service Master 10

Quantity 10

Customer 11

Make Ready / Violation 11

Work Type 11

Service Master 11

Quantity 11

Customer 12

Make Ready / Violation 12

Work Type 12

Service Master 12

Quantity 12

Customer 13

Make Ready / Violation 13

Work Type 13

Service Master 13

Quantity 13

Customer 14

Make Ready / Violation 14

Work Type 14

Service Master 14

Quantity 14

Customer 15

Make Ready / Violation 15

Work Type 15

Service Master 15

Quantity 15

Customer 16

Make Ready / Violation 16

Work Type 16

Service Master 16

Quantity 16

Customer 17

Make Ready / Violation 17

Work Type 17

Service Master 17

Quantity 17

Customer 18

Make Ready / Violation 18

Work Type 18

Service Master 18

Quantity

Customer 19

Make Ready / Violation 19

Work Type 19

Service Master 19

Quantity 19

Customer 20

Make Ready / Violation 20

Work Type 20

Service Master 20

Quantity 20

Customer 21

Make Ready / Violation 21

Work Type 21

Service Master 21

Quantity 21

Customer 22

Make Ready / Violation 22

Work Type 22

Service Master 22

Quantity 22

Customer 23

Make Ready / Violation 23

Work Type 23

Service Master 23

Quantity 23

Customer 24

Make Ready / Violation 24

Work Type 24

Service Master 24

Quantity 24

Customer 25

Make Ready / Violation 25

Work Type 25

Service Master

Quantity 25

Customer 26

Make Ready / Violation 26

Work Type 26

Service Master 26

Quantity 26

Customer 27

Make Ready / Violation 27

Work Type 27

Service Master 27

Quantity 27

Customer 28

Make Ready / Violation 28

Work Type 28

Service Master 28

Quantity 28

Customer 29

Make Ready / Violation 29

Work Type 29

Service Master 29

Quantity 29

Customer 30

Make Ready / Violation 30

Work Type 30

Service Master 30

Quantity 30

Customer 31

Make Ready / Violation 31

Work Type 31

Service Master 31

Quantity 31

Customer 32

Make Ready / Violation 32

Work Type 32

Service Master 32

Quantity 32

Customer 33

Make Ready / Violation 33

Work Type 33

Service Master 33

Quantity 33

Customer 34

Make Ready / Violation 34

Work Type 34

Service Master 34

Quantity 34

Customer 35

Make Ready / Violation 35

Work Type 35

Service Master 35

Quantity 35

Customer 36

Make Ready / Violation 36

Work Type 36

Service Master 36

Quantity 36

Customer 37

Make Ready / Violation 37

Work Type 37

Service Master 37

Quantity 37

Customer 38

Make Ready / Violation 38

Work Type 38

Service Master 38

Quantity 38

Customer 39

Make Ready / Violation 39

Work Type 39

Service Master 39

Quantity 39

Customer 40

Make Ready / Violation 40

Work Type 40

Service Master 40

Quantity 40

Crew Foreman

Crew Member 1

Crew Member 2

Crew Member 3

Crew Member 4

Crew Member 5

Crew Member 6

Crew Minutes

Explanation

Field Crew Notes

Work Complete

Work Complete Date

Preflight Customer

Preflight Quantity

Preflight Code

NJUNS Customer

NJUNS Quantity

NJUNS Code

GlobalID

created_user

created_date

last_edited_user

last_edited_date

x

y

Sheet2

VBA Use dynamic array to create custom report

DFlem

New Member

Attachments

Excel Facts

igold

Well-known Member

DFlem

New Member

DFlem

New Member

bebo021999

Well-known Member

DFlem

New Member

igold

Well-known Member

DFlem

New Member

igold

Well-known Member

DFlem

New Member

Similar threads

Forum statistics

Share this page

VBA Use dynamic array to create custom report

New Member

Attachments

Excel Facts

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New 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