HOWTO Replace from Row
 

I did NOT open your attachment. Please do NOT attach files to the ng. You
MAY be invited to send a file directly to a respondent but do not attach to
ng.

The sub I sent you will do what you asked.
suppose you have in col A A, col B A and col C C
the sub will put C in the cell in col A
so, your "selection" should be the col A range you want.
for each c in range(cells(1,1),cells(cells(65536).end(xlup).row, 1)))
or
for each c in range("a1:a"&range("a65536").end(xlup).row)


"SolaSig AB" wrote in message
...
After some Time is gone ....

May it look like this one?

Public strCnt As Integer

Sub Chck4nms()
Set strCnt = 1 /* debuger Ask heer for an Object! :(
For Each c In ActiveCell.CurrentRegion.Cells
If c = c.Offset(strCnt, 2) Then
c.Offset(, 2) = c.Offset(strCnt, 3)
Set strCnt = 1
ElseIf strCnt = 12 Then
Set strCnt = 1
Next
Else: strCnt = strCnt + 1
Next
End Sub

Please try. XLS is included
on-Line

"Don Guillett" wrote in message
...
something like - untested

for each c in selection
if c=c.offset(,1)then c=c.offset(,2)
next

"SolaSig AB" wrote in message
...
Hi All!



Here is some Help asked, since I'm not familiar with Excel
programming,
I
can't find an Answer for my problem.



Here is the Problem:



I need to replace come words in 1st.Cell with words from another
3rd.Cell
if
Content of the 1.st.Cell and the 2nd.Cells matches.



Main problem is the Volume 25.000 Strings ? 15 Rows.



As I understand the algorithm for this operation it may look like
this:

Get Ai Value

Get Bj Value

Compare Ai and Bj values for equality

If true then get Cj Value and put to Ai Value; Next Ai (Ai++)

If false then get next Bj (Bj++)



Example:



Before replacement

A B C


1 RED RED R

2 BLUE BLUE B

3 BLUE

4 RED

5 BLUE

after replacement

A B C


1 R RED R

2 B BLUE B

3 B

4 R

5 B



Thanks if there might be some ideas or help,

On-Line

HOWTO Replace from Row
 

Dear Don,



Thanks but I actually didn't understand how to make this Sub.

I have combined all your post and got following subs. But this don't work :(



Here are two Subs, but I don't know what is wrong:



Sub Chck()

for each c in range(cells(1,1),cells(cells(65536).end(xlup).row, 1))

if c=c.offset(,1)then c=c.offset(,2)

next



End Sub





Sub Chck2()

for each c in range("a1:a"&range("a65536").end(xlup).row)

if c=c.offset(,1)then c=c.offset(,2)

next



End Sub



As I see this Sub compares Values in Columns for same Row, but if cell will
be in another row the Sub will not find any match.



It may work for

RED RED R

BLUE BLUE B



But not for

RED BLUE B

BLUE RED R

HOWTO Replace from Row
 

I guess I do NOT understand your problem.

"SolaSig AB" wrote in message
...
Dear Don,



Thanks but I actually didn't understand how to make this Sub.

I have combined all your post and got following subs. But this don't work
:(



Here are two Subs, but I don't know what is wrong:



Sub Chck()

for each c in range(cells(1,1),cells(cells(65536).end(xlup).row, 1))

if c=c.offset(,1)then c=c.offset(,2)

next



End Sub





Sub Chck2()

for each c in range("a1:a"&range("a65536").end(xlup).row)

if c=c.offset(,1)then c=c.offset(,2)

next



End Sub



As I see this Sub compares Values in Columns for same Row, but if cell
will
be in another row the Sub will not find any match.



It may work for

RED RED R

BLUE BLUE B



But not for

RED BLUE B

BLUE RED R

HOWTO Replace from Row
 

Dave, How is that materially different from my original?

for each c in selection
if c=c.offset(,1)then c=c.offset(,2)
next


"Dave Peterson" wrote in message
...
how about:
Option Explicit
Sub Chck()

Dim c As Range

With ActiveSheet
For Each c In .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Cells
'compare column A with the one to its right
If LCase(c.Value) = LCase(c.Offset(0, 1).Value) Then
'if they were the same, then move
'2 over (to the right) to column A.
c.Value = c.Offset(0, 2).Value
End If
Next c
End With
End Sub



SolaSig AB wrote:

Dear Don,

Thanks but I actually didn't understand how to make this Sub.

I have combined all your post and got following subs. But this don't
work :(

Here are two Subs, but I don't know what is wrong:

Sub Chck()

for each c in range(cells(1,1),cells(cells(65536).end(xlup).row, 1))

if c=c.offset(,1)then c=c.offset(,2)

next

End Sub

Sub Chck2()

for each c in range("a1:a"&range("a65536").end(xlup).row)

if c=c.offset(,1)then c=c.offset(,2)

next

End Sub

As I see this Sub compares Values in Columns for same Row, but if cell
will
be in another row the Sub will not find any match.

It may work for

RED RED R

BLUE BLUE B

But not for

RED BLUE B

BLUE RED R

--

Dave Peterson

HOWTO Replace from Row
 

You're right about that.

"Dave Peterson" wrote in message
...
Only that it's contained in a complete macro (more than a snippet).

From your previous reply, it sounded like another voice (however
redundant)
would help.



Don Guillett wrote:

Dave, How is that materially different from my original?

for each c in selection
if c=c.offset(,1)then c=c.offset(,2)
next

"Dave Peterson" wrote in message
...
how about:
Option Explicit
Sub Chck()

Dim c As Range

With ActiveSheet
For Each c In .Range("a1", .Cells(.Rows.Count,
"A").End(xlUp)).Cells
'compare column A with the one to its right
If LCase(c.Value) = LCase(c.Offset(0, 1).Value) Then
'if they were the same, then move
'2 over (to the right) to column A.
c.Value = c.Offset(0, 2).Value
End If
Next c
End With
End Sub



SolaSig AB wrote:

Dear Don,

Thanks but I actually didn't understand how to make this Sub.

I have combined all your post and got following subs. But this don't
work :(

Here are two Subs, but I don't know what is wrong:

Sub Chck()

for each c in range(cells(1,1),cells(cells(65536).end(xlup).row, 1))

if c=c.offset(,1)then c=c.offset(,2)

next

End Sub

Sub Chck2()

for each c in range("a1:a"&range("a65536").end(xlup).row)

if c=c.offset(,1)then c=c.offset(,2)

next

End Sub

As I see this Sub compares Values in Columns for same Row, but if
cell
will
be in another row the Sub will not find any match.

It may work for

RED RED R

BLUE BLUE B

But not for

RED BLUE B

BLUE RED R

--

Dave Peterson


--

Dave Peterson

HOWTO Replace from Row
 

The redundant part or the help part?
<vbg

Don Guillett wrote:

You're right about that.

"Dave Peterson" wrote in message
...
Only that it's contained in a complete macro (more than a snippet).

From your previous reply, it sounded like another voice (however
redundant)
would help.

<<snipped
--

Dave Peterson

HOWTO Replace from Row
 

Dave, thanks for your Help with whole Sub, and thanks to Don for original
idea this really works!
(Problem was as I see only in my poor knowledge of VBA)

Now I'll try to make this Sub to work for whole B Column.
As I see Value in Cell A will be changed only if Value in Cell B matches,
but the Row have to be the same also!
It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed
with Value in C3, but in real life there is not so simple because Value for
A1 may match only Value in B3 and then Value in A1 have to be changed from
C3 Value.

Original text is much more complex there is 25.000 Rows and 15 Columns. (375
000 Cells) I will try to make all Columns separate, if this will work for a
single column.
In this single column data is been repeated some times so amount of cells in
Single "A" column will be 25 000, and amount of Cells in B and C columns
will be only about 5 000 cells per column, and cell's B Value does not
correspond to A Value in current Row in 99,99% of cases.

As I already wrote on 23rd 08 2003:

It works for

RED RED R
BLUE BLUE B



But not for

RED BLUE B
Dave thanks for your Help with whole Sub, and thanks to Don for original
idea this really works! (Problem was as I see only in my poor knowledge of
VBA)

Now I'll try to make this Sub to work for whole B Column.
As I see Value in Cell A will be changed if Value in Cell B matches, but the
Row have to be the same also! It mean IF Value in A1 Matches Value in B1
then Value in A1 will be changed with Value in C3, but in real life there is
not so simple because Value for A1 may match only Value in B3 and then Value
in A1 have to be changed from C3 Value.

Original text is much more complex there is 25.000 Rows and 15 Columns. (375
000 Cells) I will try to make all Columns separate, if this will work for a
single column. In this single column data is been repeated some times so
amount of cells in Single A column will be 25 000, and amount of Cells in B
and C columns will be only about 5 000 cells per column, and cell's B Value
does not correspond to A Value in current Row.

As I already wrote on 23rd 08 2003:

It works for

RED RED R
BLUE BLUE B



But not for

RED BLUE B
BLUE RED R
BLUE
RED
BLUE
RED
RED
BLUE

Have you any Idea how to make it work?

Thanks again in advance for your patience and help!

Andre

Sorry, Mistake!
 

Oopps!

Wrote:
It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed
with Value in _C3_, but in real life there is not so simple because Value
for
A1 may match only Value in B3 and then Value in A1 have to be changed from
C3 Value.

Have to write:
It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed
with Value in _C1_, but in real life there is not so simple because Value
for
A1 may match only Value in B3 and then Value in A1 have to be changed from
C3 Value.

Andre


"SolaSig AB" wrote in message
...
Dave, thanks for your Help with whole Sub, and thanks to Don for original
idea this really works!
(Problem was as I see only in my poor knowledge of VBA)

Now I'll try to make this Sub to work for whole B Column.
As I see Value in Cell A will be changed only if Value in Cell B matches,
but the Row have to be the same also!
It mean IF Value in A1 Matches Value in B1 then Value in A1 will be
changed
with Value in C3, but in real life there is not so simple because Value
for
A1 may match only Value in B3 and then Value in A1 have to be changed from
C3 Value.

Original text is much more complex there is 25.000 Rows and 15 Columns.
(375
000 Cells) I will try to make all Columns separate, if this will work for
a
single column.
In this single column data is been repeated some times so amount of cells
in
Single "A" column will be 25 000, and amount of Cells in B and C columns
will be only about 5 000 cells per column, and cell's B Value does not
correspond to A Value in current Row in 99,99% of cases.

As I already wrote on 23rd 08 2003:

It works for

RED RED R
BLUE BLUE B



But not for

RED BLUE B
Dave thanks for your Help with whole Sub, and thanks to Don for original
idea this really works! (Problem was as I see only in my poor knowledge of
VBA)

Now I'll try to make this Sub to work for whole B Column.
As I see Value in Cell A will be changed if Value in Cell B matches, but
the
Row have to be the same also! It mean IF Value in A1 Matches Value in B1
then Value in A1 will be changed with Value in C3, but in real life there
is
not so simple because Value for A1 may match only Value in B3 and then
Value
in A1 have to be changed from C3 Value.

Original text is much more complex there is 25.000 Rows and 15 Columns.
(375
000 Cells) I will try to make all Columns separate, if this will work for
a
single column. In this single column data is been repeated some times so
amount of cells in Single A column will be 25 000, and amount of Cells in
B
and C columns will be only about 5 000 cells per column, and cell's B
Value
does not correspond to A Value in current Row.

As I already wrote on 23rd 08 2003:

It works for

RED RED R
BLUE BLUE B



But not for

RED BLUE B
BLUE RED R
BLUE
RED
BLUE
RED
RED
BLUE

Have you any Idea how to make it work?

Thanks again in advance for your patience and help!

Andre

Sorry, Mistake!
 

Real clear.

"SolaSig AB" wrote in message
...
Oopps!

Wrote:
It mean IF Value in A1 Matches Value in B1 then Value in A1 will be
changed
with Value in _C3_, but in real life there is not so simple because Value
for
A1 may match only Value in B3 and then Value in A1 have to be changed from
C3 Value.

Have to write:
It mean IF Value in A1 Matches Value in B1 then Value in A1 will be
changed
with Value in _C1_, but in real life there is not so simple because Value
for
A1 may match only Value in B3 and then Value in A1 have to be changed from
C3 Value.

Andre


"SolaSig AB" wrote in message
...
Dave, thanks for your Help with whole Sub, and thanks to Don for
original
idea this really works!
(Problem was as I see only in my poor knowledge of VBA)

Now I'll try to make this Sub to work for whole B Column.
As I see Value in Cell A will be changed only if Value in Cell B
matches,
but the Row have to be the same also!
It mean IF Value in A1 Matches Value in B1 then Value in A1 will be
changed
with Value in C3, but in real life there is not so simple because Value
for
A1 may match only Value in B3 and then Value in A1 have to be changed
from
C3 Value.

Original text is much more complex there is 25.000 Rows and 15 Columns.
(375
000 Cells) I will try to make all Columns separate, if this will work
for
a
single column.
In this single column data is been repeated some times so amount of
cells
in
Single "A" column will be 25 000, and amount of Cells in B and C columns
will be only about 5 000 cells per column, and cell's B Value does not
correspond to A Value in current Row in 99,99% of cases.

As I already wrote on 23rd 08 2003:

It works for

RED RED R
BLUE BLUE B



But not for

RED BLUE B
Dave thanks for your Help with whole Sub, and thanks to Don for original
idea this really works! (Problem was as I see only in my poor knowledge
of
VBA)

Now I'll try to make this Sub to work for whole B Column.
As I see Value in Cell A will be changed if Value in Cell B matches, but
the
Row have to be the same also! It mean IF Value in A1 Matches Value in B1
then Value in A1 will be changed with Value in C3, but in real life
there
is
not so simple because Value for A1 may match only Value in B3 and then
Value
in A1 have to be changed from C3 Value.

Original text is much more complex there is 25.000 Rows and 15 Columns.
(375
000 Cells) I will try to make all Columns separate, if this will work
for
a
single column. In this single column data is been repeated some times so
amount of cells in Single A column will be 25 000, and amount of Cells
in
B
and C columns will be only about 5 000 cells per column, and cell's B
Value
does not correspond to A Value in current Row.

As I already wrote on 23rd 08 2003:

It works for

RED RED R
BLUE BLUE B



But not for

RED BLUE B
BLUE RED R
BLUE
RED
BLUE
RED
RED
BLUE

Have you any Idea how to make it work?

Thanks again in advance for your patience and help!

Andre

Sorry, Mistake!
 

I'm jumping in again!

For each cell in column A, check to see if matches a value in column B (any
value--not just the same row!).

And if it matches, then take the corresponding value from C (on the matched row
and copy it to the cell in column A.

A B C
aaa aac x
aab bbb y
aac aaa z
aad bbc w

So for aaa, I'd see a match in row 3 and use Z in column A.
For aac, I'd see a match in row 1 and use x in column A.

A B C
z aac x
aab bbb y
x aaa z
aad bbc w

If this is true, then I'm going to suggest a different approach.

Insert a new column B (shifting B&C to C&D)
Insert this formula in B1.
=IF(ISERROR(VLOOKUP(A1,$C$1:$D$99,2,FALSE)),A1,VLO OKUP(A1,$C$1:$D$99,2,FALSE))

Adjust the ranges to match your data (and start in the correct row).

If you change anything in column A, then column B will fix itself with the nex
calculation.

If you don't want to see column A, then you can hide it.

If this were a one-time shot (you never need to do it again), then convert
column B to values and delete column A. (one way to convert to values:
copy|Paste special|Values)



SolaSig AB wrote:

Oopps!

Wrote:
It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed
with Value in _C3_, but in real life there is not so simple because Value
for
A1 may match only Value in B3 and then Value in A1 have to be changed from
C3 Value.

Have to write:
It mean IF Value in A1 Matches Value in B1 then Value in A1 will be changed
with Value in _C1_, but in real life there is not so simple because Value
for
A1 may match only Value in B3 and then Value in A1 have to be changed from
C3 Value.

Andre

"SolaSig AB" wrote in message
...
Dave, thanks for your Help with whole Sub, and thanks to Don for original
idea this really works!
(Problem was as I see only in my poor knowledge of VBA)

Now I'll try to make this Sub to work for whole B Column.
As I see Value in Cell A will be changed only if Value in Cell B matches,
but the Row have to be the same also!
It mean IF Value in A1 Matches Value in B1 then Value in A1 will be
changed
with Value in C3, but in real life there is not so simple because Value
for
A1 may match only Value in B3 and then Value in A1 have to be changed from
C3 Value.

Original text is much more complex there is 25.000 Rows and 15 Columns.
(375
000 Cells) I will try to make all Columns separate, if this will work for
a
single column.
In this single column data is been repeated some times so amount of cells
in
Single "A" column will be 25 000, and amount of Cells in B and C columns
will be only about 5 000 cells per column, and cell's B Value does not
correspond to A Value in current Row in 99,99% of cases.

As I already wrote on 23rd 08 2003:

It works for

RED RED R
BLUE BLUE B



But not for

RED BLUE B
Dave thanks for your Help with whole Sub, and thanks to Don for original
idea this really works! (Problem was as I see only in my poor knowledge of
VBA)

Now I'll try to make this Sub to work for whole B Column.
As I see Value in Cell A will be changed if Value in Cell B matches, but
the
Row have to be the same also! It mean IF Value in A1 Matches Value in B1
then Value in A1 will be changed with Value in C3, but in real life there
is
not so simple because Value for A1 may match only Value in B3 and then
Value
in A1 have to be changed from C3 Value.

Original text is much more complex there is 25.000 Rows and 15 Columns.
(375
000 Cells) I will try to make all Columns separate, if this will work for
a
single column. In this single column data is been repeated some times so
amount of cells in Single A column will be 25 000, and amount of Cells in
B
and C columns will be only about 5 000 cells per column, and cell's B
Value
does not correspond to A Value in current Row.

As I already wrote on 23rd 08 2003:

It works for

RED RED R
BLUE BLUE B



But not for

RED BLUE B
BLUE RED R
BLUE
RED
BLUE
RED
RED
BLUE

Have you any Idea how to make it work?

Thanks again in advance for your patience and help!

Andre



--

Dave Peterson