Translate from Numbers to Descriptions
 

Each month many of us receive an auto-export in Excel that contains data that
we use to create charts and Pivot Tables from. One of the columns contains
numbers that reference a description that is not stored on the database that
contains the export.

We receive about 1000 records and what I have been doing is performing a
Search and Replace but now there are 100 descriptions!

I used to work with VLookup but was wondering if anyone has done something
like this with a macro as well.

I have Excel 2003

Translate from Numbers to Descriptions
 

This is just an example that you can adapt for your use. It assumes that
Sheet1 conains in column A the numbers that you want to replace. It assumes
that Sheet2 contains the translation table (column A has the number and
column B has the description):

Sub descriptions()
Sheets("Sheet1").Activate
Set s2 = Sheets("Sheet2")
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To n
v = Cells(i, 1).Value
For j = 1 To 100
If v = s2.Cells(j, 1).Value Then
Cells(i, 1).Value = s2.Cells(j, 2).Value
End If
Next
Next
End Sub

--
Gary''s Student - gsnu200773


"Joey" wrote:

Each month many of us receive an auto-export in Excel that contains data that
we use to create charts and Pivot Tables from. One of the columns contains
numbers that reference a description that is not stored on the database that
contains the export.

We receive about 1000 records and what I have been doing is performing a
Search and Replace but now there are 100 descriptions!

I used to work with VLookup but was wondering if anyone has done something
like this with a macro as well.

I have Excel 2003

Translate from Numbers to Descriptions
 

Thanks Gary, I am real rusty with my Macros.... My data to translate in on
Sheet 1, Column F

The Translation Table is on Sheet 2 Column A for the number and B for the
description.

Can you explain how I would update your macro info?

"Gary''s Student" wrote:

This is just an example that you can adapt for your use. It assumes that
Sheet1 conains in column A the numbers that you want to replace. It assumes
that Sheet2 contains the translation table (column A has the number and
column B has the description):

Sub descriptions()
Sheets("Sheet1").Activate
Set s2 = Sheets("Sheet2")
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To n
v = Cells(i, 1).Value
For j = 1 To 100
If v = s2.Cells(j, 1).Value Then
Cells(i, 1).Value = s2.Cells(j, 2).Value
End If
Next
Next
End Sub

--
Gary''s Student - gsnu200773


"Joey" wrote:

Each month many of us receive an auto-export in Excel that contains data that
we use to create charts and Pivot Tables from. One of the columns contains
numbers that reference a description that is not stored on the database that
contains the export.

We receive about 1000 records and what I have been doing is performing a
Search and Replace but now there are 100 descriptions!

I used to work with VLookup but was wondering if anyone has done something
like this with a macro as well.

I have Excel 2003

Translate from Numbers to Descriptions
 

Joey
Keep your responses in the same thread, otherwise it's difficult to know
to what post you are responding. HTH Otto
"Joey" wrote in message
...
Thanks Gary, I am real rusty with my Macros.... My data to translate in
on
Sheet 1, Column F

The Translation Table is on Sheet 2 Column A for the number and B for the
description.

Can you explain how I would update your macro info?

"Gary''s Student" wrote:

This is just an example that you can adapt for your use. It assumes that
Sheet1 conains in column A the numbers that you want to replace. It
assumes
that Sheet2 contains the translation table (column A has the number and
column B has the description):

Sub descriptions()
Sheets("Sheet1").Activate
Set s2 = Sheets("Sheet2")
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To n
v = Cells(i, 1).Value
For j = 1 To 100
If v = s2.Cells(j, 1).Value Then
Cells(i, 1).Value = s2.Cells(j, 2).Value
End If
Next
Next
End Sub

--
Gary''s Student - gsnu200773


"Joey" wrote:

Each month many of us receive an auto-export in Excel that contains
data that
we use to create charts and Pivot Tables from. One of the columns
contains
numbers that reference a description that is not stored on the database
that
contains the export.

We receive about 1000 records and what I have been doing is performing
a
Search and Replace but now there are 100 descriptions!

I used to work with VLookup but was wondering if anyone has done
something
like this with a macro as well.

I have Excel 2003