| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
More formatting
I print karaoke books with the artist in column 1 and the song titles in column2.. Is there a way to move each artists song titles below the artists name in column 1? col1 col2 To This Col1 Artist Song1 Artist Song2 Song1 Song3 Song2 Song3 -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=504281 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More formatting
Sub Test()
Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "B").End(xlUp).Row For i = iLastRow To 1 Step -1 If Cells(i, "A").Value < "" Then Rows(i + 1).Insert Cells(i + 1, "A").Value = Cells(i, "B").Value Else Cells(i, "A").Value = Cells(i, "B").Value End If Cells(i, "B").Value = "" Next i End Sub -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "karyoker" wrote in message ... I print karaoke books with the artist in column 1 and the song titles in column2.. Is there a way to move each artists song titles below the artists name in column 1? col1 col2 To This Col1 Artist Song1 Artist Song2 Song1 Song3 Song2 Song3 -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=504281 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More formatting
Ok Thanks thats almost there.... Instead of this: *10Years* Wasteland 3 DOORS DOWN Behind Those Eyes 3 DOORS DOWN Live For Today I need this: *10Years* Wasteland 3 DOORS DOWN Behind Those Eyes Live For Today -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=504281 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More formatting
R u saying the code is creating duplicates, or u weant it tro recognise
duplicates and remove them? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "karyoker" wrote in message ... Ok Thanks thats almost there.... Instead of this: *10Years* Wasteland 3 DOORS DOWN Behind Those Eyes 3 DOORS DOWN Live For Today I need this: *10Years* Wasteland 3 DOORS DOWN Behind Those Eyes Live For Today -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=504281 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More formatting
Yes trying to remove duplicates... In the example above 3 Doors Down has 2 songs and would like the dup artist listing removed... I thought I could format col1 with *BOLD* but when the songs are moved to col1 they are formatted bold too....Actually I would just like the artists to be underlined... Thanks.... -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=504281 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More formatting
How about this
Sub Test() Dim iLastRow As Long Dim i As Long Dim iStart As Long Dim rng As Range iLastRow = Cells(Rows.Count, "B").End(xlUp).Row iStart = iLastRow For i = iLastRow To 1 Step -1 If Cells(i, "A").Value < "" Then Rows(i + 1).Insert Cells(i + 1, "A").Value = Cells(i, "B").Value iStart = i - 1 Cells(i, "A").Font.Underline = True Else Cells(i, "A").Value = Cells(i, "B").Value If Application.CountIf(Range("A" & i & ":A" & iStart), Cells(i, "A").Value) 1 Then If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i)) End If End If End If Cells(i, "B").Value = "" Next i If Not rng Is Nothing Then rng.Delete End Sub -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "karyoker" wrote in message ... Yes trying to remove duplicates... In the example above 3 Doors Down has 2 songs and would like the dup artist listing removed... I thought I could format col1 with *BOLD* but when the songs are moved to col1 they are formatted bold too....Actually I would just like the artists to be underlined... Thanks.... -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=504281 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More formatting
Bob it is So So close!!! It still lists the artists in duplicate...Where the artist is listed for multiple songs can we delete those rows? Thanks.... -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=504281 |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More formatting
Can you give me an example of the data that I can work on?
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "karyoker" wrote in message ... Bob it is So So close!!! It still lists the artists in duplicate...Where the artist is listed for multiple songs can we delete those rows? Thanks.... -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=504281 |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More formatting
This is a short list.. Col1 and col2 are the only ones used.... +-------------------------------------------------------------------+ |Filename: Book2.zip | |Download: http://www.excelforum.com/attachment.php?postid=4265 | +-------------------------------------------------------------------+ -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=504281 |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More formatting
Sub Test()
Dim iLastRow As Long Dim i As Long Dim iStart As Long Dim rng As Range iLastRow = Cells(Rows.Count, "B").End(xlUp).Row iStart = iLastRow For i = iLastRow To 2 Step -1 If Cells(i, "A").Value < Cells(i - 1, "A").Value Then Rows(i + 1).Insert Cells(i + 1, "A").Value = Cells(i, "B").Value iStart = i - 1 Cells(i, "A").Font.Underline = True Else Cells(i, "A").Value = Cells(i, "B").Value End If Cells(i, "B").Value = "" Next i End Sub -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "karyoker" wrote in message ... This is a short list.. Col1 and col2 are the only ones used.... +-------------------------------------------------------------------+ |Filename: Book2.zip | |Download: http://www.excelforum.com/attachment.php?postid=4265 | +-------------------------------------------------------------------+ -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=504281 |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More formatting
Bob you are a Gentleman and a Scholar and I owe you one:) Thanks a bunch!!!!! -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=504281 |
|
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
More formatting
Glad we got there.
Enjoy the music ... but Jim Reeves? Bob "karyoker" wrote in message ... Bob you are a Gentleman and a Scholar and I owe you one:) Thanks a bunch!!!!! -- karyoker ------------------------------------------------------------------------ karyoker's Profile: http://www.excelforum.com/member.php...o&userid=29417 View this thread: http://www.excelforum.com/showthread...hreadid=504281 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting Error | Excel Worksheet Functions | |||
| difficulty with conditional formatting | 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) | |||
Linear Mode
