Author Topic: Excel search help  (Read 1250 times)

Excel search help
« on: 25 February, 2022, 04:18:51 pm »
I want to find all cells that contain two words.

Not adjacent, but placed in any order in a string.

The workbook is large (over 100 sheets with hundreds of rows of data on each).

Is this possible in excel?
<i>Marmite slave</i>

jiberjaber

  • ... Fancy Pants \o/ ...
  • ACME S&M^2
Re: Excel search help
« Reply #1 on: 26 February, 2022, 06:13:57 pm »
Sounds like you would need a macro in order to do that, other alternative though more manual would using the exiting search & replace with the scope of the search set to "Workbook".

Regards,

Joergen

Manotea

  • Where there is doubt...
Re: Excel search help
« Reply #2 on: 26 February, 2022, 06:34:11 pm »
Let me Google this for you..

I suspect the answer to your question is probably some variant of ...https://stackoverflow.com/questions/19504858/find-all-matches-in-workbook-using-excel-vba

Just need to tweak the match criteria... 🤠

ian

  • why would any decent person have such thoughts?
Re: Excel search help
« Reply #3 on: 26 February, 2022, 07:49:55 pm »
Why not just use two SEARCH functions and an AND?
Authoritarian Thought Leader, the Pol Pot of Powerpoint, the Stalin of Spreadsheets, the Putin of pandas

ElyDave

  • Royal and Ancient Polar Bear Society member 263583
Re: Excel search help
« Reply #4 on: 02 March, 2022, 06:27:00 am »
Conditional formatting to highlight the cells?
“Procrastination is the thief of time, collar him.” –Charles Dickens

Re: Excel search help
« Reply #5 on: 02 March, 2022, 08:49:32 am »
Conditional formatting to highlight the cells?

There are over 100 sheets in this ODS workbook.

<deity> onna bike, I really wish they'd put this data into something sensible, like YAML or JSON. Then I could just grep it.
<i>Marmite slave</i>

Jaded

  • The Codfather
  • Formerly known as Jaded
Re: Excel search help
« Reply #6 on: 02 March, 2022, 09:38:21 am »
Take the pain of exporting the data, then do it a sensible way?
If you don't like your democracy, vote against it.

Re: Excel search help
« Reply #7 on: 02 March, 2022, 05:44:40 pm »

Re: Excel search help
« Reply #8 on: 02 March, 2022, 08:17:49 pm »
Take the pain of exporting the data, then do it a sensible way?
XKCD has the answer.


https://xkcd.com/2180/

ian

  • why would any decent person have such thoughts?
Re: Excel search help
« Reply #9 on: 02 March, 2022, 08:45:59 pm »
The old joke, why spend four minutes doing something in Excel when you can do it in just four hours with R.
Authoritarian Thought Leader, the Pol Pot of Powerpoint, the Stalin of Spreadsheets, the Putin of pandas

ElyDave

  • Royal and Ancient Polar Bear Society member 263583
Re: Excel search help
« Reply #10 on: 02 March, 2022, 09:23:42 pm »
Conditional formatting to highlight the cells?

There are over 100 sheets in this ODS workbook.

<deity> onna bike, I really wish they'd put this data into something sensible, like YAML or JSON. Then I could just grep it.

Copy and paste formats
“Procrastination is the thief of time, collar him.” –Charles Dickens

Jaded

  • The Codfather
  • Formerly known as Jaded
Re: Excel search help
« Reply #11 on: 03 March, 2022, 01:34:50 am »
Take the pain of exporting the data, then do it a sensible way?
XKCD has the answer.


https://xkcd.com/2180/

 ;D
If you don't like your democracy, vote against it.

HTFB

  • The Monkey and the Plywood Violin
Re: Excel search help
« Reply #12 on: 09 March, 2022, 02:04:46 pm »
Which edition of Excel? In sufficiently recent Excel (2021 or 365) it can be done in a single formula, or at least a single formula per page.

Re: Excel search help
« Reply #13 on: 09 March, 2022, 02:18:03 pm »
Which edition of Excel? In sufficiently recent Excel (2021 or 365) it can be done in a single formula, or at least a single formula per page.

365

I really need to be able to search the entire workbook. There are over one hundred sheets.
<i>Marmite slave</i>

Re: Excel search help
« Reply #14 on: 09 March, 2022, 03:06:15 pm »
Excel is fine as stand alone, when the data doesn’t need to interface with anything else, and it’s treated as end user computing. You built you support it, don’t expect IT to try and get to grips with it and bail you out when so and so leaves.

Re: Excel search help
« Reply #15 on: 09 March, 2022, 03:18:56 pm »
The find dialog has the option to search entire workbook. I think the search phrase can be a regular expression as well.  That is likely your best option if VBA is beyond you.

HTFB

  • The Monkey and the Plywood Violin
Re: Excel search help
« Reply #16 on: 10 March, 2022, 09:46:12 am »
Which edition of Excel? In sufficiently recent Excel (2021 or 365) it can be done in a single formula, or at least a single formula per page.

365

I really need to be able to search the entire workbook. There are over one hundred sheets.
Enumerating worksheets means it's VBA time. I'll have a go at a cut-and-paste macro at lunch [edit: but not today.]

Manotea

  • Where there is doubt...
Re: Excel search help
« Reply #17 on: 14 March, 2022, 07:16:31 pm »
Hmm... how did you get on? could be done using powerquery to consolidate the data into a single table, then merging all columns then applying a filter, though the last might be easier said than done..
Might have had a go but I didn't because the solution really depends on what you want to do with the cells once you've found them ..🤠

Re: Excel search help
« Reply #18 on: 22 March, 2022, 11:10:04 am »
I moved on to other things.

Now very glad I didn't spend a lot of time on this because it turns out that the data I needed doesn't have anything in it that identifies it. There is a name, but nothing in a description or any flag.

So it is impossible to search the spreadsheet and extract a list. Relies totally on having teh knowledge. ::-)
<i>Marmite slave</i>

Manotea

  • Where there is doubt...
Re: Excel search help
« Reply #19 on: 22 March, 2022, 05:55:57 pm »
Tell me about it....

Currently trying to cross reference a bunch of tables which look as though they have direct relationships but don't because the key fields were set by different people to different standards. I've had a go at tidying the data but basically will have to throw the whole thing back at the users.

It's the only way to be sure...

Re: Excel search help
« Reply #20 on: 28 March, 2022, 03:22:01 pm »
Oh this is nothing like a jumble of key names or anything. The information is text. No identifier. In a spreadsheet with tens of thousands of entries.
<i>Marmite slave</i>