I only have limited knowledge of vb and am looking for the
code to help me do the following;

I have a spreadsheet as example following

Acc No. Acc Name Invoice No. Value
100 ABC 250 50
100 ABC 252 45
102 EFG 198 34
100 ABC 253 89
103 XYZ 134 76

I need to bring the records together onto one row for each
account no so that it looks like the following;

Acc No Acc Name Invoice No. Value Invoice No. Value etc
100 ABC 250 50 252 45
102 EFG 198 34 134 76

Is there any code that is able to look at each change in
the Account No and bring all relating invoice no's and
values onto the same row?

I am trying to achieve this for 6000 records that are
grouped together so that a mail merge can be performed for
each account no.

Any help would be great!

This should work:

Sub Tester()

Cells.Sort _
Key1:=Range("A2"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

LastRow = Cells(65536, 1).End(xlUp).Row
For i = 2 To LastRow - 1
CurNo = Cells(i, 1)
Do
j = j + 1
NextNo = Cells(i + j, 1)
If NextNo = CurNo Then ShiftCells i, j
Loop Until NextNo < CurNo
i = i + j - 1
j = 0
Next i

Columns(1).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete
y = Cells(1, 1).CurrentRegion.Columns.Count

For i = 5 To y - 1 Step 2
Cells(1, i) = "Invoice No"
Cells(1, i + 1) = "Value"
Next i

End Sub
Sub ShiftCells(i, j)
LastCell = Cells(i, 1).End(xlToRight).Column
Cells(i, LastCell + 1) = Cells(i + j, 3)
Cells(i, LastCell + 2) = Cells(i + j, 4)
Rows(i + j).ClearContents
End Sub

Shockley





"sw" wrote in message
...
I only have limited knowledge of vb and am looking for the
code to help me do the following;

I have a spreadsheet as example following

Acc No. Acc Name Invoice No. Value
100 ABC 250 50
100 ABC 252 45
102 EFG 198 34
100 ABC 253 89
103 XYZ 134 76

I need to bring the records together onto one row for each
account no so that it looks like the following;

Acc No Acc Name Invoice No. Value Invoice No. Value etc
100 ABC 250 50 252 45
102 EFG 198 34 134 76

Is there any code that is able to look at each change in
the Account No and bring all relating invoice no's and
values onto the same row?

I am trying to achieve this for 6000 records that are
grouped together so that a mail merge can be performed for
each account no.

Any help would be great!

That's great and the code works, how about if I place
other columns of information in against each account
number e.g. displays the address details for each record.
what part of the code would I need to change to enable me
to do exactly the same thing (bringing the invoice no. and
value onto same row) if I have more than 2 other columns
(e.g. at the moment it's just Acc No and Acc Name, but I
may want to have Address, Town, City columns?)

Hope this makes sense & thanks for your help on this!

-----Original Message-----
This should work:

Sub Tester()

Cells.Sort _
Key1:=Range("A2"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

LastRow = Cells(65536, 1).End(xlUp).Row
For i = 2 To LastRow - 1
CurNo = Cells(i, 1)
Do
j = j + 1
NextNo = Cells(i + j, 1)
If NextNo = CurNo Then ShiftCells i, j
Loop Until NextNo < CurNo
i = i + j - 1
j = 0
Next i

Columns(1).SpecialCells
(xlCellTypeBlanks).EntireRow.Delete
y = Cells(1, 1).CurrentRegion.Columns.Count

For i = 5 To y - 1 Step 2
Cells(1, i) = "Invoice No"
Cells(1, i + 1) = "Value"
Next i

End Sub
Sub ShiftCells(i, j)
LastCell = Cells(i, 1).End(xlToRight).Column
Cells(i, LastCell + 1) = Cells(i + j, 3)
Cells(i, LastCell + 2) = Cells(i + j, 4)
Rows(i + j).ClearContents
End Sub

Shockley





"sw" wrote in message
...
I only have limited knowledge of vb and am looking for
the
code to help me do the following;

I have a spreadsheet as example following

Acc No. Acc Name Invoice No. Value
100 ABC 250 50
100 ABC 252 45
102 EFG 198 34
100 ABC 253 89
103 XYZ 134 76

I need to bring the records together onto one row for
each
account no so that it looks like the following;

Acc No Acc Name Invoice No. Value Invoice No. Value
etc
100 ABC 250 50 252 45
102 EFG 198 34 134 76

Is there any code that is able to look at each change in
the Account No and bring all relating invoice no's and
values onto the same row?

I am trying to achieve this for 6000 records that are
grouped together so that a mail merge can be performed
for
each account no.

Any help would be great!



.

If you change the number of columns NOT to be shifted, say, if you add a
column called "Acc Address", then it must go in one of the first 3 columns,
and you would change the public constant "BaseValues" to "3" (from "2") in
the constants declaration section (first line of code).

Now the code handles any number of "BaseValues" and "ShiftValues".

The code assumes that the original sheet has headers for each populated
column, eg, in your example, the first row would look like:

Acc No Acc Name Invoice No Value Address Town City

Hope this is clear enough,
Shockley


Public Const BaseValues As Integer = 2
Public ShiftValues As Integer
Sub Tester()

ShiftValues = Cells(1, 1). _
End(xlToRight).Column - BaseValues
Cells.Sort _
Key1:=Range("A2"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
LastRow = Cells(65536, 1).End(xlUp).Row

For i = 2 To LastRow - 1
CurNo = Cells(i, 1)
Do
j = j + 1
NextNo = Cells(i + j, 1)
If NextNo = CurNo Then ShiftCells i, j
Loop Until NextNo < CurNo
i = i + j - 1
j = 0
Next i

Columns(1).SpecialCells(xlCellTypeBlanks) _
.EntireRow.Delete
y = Cells(1, 1).CurrentRegion.Columns.Count
z = BaseValues + ShiftValues + 1

For i = z To y - ShiftValues + 1 Step ShiftValues
For j = 1 To ShiftValues
Cells(1, i + j - 1) = Cells(1, BaseValues + j)
Next j
Next i

End Sub
Sub ShiftCells(i, j)
LastCell = Cells(i, 1).End(xlToRight).Column
For k = 1 To ShiftValues
Cells(i, LastCell + k) = Cells(i + j, BaseValues + k)
Next k
Rows(i + j).ClearContents
End Sub





"sw" wrote in message
...
That's great and the code works, how about if I place
other columns of information in against each account
number e.g. displays the address details for each record.
what part of the code would I need to change to enable me
to do exactly the same thing (bringing the invoice no. and
value onto same row) if I have more than 2 other columns
(e.g. at the moment it's just Acc No and Acc Name, but I
may want to have Address, Town, City columns?)

Hope this makes sense & thanks for your help on this!

-----Original Message-----
This should work:

Sub Tester()

Cells.Sort _
Key1:=Range("A2"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

LastRow = Cells(65536, 1).End(xlUp).Row
For i = 2 To LastRow - 1
CurNo = Cells(i, 1)
Do
j = j + 1
NextNo = Cells(i + j, 1)
If NextNo = CurNo Then ShiftCells i, j
Loop Until NextNo < CurNo
i = i + j - 1
j = 0
Next i

Columns(1).SpecialCells
(xlCellTypeBlanks).EntireRow.Delete
y = Cells(1, 1).CurrentRegion.Columns.Count

For i = 5 To y - 1 Step 2
Cells(1, i) = "Invoice No"
Cells(1, i + 1) = "Value"
Next i

End Sub
Sub ShiftCells(i, j)
LastCell = Cells(i, 1).End(xlToRight).Column
Cells(i, LastCell + 1) = Cells(i + j, 3)
Cells(i, LastCell + 2) = Cells(i + j, 4)
Rows(i + j).ClearContents
End Sub

Shockley





"sw" wrote in message
...
I only have limited knowledge of vb and am looking for
the
code to help me do the following;

I have a spreadsheet as example following

Acc No. Acc Name Invoice No. Value
100 ABC 250 50
100 ABC 252 45
102 EFG 198 34
100 ABC 253 89
103 XYZ 134 76

I need to bring the records together onto one row for
each
account no so that it looks like the following;

Acc No Acc Name Invoice No. Value Invoice No. Value
etc
100 ABC 250 50 252 45
102 EFG 198 34 134 76

Is there any code that is able to look at each change in
the Account No and bring all relating invoice no's and
values onto the same row?

I am trying to achieve this for 6000 records that are
grouped together so that a mail merge can be performed
for
each account no.

Any help would be great!



.