Changing website hyperlink data to cell comments

I took a look at your Workbook to see what you were talking about. Based on what I can see, those definitions are not part of the COPY/PASTE, but instead displayed on mouse hover like a traditional Screen Tip.

The URL's look workable for batch Web Scraping, but when I tried connecting using Excels "From Web" tool on the Data tab, it kept failing to connect to their site. Most likely being rejected by the Site itself.

I figured if you could use the URL's for each Term to scrape that Definition information into Excel, that would save a ton of time. I tried, but I didn't have any luck. Perhaps others more versed on using Power Query can assist or possibly a better way to connect to their site.

I've used a similar tactic for a list of books based on a Unique ID in the URL.

For example, your root URL's are appended by each term; dashes are used to replace the spaces as seen in each URL:

3P is <URL prefix>/3p
Alpha Risk is <URL prefix>/alpha-risk
B10 Life is <URL prefix>/b10-life

If you can get the web scraping to work, you might be able to extract it for all of the URL's you are interested in.
Of course, you would need to use Find & Replace to replace the Spaces ( ) with Dashes (-) before building the full URL to scrap from. There is no guarantee that web scraping their site is even possible, but it's worth looking into.

Please Login or Register  to view this content.

$A2 being the cell that holds the properly formatted terms: from B10 Life to b10-life

Another thing you could try is to contact them to see if they have a downloadable version of that information, they might be willing to share with you.

Either way, best of luck. That's a ton of information and the manhours building it manually is incomprehensible to say the least.

Hi Xcellentform,

I was thinking more about your end goal and was wondering if you considered looking to other sites with the same information?

I did a quick search for "download six sigma terms and definitions" and found some other sources that you might benefit from.

The top hits I saw were:

1. goskills DOT COM has both terms (A-Z) and definitions on a single page, along with a few acronyms and symbols.
2. chisolutionsinc DOT COM This one is a 6-page PDF with selectable text, but I noticed it only goes up to "Y", no "Z".
3. sixsigmaonline DOT ORG has basically the same thing as goskills.com.
4. projectsmart DOT CU DOT UK This one has a nice list of terms and definitions as well. This one might be easier to manipulate in Excel because the terms and definitions are over/under and not side/side.

I can't post links yet, so I used bitly to shorten the URLs. Just preface "https:" for each of the following:

1. //bit.ly/3IZcjtN
2. //bit.ly/3ireOtX
3. //bit.ly/3XGB5D5
4. //bit.ly/3XqfL4i

I took a stab at #4:

I was able to copy all into Excel, then delete the empty cell (rows) and then format every other row to move them to a separate column to line them up side by side.

1. Once pasted into Excel (Cell A2), leave them selected. On the Home tab, go to Find & Select > Go To Special… > select Blanks > OK
2. Now that only the blank cells are selected, we can Delete those rows: Delete > Delete Sheet Rows
3. I used Conditional Formatting to highlight (Fill color) every other row using the following formula: =MOD(ROW(A1),2)-0
4. We want to filter this column to show only the Definitions. Just type any letter in the empty cell A1. Leaving A1 selected, turn on the filter: Sort & Filter > Filter
5. Now just Filter by Color > No Fill
6. You should only see the Definitions now. Select all the visible cells now and then Copy and Paste these definitions below the bottom of this list (cell A247) in my case; Paste Values only.
7. You can delete the definitions that were filtered; select and delete cell contents; not rows.
8. Clear the Filter but leave the cells selected.
9. Repeat Steps 1 & 2 to delete those newly created Blank Rows.
10. Now you can move your definitions into Column B and they will line up with their Terms.

One thing to note is that the third term is missing. It's actually the definition of their 2nd term.; a duplicate. You just have to figure out what the proper term is based on the definition.

I know this doesn't address your interest in getting "definitions" into a Comment, but it's a good start with getting definitions into Excel quickly.

Hi Zero,

Wow, you are one dogged individual! I will look more into this this afternoon when I get a chance, but it seems like you surely solved most or all of the issues. The reason for all of it, like you may have noticed, is that even though LSS is getting to be quite popular, it still is a mess on the top end where there are no standardized terms, dictionaries, conflicting definitions, and sporadic categorization of definitions. There is no Miriam Websters LSS Dictionary, and since LSS draws on knowledge from accounting, engineering, QC, business, law/ legal, statistics, excel, etc. and the fact that every new author, grad student, or LSS Black Belt wants to add new terms or change existing terms.....it really has made a mess of things which is why new students struggle with it initially. LSS doesn't even have standardized symbols for graphs and charts!!! Haha! My first source dictionary had a paltry 100 terms and acronyms defined while my current running dictionary is up to about 950 terms and counting, so some of your sources are either redundant or possibly even new definitions to add to my running dictionary.

Even after the definitions get sorted out, then the next phase that I am also working on in tandem, is making a comprehensive flowchart to identify which parts of the LSS process many of the actionable terms fall into. It's a lofty project but one that also does not seem to exist for LSS, or the ones that do exist map out maybe 20 or 30 of the processes leaving somebody with question marks on the other 250 processes or so.

I've stepped away from Excel for a decade (been using google sheets due to being free) and so just getting back in the saddle with Office 365 and such and apparently haven't found the difference between Notes and Comments, so I greatly appreciate that advice you dropped too.

My original dictionary did have definitions in cells of adjoining columns, and the visual sheet got really messy and reading the long definitions by scrolling right was just super distracting. I know changing it to text boxes of any kind is blowing up the bloat which has been, and will continue to be, an ongoing challenge with documents of this size.

Thanks again for your continued dogged interest and help with this topic which I think is quite relevant to lots of others that are doing website scraping projects, which is a bit new for me as well. If the topic of LSS is of interest to you, I will keep you in the loop with the progression of my project.