Formatting issue

I'd like this:

Column A Column B
A 1
A 2
A 3
A 4
B 1
B 2
C 1
D 1

to look like:

A 1 2 3 4
B 1 2
C 1
D 1

The numbers should be in the same cell, not different columns.

Easy, right?

Thanks.

Yep!

Sub MoveData()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
Cells(i, "B").Resize(1, 25).Copy Destination:=Cells(i - 1, "C")
Cells(i, "A").EntireRow.Delete
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"adin" <[email protected]> wrote in message
news:[email protected]...
> I'd like this:
>
> Column A Column B
> A 1
> A 2
> A 3
> A 4
> B 1
> B 2
> C 1
> D 1
>
> to look like:
>
> A 1 2 3 4
> B 1 2
> C 1
> D 1
>
> The numbers should be in the same cell, not different columns.
>
> Easy, right?
>
> Thanks.
>
>

how many rows of data do you have and how many data points are there likely
to be Max in A?
if there are not very many and your data is in column a and b
sort the data set by column A
Select each segment of Column B for identical a values and copy
go to column d first row of A and paste special transpose do this for each
unique Column A value.
in coulumn C enter
=concatenate(D1:H1)
or whatever range is appropriate
if you use autofilter on coulumn D and select non blanks your display will
be approximately what you want.

or you could do a macro similar to

sub setup()
r=2 ' or whatever is appropriate for second row of data
rr=1 'or whatever row you want the output to start.
com=cells(r,1) 'ssuming Data is in first column
res = ""
while cells(r,1)<>""
if cells(r,1)<>cells(r-1,1) then
Cells(rr,3)=com
cells(rr,4)=res
rr=rr+1
else
res=res & " " & cells(r,2)
end if
r=r+1
wend
end sub

If you want a space between the outputs from the cells

Note this is a brute force level of macro, If it will be used a lot, it
should be more formal using option expicit and dim statments etc.





"adin" wrote:

> I'd like this:
>
> Column A Column B
> A 1
> A 2
> A 3
> A 4
> B 1
> B 2
> C 1
> D 1
>
> to look like:
>
> A 1 2 3 4
> B 1 2
> C 1
> D 1
>
> The numbers should be in the same cell, not different columns.
>
> Easy, right?
>
> Thanks.
>
>

One try ..

Suppose this data-set is in Sheet1, A1:B8

> A 1
> A 2
> A 3
> A 4
> B 1
> B 2
> C 1
> D 1

In Sheet2
------------
Listed in A1 down are: A, B, C, D

