Question description:
This user has given permission to use the problem statement for this
blog.
I have a workbook with 2 sheets. First sheet has column A which is a device name, then columns B - infinity that have numeric "tags" for that device. Each device can have one or 20 "tags", tags are all numerical. Worksheet 2 has column A which is numeric value and B which is a human readable value of what the tag is. Question is, how do I replace all the tag numbers in worksheet 1 with the actual human readable values is sheet 2?
Solved by T. H. in 16 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
24/07/2018 - 09:38
Hello there, welcome to Got It Pro.
Excelchat Expert
24/07/2018 - 09:38
Hope you are having a good day.
Excelchat Expert
24/07/2018 - 09:38
Could you please upload the file you are having problems with? Please use the attachments button on the right of the chat tab.
User
24/07/2018 - 09:38
Hi Thanks. I can't really upload it, it may have sensitive info.
Excelchat Expert
24/07/2018 - 09:39
No worries.
Excelchat Expert
24/07/2018 - 09:39
Could you please create a sample data set here in the document preview window in the middle of the screen?
User
24/07/2018 - 09:42
It's almost dinner, that's all I could think of. But the idea is I grab the "tags" from sheet 1, and replace the value in those columns, with whatever column B is in sheet 2.
User
24/07/2018 - 09:42
Does that make sense?
Excelchat Expert
24/07/2018 - 09:42
Yes
Excelchat Expert
24/07/2018 - 09:42
Please allow me a few moments.
User
24/07/2018 - 09:46
Yep, like that
Excelchat Expert
24/07/2018 - 09:46
Okay.
Excelchat Expert
24/07/2018 - 09:46
Do you need me to hide the errors with a blank or an alternative message?
User
24/07/2018 - 09:47
Hide would be good, the rows will have variable # of columns
Excelchat Expert
24/07/2018 - 09:48
No problem.
Excelchat Expert
24/07/2018 - 09:48
Alright, does this look good?
User
24/07/2018 - 09:48
Magic. Looks great
Excelchat Expert
24/07/2018 - 09:48
Alright, let me explain what I did.
Excelchat Expert
24/07/2018 - 09:49
Well, you can't have a value and a formula in a cell at the same time.
Excelchat Expert
24/07/2018 - 09:49
So, I duplicated the first worksheet.
Excelchat Expert
24/07/2018 - 09:49
Then in B2, I assigned the formula =IFERROR(VLOOKUP('Copy of Sheet1'!B2,Sheet2!$A$1:$B$5,2,FALSE),"")
Excelchat Expert
24/07/2018 - 09:50
What it says is look for number 230 in sheet 2 and return the corresponding item name.
Excelchat Expert
24/07/2018 - 09:51
The structure for VLOOKUP is = VLOOKUP (Lookup_Value, Lookup_Range, Return_Column, Match_Type)
Excelchat Expert
24/07/2018 - 09:51
And if it doesn't find a match, keep it blank. Which we specified by ""
Excelchat Expert
24/07/2018 - 09:51
That's about it.
Excelchat Expert
24/07/2018 - 09:51
Do you follow? Do I need to repeat anything?
User
24/07/2018 - 09:52
I think I follow...can I copy that?
Excelchat Expert
24/07/2018 - 09:53
You have the conversation record on your Got It Pro account.
User
24/07/2018 - 09:53
Excellent. I will give this a try on the real document. You are amazing, thank you!
Excelchat Expert
24/07/2018 - 09:53
And you can download the file from File > Download As > Microsoft Excel (.xlsx)
Excelchat Expert
24/07/2018 - 09:53
You're welcome.
Excelchat Expert
24/07/2018 - 09:53
Kindly end the session if your query is resolved.
Excelchat Expert
24/07/2018 - 09:53
Thanks for using Got It Pro. Please come back again with your Excel related queries.
Excelchat Expert
24/07/2018 - 09:53
Have a good day.
User
24/07/2018 - 09:54
You as well.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.