UD
asked on
Tricky Data to auto populate
Dear Experts,
I'm looking for an excel macro to auto fill cells in Column B based on selected description from cells in column A. I tried using Vlookup but this is not completely successful.
Example:
Cell A1 - Hollywood/entries/Jan 2018 - result in column B1 should be - Hollywood
Cell A2 - Hwood/entries/Dec 2017 - result in column B2 should be - Hollywood
Cell A3 - Sales/Jan - Result in Column B3 should be - January
in above example, the source is not definite and specific information is sometimes expanded sometimes abbreviated, and this is just example, similar to Hollywood and Jan, the list is exhaustive.
Any help to crack this is highly appreciated.
I'm looking for an excel macro to auto fill cells in Column B based on selected description from cells in column A. I tried using Vlookup but this is not completely successful.
Example:
Cell A1 - Hollywood/entries/Jan 2018 - result in column B1 should be - Hollywood
Cell A2 - Hwood/entries/Dec 2017 - result in column B2 should be - Hollywood
Cell A3 - Sales/Jan - Result in Column B3 should be - January
in above example, the source is not definite and specific information is sometimes expanded sometimes abbreviated, and this is just example, similar to Hollywood and Jan, the list is exhaustive.
Any help to crack this is highly appreciated.
Did you mean to upload a file?
ASKER
No, it's just to work with excel file.
Other Details - Extension .xls and version 2010.
Other Details - Extension .xls and version 2010.
Welcome to Experts Exchange Uday. In order to help you we need to see your Excel workbook or a sample workbook. To do that, click the 'Attach File' link that you'll find at the bottom of a post, click the blue 'Upload File' button that will appear, and then click the blue 'Submit' button.
ASKER
Thank you Martin...
Attached is the file with sample data and my working, please take a look. Version and file extension are different but hope this will not impact the output.
Regards,
Uday
Tricky-Data.xlsx
Attached is the file with sample data and my working, please take a look. Version and file extension are different but hope this will not impact the output.
Regards,
Uday
Tricky-Data.xlsx
There may be a VLookup or other built-in formula solution, but if not then you can use the macro in this workbook. To run the macro, type Ctrl+Shit+L
29109000.xlsm
29109000.xlsm
ASKER
Yep Agree Martin... I'm trying to explore more options with formulas as i'm toddler with Macros.
I tried to execute the macro you shared, looks like Variable needs to be defined and after tweaking it to define variable it leads to more challenges.
I tried to execute the macro you shared, looks like Variable needs to be defined and after tweaking it to define variable it leads to more challenges.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Appreciate your quick response Martin, please see the attached snapshot.
Type-Mismatch.JPG
Type-Mismatch.JPG
Can you attach your actual workbook because my code works without error with the data you supplied? If you can’t do that then please tell me which line is generating the error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Subodh and Martin.
The last one worked perfect!
I believe my column J & K should be updated whenever there are new unique description entries in the list for column B.
Is it insane asking to get the result in column C, assuming there are no values for columns J & K ( I know as basic excel has to look into something to get the desired value).
Say if Column B has 100 unique descriptions, should i update column J & K... with 100 unique entries?
The last one worked perfect!
I believe my column J & K should be updated whenever there are new unique description entries in the list for column B.
Is it insane asking to get the result in column C, assuming there are no values for columns J & K ( I know as basic excel has to look into something to get the desired value).
Say if Column B has 100 unique descriptions, should i update column J & K... with 100 unique entries?
ASKER
Please see the attached file as reference.
Tricky-Data.xlsm
Tricky-Data.xlsm
@Uday
I think the code was written by Martin and I only tweaked it a bit so it would be better if you split the points considering the prime contribution of Martin to this question.
Please raise a request attention to reopen the question and split the points accordingly.
Also, for any variation in the question, please open a new question and Martin will continue supporting you in the new question as well.
I think the code was written by Martin and I only tweaked it a bit so it would be better if you split the points considering the prime contribution of Martin to this question.
Please raise a request attention to reopen the question and split the points accordingly.
Also, for any variation in the question, please open a new question and Martin will continue supporting you in the new question as well.
ASKER
My bad, not good enough with navigating the site options.
Please share the link which has this option. I tried searching it but no luck with including the below link
https://www.experts-exchange.com/questions/29017814/Request-Attention-Button.html
Please share the link which has this option. I tried searching it but no luck with including the below link
https://www.experts-exchange.com/questions/29017814/Request-Attention-Button.html
I guess Martin has administrative privilege to reopen the question. :)
I guess Martin has administrative privilege to reopen the question.I don't, so we'll need to wait for a moderator.
ASKER
@ Martin, I sent a request to Moderators based on Subodh's suggestion on sharing points. Let's wait for an answer.
Cheers,
Uday
Cheers,
Uday