Link to home
Start Free TrialLog in
Avatar of UD
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.
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Did you mean to upload a file?
Avatar of UD
UD

ASKER

No, it's just to work with excel file.
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.
Avatar of UD

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
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
Avatar of UD

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.
SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of UD

ASKER

Appreciate your quick response Martin, please see the attached snapshot.
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of UD

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?
Avatar of UD

ASKER

Please see the attached file as reference.
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.
Avatar of UD

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
I guess Martin has administrative privilege to reopen the question. :)
Or you can click the following link to send your request.
User generated image
I guess Martin has administrative privilege to reopen the question.
I don't, so we'll need to wait for a moderator.
Avatar of UD

ASKER

@ Martin, I sent a request to Moderators based on Subodh's suggestion on sharing points. Let's wait for an answer.

Cheers,
Uday