Good morning, I’ve been given one of the messiest sets of spreadsheets that I’ve literally ever seen – it’s astonishing! The engineers & sales people record their notes on the spreadsheets but is “scattergun” to put it politely haha
i.e. none of the columns or rows have any pre-determined fields of any kind.
BUT the sheets on this file contain some absolute gems of information and I’ve been tasked with trying to find a way for people to search through it & view the information that’s needed quicker & more effectively.
The main way that it’s currently being done is ctrl-find & search workbook (which takes into account the different sheets)
The advantages of this are: it takes you the cursor to “where” the desired search string is.
… that’s extremely handy because as the information is scattergunned around that particular spot, you can then look around it to find the info that you need. As there are no pre-determined fields, it could be 2 or 3 cells above it or below it, or to the left or to the right, deep joy!!!
BUT the main problem is that the part references used in our industry contain non-alphanumeric characters (dash, slash, space etc). And sometimes these are included when information is entered onto the spreadsheets, but sometimes they are left off.
The ways that I have tried to get around this are:
1. Inserting a helper column where I’ve concatenated all of the info into one cell, then trimmed those characters out of it. BUT when the number of characters gets too big for the function, it stops working.
2. Trying to get people to use wildcards and asterisks when searching using Ctrl-F. But complaints flooded in takes too long to type out the asterisks and question marks and then sift through the results. In fact, I got shouted at!
I’m wondering if there is a way that could do all (or some!) of this:
1. take the scattergunned file, insert a helper row (tick – even I can do that!)
2. include some code which says: special_find
3. as you hit find next, the cursor moves to the next occurrence of the desired search string in the same way as ctrl + f (and searches across the different worksheets or even across the different files that are open)
4. in an absolutely perfect world (& wouldn’t that be nice), you could enter a search string that contains non-alphanumeric characters & the function would still work. (But I may be dreaming for the impossible there!!)
So for example on file: “scattergun01”, sheet “01”
<tbody>
</tbody>
Any or all help would be very much appreciated!
With thanks
Neil
i.e. none of the columns or rows have any pre-determined fields of any kind.
BUT the sheets on this file contain some absolute gems of information and I’ve been tasked with trying to find a way for people to search through it & view the information that’s needed quicker & more effectively.
The main way that it’s currently being done is ctrl-find & search workbook (which takes into account the different sheets)
The advantages of this are: it takes you the cursor to “where” the desired search string is.
… that’s extremely handy because as the information is scattergunned around that particular spot, you can then look around it to find the info that you need. As there are no pre-determined fields, it could be 2 or 3 cells above it or below it, or to the left or to the right, deep joy!!!
BUT the main problem is that the part references used in our industry contain non-alphanumeric characters (dash, slash, space etc). And sometimes these are included when information is entered onto the spreadsheets, but sometimes they are left off.
The ways that I have tried to get around this are:
1. Inserting a helper column where I’ve concatenated all of the info into one cell, then trimmed those characters out of it. BUT when the number of characters gets too big for the function, it stops working.
2. Trying to get people to use wildcards and asterisks when searching using Ctrl-F. But complaints flooded in takes too long to type out the asterisks and question marks and then sift through the results. In fact, I got shouted at!
I’m wondering if there is a way that could do all (or some!) of this:
1. take the scattergunned file, insert a helper row (tick – even I can do that!)
2. include some code which says: special_find
3. as you hit find next, the cursor moves to the next occurrence of the desired search string in the same way as ctrl + f (and searches across the different worksheets or even across the different files that are open)
4. in an absolutely perfect world (& wouldn’t that be nice), you could enter a search string that contains non-alphanumeric characters & the function would still work. (But I may be dreaming for the impossible there!!)
So for example on file: “scattergun01”, sheet “01”
| Special_search | ABC_12/3 | |
| enquiry logged by PJH Feb '18 | ||
| ABC 123 x10 pcs for Dave at Allstars | ||
| we can source from BrightWorks | ||
| notes from April '18 | ||
| ABC12-3 no longer in stock at Brightworks but equivalent = XYZ789 | make sure they specify the plating | don't forget the min order values |
<tbody>
</tbody>
Any or all help would be very much appreciated!
With thanks
Neil
