how to find all occurrences of text and show at once

16.66.1 - what version of excel is that ?

A sample sheet would help here, and possibly will enable a quicker and more accurate solution for you.


The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

thanks, just for clarity can you include that info in the profile - see mine for instance

365 (Mac OSX) (16.82 - (24021116))

ok, have a look at these screen shots

There are NO explanations and NO expected results, as far as I can see.

So what text are you looking for? What results to you expect to see? Where do you expect to see them?

In that file, give an example of the text you are looking for.

If I understand YOU correctly...

then show me what result you expect a FORMULA to show you.

If I have not understood you correctly, then you need to explain more clearly!!

No reply, so a guess...

=LET(A,VSTACK(Sheet1:Sheet2!A4:H100),B,FILTER(A,BYROW(A,LAMBDA(x,SUM(--ISNUMBER(SEARCH("Jim",x)))))>0),VSTACK(Sheet1!A2:H2,IF(B="","",B)))

Fair enough so. In that case, I don't think that you can do that. One sheet at a time with advanced filter, maybe... but not across a workbook (I think).

But take a look at the file. It >>might<< offer a workaround for you.

LoL. I attached the wrong file, in any event. Idiot.

Looking at the formula in the file in Post 16 (the CORRECT one.... Dohhh!).


=LET(A,VSTACK(Sheet1:Sheet2!A4:H100),B,FILTER(A,BYROW(A,LAMBDA(x,SUM(--ISNUMBER(SEARCH(G1,x)))))>0),VSTACK(Sheet1!A2:H2,IF(B="","",B)))

Subformula A stacks sheets 1 &2 above each other. the blue bit is more complicated:

FILTER(A,BYROW(A,LAMBDA(x,SUM(--ISNUMBER(SEARCH(G1,x)))))>0)

row by row, it looks through stack arising from A for the search term in G1. If it finds it, it returns TRUE or FALSE. The -- turns them into 1 and 0. Sum adds them up, row by row. Filter then returns only those rows where the sum >0.

The final result (green) stacks the headers and the result of the filter, with any blank cells being returned as blanks.... arather than the Excel default, which would be to return them as irritating zeros.


You're welcome. Thanks for letting us know that you got an answer.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

Wow, that's all way over my head. Thanks so much for all the help/work.

I guess my last question is, where do I paste that formula so it runs on my entire workbook? And do I need to make changes so it applies to the dozens of tabs in the full workbook?

Enjoy the weekend.

Scott

Paste it wherever you like!! It needs minimal adjustment.

=LET(A,VSTACK(Sheet1:Sheet2!A4:H100),B,FILTER(A,BYROW(A,LAMBDA(x,SUM(--ISNUMBER(SEARCH(G1,x)))))>0),VSTACK(Sheet1!A2:H2,IF(B="","",B)))

1. Assume you have 25 sheets, and you want to search 20 of them. Ensure the 20 are beside each other in the tab list. The others can be to the left, the right or both... but not in the middle... of your 20.

2. Blue. Change this to the name of the first of the 20 sheets

3. Cyan. Change this to the name of the last of the 20 sheets.

4. Green. Ensure this is an adequate range (don't go mad, using 100,000 rows, if you only have 100...)

5. Red. Change this to reference the cell containing your search term.

6. Purple. Make sure this covers ONE set of header rows, from ONE of the sheets. It can be any of them.

Fingers crossed... it >>>should<<< now just work. So, a nice formula for a problem that had "nothing to do with formulas". Never underestimate the power fo formulae (especially with O365).


You're welcome. Thanks for letting us know that you got an answer.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.