Can you use the "find" function to build a new list?

[Mspalding]

I have not learned excel, so I am working on this with frustration.
The following is my little project,
I downloaded my entire contact list (over 7000 contacts) from my iPhone into an excel spreadsheet.
Next, we must pull every contact with the word “agent” to make a new list. The new list will only have the agent's contacts.
I will need to sort and clean out the data from the new list to get a good working list to upload into a CRM platform for my work.
Can someone help me with this?

 

[ETAF]

depending on the layout = is the word Agent in a column ? with other words ?
what version of excel do you have - as FILTER() may work for this if latest version 365

a sample of you data would help here
A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

 

[Mspalding]

yes, got the latest version 365. Ok I hope I can do that, I have no skills in this application however, I will give it my best

 

[ETAF]

just a sample is all we need - maybe take the 1st 10 of the contacts you have and copy to a new spreadsheet and clear the name and phone number columns - or anything else sensitive
i have a play around later

 

[ETAF]

you can use filter
I have put in sheet2
=FILTER(Sheet1!A1:C20,Sheet1!C1:C20="agent","No results")
where
A1 to C20 is all the contact list - in sheet1
C1:c20 - is the column with Agent in Sheet1

maybe this link which explains may help

FILTER Function

The Excel FILTER function is used to extract matching values from data based on one or more conditions. The output from FILTER is dynamic. If source data or criteria change, FILTER will return a new set of results. This makes FILTER a flexible way to isolate and inspect data without altering the...

exceljet.net

Mod Edit: File removed due to having personal data

 

[Mspalding]

depending on the layout = is the word Agent in a column ? with other words ?
what version of excel do you have - as FILTER() may work for this if latest version 365

a sample of you data would help here
A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

 

[ETAF]

I have added a column with the formula
=ISNUMBER(FIND("agent",C2,1))

so it searches the column C details where it contains the word AGENT and then returns a TRUE or FALSE
We can change to search for A
we can also change so rather than TRUE / FALSE it outputs - Agent or blank

then on sheet 2 - I have extracted all the details where the new column = TRUE

so you have a list of AGENT ONLY

Are these made up names and address or real - as i mentioned

with all sensitive data removed

 

[Michael Gramm]

Might want to use search instead of find since find is case sensitive.

 

[Mspalding]

you can use filter
I have put in sheet2
=FILTER(Sheet1!A1:C20,Sheet1!C1:C20="agent","No results")
where
A1 to C20 is all the contact list - in sheet1
C1:c20 - is the column with Agent in Sheet1

maybe this link which explains may help

FILTER Function

The Excel FILTER function is used to extract matching values from data based on one or more conditions. The output from FILTER is dynamic. If source data or criteria change, FILTER will return a new set of results. This makes FILTER a flexible way to isolate and inspect data without altering the...

exceljet.net

see sample here

I just got to my laptop, Thank you I will try it out!

 

[ETAF]

As Mentioned

Might want to use search instead of find since find is case sensitive.

I have updated to use Search

 

[Mspalding]

I have added a column with the formula
=ISNUMBER(FIND("agent",C2,1))

so it searches the column C details where it contains the word AGENT and then returns a TRUE or FALSE
We can change to search for A
we can also change so rather than TRUE / FALSE it outputs - Agent or blank

then on sheet 2 - I have extracted all the details where the new column = TRUE

so you have a list of AGENT ONLY

Are these made up names and address or real - as i mentioned

they real names and ph #'s I was sure how to blank out the content with deleting the sheet

 

[ETAF]

I have setup the data with no real information
here

I have asked a moderator to delete all the OLD spreadsheets - as this is a public forum

 

[Mspalding]

All of this is still not sinking in my brain as to how I can extract all the "agent" contact data out of the 7200 contacts in the excel sheet. Do I open the worksheet and use a formula or use the search key?

 

[Mspalding]

I have setup the data with no real information
here

I have asked a moderator to delete all the OLD spreadsheets - as this is a public forum

Thank you for doing that

 

[Mspalding]

do I take all the data from the bulk list of 7200 contacts and send the data to the sheet you set up to get the desired results?

 

[p45cal]

Instead of adding a new column to the source data, you can incorporate that into the Filter function; so in any cell:
=FILTER(A1:L34,ISNUMBER(SEARCH("agent",A1:A34)))
This can be made to look a bit neater by replacing zeroes in the result with "":
=FILTER(IF(A1:L34="","",A1:L34),ISNUMBER(SEARCH("agent",A1:A34)))
ps. I note that when column A contains agent column C also always contains agent, but the reverse cannot be said, so I filtered according to column A.