Automatically replacing values with text

[Thintin]

Hi.

I'm looking for advice about the simplest way to make automatic changes to the values of whole column. A spreadsheet I receive has a column titled 'categories' which has numbers in the cells (these numbers relate to categories used by the author). I'd like to change them to my own category labels which will be text. There are a lot of different numbers which will be resolved to the same category labels; so of the 30 to 50 number codes there will only be about 15-20 categories.

I'm using Excel 2010.

I hope this all makes sense!

Thanks, Phil.

[Glenn Kennedy]

A simple table relating the numbers to the text and a VLOOKUP will do this. I can't be more specific without seeing a sample sheet. So...

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[63falcondude]

Try building a lookup table with the existing numbers in one column and the associated values in the column next to it.

Then, you can create another column using a VLOOKUP or INDEX MATCH formula to match the numbers to the text strings.

Note that you cannot have both a manually entered value and a formula in the same cell so the numbers and matched values will have to be in separate columns.

If you would like more specific help, consider uploading a small representative sample of your data along with the desired results (manually entered).

To upload an Excel workbook, click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Thintin]

Okay. Like this?

[63falcondude]

You can use the sample that you shared in post #4 as your lookup table.

Then, if the categories are in column E (staring in E2), use this formula in column F:

F2 =VLOOKUP(E2,A:C,3,FALSE)

[AliGW]

You are on the right track.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G
1	9118	equalls	Apparel			9046	Gadgets
2	9046	equalls	Gadgets
3	100009	equalls	Homeware
4	100577	equalls	Homeware
5	232	equalls	Homeware
6	350	equalls	Toys
7	100058	equalls	Seasonal
8	etc		etc

Sheet: Categories sample

Excel 2016 (Windows) 32 bit

	G
1	=VLOOKUP(F1,$A$1:$C$7,3,0)

Sheet: Categories sample

[Thintin]

Thanks for the replies. I've just noticed that when I saved the spreadsheet as a .csv it only saved the first sheet (there were 3). I'll upload again as .xls and see if it changes your responses.

[AliGW]

Sorry - please explain why three worksheets will make a difference.

EDIT: Ah! Well, that's a completely different request! I think you'll need VBA for this. Shall I move the thread for you?

[Thintin]

I only used 3 sheets to separate the information for the question. The spreadsheet I receive is just one sheet and I only need to replace the column of category codes (sheet 1 on my example) with category names (which would look like sheet 2 on my example). Sheet 3 was just how the codes would translate to titles. Is this still handled better by VBA?

Thanks.

[AliGW]

Well, if you want values replacing, then yes. Unless you want to run Find and Replace as many times as necessary?

[Thintin]

I'm a novice but the sheet has c1800 rows and is updated daily so I don't think find/replace will be a viable option. If you're suggesting VBA then I'm happy to give it a go as you seem to be a bit more clued-up than me :-)

p.s yes, please move the thread :-)

[JeteMc]

May not be what you were looking for, however since this has gone unsolved for several days, I thought that I would offer.
You get the category numbers along with other data (I assume) on one sheet and, I propose, through formulas have a result on another, which would then become your final product.
The formulas, as there are sometimes more than one category number, that convert numbers to text are:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The formula to display the category names on the result sheet is:

Please Login or Register  to view this content.

Note: undefined category numbers result in an #N/A error, which could be caught by error trapping (i.e. IFERROR) however I assume that all category numbers will be assigned a name.
The rest of the data is displayed on the result sheet is: ='original data'!B2
Let us know if you have any questions.

[Thintin]

Hi. Sorry for the late reply, the forum hadn't told me there'd been another post! This looks like it does exactly what I need, thanks! Would you use some kind of macro to automate this on a new spreadsheet created from a CSV feed? I'm looking for the simplest way to take a CSV/XML feed, make the changes and then import the new table to a website in order to update the existing listings?

[JeteMc]

I am pretty much VBA illiterate, so I can't help you with that part. Perhaps if you were to mark this thread as solved, then open a new thread on the Excel Programming/VBA/Macros forum someone there would be able to help. Be sure to either upload the file from post #12 or link to this thread so that the contributors over there will have a starting point.
Best of luck and let us know if you have any questions.

[Thintin]

Great advice, I will do just that :-)

[JeteMc]

To mark a thread as 'Solved' use the thread tools drop down which is on the right side of the ribbon above the first post on this page.