HOWTO Replace from Row

  • Thread starter Thread starter SolaSig AB
  • Start date Start date
S

SolaSig AB

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
 
something like - untested

for each c in selection
if c=c.offset(,1)then c=c.offset(,2)
next
 
Thanks for idea,
but I didn't actually understand how to start routine in Excel
as function process. Or this will be possible only with VBA?

For example how I do NEXT function?

Thanks again,
On-Line

something like - untested

for each c in selection
if c=c.offset(,1)then c=c.offset(,2)
next
 
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)
 
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, How is that materially different from my original?

for each c in selection
if c=c.offset(,1)then c=c.offset(,2)
next
 
You're right about that.

Dave Peterson said:
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.
Click to expand...
 
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
 
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
 
Real clear.

SolaSig AB said:
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


for cells there for in
Click to expand...
 
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,VLOOKUP(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)
 
Back
Top