Formatting issue

A

adin

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.
 
B

Bob Phillips

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)
 
B

bj

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.
 
M

Max

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
Click to expand...

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
Click to expand...

(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
 
A

adin

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.
 
A

adin

This sheet has over 900 rows, unfortunately.

But thanks anyway.



Max said:
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
Click to expand...

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
Click to expand...

(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 said:
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.
Click to expand...
Click to expand...
 
B

Bob Phillips

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)
 
A

adin

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.
 
B

Bob Phillips

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)
 
Top