Prev: General Question on one line of code - parentheses and quotation marks
Next: Using Select Case on Single column only
From: Jim Berglund on 21 May 2010 17:34
This works - make sure your data is in 3 columns, starting in Cell A1
Jim Berglund

Sub Concatenate()
Dim i, j, n, p As Integer

i = 2
n = 2
p = 2

Cells(i, 4).Value = Cells(i, 1).Value
ActiveSheet.Range("A1").Select
j = ActiveCell.CurrentRegion.Rows.Count

For i = 2 To j
While Cells(i, 1).Value = Cells(p, 4).Value
Cells(n, 5).Value = (Cells(n, 5).Value & ", " & Cells(i, 2).Value) & "
$" & Cells(i, 3).Value
Cells(n, 6).Value = Cells(n, 6).Value + Cells(i, 3).Value
i = i + 1

Wend
p = p + 1
n = n + 1
Cells(p, 4).Value = Cells(i, 1).Value
i = i - 1
Next

End Sub


"ILoveMyCorgi" <ILoveMyCorgi(a)discussions.microsoft.com> wrote in message
news:9166781B-1128-478E-973E-76D046A23E9E(a)microsoft.com...
> I have an Excel spreadsheet with three columns: ColA has a student number,
> ColB has a comment, and ColC has an amount. I have many rows of different
> comments and amounts for the same student number followed by rows with new
> student numbers and so on. I need to move all of columns B and columns C
> to
> the same row of the first line for the student number and move on to the
> next
> student number. What I am trying to do is have all the data for one
> student
> on one row so that I can merge the data with a Word document.
>
> For instance,
> 1495 writing in book $10.00
> 1495 football trans $ 5.00
> 3456 Water damage $15.00
> 3456 Lost Textbook $35.00
>
> Witn an outcome of:
> 1495 writing in book $10.00 football trans $5.00
> 3456 Water damage $15.00 Lost Textbook $35.00
>
> I hope someone can help me with this. Thak you.

From: Rich Locus on 21 May 2010 17:45
I found your problem. When you copied and pasted the program, it broke up
some of the longer lines into two separate lines, which is a no-no unless you
use the "underscore" continuation character.

Notice that you have two groups of "2 lines" that are red. Each of those
groups should be one continuous line that is not broken into two. That will
fix your issue, and PLEASE check that this is the answer when it works.
--
Rich Locus
Logicwurks, LLC


"ILoveMyCorgi" wrote:

> I have an Excel spreadsheet with three columns: ColA has a student number,
> ColB has a comment, and ColC has an amount. I have many rows of different
> comments and amounts for the same student number followed by rows with new
> student numbers and so on. I need to move all of columns B and columns C to
> the same row of the first line for the student number and move on to the next
> student number. What I am trying to do is have all the data for one student
> on one row so that I can merge the data with a Word document.
>
> For instance,
> 1495 writing in book $10.00
> 1495 football trans $ 5.00
> 3456 Water damage $15.00
> 3456 Lost Textbook $35.00
>
> Witn an outcome of:
> 1495 writing in book $10.00 football trans $5.00
> 3456 Water damage $15.00 Lost Textbook $35.00
>
> I hope someone can help me with this. Thak you.
From: Rich Locus on 21 May 2010 18:45
Jim:

Pretty nifty approach. It worked well except that the notes in column b for
the very first row didn't get copied. Cool concatenation code!! I will add
this to my library of examples.
--
Rich Locus
Logicwurks, LLC


"Jim Berglund" wrote:

> This works - make sure your data is in 3 columns, starting in Cell A1
> Jim Berglund
>
> Sub Concatenate()
> Dim i, j, n, p As Integer
>
> i = 2
> n = 2
> p = 2
>
> Cells(i, 4).Value = Cells(i, 1).Value
> ActiveSheet.Range("A1").Select
> j = ActiveCell.CurrentRegion.Rows.Count
>
> For i = 2 To j
> While Cells(i, 1).Value = Cells(p, 4).Value
> Cells(n, 5).Value = (Cells(n, 5).Value & ", " & Cells(i, 2).Value) & "
> $" & Cells(i, 3).Value
> Cells(n, 6).Value = Cells(n, 6).Value + Cells(i, 3).Value
> i = i + 1
>
> Wend
> p = p + 1
> n = n + 1
> Cells(p, 4).Value = Cells(i, 1).Value
> i = i - 1
> Next
>
> End Sub
>
>
> "ILoveMyCorgi" <ILoveMyCorgi(a)discussions.microsoft.com> wrote in message
> news:9166781B-1128-478E-973E-76D046A23E9E(a)microsoft.com...
> > I have an Excel spreadsheet with three columns: ColA has a student number,
> > ColB has a comment, and ColC has an amount. I have many rows of different
> > comments and amounts for the same student number followed by rows with new
> > student numbers and so on. I need to move all of columns B and columns C
> > to
> > the same row of the first line for the student number and move on to the
> > next
> > student number. What I am trying to do is have all the data for one
> > student
> > on one row so that I can merge the data with a Word document.
> >
> > For instance,
> > 1495 writing in book $10.00
> > 1495 football trans $ 5.00
> > 3456 Water damage $15.00
> > 3456 Lost Textbook $35.00
> >
> > Witn an outcome of:
> > 1495 writing in book $10.00 football trans $5.00
> > 3456 Water damage $15.00 Lost Textbook $35.00
> >
> > I hope someone can help me with this. Thak you.
>
> .
>
From: JLGWhiz on 21 May 2010 19:20
Make a copy of your sheet and try this code on it. It seems to work, but to
be safe, it is better to test it first.

