If I export it to a CSV file I'll just put in on my Linux machine and use grep.
--- Frank C. Wimberly 140 Calle Ojo Feliz, Santa Fe, NM 87505 505 670-9918 Santa Fe, NM On Sat, Nov 26, 2022, 2:36 PM Gary Schiltz <[email protected]> wrote: > Instead of digging into the guts of Excel with Visual Basic, I think > it would be about as easy (and maybe more fun) to import the > spreadsheet into a database that you could query. If I were to take a > first crack at it, I would first export the Excel file to a comma > separated values file (CSV file). Then install MySQL and MySQL > Workbench. It wouldn't surprise me if there was a Workbench option to > import a CSV file as a new table in the current database. Then you > could write plain SQL queries to look for the data you want, or go as > far as writing some Java code using JDBC to do the queries. > > Or instead of MySQL and Workbench, you could try LibreOffice Base (the > database component) to accomplish the same thing. This would be the > open source equivalent of using Microsoft Access. > > All this from memory and speculation, of course (isn't that what us > FRIAM'ers are best at?).. > > Gary > > On Sat, Nov 26, 2022 at 11:20 AM Frank Wimberly <[email protected]> > wrote: > > > > Thanks, Brent. > > > > 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. > > > > I used to program macros in VBA but I only did it for a few months and > that was about 15 years ago. > > > > Frank > > --- > > Frank C. Wimberly > > 140 Calle Ojo Feliz, > > Santa Fe, NM 87505 > > > > 505 670-9918 > > Santa Fe, NM > > > > On Sat, Nov 26, 2022, 9:03 AM Brent Auble <[email protected]> wrote: > >> > >> 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. > >> > >> 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. > >> > >> 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). > >> > >> What are you trying to accomplish? > >> > >> Thanks, > >> > >> Brent > >> > >> > >> > >> On Sat, Nov 26, 2022 at 10:42 AM, Frank Wimberly > >> <[email protected]> wrote: > >> 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. > >> > >> -- > >> Frank Wimberly > >> 140 Calle Ojo Feliz > >> Santa Fe, NM 87505 > >> 505 670-9918 > >> > >> Research: https://www.researchgate.net/profile/Frank_Wimberly2 > >> -. --- - / ...- .- .-.. .. -.. / -- --- .-. ... . / -.-. --- -.. . > >> FRIAM Applied Complexity Group listserv > >> Fridays 9a-12p Friday St. Johns Cafe / Thursdays 9a-12p Zoom > https://bit.ly/virtualfriam > >> to (un)subscribe http://redfish.com/mailman/listinfo/friam_redfish.com > >> FRIAM-COMIC http://friam-comic.blogspot.com/ > >> archives: 5/2017 thru present > https://redfish.com/pipermail/friam_redfish.com/ > >> 1/2003 thru 6/2021 http://friam.383.s1.nabble.com/ > >> > >> -. --- - / ...- .- .-.. .. -.. / -- --- .-. ... . / -.-. --- -.. . > >> FRIAM Applied Complexity Group listserv > >> Fridays 9a-12p Friday St. Johns Cafe / Thursdays 9a-12p Zoom > https://bit.ly/virtualfriam > >> to (un)subscribe http://redfish.com/mailman/listinfo/friam_redfish.com > >> FRIAM-COMIC http://friam-comic.blogspot.com/ > >> archives: 5/2017 thru present > https://redfish.com/pipermail/friam_redfish.com/ > >> 1/2003 thru 6/2021 http://friam.383.s1.nabble.com/ > > > > -. --- - / ...- .- .-.. .. -.. / -- --- .-. ... . / -.-. --- -.. . > > FRIAM Applied Complexity Group listserv > > Fridays 9a-12p Friday St. Johns Cafe / Thursdays 9a-12p Zoom > https://bit.ly/virtualfriam > > to (un)subscribe http://redfish.com/mailman/listinfo/friam_redfish.com > > FRIAM-COMIC http://friam-comic.blogspot.com/ > > archives: 5/2017 thru present > https://redfish.com/pipermail/friam_redfish.com/ > > 1/2003 thru 6/2021 http://friam.383.s1.nabble.com/ > > -. --- - / ...- .- .-.. .. -.. / -- --- .-. ... . / -.-. --- -.. . > FRIAM Applied Complexity Group listserv > Fridays 9a-12p Friday St. Johns Cafe / Thursdays 9a-12p Zoom > https://bit.ly/virtualfriam > to (un)subscribe http://redfish.com/mailman/listinfo/friam_redfish.com > FRIAM-COMIC http://friam-comic.blogspot.com/ > archives: 5/2017 thru present > https://redfish.com/pipermail/friam_redfish.com/ > 1/2003 thru 6/2021 http://friam.383.s1.nabble.com/ >
-. --- - / ...- .- .-.. .. -.. / -- --- .-. ... . / -.-. --- -.. . FRIAM Applied Complexity Group listserv Fridays 9a-12p Friday St. Johns Cafe / Thursdays 9a-12p Zoom https://bit.ly/virtualfriam to (un)subscribe http://redfish.com/mailman/listinfo/friam_redfish.com FRIAM-COMIC http://friam-comic.blogspot.com/ archives: 5/2017 thru present https://redfish.com/pipermail/friam_redfish.com/ 1/2003 thru 6/2021 http://friam.383.s1.nabble.com/
