Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2003 Command descriptions | Excel Discussion (Misc queries) | |||
link colors to descriptions in bar charts | Charts and Charting in Excel | |||
IF and OR functions to obtain text descriptions | Excel Worksheet Functions | |||
"translate" numbers into words | Excel Discussion (Misc queries) | |||
Adding cells with similar descriptions | Excel Worksheet Functions |