How to find and extract

%3CLINGO-SUB%20id%3D%22lingo-sub-2370502%22%20slang%3D%22en-US%22%3EHow%20to%20find%20and%20extract%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2370502%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20would%20I%20find%20a%20name%20in%20a%20database%20and%20copy%20the%20entire%20multiple%20rows%20that%20name%20appears%20in%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2370502%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2371544%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20find%20and%20extract%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2371544%22%20slang%3D%22en-US%22%3EWhat%20kind%20of%20database%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2371976%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20find%20and%20extract%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2371976%22%20slang%3D%22en-US%22%3EIt%20is%20a%20(csv)%20spreadsheet%2C%20that%20has%20list%20of%20names%2C%20amounts%20of%20DNA%2C%20and%20common%20ancestors.%20I%20need%20to%20extract%20specific%20names%20that%20are%20in%20multiple%20rows%20to%20place%20them%20in%20a%20new%20spreadsheet%2C%20to%20analyze%20the%20data.%20I%20am%20using%20that%20find%20function%20and%20it%20takes%20hours%20to%20extract%20one%20name%20to%20the%20new%20spreadsheet.%3C%2FLINGO-BODY%3E
New Contributor

How would I find a name in a database and copy the entire multiple rows that name appears in?

5 Replies
What kind of database?
It is a (csv) spreadsheet, that has list of names, amounts of DNA, and common ancestors. I need to extract specific names that are in multiple rows to place them in a new spreadsheet, to analyze the data. I am using that find function and it takes hours to extract one name to the new spreadsheet.

@migoblu 

 

Are you familiar with the FILTER function? From your description it would appear to be what you're looking for. It's a new function that requires the newest versions of Excel. Here's a video that explains it

https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...
Have a look at the "From Text/CSV" button on the Data tab of the ribbon. It might help you filter more quickly.

Hi @migoblu 

 

You import the csv file into excel:

Click Data > From file > From text/Csv

Yea_So_0-1621554232068.png

A dialog box pops up to browse for the csv file,

Yea_So_2-1621554472561.png

find it and select it then press Import

another dialog box pops up

Yea_So_3-1621554523851.png

Click Transform Data then this will pop up

Yea_So_4-1621554570927.png

click Close & Load then you will see the csv file got imported into excel it will look like this. click the x in Queries & Connections to close that navigation panel

Yea_So_5-1621554666046.png

In a new sheet, type the name you're looking for example 250190

then in a couple of cells below it enter a formula like this:

=FILTER(Book2[#All],ISNUMBER(SEARCH($A$1,Book2[[#All],[Part '#]],1)))

the search formula is basically saying search $A$1 (which is the name that you're looking for that you typed on cell A1, then selecting the entire column called Part # of the csv that was imported example: Search($A$1, Column range where the name is) then put that formula into an

ISNUMBER(Put the search formula in here)

then create the formula

FILTER(Select the whole range that was imported, then the other two fromulas in here)

so

formula #1 SEARCH($A$1,Book2[[#All],[Part '#]],1)

formula #2 ISNUMBER(Put formula #1 in here)

formula #3 FILTER(Select entire range of the imported csv, Formula #1&2 nested here)))

and the result will show:

Yea_So_6-1621556209994.png

 

Hope that helps.