 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2008conditional merging of two columns?
I want to merge the two columns, sample data below: CNM1GBLO #N/A CNM1GBLO #N/A CNP1GBLO #N/A CNP1GBLO NIMWGBLO CNQ1GBLO #N/A CNR1GBLO #N/A CNR1GBLO #N/A What I want is for any value in the righthand column which is not equal to #N/A to overwrite the value in the lefthand column on the corresponding row. So in this case, after the merge, column A would remain the same, except that CNP1GBLO would now overwritten with NIMWGBLO. Hi, Try this, right click your sheet tab, view code and paste this in Sub stance() lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set myrange = Range("B1:B" & lastrow) For Each c In myrange If Not Application.WorksheetFunction.IsNA(c.Value) Then c.Offset(0, -1).Value = c.Value End If Next End Sub Mike > I want to merge the two columns, sample data below: > [quoted text clipped - 12 lines] > So in this case, after the merge, column A would remain the same, > except that CNP1GBLO would now overwritten with NIMWGBLO. Here is a quick one to suffice... You will probably want to edit the FOR loop to accommodate the number of rows needed. And maybe even the column references if your data is not in column A and B. Please let me know if you need any help with this. Mark Sub test() Dim row As Long For row = 1 To 5 If Cells(row, 2).Text = "#N/A" Then ' Do nothing Else: Cells(row, 1).Value = Cells(row, 2).Value End If Next End Sub > I want to merge the two columns, sample data below: > [quoted text clipped - 12 lines] > So in this case, after the merge, column A would remain the same, > except that CNP1GBLO would now overwritten with NIMWGBLO. Thanks - the data isn't in columns A and B - how do I amend to reflect this? Does A=1 and B=2 in your script? > Here is a quick one to suffice... > [quoted text clipped - 37 lines] > > So in this case, after the merge, column A would remain the same, > > except that CNP1GBLO would now overwritten with NIMWGBLO. Hi, For my code change the B in these 2 lines to the column where is #NA! is lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set myrange = Range("B1:B" & lastrow) This line represnts an offset form that column c.Offset(0, -1).Value = c.Value -1 is one to the left -2 is 2 etc and plus values are to the right Mike > Thanks - the data isn't in columns A and B - how do I amend to reflect > this? Does A=1 and B=2 in your script? [quoted text clipped - 40 lines] > > > So in this case, after the merge, column A would remain the same, > > > except that CNP1GBLO would now overwritten with NIMWGBLO. Exactly... With the following line: If Cells(row, 2).Text = "#N/A" Then row = the current FOR loop and will perform an action on that particular row and the "2" represents the column Each time I used Cells(row, SOMENUMBER).Value or Text... Just change the SOMENUMBER to reflect the column you need. Mark > Thanks - the data isn't in columns A and B - how do I amend to reflect > this? Does A=1 and B=2 in your script? [quoted text clipped - 40 lines] >> > So in this case, after the merge, column A would remain the same, >> > except that CNP1GBLO would now overwritten with NIMWGBLO. Fantastic - it works!! Thanks a bunch. > Exactly... > [quoted text clipped - 58 lines] > >> > So in this case, after the merge, column A would remain the same, > >> > except that CNP1GBLO would now overwritten with NIMWGBLO. No problem... Glad to help... Mark > Fantastic - it works!! > [quoted text clipped - 65 lines] >> >> > So in this case, after the merge, column A would remain the same, >> >> > except that CNP1GBLO would now overwritten with NIMWGBLO. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.