Excel formula for pulling specific names from fields

[ace8pga8]

i need an excel formula that can locate specific names of vendors from a journal line description field. There are over 300 vendors currently that i have to sort through manually in the description field through adding a column. Conventional lookup formulas will not work as the journal line description field is not consistent...i.e. the vendor name could appear at the beginning, middle, or end of the description field.

[alansidman]

Hi and welcome to the forum

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[ace8pga8]

Here you go Alan. The only other formulas I tried was =MID and a Macro that pulls out the 'n'th word in a field, as shown below. Since I was unable to use those methods I simply did a Text filter 'contains' and copied/pasted the vendor name to the Vendor column. There are over 45k rows of data.

Thanks in advance for your assistance.

Please Login or Register  to view this content.

Capture.PNG

[AliGW]

Please provide a workbook, NOT a picture.

[ace8pga8]

here you go!Sample.xlsx

[alansidman]

I created a parameter query in Power Query. In the attached you will find a lookup field. Type the vendor you wish to look up using all caps and then on the Data Tab of the ribbon select Refresh All. Then the lines containing that vendor will populate.

[alansidman]

This would not post in the above thread. Additional info on Power Query

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[JEC.]

Or:

Please Login or Register  to view this content.

You can also use this method if you need to search for multiple vendors. Requires a small adaption.

[Glenn Kennedy]

To be honest, it's not much of a sample... 129 rows, for all of which the expected answer is "Microsoft Corporation".

Can we have 10-20 rows that REALLY represent your data, assuming that the above offerings don't float your boat.

[ace8pga8]

Hi Glenn...ask and you shall receive...

Sample.xlsx

[ace8pga8]

Thaks JEC....yes my template has multiple vendors (over 300)...see attached.Sample.xlsx

[ace8pga8]

wow...thanks alan i will check it out and get back to you on progress!

[Glenn Kennedy]

I asked for 10-20 rows, not 48,258. I am not prepared to embark on manual checking of even 1% of that number of rows, so... sorry - over to others.

[ace8pga8]

Apologies Glenn....i have reduced the number of rows considerably...i misinterpreted your email. Does this work?Sample new.xlsx

[ace8pga8]

Thank you Alan! I am familiar with Power Query but was unaware of that functionality.

[ace8pga8]

Thank you JEC...what is the small adaption for searching for multiple vendors? I have over 300...

[Glenn Kennedy]

Like this:

=IFERROR(LOOKUP(1000,SEARCH($F$4:$F$18,A4),$G$4:$G$18),"")

[ace8pga8]

Wow this is awesome! Thanks for your help Glenn!

[Glenn Kennedy]

You're welcome.

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[ace8pga8]

Thank you everyone for your help! Much appreciated...

[ace8pga8]

Alan...what significance does the lookup value "1000" have in the formula? what happens if i were to enter 5000 or 2000? Is that the number of rows to search?

Also, i just found out there are certain cases where there are two vendors within one journal description which i am attaching herein. Apologies this just came to my attention.

Sample new.xlsx