Hi all,
I have sent out a survey to a list of email address (in the thousands). I also have a list of who has responded. Now I am trying to figure out how to get a list of the people who have not yet responded. Any ideas? Is there a way to "delete" names on one list from another so I'm left with those who have not responded?
Thanks!
garbo7441
Favorite Answer
Edit: here is a macro to delete the 'respondents' from the master list, then sort the remaining 'yet to respond' invitees.
Copy the following macro to the clipboard:
Sub UpdateInvitees()
Dim i, LastRow, LastRowB
LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastRowB = Range("B" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
For j = 1 To LastRowB
If Cells(i, "A").Value = Cells(j, "B").Value Then
Cells(i, "A").ClearContents
End If
Next j
Next i
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
End Sub
Press ALT + F11
In the menus at the top of the VBE, select INSERT > MODULE
Paste the macro into the editing area to the right.
Close the VBE, returning to the worksheet.
Press ALT + F8
When the Macros window opens, highlight the macro and click 'Options...'
Enter a letter to be used as a keyboard shortcut and click 'OK'.
Close the Macros window.
To revise the list, press CTRL + your shortcut letter.
Save the workbook.
As more responses are received, and entered into column B, just run the macro again at any time to updated the Invitee list.
Scrawny
This is a possible solution to your problem. Insert a column next to the list that has all the email addresses and enter this formula in that column and copy down the list of your data (having made the appropriate changes to the formula to match your data:
=IF(COUNTIF($D$1:$D400,$A1),"in list","")
The $D$1:$D$400 is a field in the list of respondents that corresponds to a field in the total list.
The $A1 is the corresponding field in the $D$1:$D$400. So, in this example the email addresses are in column A of the main list and are in column D of the respondents list.
The formula actually says: If the contents of A1 is found in the range D1:D400, return "in list" otherwise enter nothing. Then it goes to A2 and searches the same range D1:D400 for a match and if found returns "in list" or if not found returns nothing.
Joy
If I have understand you, You can use countif function if the data are identical in both column If the largest column is A and the smallest is in Range D1:D100 then you can type the formula like this in column B: =COUNTIF(D1:D100,A2) I have assumed row 1 is header and that you are in cell B2 drag and drop till the end of the column A If the data is found in the range it should display a value greater than 0. Then you filter on the value greater then 0 and delete the items (use the del or delete button on the keyboard) If necessary you need to sort your data again Recommended create a backup of your file in case of