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

"(E-Mail Removed)" wrote:

> 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.
>
>

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




<(E-Mail Removed)> wrote in message
news:9ce30268-c048-461b-bc92-(E-Mail Removed)...
> 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.
>

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?

On Mar 20, 11:35 am, "Mark Ivey" <wmivey6311NOS...@hotmail.com> wrote:
> 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
>
> <wreth...@gmail.com> wrote in message
>
> news:9ce30268-c048-461b-bc92-(E-Mail Removed)...
>
> > 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,

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


"(E-Mail Removed)" wrote:

> 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?
>
> On Mar 20, 11:35 am, "Mark Ivey" <wmivey6311NOS...@hotmail.com> wrote:
> > 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
> >
> > <wreth...@gmail.com> wrote in message
> >
> > news:9ce30268-c048-461b-bc92-(E-Mail Removed)...
> >
> > > 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.
>
>

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

<(E-Mail Removed)> wrote in message
news:621b8346-b4a4-4d6d-96f4-(E-Mail Removed)...
> 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?
>
> On Mar 20, 11:35 am, "Mark Ivey" <wmivey6311NOS...@hotmail.com> wrote:
>> 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
>>
>> <wreth...@gmail.com> wrote in message
>>
>> news:9ce30268-c048-461b-bc92-(E-Mail Removed)...
>>
>> > 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.
>

Fantastic - it works!!

Thanks a bunch.

On Mar 20, 12:36 pm, "Mark Ivey" <wmivey6311NOS...@hotmail.com> wrote:
> 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
>
> <wreth...@gmail.com> wrote in message
>
> news:621b8346-b4a4-4d6d-96f4-(E-Mail Removed)...
>
> > 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?
>
> > On Mar 20, 11:35 am, "Mark Ivey" <wmivey6311NOS...@hotmail.com> wrote:
> >> 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
>
> >> <wreth...@gmail.com> wrote in message
>
> >>news:9ce30268-c048-461b-bc92-(E-Mail Removed)...
>
> >> > 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.

No problem...

Glad to help...

Mark

<(E-Mail Removed)> wrote in message
news:59ddc15d-09d3-4663-9ae2-(E-Mail Removed)...
> Fantastic - it works!!
>
> Thanks a bunch.
>
> On Mar 20, 12:36 pm, "Mark Ivey" <wmivey6311NOS...@hotmail.com> wrote:
>> 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
>>
>> <wreth...@gmail.com> wrote in message
>>
>> news:621b8346-b4a4-4d6d-96f4-(E-Mail Removed)...
>>
>> > 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?
>>
>> > On Mar 20, 11:35 am, "Mark Ivey" <wmivey6311NOS...@hotmail.com> wrote:
>> >> 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
>>
>> >> <wreth...@gmail.com> wrote in message
>>
>> >>news:9ce30268-c048-461b-bc92-(E-Mail Removed)...
>>
>> >> > 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.
>