31516

Any way to script excel to run a fancy search for multiple queries at once?

Question:

This should be a simple task but I'm new to excel and not sure how to run a fancy search.

I'm just trying to view the results of this search, I don't need it to make changes to the cells on the bases of this search.

I'm using control + F to open the search box.

It only allows me to type in one single string, such as "Johnson". Problem is, it finds many rows that contain the name Johnson.

So I'd like to further add a number, such as 1.3338, which it can look for in any cell that's on the same ROW where it found Johnson.

It would then return, for example, Row number 81, which has a cell that contains Johnson and another cell that contains 1.3338.

Is there a way to do this? Perhaps by writing some kind of script for Excel?Thanks!

Answer1:

No need for a script. Assuming the names are in A and the values are in B, you can set-up a search area in another part of the sheet like so:

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/1DnTO.png" data-original="https://i.stack.imgur.com/1DnTO.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

Formulas:<br />F3: =IFERROR(MATCH(F1,IF(B1:B30=F2,A1:A30),0),"No match.")<br />F4: =HYPERLINK("#A"&F3,"Go to row.")

The formula in F3 is an array formula, so you should input it using <kbd>Ctrl-Shift-Enter</kbd> instead of just <kbd>Enter</kbd>.

The cell in F4 will turn into a hyperlink and will allow you to go to the cell immediately.

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/p7Fn5.png" data-original="https://i.stack.imgur.com/p7Fn5.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

Other alternatives are VBA or filters. However, I believe this suffices if simple returning of row and a linker is all you need.

Let us know if this helps.

Recommend

  • Search Facebook by first name with Koala
  • Get highest value from a file using mSL and mIRC
  • How to enforce project-wide unique ids/error codes for easily finding the origin of the error in sou
  • Finding max value in CUDA
  • Python: sending key press events over SSH
  • Count New Lines in Text File
  • If I am creating a simple client server application in IntelliJ, how should this work?
  • What is the use of a session store?
  • Using MouseListener to select a range of cells in a grid
  • How to explicitly/implicitly implemented interface members in C++/CLI?
  • Center align outputs in ipython notebook
  • OOP Javascript - Is “get property” method necessary?
  • custom UITableViewCell with image for highlighting
  • Cancel a live stream “fast motion” catch-up in Flash
  • Is there a javascript serializer for JSON.Net?
  • Retrieving value from sql ExecuteScalar()
  • Sending data from AppleScript to FileMaker records
  • Join two tables and save into third-sql
  • Where to put my custom functions in Wordpress?
  • vba code to select only visible cells in specific column except heading
  • How to model a transition system with SPIN
  • ORA-29908: missing primary invocation for ancillary operator
  • Do I've to free mysql result after storing it?
  • Unanticipated behavior
  • Transpose CSV data with awk (pivot transformation)
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Buffer size for converting unsigned long to string
  • Angular 2 constructor injection vs direct access
  • Java static initializers and reflection
  • Android Google Maps API OnLocationChanged only called once
  • Can't mass-assign protected attributes when import data from csv file
  • Django query for large number of relationships
  • Sorting a 2D array using the second column C++
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • Binding checkboxes to object values in AngularJs
  • How can I use `wmic` in a Windows PE script?
  • UserPrincipal.Current returns apppool on IIS
  • Unable to use reactive element in my shiny app
  • Converting MP3 duration time
  • How to push additional view controllers onto NavigationController but keep the TabBar?