Select D1:K1
(i.e. a horizontal range** equal in size to
the # of rows of data in Sheet1)

Put in the formula bar:
=IF(TRANSPOSE(IF(Sheet1!$A$1:$A$8=A1,Sheet1!$B$1:$B$8,""))=0,"",TRANSPOSE(IF
(Sheet1!$A$1:$A$8=A1,Sheet1!$B$1:$B$8,"")))

Array enter the formula,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER

Put in B2:
=TRIM(D1&" "&E1&" "&F1&" "&G1&" "&H1&" "&I1&" "&J1&" "&K1)

Select B2:K2, fill down

You'll get in A1:B4:

> A 1 2 3 4
> B 1 2
> C 1
> D 1

(Hide away cols D to K, if desired)

**This condition will unfortunately, limit you
to cover up to a max of ~245 rows of data in Sheet1,
[ Max cols is 256, less 11 cols used = 244 ]

Adapt / extend to suit
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"adin" <[email protected]> wrote in message
news:[email protected]...
> I'd like this:
>
> Column A Column B
> A 1
> A 2
> A 3
> A 4
> B 1
> B 2
> C 1
> D 1
>
> to look like:
>
> A 1 2 3 4
> B 1 2
> C 1
> D 1
>
> The numbers should be in the same cell, not different columns.
>
> Easy, right?
>
> Thanks.
>
>

It worked, except it put each of the values from column B into their own
cells in subsequent columns. I sorta need those values in the same cell.

I do, however, appreciate your efforts.


"Bob Phillips" wrote:

> Yep!
>
> Sub MoveData()
> Dim iLastRow As Long
> Dim i As Long
>
> iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> For i = iLastRow To 2 Step -1
> If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
> Cells(i, "B").Resize(1, 25).Copy Destination:=Cells(i - 1, "C")
> Cells(i, "A").EntireRow.Delete
> End If
> Next i
>
> End Sub
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "adin" <[email protected]> wrote in message
> news:[email protected]...
> > I'd like this:
> >
> > Column A Column B
> > A 1
> > A 2
> > A 3
> > A 4
> > B 1
> > B 2
> > C 1
> > D 1
> >
> > to look like:
> >
> > A 1 2 3 4
> > B 1 2
> > C 1
> > D 1
> >
> > The numbers should be in the same cell, not different columns.
> >
> > Easy, right?
> >
> > Thanks.
> >
> >
>
>
>

This sheet has over 900 rows, unfortunately.

But thanks anyway.



"Max" wrote:

> One try ..
>
> Suppose this data-set is in Sheet1, A1:B8
>
> > A 1
> > A 2
> > A 3
> > A 4
> > B 1
> > B 2
> > C 1
> > D 1
>
> In Sheet2
> ------------
> Listed in A1 down are: A, B, C, D
>
> Select D1:K1
> (i.e. a horizontal range** equal in size to
> the # of rows of data in Sheet1)
>
> Put in the formula bar:
> =IF(TRANSPOSE(IF(Sheet1!$A$1:$A$8=A1,Sheet1!$B$1:$B$8,""))=0,"",TRANSPOSE(IF
> (Sheet1!$A$1:$A$8=A1,Sheet1!$B$1:$B$8,"")))
>
> Array enter the formula,
> i.e. press CTRL+SHIFT+ENTER,
> instead of just pressing ENTER
>
> Put in B2:
> =TRIM(D1&" "&E1&" "&F1&" "&G1&" "&H1&" "&I1&" "&J1&" "&K1)
>
> Select B2:K2, fill down
>
> You'll get in A1:B4:
>
> > A 1 2 3 4
> > B 1 2
> > C 1
> > D 1
>
> (Hide away cols D to K, if desired)
>
> **This condition will unfortunately, limit you
> to cover up to a max of ~245 rows of data in Sheet1,
> [ Max cols is 256, less 11 cols used = 244 ]
>
> Adapt / extend to suit
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "adin" <[email protected]> wrote in message
> news:[email protected]...
> > I'd like this:
> >
> > Column A Column B
> > A 1
> > A 2
> > A 3
> > A 4
> > B 1
> > B 2
> > C 1
> > D 1
> >
> > to look like:
> >
> > A 1 2 3 4
> > B 1 2
> > C 1
> > D 1
> >
> > The numbers should be in the same cell, not different columns.
> >
> > Easy, right?
> >
> > Thanks.
> >
> >
>
>
>

It's easy to adapt, like so

Sub MoveData()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
Cells(i - 1, "B").Value = Cells(i - 1, "B").Value & _
Cells(i, "B").Value
Cells(i, "A").EntireRow.Delete
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"adin" <[email protected]> wrote in message
news:[email protected]...
> It worked, except it put each of the values from column B into their own
> cells in subsequent columns. I sorta need those values in the same cell.
>
> I do, however, appreciate your efforts.
>
>
> "Bob Phillips" wrote:
>
> > Yep!
> >
> > Sub MoveData()
> > Dim iLastRow As Long
> > Dim i As Long
> >
> > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > For i = iLastRow To 2 Step -1
> > If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
> > Cells(i, "B").Resize(1, 25).Copy Destination:=Cells(i - 1,
"C")
> > Cells(i, "A").EntireRow.Delete
> > End If
> > Next i
> >
> > End Sub
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "adin" <[email protected]> wrote in message
> > news:[email protected]...
> > > I'd like this:
> > >
> > > Column A Column B
> > > A 1
> > > A 2
> > > A 3
> > > A 4
> > > B 1
> > > B 2
> > > C 1
> > > D 1
> > >
> > > to look like:
> > >
> > > A 1 2 3 4
> > > B 1 2
> > > C 1
> > > D 1
> > >
> > > The numbers should be in the same cell, not different columns.
> > >
> > > Easy, right?
> > >
> > > Thanks.
> > >
> > >
> >
> >
> >

Looks good!

Now here's the fun part - I'd like to add a carriage return after each value
instead of a space so that each value is on top of one another within the
same cell. I can't seem to locate the carriage return charater(s) anywhere.



"Bob Phillips" wrote:

> It's easy to adapt, like so
>
> Sub MoveData()
> Dim iLastRow As Long
> Dim i As Long
>
> iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> For i = iLastRow To 2 Step -1
> If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
> Cells(i - 1, "B").Value = Cells(i - 1, "B").Value & _
> Cells(i, "B").Value
> Cells(i, "A").EntireRow.Delete
> End If
> Next i
>
> End Sub
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "adin" <[email protected]> wrote in message
> news:[email protected]...
> > It worked, except it put each of the values from column B into their own
> > cells in subsequent columns. I sorta need those values in the same cell.
> >
> > I do, however, appreciate your efforts.
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Yep!
> > >
> > > Sub MoveData()
> > > Dim iLastRow As Long
> > > Dim i As Long
> > >
> > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > > For i = iLastRow To 2 Step -1
> > > If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
> > > Cells(i, "B").Resize(1, 25).Copy Destination:=Cells(i - 1,
> "C")
> > > Cells(i, "A").EntireRow.Delete
> > > End If
> > > Next i
> > >
> > > End Sub
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "adin" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I'd like this:
> > > >
> > > > Column A Column B
> > > > A 1
> > > > A 2
> > > > A 3
> > > > A 4
> > > > B 1
> > > > B 2
> > > > C 1
> > > > D 1
> > > >
> > > > to look like:
> > > >
> > > > A 1 2 3 4
> > > > B 1 2
> > > > C 1
> > > > D 1
> > > >
> > > > The numbers should be in the same cell, not different columns.
> > > >
> > > > Easy, right?
> > > >
> > > > Thanks.
> > > >
> > > >
> > >
> > >
> > >
>
>
>

God-darn, why don't you tell me what you want at the start (vbg>

Sub MoveData()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
Cells(i - 1, "B").Value = Cells(i - 1, "B").Value & _
Chr(10) & Cells(i, "B").Value
Cells(i, "A").EntireRow.Delete
End If
Next i
Columns(2).WrapText = True

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"adin" <[email protected]> wrote in message
news:[email protected]...
> Looks good!
>
> Now here's the fun part - I'd like to add a carriage return after each
value
> instead of a space so that each value is on top of one another within the
> same cell. I can't seem to locate the carriage return charater(s)
anywhere.
>
>
>
> "Bob Phillips" wrote:
>
> > It's easy to adapt, like so
> >
> > Sub MoveData()
> > Dim iLastRow As Long
> > Dim i As Long
> >
> > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > For i = iLastRow To 2 Step -1
> > If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
> > Cells(i - 1, "B").Value = Cells(i - 1, "B").Value & _
> > Cells(i, "B").Value
> > Cells(i, "A").EntireRow.Delete
> > End If
> > Next i
> >
> > End Sub
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "adin" <[email protected]> wrote in message
> > news:[email protected]...
> > > It worked, except it put each of the values from column B into their
own
> > > cells in subsequent columns. I sorta need those values in the same
cell.
> > >
> > > I do, however, appreciate your efforts.
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Yep!
> > > >
> > > > Sub MoveData()
> > > > Dim iLastRow As Long
> > > > Dim i As Long
> > > >
> > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > > > For i = iLastRow To 2 Step -1
> > > > If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
> > > > Cells(i, "B").Resize(1, 25).Copy Destination:=Cells(i -
1,
> > "C")
> > > > Cells(i, "A").EntireRow.Delete
> > > > End If
> > > > Next i
> > > >
> > > > End Sub
> > > >
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "adin" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I'd like this:
> > > > >
> > > > > Column A Column B
> > > > > A 1
> > > > > A 2
> > > > > A 3
> > > > > A 4
> > > > > B 1
> > > > > B 2
> > > > > C 1
> > > > > D 1
> > > > >
> > > > > to look like:
> > > > >
> > > > > A 1 2 3 4
> > > > > B 1 2
> > > > > C 1
> > > > > D 1
> > > > >
> > > > > The numbers should be in the same cell, not different columns.
> > > > >
> > > > > Easy, right?
> > > > >
> > > > > Thanks.
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Thanks for posting back !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"adin" <[email protected]> wrote in message
news:[email protected]...
> This sheet has over 900 rows, unfortunately.
>
> But thanks anyway.

This works like a charm. You get the cookie.

Thanks for your assitance!

"Bob Phillips" wrote:

> God-darn, why don't you tell me what you want at the start (vbg>
>
> Sub MoveData()
> Dim iLastRow As Long
> Dim i As Long
>
> iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> For i = iLastRow To 2 Step -1
> If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
> Cells(i - 1, "B").Value = Cells(i - 1, "B").Value & _
> Chr(10) & Cells(i, "B").Value
> Cells(i, "A").EntireRow.Delete
> End If
> Next i
> Columns(2).WrapText = True
>
> End Sub
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "adin" <[email protected]> wrote in message
> news:[email protected]...
> > Looks good!
> >
> > Now here's the fun part - I'd like to add a carriage return after each
> value
> > instead of a space so that each value is on top of one another within the
> > same cell. I can't seem to locate the carriage return charater(s)
> anywhere.
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > > It's easy to adapt, like so
> > >
> > > Sub MoveData()
> > > Dim iLastRow As Long
> > > Dim i As Long
> > >
> > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > > For i = iLastRow To 2 Step -1
> > > If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
> > > Cells(i - 1, "B").Value = Cells(i - 1, "B").Value & _
> > > Cells(i, "B").Value
> > > Cells(i, "A").EntireRow.Delete
> > > End If
> > > Next i
> > >
> > > End Sub
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "adin" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > It worked, except it put each of the values from column B into their
> own
> > > > cells in subsequent columns. I sorta need those values in the same
> cell.
> > > >
> > > > I do, however, appreciate your efforts.
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Yep!
> > > > >
> > > > > Sub MoveData()
> > > > > Dim iLastRow As Long
> > > > > Dim i As Long
> > > > >
> > > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > > > > For i = iLastRow To 2 Step -1
> > > > > If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
> > > > > Cells(i, "B").Resize(1, 25).Copy Destination:=Cells(i -
> 1,
> > > "C")
> > > > > Cells(i, "A").EntireRow.Delete
> > > > > End If
> > > > > Next i
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "adin" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > I'd like this:
> > > > > >
> > > > > > Column A Column B
> > > > > > A 1
> > > > > > A 2
> > > > > > A 3
> > > > > > A 4
> > > > > > B 1
> > > > > > B 2
> > > > > > C 1
> > > > > > D 1
> > > > > >
> > > > > > to look like:
> > > > > >
> > > > > > A 1 2 3 4
> > > > > > B 1 2
> > > > > > C 1
> > > > > > D 1
> > > > > >
> > > > > > The numbers should be in the same cell, not different columns.
> > > > > >
> > > > > > Easy, right?
> > > > > >
> > > > > > Thanks.
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>