Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help concatenating, or maybe a macro would be better.
Have a part number listed in A column, it refers a new updated part number in
column C. I would like to concatenate it to a line of text. (or maybe a macro would be better) Column and numbers look like this: Column A Column B Column C Old number use new number New Number 01-261-0340 use new number 01-261-0340-01 95-0885 use new number 01-261-0340-01 95-0885-01 use new number 01-261-0340-01 I want to get it so it reads in a line of Text as: 01-261-0340-01 replaces 01-261-0340 , 95-0885 , 95-0885-01 Thanks for looking, and any help would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help concatenating, or maybe a macro would be better.
Since you have mutiple replacements it is bestt to use a simple macro than complicated macros. the code assume the following 1) the orginal data is on sheet 1 in columns A - C 2) The original data has a header row. Data starts on row 2 3) The code sorts the original data by colum C and then column A 4) the results are put on sheet 2 starting in row 1 Sub GetReplacement() Set SourceSht = Sheets("Sheet1") Set DestSht = Sheets("Sheet2") Newrow = 1 With Sheets("sheet1") LastRow = .Range("A" & Rows.Count).End(xlUp).Row 'sort data by column C thenA .Rows("1:" & LastRow).Sort _ header:=xlYes, _ key1:=.Range("C1"), _ order1:=xlAscending, _ key2:=.Range("A1"), _ order2:=xlAscending RowCount = 2 OutputStr = "" Do While .Range("A" & RowCount) < "" Original = .Range("A" & RowCount) If OutputStr = "" Then Replacement = .Range("C" & RowCount) OutputStr = Replacement & " replaces " & Original Else OutputStr = OutputStr & " , " & Original End If If .Range("C" & RowCount) < .Range("C" & (RowCount + 1)) Then DestSht.Range("A" & Newrow) = OutputStr Newrow = Newrow + 1 OutputStr = "" End If RowCount = RowCount + 1 Loop End With End Sub -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145268 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help concatenating, or maybe a macro would be better.
In D1
= C1 & " replaces " & A1 In D2 = C1 & ", " & A2 then replicate D2 down "Lakewoodsale" wrote: Have a part number listed in A column, it refers a new updated part number in column C. I would like to concatenate it to a line of text. (or maybe a macro would be better) Column and numbers look like this: Column A Column B Column C Old number use new number New Number 01-261-0340 use new number 01-261-0340-01 95-0885 use new number 01-261-0340-01 95-0885-01 use new number 01-261-0340-01 I want to get it so it reads in a line of Text as: 01-261-0340-01 replaces 01-261-0340 , 95-0885 , 95-0885-01 Thanks for looking, and any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Macro for concatenating multiple worksheet | Excel Discussion (Misc queries) | |||
concatenating | Excel Discussion (Misc queries) | |||
Concatenating a zero | Excel Programming | |||
VB Macro for concatenating data... | Excel Programming | |||
Concatenating | New Users to Excel |