Re Arranging Data in Excel

DEBR · May 10, 2011

The data below is downloaded from our inhouse software program. I would like to set the data into a table. The first spreadsheet is how it is downloaded. THe second spreadsheet is how I would like the table to be.
Please help to write a macro.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Invoice No 45678</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Order Nr 1234 DD20.08.1999 Order Type VOR </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD>PULLEY</TD><TD></TD><TD style="TEXT-ALIGN: right">8979421870</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD></TD><TD>5 EA </TD><TD>478.17 ZAR</TD><TD>1 EA</TD><TD style="TEXT-ALIGN: right">478.17</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">478.17</TD><TD style="TEXT-ALIGN: right">478.17</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Delivery no. </TD><TD style="TEXT-ALIGN: right">9.4E+09</TD><TD>Subtotal</TD><TD style="TEXT-ALIGN: right">478.17</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Item</TD><TD></TD><TD>Description </TD><TD></TD><TD>Material</TD><TD></TD><TD></TD><TD></TD><TD>ZAR</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD></TD><TD>Qty</TD><TD>Price</TD><TD>Price unit</TD><TD>Value</TD><TD>Trade Disc %</TD><TD>Trade Disc value</TD><TD>Net value</TD><TD>Total net value</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>Items total </TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">478.17</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>Output Tax </TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">0.14</TD><TD style="TEXT-ALIGN: right">478.17</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">66.94</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>Final Amount </TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">545.11</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>Directors: D.Duck* (President & Managing Director, ABC Africa)</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>*Brazil</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Invoice No 12345</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD>Item</TD><TD></TD><TD>Description </TD><TD></TD><TD>Material</TD><TD></TD><TD></TD><TD></TD><TD>ZAR</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD></TD><TD>Qty</TD><TD>Price</TD><TD>Price unit</TD><TD>Value</TD><TD>Trade Disc %</TD><TD>Trade Disc value</TD><TD>Net value</TD><TD>Total net value</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD>Order Nr 2468 DD01.04.1999 Order Type STK </TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD>BUSH;LEAF SPRING</TD><TD></TD><TD style="TEXT-ALIGN: right">8970748260</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD></TD><TD>2 EA </TD><TD>98.92 ZAR</TD><TD>1 EA</TD><TD style="TEXT-ALIGN: right">197.84</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">98.92</TD><TD style="TEXT-ALIGN: right">197.84</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD>OUTLET ASM-WAT</TD><TD></TD><TD style="TEXT-ALIGN: right">12605785</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD></TD><TD>1 EA </TD><TD>327.38 ZAR</TD><TD>1 EA</TD><TD style="TEXT-ALIGN: right">327.38</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">327.38</TD><TD style="TEXT-ALIGN: right">327.38</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Delivery no. </TD><TD style="TEXT-ALIGN: right">9.4E+09</TD><TD>Subtotal</TD><TD style="TEXT-ALIGN: right">525.22</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD>Items total </TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">525.22</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD>Output Tax </TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">0.14</TD><TD style="TEXT-ALIGN: right">525.22</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">73.53</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD>Final Amount </TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">598.75</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">36</TD><TD>Directors: D.Duck* (President & Managing Director, ABC Africa)</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">37</TD><TD>*Brazil</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Invoice No</TD><TD>Order Nr</TD><TD>DD</TD><TD>Order Type</TD><TD>Line</TD><TD>Description</TD><TD>Part No</TD><TD>Qty</TD><TD>Price</TD><TD>Delivery No</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">45678</TD><TD style="TEXT-ALIGN: right">1234</TD><TD>20.08.1999</TD><TD>VOR</TD><TD style="TEXT-ALIGN: right">1</TD><TD>PULLEY</TD><TD style="TEXT-ALIGN: right">8979421870</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">478.17</TD><TD style="TEXT-ALIGN: right">9401631530</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">12345</TD><TD style="TEXT-ALIGN: right">2468</TD><TD>01.04.1999</TD><TD>STK</TD><TD style="TEXT-ALIGN: right">1</TD><TD>BUSH;LEAF SPRING</TD><TD style="TEXT-ALIGN: right">8970748260</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">98.92</TD><TD style="TEXT-ALIGN: right">9401633034</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">12345</TD><TD style="TEXT-ALIGN: right">2468</TD><TD>01.04.2000</TD><TD>STK</TD><TD style="TEXT-ALIGN: right">2</TD><TD>OUTLET ASM-WAT</TD><TD style="TEXT-ALIGN: right">12605785</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">327.38</TD><TD style="TEXT-ALIGN: right">9401633034</TD></TR></TBODY></TABLE>

