Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
At each change in data concatenate text values in contiguous ranges
I am editing a book for publication and I want to use Excel as an aid to
compiling the index. I need a way of concatenating the page numbers, which will be stored as text values in contiguous ranges of cells that are defined by changes in a text variable. The concatenated values also need to be separated by commas. Thus, if this is the input: row0 Col A Col B row1 antelope 21 row2 antelope 22-3 row3 antelope 25 row4 bears 19 row5 bison 31 row6 bison 33 row7 Colorado 14 row8 Colorado 17 the desired output would be: row0 Col X Col Y row1 antelopes 21, 22-3, 25 row2 bears 19 row3 bison 31,33 row4 Colorado 14, 17 (Alternatively the output could be on rows 1, 4, 5, 7 etc; or on rows 3, 4, 6, 8 etc; or anywhere else convenient). This is similar to the query titled "At each change in data apply formula", posted by "Shon" on December 11th, 2009, but I'm not sure that it is quite the same. I would appreciate any suggestions for how to approach this. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
At each change in data concatenate text values in contiguousranges
On Jul 2, 2:28*am, john-c wrote:
I am editing a book for publication and I want to use Excel as an aid to *compiling the index. I need a way of concatenating the page numbers, which will *be stored as text values in contiguous ranges of cells that are defined by *changes in a text variable. The concatenated values also need to be separated by *commas. *Thus, if this is the input: *row0 * Col A * * * * * Col B *row1 * * antelope * * * * * * *21 *row2 * * antelope * * * * * * *22-3 *row3 * * antelope * * * * * * *25 *row4 * * bears * * * * 19 *row5 * * bison * * * * 31 *row6 * * bison * * * * 33 *row7 * * Colorado * * * * * * *14 *row8 * * Colorado * * * * * * *17 *the desired output would be: *row0 * * Col X * * * * Col Y *row1 * * antelopes * * * * * * 21, 22-3, 25 *row2 * * bears * * * * 19 *row3 * * bison * * * * 31,33 *row4 * * Colorado * * * * * * *14, 17 *(Alternatively the output could be on rows 1, 4, 5, 7 etc; or on rows 3, 4, 6, 8 *etc; or anywhere else convenient). *This is similar to the query titled "At each change in data apply *formula", posted by "Shon" on December 11th, 2009, but I'm not *sure that it is quite the same. I would appreciate any suggestions for how to *approach this. The macro below will do as desired Option Explicit Sub rearrangedataSAS() Dim i As Long For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 If Cells(i - 1, 1) = Cells(i, 1) Then Cells(i - 1, 2) = Cells(i - 1, 2) & "," & Application.Trim(Cells(i, 2)) Rows(i).Delete End If Next i End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
At each change in data concatenate text values in contiguous ranges
Don Guillett Excel MVP wrote on 07/02/2010 08:35 ET :
On Jul 2, 2:28 am, john-c wrote: I am editing a book for publication and I want to use Excel as an aid to compiling the index. I need a way of concatenating the page numbers, which will be stored as text values in contiguous ranges of cells that are defined by changes in a text variable. The concatenated values also need to be separated by commas. Thus, if this is the input: row0 Col A Col B row1 antelope 21 row2 antelope 22-3 row3 antelope 25 row4 bears 19 row5 bison 31 row6 bison 33 row7 Colorado 14 row8 Colorado 17 the desired output would be: row0 Col X Col Y row1 antelopes 21, 22-3, 25 row2 bears 19 row3 bison 31,33 row4 Colorado 14, 17 (Alternatively the output could be on rows 1, 4, 5, 7 etc; or on rows 3, 4, 6, 8 etc; or anywhere else convenient). This is similar to the query titled "At each change in data apply formula", posted by "Shon" on December 11th, 2009, but I'm not sure that it is quite the same. I would appreciate any suggestions for how to approach this. The macro below will do as desired Option Explicit Sub rearrangedataSAS() Dim i As Long For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 If Cells(i - 1, 1) = Cells(i, 1) Then Cells(i - 1, 2) = Cells(i - 1, 2) & "," & Application.Trim(Cells(i, 2)) Rows(i).Delete End If Next i End Sub Don, many thanks for this, which works nicely. I haven't written a macro in VBA before but with this as a start I should be able to adapt it for variants of the problem if I need to. John C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate contiguous fields on one worksheet into 1 field on ano | Excel Programming | |||
Gathering data from multiple, non-contiguous ranges | Excel Programming | |||
Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column | Excel Programming | |||
Data validation: concatenate two separate ranges in the List? | Excel Discussion (Misc queries) | |||
TTest with values in non-contiguous ranges | Excel Worksheet Functions |