Extracting Multiple Columns with Search

Question

I am having trouble getting my table to extract information across multiple columns. Currently I have two excel sheets:

The first excel sheet is a master log with a giant table filled with the hyperlinks and names of all the Engineering Standard Procedures for my company. This is the table I am extracting information from.

The second excel sheet is pictured below. It's purpose is that any word typed into the search bar returns any partial results from that first excel sheet in it. Currently my table functions by only searching one column and returning the hyperlink for that esp stated.

Here is the original link to the screenshots. Thanks to the help of @Jvdv.

What I would like is either of the following..

1. After a keyword is entered into the search bar. The results show the keywords associated with that ESP along with the ESP name and Hyperlink.

For example: If I type in the word Sales. The first column of my extraction table would read "Material Vendor Identification Chart" The keywords column would have "materials sales" in it and the hyperlink column would provide the respective hyperlink.

or

2. After a keyword is entered into the search bar. The program searches both the keywords and the Name of the ESP and returns a partial match with the hyperlink.

Answer 1

The following solution is using Power Query which is available in Excel 2010 and later versions. Mine is Excel 2016.

To tackle your question, I used the following sample data. It is stored in an Table called Tbl_ESP.

Please note I've added a new column called Hyperlinks which displays the link behind each functional hyperlinks in column A. At the moment there is no excel formula to do that automatically so you have to either use VBA or manually copy and paste links in the new column. The purpose of this step is to allow you to have the hyperlinks working in the Search Result Table.

In another worksheet I created the following Table called Tbl_Search, in which you can type in the key words and it is case insensitive. In my solution you can only use 'space' to separate each key word (you can change the setting to use comma or other preferred delimiter to separate each key word).

Then you can use From Table function in the Data tab to add both tables to the Power Query Editor. You can add one first, then exit the editor and add another one using the same function. Check this article out for other methods: The Complete Guide to Installing Power Query

I started working with Tbl_Search first in the Power Query Editor with the following steps:

1. changed the text string to lower-case;
2. Split the text by "space" and put each word in a new row;

3. Filled Down the column to overwrite null in case there is only one key word;
4. Convert the column to a List.

I have re-named this query as Search, which will be used as the search criteria later on.

Then I started working on the Tbl_ESP in the following steps:

1. Added a custom column called Combined to concatenate the strings in Description and KeyWords column into one text string with a space in between;
2. convert the text string from last step into lower-case;
3. Added another custom column called Match with the following formula, which aims to find out if any of the key words from the Search list is contained in the text string from previous step, if so returns TRUE otherwise FALSE;

=List.Count(Splitter.SplitTextByAnyDelimiter(Search)([Combined]))>1

4. Filter the Match column with TRUE only;
5. Remove other columns except Description, KeyWords, and Hyperlinks;
6. Added an Index Column starts from 1 and move this column to the beginning of the table, then I have the following:

Now it's time to Close and Load the above table to the worksheet where your Search table sits.

The last step is to add a column to the end of the above table and type in the following formula:

=IF(ISBLANK(G4),LEFT(E4,6)&" Obsolete",HYPERLINK(G4,LEFT(E4,6)))

Then you should have the following:

You can choose to hide Column G Hyperlinks so it will only show the name of the ESP # with hyperlink embedded.

Each time you type in a new string of key words in the search cell, you need to go to Data tab to Refresh Data, or press Ctrl+Alt+F5 to get an updated search result.

Please note all steps performed above are using built-in functions of Power Query Editor, and you can google all the know-hows online but feel free to ask me any questions.

Here are the codes behind the scene for reference only:

For Tbl_Search

let
    Source = Excel.CurrentWorkbook(){[Name="Tbl_Search"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Search", type text}}),
    #"Lowercased Text" = Table.TransformColumns(#"Changed Type",{{"Search", Text.Lower, type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Lowercased Text", {{"Search", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Search"),
    #"Filled Down" = Table.FillDown(#"Split Column by Delimiter",{"Search"}),
    Column1 = #"Filled Down"[Search]
in
    Column1

For Tbl_ESP

let
    Source = Excel.CurrentWorkbook(){[Name="Tbl_ESP"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ESP #", type text}, {"Description", type text}, {"KeyWords", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Combined", each [Description]&" "&[KeyWords]),
    #"Lowercased Text" = Table.TransformColumns(#"Added Custom1",{{"Combined", Text.Lower, type text}}),
    #"Added Custom" = Table.AddColumn(#"Lowercased Text", "Match", each List.Count(Splitter.SplitTextByAnyDelimiter(Search)([Combined]))>1),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Match] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Description", "KeyWords", "Hyperlinks"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 1, 1),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "#"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"#", "Description", "KeyWords", "Hyperlinks"})
in
    #"Reordered Columns"