hiker95 · May 10, 2011

DEBR,

Sample data before the macro:

Excel Workbook

A

B

C

D

E

F

G

H

I

1

2

Invoice No 45678

3

4

5

Order Nr 1234 DD20.08.1999 Order Type VOR

6

1

PULLEY

8979421870

7

5 EA

478.17 ZAR

1 EA

478.17

0

478.17

8

Delivery no.

9401631530

Subtotal

478.17

9

Item

Description

Material

ZAR

10

Qty

Price

Price unit

Value

Trade Disc %

Trade Disc value

Net value

Total net value

11

Items total

478.17

12

Output Tax

0.14

478.17

66.94

13

Final Amount

545.11

14

15

16

Directors: D.Duck* (President & Managing Director, ABC Africa)

17

*Brazil

18

19

20

Invoice No 12345

21

Item

Description

Material

ZAR

22

Qty

Price

Price unit

Value

Trade Disc %

Trade Disc value

Net value

Total net value

23

Order Nr 2468 DD01.04.1999 Order Type STK

24

1

BUSH;LEAF SPRING

8970748260

25

2 EA

98.92 ZAR

1 EA

197.84

0

98.92

197.84

26

2

OUTLET ASM-WAT

12605785

27

1 EA

327.38 ZAR

1 EA

327.38

0

327.38

28

Delivery no.

9401633034

Subtotal

525.22

29

30

31

Items total

525.22

32

Output Tax

0.14

525.22

73.53

33

Final Amount

598.75

34

35

36

Directors: D.Duck* (President & Managing Director, ABC Africa)

37

*Brazil

38

Sheet1

Excel Workbook

L

M

N

O

P

Q

R

S

T

U

1

2

3

4

5

6

Sheet1

After the macro:

Excel Workbook

L

M

N

O

P

Q

R

S

T

U

1

2

Invoice No

Order NR

DD

Order Type

Line

Description

Part No

Qty

Price

Delivery No

3

45678

1234

20.08.1999

VOR

1

PULLEY

8979421870

5

478.17

9401631530

4

12345

2468

01.04.1999

STK

1

BUSH;LEAF SPRING

8970748260

2

98.92

9401633034

5

12345

2468

01.04.1999

STK

2

OUTLET ASM-WAT

12605785

1

327.38

9401633034