Sub stuFee()
Dim sh As Worksheet, lr As Long, rng As Range
Dim c As Range, f As Range
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)

For Each c In rng
sNum = c.Value
With rng
Set f = .Find(sNum, LookIn:=xlValues)
If Not f Is Nothing Then
fAddr = c.Address
Do
If f.Address <> c.Address Then
lc = sh.Cells(c.Row, Columns.Count).End(xlToLeft).Column
f.Offset(0, 1).Resize(1, 2).Copy sh.Cells(c.Row, lc + 1)
Rows(f.Row).Delete
End If
Set f = .FindNext(c)

Loop While Not c Is Nothing And f.Address <> fAddr
End If
End With
Next
End Sub



"ILoveMyCorgi" <ILoveMyCorgi(a)discussions.microsoft.com> wrote in message
news:9166781B-1128-478E-973E-76D046A23E9E(a)microsoft.com...
>I have an Excel spreadsheet with three columns: ColA has a student number,
> ColB has a comment, and ColC has an amount. I have many rows of different
> comments and amounts for the same student number followed by rows with new
> student numbers and so on. I need to move all of columns B and columns C
> to
> the same row of the first line for the student number and move on to the
> next
> student number. What I am trying to do is have all the data for one
> student
> on one row so that I can merge the data with a Word document.
>
> For instance,
> 1495 writing in book $10.00
> 1495 football trans $ 5.00
> 3456 Water damage $15.00
> 3456 Lost Textbook $35.00
>
> Witn an outcome of:
> 1495 writing in book $10.00 football trans $5.00
> 3456 Water damage $15.00 Lost Textbook $35.00
>
> I hope someone can help me with this. Thak you.


From: JLGWhiz on 21 May 2010 21:02
Just noticed that a couple of lines are long enough to cause line wrap.
This should paste into the code window better.

Sub stuFee()
Dim sh As Worksheet, lr As Long, rng As Range
Dim c As Range, f As Range
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
For Each c In rng
sNum = c.Value
With rng
Set f = .Find(sNum, LookIn:=xlValues)
If Not f Is Nothing Then
fAddr = c.Address
Do
If f.Address <> c.Address Then
lc = sh.Cells(c.Row, Columns. _
Count).End(xlToLeft).Column
f.Offset(0, 1).Resize(1, 2). _
Copy sh.Cells(c.Row, lc + 1)
Rows(f.Row).Delete
End If
Set f = .FindNext(c)
Loop While Not c Is Nothing And _
f.Address <> fAddr
End If
End With
Next
End Sub







"JLGWhiz" <JLGWhiz(a)cfl.rr.com> wrote in message
news:eJW$lwT%23KHA.5464(a)TK2MSFTNGP05.phx.gbl...
> Make a copy of your sheet and try this code on it. It seems to work, but
> to be safe, it is better to test it first.
>
> Sub stuFee()
> Dim sh As Worksheet, lr As Long, rng As Range
> Dim c As Range, f As Range
> Set sh = ActiveSheet
> lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
> Set rng = sh.Range("A2:A" & lr)
>
> For Each c In rng
> sNum = c.Value
> With rng
> Set f = .Find(sNum, LookIn:=xlValues)
> If Not f Is Nothing Then
> fAddr = c.Address
> Do
> If f.Address <> c.Address Then
> lc = sh.Cells(c.Row, Columns.Count).End(xlToLeft).Column
> f.Offset(0, 1).Resize(1, 2).Copy sh.Cells(c.Row, lc + 1)
> Rows(f.Row).Delete
> End If
> Set f = .FindNext(c)
>
> Loop While Not c Is Nothing And f.Address <> fAddr
> End If
> End With
> Next
> End Sub
>
>
>
> "ILoveMyCorgi" <ILoveMyCorgi(a)discussions.microsoft.com> wrote in message
> news:9166781B-1128-478E-973E-76D046A23E9E(a)microsoft.com...
>>I have an Excel spreadsheet with three columns: ColA has a student number,
>> ColB has a comment, and ColC has an amount. I have many rows of
>> different
>> comments and amounts for the same student number followed by rows with
>> new
>> student numbers and so on. I need to move all of columns B and columns C
>> to
>> the same row of the first line for the student number and move on to the
>> next
>> student number. What I am trying to do is have all the data for one
>> student
>> on one row so that I can merge the data with a Word document.
>>
>> For instance,
>> 1495 writing in book $10.00
>> 1495 football trans $ 5.00
>> 3456 Water damage $15.00
>> 3456 Lost Textbook $35.00
>>
>> Witn an outcome of:
>> 1495 writing in book $10.00 football trans $5.00
>> 3456 Water damage $15.00 Lost Textbook $35.00
>>
>> I hope someone can help me with this. Thak you.
>
>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: General Question on one line of code - parentheses and quotation marks
Next: Using Select Case on Single column only