Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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" wrote in message ... 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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 a 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 <atyahoo<dotcom ---- "adin" wrote in message ... 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. |
#5
|
|||
|
|||
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" wrote in message ... 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. |
#6
|
|||
|
|||
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 a 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 <atyahoo<dotcom ---- "adin" wrote in message ... 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. |
#7
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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. |
#8
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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. |
#9
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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. |
#10
|
|||
|
|||
Thanks for posting back !
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "adin" wrote in message ... This sheet has over 900 rows, unfortunately. But thanks anyway. |
#11
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Formatting dates in the future | Excel Worksheet Functions | |||
Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |