Find and replace 9000 times?

[jago25_98]

There are 2 columns of data, A, and B. A contains numbers and B
contains what these numbers stand for. These columns contain about 9000
rows of data.

How do I replace the data in column A with that in column B?

One method is to use find and replace, going down column A and
replacing them by hand for all 9000 rows:-

For example:

1 = Avon
2 = Devon
3 = UK
4 = USA

^ press ctrl+H, type in 1 in the find box, and Avon in the replace box.
Run and repeat replacing all instances in the database 9000 times.

Is VBA the only option, and if so how may I do so?

TIA if you can!

 

[Peo Sjoblom]

I don't understand the problem? Why don't you just delete column A?

 

[Earl Kiosterud]

Jago,

I'm not sure I understand either. If for some reason you don't want to
delete the column as Peo has suggested, you can copy column B into column A:
Select column B (click in the header). Drag the fill handle (lower right
corner of B1) to the left into column A.

 

[Katherine Coombs]

In cell A1 type =B1. Drag this formula down, or double click on the little
black cross at the bottom right hand corner of the cell to automatically
fill it to the last instance (you should also check that it has extended far
enough though).

Then highlight column A and go to Edit-->Copy. Then go to Edit-->Paste
Special and select Paste Values and press OK. Press Enter to paste it over
the existing column A contents, and then Column A will match column B. You
can then delete or clear the contents of Column B as needed.

HTH,
Katherine

 

[Gord Dibben]

Katherine

It is very late here so may be missing something esoteric but wouldn't just
deleting column A achieve the same results with a few less steps?

Gord Dibben XL2002

In cell A1 type =B1. Drag this formula down, or double click on the little
black cross at the bottom right hand corner of the cell to automatically
fill it to the last instance (you should also check that it has extended far
enough though).

Then highlight column A and go to Edit-->Copy. Then go to Edit-->Paste
Special and select Paste Values and press OK. Press Enter to paste it over
the existing column A contents, and then Column A will match column B. You
can then delete or clear the contents of Column B as needed.

HTH,
Katherine

 

[jago25_98]

ok I'll try to be a little clearer.

I have a database with about 10,000 entries.

Each entry is a code that stands for something. This was presumably
done to save time.

I now need to convert each code to its assigned meaning using a lookup
table that is available in a seporate worksheet.

Example lookup table:

1011 - 4 Miles
1012 - 6 Miles
2011 - James
2012 - Michael
4018 - Old
4019 - Young

And example dataset that needs to be converted:

2012
2012
4019
2011
2012
4018
2011
....
...etc

 

[Arvi Laanemets]

Hi

With your data in column A on Sheet1 and lookup table in range A2:B100 on
Sheet2
In some empty column different from column A (you can insert one, p.e. as
column B, when needed), p.e. into cell B2, enter the formula
=VLOOKUP(A1,Sheet2!$A$2:$B$100,2,FALSE)
and copy to range like one with codes in column A

When you then don't need the codes anymore, then select the range with
formulas, copy it, and then PasteSpecial.Values - and after that delete
former column A (or leave them as they are - it's your choice).

 

[jago25_98]

functions of interest but I'm not sure how to use:

SEARCH
SUBSITUTE
VLOOKUP
LOOKUP

here's the database I'm working on:

'database' (http://www.termisoc.org/~j/files/SWdata.xls) (1.7mb)

The `key` or `lookup` table is in the furthest left sheet. The data
that needs to be resolved using this lookup table from codes to string
variable data is in the `landslide data` sheet in the middle.

 

[Katherine Coombs]

Hi Gord,

If you have a cell in, say C1 that is =A1 and you just delete column A, it
will return #REF! so by replacing the contents rather than deleting the
column, you avoid this.

Cheers,
Katherine

Katherine

It is very late here so may be missing something esoteric but wouldn't just
deleting column A achieve the same results with a few less steps?

Gord Dibben XL2002

 

[Max]

Try this:

Insert a new sheet

Copy over your headers in rows 1 & 2 from the Landslides data sheet

Put in A3
: =OFFSET('lookup table-raw'!$A$1,MATCH('Landslides data'!A3,'lookup
table-raw'!$A:$A,0)-1,1,1,1)

Copy A3 across to AF3 (2nd last col on the right),
then copy down to row 1627 (last row)

For col AG

put in AG3: ='Landslides data'!AG3
copy down to row 1627

The above should return what you want.
--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email

 

[lpolliard]

I think everyone is off the mark.

Jago, you need to use the vlookup function. The function wizard wil
help you set it up. See attachment. Ref Data contains your list o
codes. Be sure to sort in ascending order as required to use th
vlookup function. A1 on sheet1 is your lookup value. B1 is th
description from the Ref Data using a vlookup. Just hide column A i
you don't want to see the code or paste special value column B an
delete column A.

Hope this helps

+----------------------------------------------------------------
| Attachment filename: book1.xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=356633
+----------------------------------------------------------------

 

[Max]

Did you see & try out my suggestion to Jago
on a d/l copy of the 1.7M file (via the link provided by Jago)?
(your sweeping statement > I think everyone is off the mark.
compelled this reply)

And... I'm pretty sure my suggestion would have given
Jago exactly what was pursued,
ie without need to further amend the formula given,
albeit Jago has since not / may not? feedback further
(would be nice to have some feedback from Jago, though).

Well, guess one could say I ventured the extra mile
to d/l Jago's file as I wanted to see if I could help Jago out.

Actually, Arvi L. had earlier posted a workable VLOOKUP() using FALSE
as the 4th parameter, which if translated correctly by Jago to suit, would
also have given the correct returns desired.

But I perceived that Jago might have encountered some problems
in implementing Arvi's suggestion in view of his/her feedback
giving the link to the 1.7M file.

Allow me some comments on what you suggested...

You gave this example formula in your attachment
: =VLOOKUP(A1,'ref data'!A1:B27,2,FALSE)

This is similar to what Arvi L. gave except that
'ref data'!A1:B27 should have absolute cell refs, ie
'ref data'!$A$1:$B$27

otherwise you will get erroneous results when you
copy the VLOOKUP across/down

... Be sure to sort in ascending order

as required to use the vlookup function.

Note that your above statement does not apply in this instance.
'ref data'!$A$1:$B$27 need *not* be sorted
since we are looking for an exact match
using FALSE as the 4th param.

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
-------------------------------------------

 

[Max]

So, did it work for you, Jago?

Some feedback, please.