6

Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit
Option Base 1
Sub ReorgData()
' hiker95, 05/10/2011
' http://www.mrexcel.com/forum/showthread.php?t=549125
Dim c As Range, firstaddress As String
Dim a As Long, aa As Long, LR As Long, i As Long, NR As Long, b As Long
Dim Iary, Sp
Application.ScreenUpdating = False
Columns("L:U").ClearContents
Range("L2:U2") = [{"Invoice No","Order NR","DD","Order Type","Line","Description","Part No","Qty","Price","Delivery No"}]
LR = Cells(Rows.Count, 1).End(xlUp).Row
i = Application.CountIf(Columns(6), "Invoice No*")
ReDim Iary(1 To i + 1)
With Columns(6)
  i = 0
  Set c = .Find("Invoice No*", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstaddress = c.Address
    Do
      i = i + 1
      Iary(i) = c.Row
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
  End If
End With
i = i + 1
Iary(i) = LR
For i = LBound(Iary) To UBound(Iary) - 1
  NR = Range("L" & Rows.Count).End(xlUp).Offset(1).Row
  Sp = Split(Range("F" & Iary(i)), " ")
  Range("L" & NR).Value = Sp(UBound(Sp))
  b = 0
  For a = Iary(i) To Iary(i + 1) - 1 Step 1
    If Cells(a, 1) = "" Then
      'do nothing
    ElseIf InStr(Cells(a, 1), "Order Nr") > 0 Then
      Sp = Split(Cells(a, 1), " ")
      Range("M" & NR).Value = Sp(2)
      Range("N" & NR).Value = Right(Sp(3), Len(Sp(3)) - 2)
      Range("O" & NR).Value = Sp(6)
    ElseIf IsNumeric(Cells(a, 1)) Then
      b = b + 1
      If b = 1 Then
        Range("P" & NR).Value = Cells(a, 1).Value
        Range("Q" & NR).Value = Cells(a, 3).Value
        Range("R" & NR).Value = Cells(a, 5).Value
        Sp = Split(Cells(a + 1, 2), " ")
        Range("S" & NR).Value = Sp(0)
        Sp = Split(Cells(a + 1, 3), " ")
        Range("T" & NR).Value = Sp(0)
        For aa = a To Iary(i + 1) - 1 Step 1
          If Cells(aa, 6) = "Delivery no." Then
            Range("U" & NR).Value = Cells(aa, 7).Value
            Exit For
          End If
        Next aa
      ElseIf b > 1 Then
        NR = NR + 1
        Range("L" & NR).Resize(, 4).Value = Range("L" & NR - 1).Resize(, 4).Value
        Range("P" & NR).Value = Cells(a, 1).Value
        Range("Q" & NR).Value = Cells(a, 3).Value
        Range("R" & NR).Value = Cells(a, 5).Value
        Sp = Split(Cells(a + 1, 2), " ")
        Range("S" & NR).Value = Sp(0)
        Sp = Split(Cells(a + 1, 3), " ")
        Range("T" & NR).Value = Sp(0)
        For aa = a To Iary(i + 1) - 1 Step 1
          If Cells(aa, 6) = "Delivery no." Then
            Range("U" & NR).Value = Cells(aa, 7).Value
            Exit For
          End If
        Next aa
      End If
    Else
      'do nothing
    End If
  Next a
  b = 0
Next i
Columns("L:U").AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ReorgData macro.

hiker95 · May 10, 2011

DEBR,

After the updated macro:

Excel Workbook

L

M

N

O

P

Q

R

S

T

U

1

Invoice No

Order NR

DD

Order Type

Line

Description

Part No

Qty

Price

Delivery No

2

45678

1234

20.08.1999

VOR

1

PULLEY

8979421870

5

478.17

9401631530

3

12345

2468

01.04.1999

STK

1

BUSH;LEAF SPRING

8970748260

2

98.92

9401633034

4

12345

2468

01.04.1999

STK

2

OUTLET ASM-WAT

12605785

1

327.38

9401633034

5

Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:

Option Explicit
Option Base 1
Sub ReorgDataV2()
' hiker95, 05/10/2011
' http://www.mrexcel.com/forum/showthread.php?t=549125
Dim c As Range, firstaddress As String
Dim a As Long, aa As Long, LR As Long, i As Long, NR As Long, b As Long
Dim Iary, Sp
Application.ScreenUpdating = False
Columns("L:U").ClearContents
Range("L1:U1") = [{"Invoice No","Order NR","DD","Order Type","Line","Description","Part No","Qty","Price","Delivery No"}]
LR = Cells(Rows.Count, 1).End(xlUp).Row
i = Application.CountIf(Columns(6), "Invoice No*")
ReDim Iary(1 To i + 1)
With Columns(6)
  i = 0
  Set c = .Find("Invoice No*", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstaddress = c.Address
    Do
      i = i + 1
      Iary(i) = c.Row
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
  End If
End With
i = i + 1
Iary(i) = LR
For i = LBound(Iary) To UBound(Iary) - 1
  NR = Range("L" & Rows.Count).End(xlUp).Offset(1).Row
  Sp = Split(Range("F" & Iary(i)), " ")
  Range("L" & NR).Value = Sp(UBound(Sp))
  b = 0
  For a = Iary(i) To Iary(i + 1) - 1 Step 1
    If Cells(a, 1) = "" Then
      'do nothing
    ElseIf InStr(Cells(a, 1), "Order Nr") > 0 Then
      Sp = Split(Cells(a, 1), " ")
      Range("M" & NR).Value = Sp(2)
      Range("N" & NR).Value = Right(Sp(3), Len(Sp(3)) - 2)
      Range("O" & NR).Value = Sp(6)
    ElseIf IsNumeric(Cells(a, 1)) Then
      b = b + 1
      If b = 1 Then
        Range("P" & NR).Value = Cells(a, 1).Value
        Range("Q" & NR).Value = Cells(a, 3).Value
        Range("R" & NR).Value = Cells(a, 5).Value
        Sp = Split(Cells(a + 1, 2), " ")
        Range("S" & NR).Value = Sp(0)
        Sp = Split(Cells(a + 1, 3), " ")
        Range("T" & NR).Value = Sp(0)
        For aa = a To Iary(i + 1) - 1 Step 1
          If Cells(aa, 6) = "Delivery no." Then
            Range("U" & NR).Value = Cells(aa, 7).Value
            Exit For
          End If
        Next aa
      ElseIf b > 1 Then
        NR = NR + 1
        Range("L" & NR).Resize(, 4).Value = Range("L" & NR - 1).Resize(, 4).Value
        Range("P" & NR).Value = Cells(a, 1).Value
        Range("Q" & NR).Value = Cells(a, 3).Value
        Range("R" & NR).Value = Cells(a, 5).Value
        Sp = Split(Cells(a + 1, 2), " ")
        Range("S" & NR).Value = Sp(0)
        Sp = Split(Cells(a + 1, 3), " ")
        Range("T" & NR).Value = Sp(0)
        For aa = a To Iary(i + 1) - 1 Step 1
          If Cells(aa, 6) = "Delivery no." Then
            Range("U" & NR).Value = Cells(aa, 7).Value
            Exit For
          End If
        Next aa
      End If
    Else
      'do nothing
    End If
  Next a
  b = 0
Next i
Columns("L:U").AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ReorgDataV2 macro.

DEBR · May 11, 2011

Hi
The code does work, but it is not putting the delivery note no.
Below is the original invoice. The invoice no is in a different column and this invoice has 2 pages. Please could you reset the code to read from this invoice.Also bear in mind that I have +/- 500 invoices in my spreadsheet all with different number of items, some 1 page and some 2 pages but there is a 3 row space between them after "Brazil".

Your assistance is greatly appreciated.
Thank You in advance.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD> </TD><TD>P&A Sales Tax Invoice 9202325070</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD>Date:29.04.2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD> </TD><TD>Customer number </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD style="TEXT-ALIGN: right">1331</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD>Your VAT Registration number</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD><TD>ZA4350110971</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD> </TD><TD>These parts and accessories are manufactured</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD> </TD><TD>to General Motors specification </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>PO Box 585</TD><TD>Page 1 of 2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">3610</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>Requirements:</TD><TD>Terms of payment 1 Calendar Month from Statement</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>Item</TD><TD> </TD><TD>Description </TD><TD> </TD><TD>Material</TD><TD> </TD><TD> </TD><TD> </TD><TD>ZAR</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD> </TD><TD>Qty</TD><TD>Price</TD><TD>Price unit</TD><TD>Value</TD><TD>Trade Disc %</TD><TD>Trade Disc value</TD><TD>Net value</TD><TD>Total net value</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>Order No.: 0003367320 DD 28.04.2011; Order Type YSTK; Ship. Method ;Cust. Order no. 84317_PART1;Delivery note 9401632395 </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD>MOUNT ASM;ENG FRT</TD><TD> </TD><TD style="TEXT-ALIGN: right">93302281</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD> </TD><TD>5 EA </TD><TD>130.84 ZAR</TD><TD>1 EA</TD><TD style="TEXT-ALIGN: right">654.2</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">130.84</TD><TD style="TEXT-ALIGN: right">654.2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD>CLUTCH KIT</TD><TD> </TD><TD>TFK003</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD> </TD><TD>2 EA </TD><TD>763.82 ZAR</TD><TD>1 EA</TD><TD style="TEXT-ALIGN: right">1527.64</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">763.82</TD><TD style="TEXT-ALIGN: right">1527.64</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD> </TD><TD>These parts and accessories are manufactured</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD> </TD><TD>to General Motors specification </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD> </TD><TD>Page 2 of 2</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD>PO Box 585</TD><TD>Sales Tax Invoice Doc.no./Date </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD style="TEXT-ALIGN: right">3610</TD><TD>9202325070/29.04.2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD>Item</TD><TD> </TD><TD>Description </TD><TD> </TD><TD>Material</TD><TD> </TD><TD> </TD><TD> </TD><TD>ZAR</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD> </TD><TD>Qty</TD><TD>Price</TD><TD>Price unit</TD><TD>Value</TD><TD>Trade Disc %</TD><TD>Trade Disc value</TD><TD>Net value</TD><TD>Total net value</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">36</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">37</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">38</TD><TD style="TEXT-ALIGN: right">3</TD><TD> </TD><TD>STARTER MOTOR</TD><TD> </TD><TD style="TEXT-ALIGN: right">93614584</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">39</TD><TD> </TD><TD>1 EA </TD><TD>749.19 ZAR</TD><TD>1 EA</TD><TD style="TEXT-ALIGN: right">749.19</TD><TD style="TEXT-ALIGN: right">10.75- </TD><TD style="TEXT-ALIGN: right">80.54- </TD><TD style="TEXT-ALIGN: right">668.65</TD><TD style="TEXT-ALIGN: right">668.65</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">40</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Delivery no. </TD><TD style="TEXT-ALIGN: right">9.4E+09</TD><TD>Subtotal</TD><TD style="TEXT-ALIGN: right">4707.41</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">41</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">42</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">43</TD><TD>Items total </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">4707.41</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">44</TD><TD>Output Tax </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">0.14</TD><TD style="TEXT-ALIGN: right">4707.41</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">659.04</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">45</TD><TD>Final Amount </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">5366.45</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">46</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">47</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">48</TD><TD>Directors: E.Lourencon* (President & Managing Director, GM Africa),J.M. Baepi, M.G. Sacke</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">49</TD><TD>*Brazil</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

hiker95 · May 11, 2011

DEBR,

The macro works correctly for your first set of screenshots.

But, it will not work correctly with your latest screenshot - the data has shifted left.

I am willing to look at an actual workbook containg the worksheet with the below screenshots. But, I will need many more samples of the Invoices below the screenshot shown. And, I will need to see beginning in colulm L, what the results should be from the first and second invoice.

You can upload your workbook to www.box.net and provide us with a link to your workbook.

hiker95 · May 11, 2011

DEBR,

The macro works correctly for your first set of screenshots.

But, it will not work correctly with your latest screenshot - the data has shifted right.

I am willing to look at an actual workbook containg the worksheet with the below screenshots. But, I will need many more samples of the Invoices below the screenshot shown. And, I will need to see beginning in colulm L, what the results should be from the first and second invoice.

You can not give us screenshots[/u], you can upload your workbook to www.box.net and provide us with a link to your workbook.

DEBR · May 12, 2011

Hi

Please find file to the link below:
http://www.box.net/shared/nf5cp63mqo

I would like to thank you for helping me in advance. It would make my workload much lighter.

Thank You

hiker95 · May 12, 2011

DEBR,

The following is based on your latest workbook on Box.Net.

Sample data after the updated macro (only a few rows are shown from the total of 151 rows):

Excel Workbook

L

M

N

O

P

Q

R

S

T

U

1

Invoice No

Order NR

DD

Order Type

Line

Description

Part No

Qty

Price

Delivery No

2

9202323920

3339649

30.03.2011

YVOR

1

PULLEY

8979421870

1

478.17

9401631530

3

9202323926

3345786

04.04.2011

YVOR

1

MOLDING A-ROOF

96543107

1

113

9401631413

36

9202325068

3367547

29.04.2011

YEMG

1

BUSH;LEAF SPRING

8970748260

2

98.92

9401633034

37

9202325068

3367547

29.04.2011

YEMG

2

OUTLET ASM-WAT

12605785

1

327.38

9401633034

38

9202325069

3367541

29.04.2011

YEMG

1

BUSHING;LWR CONTROL ARM

93395604

2

58.58

9401633023

39

9202325070

3367320

28.04.2011

YSTK

1

CABLE ASM MANUAL TRANSM SHIFT LEVER

93386267

1

744.24

9401632395

40

9202325070

3367320

28.04.2011

YSTK

2

CABLE ASM;PARK BRAKE

93601359

1

168.31

9401632395

41

9202325070

3367320

28.04.2011

YSTK

3

HANDBRAKE CABLE - INTERMEDIATE

93598311

1

54.49

9401632395

42

9202325070

3367320

28.04.2011

YSTK

4

HAND BRAKE CABLE ASM-RR-R

93597527

1

93.16

9401632395

43

9202325070

3367320

28.04.2011

YSTK

5

HAND BRAKE CABLE ASM-RR-L

93597526

2

92.59

9401632395

44

9202325070

3367320

28.04.2011

YSTK

6

0IPER BLADE - DRIVER SIDE

93194632

1

129.7

9401632395

45

9202325070

3367320

28.04.2011

YSTK

7

DISC PADS FRONT (SET)

93188726

1

366.93

9401632395

46

9202325070

3367320

28.04.2011

YSTK

8

TAIL LAMP WITH CIRCUIT;LH

93313893

1

168.78

9401632395

47

9202325070

3367320

28.04.2011

YSTK

9

MOUNT ASM;ENG FRT

93302281

5

130.84

9401632395

48

9202325070

3367320

28.04.2011

YSTK

10

CLUTCH KIT

TFK003

2

763.82

9401632395

49

9202325070

3367320

28.04.2011

YSTK

11

STARTER MOTOR

93614584

1

749.19

9401632395

50

9202325071

3366324

28.04.2011

YEMG

1

PIPE WATER OUTLET

8976020470

1

345.55

9401632168

51

9202325072

3356852

14.04.2011

YEMG

1

BLADE; WIPER

8980537880

1

179.34

9401631995

Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit
Option Base 1
Sub ReorgDataV3()
' hiker95, 05/12/2011
' http://www.mrexcel.com/forum/showthread.php?t=549125
Dim c As Range, firstaddress As String
Dim a As Long, aa As Long, LR As Long, i As Long, NR As Long, b As Long
Dim Iary, Sp, myChar As String
Application.ScreenUpdating = False
myChar = Chr(160)
With Range("B1", Range("B" & Rows.Count).End(xlUp))
  .Replace What:=myChar, Replacement:=" ", LookAt:=xlPart, _
  SearchFormat:=False, ReplaceFormat:=False
End With
Columns("L:U").ClearContents
Range("L1:U1") = [{"Invoice No","Order NR","DD","Order Type","Line","Description","Part No","Qty","Price","Delivery No"}]
i = Application.CountIf(Columns(1), "*Brazil")
ReDim Iary(1 To i + 1)
For a = 1 To 10 Step 1
  If InStr(Cells(a, 2), "Invoice") > 0 Then
    Iary(1) = a
    Exit For
  End If
Next a
With Columns(1)
  i = 1
  Set c = .Find("*Brazil", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstaddress = c.Address
    Do
      i = i + 1
      Iary(i) = c.Row + 4
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
  End If
End With
For i = LBound(Iary) To UBound(Iary) - 1
  NR = Range("L" & Rows.Count).End(xlUp).Offset(1).Row
  Sp = Split(Range("B" & Iary(i)), " ")
  Range("L" & NR).Value = Sp(UBound(Sp))
  b = 0
  For a = Iary(i) To Iary(i + 1) - 1 Step 1
    If Cells(a, 1) = "" Then
      'do nothing
    ElseIf InStr(Cells(a, 1), "Order No") > 0 Then
      Sp = Split(Cells(a, 1), " ")
      Range("M" & NR).Value = Sp(2)
      Range("N" & NR).Value = Left(Sp(4), Len(Sp(4)) - 1)
      Range("O" & NR).Value = Left(Sp(7), Len(Sp(7)) - 1)
    ElseIf IsNumeric(Cells(a, 1)) Then
      b = b + 1
      If b = 1 Then
        Range("P" & NR).Value = Cells(a, 1).Value
        Range("Q" & NR).Value = Cells(a, 3).Value
        Range("R" & NR).Value = Cells(a, 5).Value
        Range("S" & NR).Value = Left(Cells(a + 1, 2), Len(Cells(a + 1, 2)) - 3)
        Sp = Split(Cells(a + 1, 3), " ")
        Range("T" & NR).Value = Sp(0)
        For aa = a To Iary(i + 1) - 1 Step 1
          If InStr(Cells(aa, 6), "Delivery no.") > 0 Then
            Range("U" & NR).Value = Cells(aa, 7).Value
            Exit For
          End If
        Next aa
      ElseIf b > 1 Then
        NR = NR + 1
        Range("L" & NR).Resize(, 4).Value = Range("L" & NR - 1).Resize(, 4).Value
        Range("P" & NR).Value = Cells(a, 1).Value
        Range("Q" & NR).Value = Cells(a, 3).Value
        Range("R" & NR).Value = Cells(a, 5).Value
        Sp = Split(Cells(a + 1, 2), " ")
        Range("S" & NR).Value = Sp(0)
        Sp = Split(Cells(a + 1, 3), " ")
        Range("T" & NR).Value = Sp(0)
        For aa = a To Iary(i + 1) - 1 Step 1
          If InStr(Cells(aa, 6), "Delivery no.") > 0 Then
            Range("U" & NR).Value = Cells(aa, 7).Value
            Exit For
          End If
        Next aa
        Range("U" & NR).Value = Range("U" & NR - 1).Value
      End If
    Else
      'do nothing
    End If
  Next a
  b = 0
Next i
Columns("L:U").AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ReorgDataV3 macro.

DEBR · May 13, 2011

Thank you very much, it works perfectly.
If its not to much to ask if you could add Cust Order No to the table.

eg:Cust Ord No
DEN81396

Thank You again for all your help.

hiker95 · May 13, 2011

DEBR,

If its not to much to ask if you could add Cust Order No to the table.

I did ask for (which you did not supply):
And, I will need to see beginning in colulm L, what the results should be from the first and second invoice.

From the last workbook on Box.Net, I will need a screenshot: manually completed by you, beginning in column L, cell L1, all the titles, and beginning in L2, the data from the first two Invoices.

Re Arranging Data in Excel

DEBR

New Member

Excel Facts

hiker95

Well-known Member

hiker95

Well-known Member

DEBR

New Member

hiker95

Well-known Member

hiker95

Well-known Member

DEBR

New Member

hiker95

Well-known Member

DEBR

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

Re Arranging Data in Excel

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New 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