Filtered Lookup List

[pbutcheck]

Now that people have learned I know Excel just SLIGHTLY better than the average joe, I have suddenly found myself the recipient of every Excel project in the business. So here's the latest:

Marketing/Mailing campaign involving 10s of thousands of people. Our team wants to keep track of everyone who responds to this one-time project (the project will span several days, but it isn't an on-going project).

Every time someone responds to our campaign (in writing), we need to call these individuals back. Therefore, we'll need to track 1) who was contacted, 2) who responded, 3) who called back the individuals and what their notes read.

I found a Contact Management template for this project and implemented it. It is nearly perfect. It can list all of the contactees, their address, etc. When they respond, we can fill out YES/NO fields and document their phone #s and Email address, etc.

It even has a Call Log sheet that will lookup the name and retrieve the phone/email information - and store a log of who called, when, and what their notes are. I'm 99% there.

My questions are regarding the fine-tuning you experts may be able to provide.

1) when looking up the source information, it will give a list of all 10k+ people. But we really only need to see the people who've responded to YES on the PURCHASED column. Is there a way to filter the lookup to only pull people IF column I=YES?

If that is not possible, do you have another idea/solution that would help?

My areas of concerns are:

1. several people will be working on this; each with very low levels of excel experience. Trying to keep it user-friendly
2. This is a rolling project for several weeks/months. There won't be a 'final' date in which all the data is received and THEN we can do something to move forward. All data will be worked on and managed during the entire project; a rolling collection and marketing campaign.

Master List worksheet:
[TABLE="width: 1551"]
<tbody>[TR]
[TD="align: left"]

Name

<tbody>
</tbody>

[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zipcode[/TD]
[TD]County[/TD]
[TD="align: left"]

Campaign Date

<tbody>
</tbody>

[/TD]
[TD]Purchased[/TD]
[TD="align: left"]

If yes, When?

<tbody>
</tbody>

[/TD]
[TD]Option 1[/TD]
[TD]Option 2[/TD]
[TD]Phone 1[/TD]
[TD="align: left"]

Phone 2

<tbody>
</tbody>

[/TD]
[TD]Email[/TD]
[TD]Date Rcvd[/TD]
[TD]No Contact[/TD]
[/TR]
[TR]
[TD]__TEST DATA 1[/TD]
[TD]1234 Somewhere Street[/TD]
[TD]Glenolden[/TD]
[TD]PA[/TD]
[TD]19036[/TD]
[TD]Delaware[/TD]
[TD="align: right"]1/1/2015[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]215.555.1212[/TD]
[TD]610.555.1212[/TD]
[TD]test@email.com[/TD]
[TD]8/25/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]__TEST DATA 3[/TD]
[TD]1236 Somewhere Street[/TD]
[TD]Perkasie[/TD]
[TD]PA[/TD]
[TD]18944[/TD]
[TD]Bucks[/TD]
[TD="align: right"]8/26/2015[/TD]
[TD]Yes[/TD]
[TD]4/15/2015[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]215.555.1111[/TD]
[TD][/TD]
[TD]testing2@email.com[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]__TEST DATA 8[/TD]
[TD]1241 Somewhere Street[/TD]
[TD]Jersey Shore[/TD]
[TD]PA[/TD]
[TD]17740[/TD]
[TD]Lycoming[/TD]
[TD="align: right"]8/19/2015[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]610.555.3333[/TD]
[TD][/TD]
[TD]testing4@email.com[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]__TEST DATA 14[/TD]
[TD]1247 Somewhere Street[/TD]
[TD]Dawson[/TD]
[TD]PA[/TD]
[TD]15428[/TD]
[TD]Fayette[/TD]
[TD="align: right"]8/21/2015[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]__TEST DATA 40[/TD]
[TD]1273 Somewhere Street[/TD]
[TD]Somerset[/TD]
[TD]PA[/TD]
[TD]15501[/TD]
[TD]Somerset[/TD]
[TD="align: right"]8/21/2015[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Call Log Sheet
[TABLE="width: 1114"]
<tbody>[TR]
[TD="align: left"]

Call #

<tbody>
</tbody>

[/TD]
[TD]Client[/TD]
[TD]Phone[/TD]
[TD]Email[/TD]
[TD]Caller[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD="align: left"]

Notes

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]__TEST DATA 3
(Lookup List for Master list; which will be more than 10k)[/TD]
[TD]215.555.1111
VLookup field pulls phone #[/TD]
[TD]testing2@email.com
VLookup field pulls email[/TD]
[TD]Ben
List lookup of callers[/TD]
[TD]08/27/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]__TEST DATA 1[/TD]
[TD]215.555.1212[/TD]
[TD]test@email.com[/TD]
[TD]Adrienne[/TD]
[TD]08/25/2015[/TD]
[TD][/TD]
[TD]asd ezsadf[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]__TEST DATA 3[/TD]
[TD]215.555.1111[/TD]
[TD]testing2@email.com[/TD]
[TD]Jim[/TD]
[TD]08/15/2015[/TD]
[TD][/TD]
[TD]adsfasd[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]__TEST DATA 4[/TD]
[TD]212.555.3333[/TD]
[TD]testing3@email.com[/TD]
[TD]Adrienne[/TD]
[TD]08/26/2015[/TD]
[TD][/TD]
[TD]called[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]__TEST DATA 4[/TD]
[TD]212.555.3333[/TD]
[TD]testing3@email.com[/TD]
[TD]Ben[/TD]
[TD]08/27/2015[/TD]
[TD][/TD]
[TD]asdfasdg[/TD]
[/TR]
</tbody>[/TABLE]


