Hi all
I am looking for assistance to create a search function that will search across multiple columns and return those results in a separate worksheet. In the attached sample I have 2 worksheets - one named 'Name_Changes' and the other 'Search_Results'.
The 'Name changes contains the data and 'Search results' should have the output of the search results.
Looking at the attached sample - Column A shows original name and column B shows the name change. The yellow highlighted names contain a history of name changes over time for this particular name. I am wanting to be able to enter a 'key' word that will be used to lookup and return history of name changes for that word. You can see cell E2 has the word we want to search ('smith'). The macro should search column A&B and return the history of the name changes to the word in E2 (smith). I have created an result of this search in the output section in the sample below. You can see how the history is kept together where the name 'Smith company' has had a few changes and ultimately ended with the name ' Smith group'. I want to return the whole history and include the dates contained in column C 'Effective date'. the output of the search results should be pasted into cell A1 in the 'Search_results' worksheet and include the headings of each column.
The blue highlighted names also provides name changes that relate to each other - if I want to search for the word 'Southern or stars or all or more' the output should be the combination of rows 5 & 8.
I do hope this makes sense.
I am looking for assistance to create a search function that will search across multiple columns and return those results in a separate worksheet. In the attached sample I have 2 worksheets - one named 'Name_Changes' and the other 'Search_Results'.
The 'Name changes contains the data and 'Search results' should have the output of the search results.
Looking at the attached sample - Column A shows original name and column B shows the name change. The yellow highlighted names contain a history of name changes over time for this particular name. I am wanting to be able to enter a 'key' word that will be used to lookup and return history of name changes for that word. You can see cell E2 has the word we want to search ('smith'). The macro should search column A&B and return the history of the name changes to the word in E2 (smith). I have created an result of this search in the output section in the sample below. You can see how the history is kept together where the name 'Smith company' has had a few changes and ultimately ended with the name ' Smith group'. I want to return the whole history and include the dates contained in column C 'Effective date'. the output of the search results should be pasted into cell A1 in the 'Search_results' worksheet and include the headings of each column.
The blue highlighted names also provides name changes that relate to each other - if I want to search for the word 'Southern or stars or all or more' the output should be the combination of rows 5 & 8.
I do hope this makes sense.
| Book1 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Current name | New name | Effective date | Search database | |||||
| 2 | Smith company | Smith partnership | 1/04/2003 | smith | |||||
| 3 | Smith partnership | Smith & Co. | 23/04/2009 | ||||||
| 4 | Hero forms | Any comp | 1/07/2012 | ||||||
| 5 | Southern Stars | All stars | 25/01/2015 | ||||||
| 6 | I'm tired | New energy | 17/04/2016 | ||||||
| 7 | Smith & Co. | Smith group | 11/07/2019 | ||||||
| 8 | All stars | No more stars | 25/12/2019 | Output | |||||
| 9 | |||||||||
| 10 | Smith company | Smith partnership | 1/04/2003 | ||||||
| 11 | Smith partnership | Smith & Co. | 23/04/2009 | ||||||
| 12 | Smith & Co. | Smith group | 11/07/2019 | ||||||
| 13 | |||||||||
| 14 | |||||||||
Name_Changes | |||||||||
