<div dir="auto">If I export it to a CSV file I'll just put in on my Linux machine and use grep.<br><br><div data-smartmail="gmail_signature">---<br>Frank C. Wimberly<br>140 Calle Ojo Feliz, <br>Santa Fe, NM 87505<br><br>505 670-9918<br>Santa Fe, NM</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sat, Nov 26, 2022, 2:36 PM Gary Schiltz <<a href="mailto:gary@naturesvisualarts.com">gary@naturesvisualarts.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Instead of digging into the guts of Excel with Visual Basic, I think<br>
it would be about as easy (and maybe more fun) to import the<br>
spreadsheet into a database that you could query. If I were to take a<br>
first crack at it, I would first export the Excel file to a comma<br>
separated values file (CSV file). Then install MySQL and MySQL<br>
Workbench. It wouldn't surprise me if there was a Workbench option to<br>
import a CSV file as a new table in the current database. Then you<br>
could write plain SQL queries to look for the data you want, or go as<br>
far as writing some Java code using JDBC to do the queries.<br>
<br>
Or instead of MySQL and Workbench, you could try LibreOffice Base (the<br>
database component) to accomplish the same thing. This would be the<br>
open source equivalent of using Microsoft Access.<br>
<br>
All this from memory and speculation, of course (isn't that what us<br>
FRIAM'ers are best at?)..<br>
<br>
Gary<br>
<br>
On Sat, Nov 26, 2022 at 11:20 AM Frank Wimberly <<a href="mailto:wimberly3@gmail.com" target="_blank" rel="noreferrer">wimberly3@gmail.com</a>> wrote:<br>
><br>
> Thanks, Brent.<br>
><br>
> Stanford published a spreadsheet with about 200,000 researchers. Each row contains name, institution name, country, etc. I want to be able to search for the data about a given individual, the people associated with an institution, etc.<br>
><br>
> I used to program macros in VBA but I only did it for a few months and that was about 15 years ago.<br>
><br>
> Frank<br>
> ---<br>
> Frank C. Wimberly<br>
> 140 Calle Ojo Feliz,<br>
> Santa Fe, NM 87505<br>
><br>
> 505 670-9918<br>
> Santa Fe, NM<br>
><br>
> On Sat, Nov 26, 2022, 9:03 AM Brent Auble <<a href="mailto:brent@auble.net" target="_blank" rel="noreferrer">brent@auble.net</a>> wrote:<br>
>><br>
>> If you just want to find something, using the Find option from the menu will work and it should give you the option of searching within the current sheet or across all sheets in the workbook. If there are multiple results, it should pop up a docked window at the bottom of the spreadsheet to show everything it's found.<br>
>><br>
>> However, if you want to do it programmatically, I'm not sure if any of the built-in functions (formulas) will handle that well, mostly because of the complexity of handling multiple results. Excel functions try really hard to just return single values so they can populate just the cell they're in. There are some exceptions, where a formula will run and put its results in multiple other cells, but those are uncommon because they can indiscriminately overwrite existing cell values.<br>
>><br>
>> The is likely a capability in the Visual Basic language used for scripting Excel to handle searching, but it's not something I've used before (despite having done a good bit of Excel VBA in the past).<br>
>><br>
>> What are you trying to accomplish?<br>
>><br>
>> Thanks,<br>
>><br>
>> Brent<br>
>><br>
>><br>
>><br>
>> On Sat, Nov 26, 2022 at 10:42 AM, Frank Wimberly<br>
>> <<a href="mailto:wimberly3@gmail.com" target="_blank" rel="noreferrer">wimberly3@gmail.com</a>> wrote:<br>
>> I used to know Excel pretty well but that was decades ago. I want to find a given string, say "Wimberly" in a large spreadsheet. How do I do that simply? FIND function wants to provide the location within a larger string of a substring. I want something like the Unix "grep' command.<br>
>><br>
>> --<br>
>> Frank Wimberly<br>
>> 140 Calle Ojo Feliz<br>
>> Santa Fe, NM 87505<br>
>> 505 670-9918<br>
>><br>
>> Research: <a href="https://www.researchgate.net/profile/Frank_Wimberly2" rel="noreferrer noreferrer" target="_blank">https://www.researchgate.net/profile/Frank_Wimberly2</a><br>
>> -. --- - / ...- .- .-.. .. -.. / -- --- .-. ... . / -.-. --- -.. .<br>
>> FRIAM Applied Complexity Group listserv<br>
>> Fridays 9a-12p Friday St. Johns Cafe / Thursdays 9a-12p Zoom <a href="https://bit.ly/virtualfriam" rel="noreferrer noreferrer" target="_blank">https://bit.ly/virtualfriam</a><br>
>> to (un)subscribe <a href="http://redfish.com/mailman/listinfo/friam_redfish.com" rel="noreferrer noreferrer" target="_blank">http://redfish.com/mailman/listinfo/friam_redfish.com</a><br>
>> FRIAM-COMIC <a href="http://friam-comic.blogspot.com/" rel="noreferrer noreferrer" target="_blank">http://friam-comic.blogspot.com/</a><br>
>> archives: 5/2017 thru present <a href="https://redfish.com/pipermail/friam_redfish.com/" rel="noreferrer noreferrer" target="_blank">https://redfish.com/pipermail/friam_redfish.com/</a><br>
>> 1/2003 thru 6/2021 <a href="http://friam.383.s1.nabble.com/" rel="noreferrer noreferrer" target="_blank">http://friam.383.s1.nabble.com/</a><br>
>><br>
>> -. --- - / ...- .- .-.. .. -.. / -- --- .-. ... . / -.-. --- -.. .<br>
>> FRIAM Applied Complexity Group listserv<br>
>> Fridays 9a-12p Friday St. Johns Cafe / Thursdays 9a-12p Zoom <a href="https://bit.ly/virtualfriam" rel="noreferrer noreferrer" target="_blank">https://bit.ly/virtualfriam</a><br>
>> to (un)subscribe <a href="http://redfish.com/mailman/listinfo/friam_redfish.com" rel="noreferrer noreferrer" target="_blank">http://redfish.com/mailman/listinfo/friam_redfish.com</a><br>
>> FRIAM-COMIC <a href="http://friam-comic.blogspot.com/" rel="noreferrer noreferrer" target="_blank">http://friam-comic.blogspot.com/</a><br>
>> archives: 5/2017 thru present <a href="https://redfish.com/pipermail/friam_redfish.com/" rel="noreferrer noreferrer" target="_blank">https://redfish.com/pipermail/friam_redfish.com/</a><br>
>> 1/2003 thru 6/2021 <a href="http://friam.383.s1.nabble.com/" rel="noreferrer noreferrer" target="_blank">http://friam.383.s1.nabble.com/</a><br>
><br>
> -. --- - / ...- .- .-.. .. -.. / -- --- .-. ... . / -.-. --- -.. .<br>
> FRIAM Applied Complexity Group listserv<br>
> Fridays 9a-12p Friday St. Johns Cafe / Thursdays 9a-12p Zoom <a href="https://bit.ly/virtualfriam" rel="noreferrer noreferrer" target="_blank">https://bit.ly/virtualfriam</a><br>
> to (un)subscribe <a href="http://redfish.com/mailman/listinfo/friam_redfish.com" rel="noreferrer noreferrer" target="_blank">http://redfish.com/mailman/listinfo/friam_redfish.com</a><br>
> FRIAM-COMIC <a href="http://friam-comic.blogspot.com/" rel="noreferrer noreferrer" target="_blank">http://friam-comic.blogspot.com/</a><br>
> archives: 5/2017 thru present <a href="https://redfish.com/pipermail/friam_redfish.com/" rel="noreferrer noreferrer" target="_blank">https://redfish.com/pipermail/friam_redfish.com/</a><br>
> 1/2003 thru 6/2021 <a href="http://friam.383.s1.nabble.com/" rel="noreferrer noreferrer" target="_blank">http://friam.383.s1.nabble.com/</a><br>
<br>
-. --- - / ...- .- .-.. .. -.. / -- --- .-. ... . / -.-. --- -.. .<br>
FRIAM Applied Complexity Group listserv<br>
Fridays 9a-12p Friday St. Johns Cafe / Thursdays 9a-12p Zoom <a href="https://bit.ly/virtualfriam" rel="noreferrer noreferrer" target="_blank">https://bit.ly/virtualfriam</a><br>
to (un)subscribe <a href="http://redfish.com/mailman/listinfo/friam_redfish.com" rel="noreferrer noreferrer" target="_blank">http://redfish.com/mailman/listinfo/friam_redfish.com</a><br>
FRIAM-COMIC <a href="http://friam-comic.blogspot.com/" rel="noreferrer noreferrer" target="_blank">http://friam-comic.blogspot.com/</a><br>
archives: 5/2017 thru present <a href="https://redfish.com/pipermail/friam_redfish.com/" rel="noreferrer noreferrer" target="_blank">https://redfish.com/pipermail/friam_redfish.com/</a><br>
1/2003 thru 6/2021 <a href="http://friam.383.s1.nabble.com/" rel="noreferrer noreferrer" target="_blank">http://friam.383.s1.nabble.com/</a><br>
</blockquote></div>