My area of focus is the lookup list for the Call Log sheet, which is currently pulling all 10k+ names - but we only need the names of those who have responded and who have replied "Yes" to Purchase question

 

[oseitutuSamuel]

You can use advanced filtering (DATA > Sort & Filtering >Advanced. A bit difficult to explain. Lets say you want to filter by a column called "Responded" which have values "YES" and "NO" in the column. Lets further say that your data starts at row 5 and the "Responded" column is at cell B5. In cell B2, type "Responded" (same as the column you want to filter by). Then in cell B3 type "YES" (or "NO" depending on what you want to filter) Then use the Advanced filter. Excel will ask for the range of data you want to filter and also the filtering criteria range. In this example the filtering criteria range is B2:B3

 

[pbutcheck]

You can use advanced filtering (DATA > Sort & Filtering >Advanced.

Thanks for offering help; but I may be missing something elemental here. Confession; rarely used Advanced Filtering... so I may not be getting it.

This appears to do the same thing that the standard filter does; it can truncate the list to only show those with YES in the column. However, the Call Log worksheet lookup list still shows all 10k+ names. Am I using this incorrectly?

If I may, I'll simplify it further for our communications

Worksheet one has this:
A B
[TABLE="width: 201"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]__TEST DATA 1[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]__TEST DATA 2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]__TEST DATA 3[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]__TEST DATA 4[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]__TEST DATA 5[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2 has a lookup list that pulls from Column A of sheet1. However, I don't want to see TEST DATA 2, 4 or 5 because they did not respond (yet), or have responded "no". I only want the lookup-list to show the YES values.

How would I use the Advanced Filtering to affect sheet2's lookup list?

 

[pbutcheck]

Update: I have successfully created an Advanced Filter for the spreadsheet, and that is not what I need to do.

To clarify, the Call log is critical for tracking who called who, when, etc. There are a pool of people who need to call SOMEONE on that list who responded. And they may call this person multiple times before reaching them. Therefore, the lookup list would ultimately pull only specific criteria information. However, it currently pulls everyone on the masterlist. Scrolling through 10k of names, when there are only 300 responses is the current problem.

I hope I was better able to explain what I'm trying to do. It appears that the 'call log' is the source of my problems, not filtering the primary data. Thank you again - I hope this makes better sense.

 

[MARZIOTULLIO]

Hi maybe ?

[TABLE="width: 168"]
<colgroup><col width="56" span="3" style="width:42pt"> </colgroup><tbody>[TR]
[TD="width: 56"]name[/TD]
[TD="width: 56"]phone[/TD]
[TD="width: 56"]y/N[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD] YES[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3 [/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]4[/TD]
[TD]YES[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 280"]
<tbody>[TR]
[TD="colspan: 5"]=IF(C2=$C$2,VLOOKUP(A2,$A$2:$C$4,2,FALSE),"No")[/TD]
[/TR]
</tbody>[/TABLE]

 

[pbutcheck]

Hi. That is a great way to filter the master list. But it doesn't help auto-reference a call log.

The call log is meant to find the names of our marketed audience, and when selected pull their phone/email. Then someone should make an entry that a call/email was made today, and leave a note. This may happen 3 or 4 times per person. We can't just filter the master list (alone).

Unfortunately all of my filtering knowledge doesn't impact the lookup reference.

If I must start over - what would you recommend?
Criteria:

• One master list that is on a rolling collection; with information, phone #, and email collected over the next several weeks
• The ability to select from the master list anyone who responded AND who didn't satisfy our information collection (not pulling people we already marked as complete)
• The ability to keep a log of who was called, and who did the calling, as well as notes on the calls. Knowing that there will be multiple calls made for each individual on the master list (therefore, just adding one additional 'column' on the master list will not suffice

 

[oseitutuSamuel]

Lets try this approach:

Make a table from your Master data. Lets call it Table1. To create the table do this: Select a cell within your Master data. Click INSERT>Table. Make sure that the whole of your Master data is selected, including the column headers. Then create about 4 empty rows above Table1. Above Table1 (in an empty cell), type "Purchased" (or the column you want to filter by). In the next cell below the "Purchased" cell, type "Yes" (your filtering criteria). So lets say you have "Purchased" in cell B2 and "Yes" in B3. Make sure there is at least one empty row between Table1 and B3. Click the Developer tab in the Ribbon. If you don't have the Developer tab do this: Click File > Options > Customize Ribbon. On the dialog box that appears, select Main Tabs from the dropdown (far right). Put a check (tick) the Developer box and close the dialog box. (If you don't see "Developer" among the list it means your version of Excel does not have it. Hard Luck!). Now click the Developer tab. In the Controls group, click Insert. From the icons below "Form Controls", click "Button(Form Control)" icon. Drag the mouse to create a button on the sheet. When you release the mouse you will get the "Assign Macro" dialog box. Click "New". You will see something like this:

Sub Button1_Click()
Put code here
End sub

Please note that you won't see "Put code here". Now in place of "Put code here" insert the following code:
Range("Table1[#All]").Select
Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("B2:B3"), CopyToRange:=Range("XXXX:YYYY"), Unique:=False.

Remember your table is called Table1. B2 and B3 are where you have typed "Purchased" and "Yes". The XXXX:YYYY is the range where the filtered data will display. So if you have 10 columns it will be Range("D1:D10"). Make sure that initially there is no data in this range. Now whenever you modify the Master data (say changing a NO to a YES), you click the button, and the filtered data will refresh and you see only rows with "Yes". This is a general approach. You may have to tweak it a bit to suit your situation. If I were you I will create a dummy Master data with few rows and play with it. If this is not what you want, sorry. But at least you have learned something

Hope it helps

 

[pbutcheck]

Wow - this is awesome. There is SO much to learn! I had no idea, so this opened a whole new world for me. Thank you.

I feel I am teasingly close. I started on a fresh screen. I inserted a table (as advised). My table goes from A5:H305. Of these, 4 records have 'YES' in the Purchased? column. Following your instructions, I did as instructed. But I think the CopyToRange may not be correct.

Sub PurchasedYes()
Range("Table2[#All]").Select
Range("Table2[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("J5:J900"), Unique:=False
End Sub

I advised it could fill in column J - and randomly set it to 900 rows. Then I tried J5:J9, etc. When I try to run the macro, it gives me this error:

Run-time error '1004':
The exact range has a missing or illegal field name.

So I'm sure it must be the way I formatted it.

And in that light, is there a way to direct the results to a different sheet? I honestly tried to google research this myself, but the information appears to be way over my head.

 

[oseitutuSamuel]

Yea, I agree...It can sometimes be frustrating if you are attempting such things for the first time!. First make sure that the target cells have no values. All the cells must be empty. If you want to direct the output to another sheet, just precede the cells with the sheet name. For example if you want to direct to range J5:J90 in a sheet called "AAA" , I believe you can do: 'AAA'!J5:J90. My advice is that try and use macros as much as possible. They give you an idea how things are done. Just run a macro and copy some cells from one sheet to another and see how the macro handles it. The macro will give you a lot of garbage but it's still useful. Let me know how you go.

 

[oseitutuSamuel]

I have posted further solution with sample data and a step-by-step instruction. When I checked on the forum, it does not appear to have gone through. If you don't mind just email me and I will send it directly to you. On the other hand if you see it on the forum then that's Ok. My email address is: oseitutu@iinet.net.au (Post was sent 2:30pm Tuesday, West Australian time). No need to email me if this post comes through to the Forum.