How can I remove rows with unique values? As in only keeping rows with duplicate values?
- by user1456405
Here's the conundrum, I'm a complete and utter noob when it comes to programming. I understand the basics, but am still learning javascript.
I have a spreadsheet of surveys, in which I need to see how particular users have varied over time. As such, I need to disregard all rows with unique values in a particular column.
The data looks like this:
Response Date Response_ID Account_ID Q.1
10/20/2011 12:03:43 PM 23655956 1168161 8
10/20/2011 03:52:57 PM 23660161 1168152 0
10/21/2011 10:55:54 AM 23672903 1166121 7
10/23/2011 04:28:16 PM 23694471 1144756 9
10/25/2011 06:30:52 AM 23732674 1167449 7
10/25/2011 07:52:28 AM 23734597 1087618 5
I've found a way to do so in VBA, which sucks as I have to use excel, per below:
Sub Del_Unique()
Application.ScreenUpdating = False
Columns("B:B").Insert Shift:=xlToRight
Columns("A:A").Copy Destination:=Columns("B:B")
i = Application.CountIf(Range("A:A"), "<>") + 50
If i > 65536 Then i = 65536
Do
If Application.CountIf(Range("B:B"), Range("A" & i)) = 1 Then
Rows(i).Delete
End If
i = i - 1
Loop Until i = 0
Columns("B:B").Delete
Application.ScreenUpdating = True
End Sub
But that requires mucking about. I'd really like to do it in Google Spreadsheets with a script that won't have to be changed. Closest I can get is retrieving all duplicate user ids from the range, but can't associate that with the row. That code follows:
function findDuplicatesInSelection() {
var activeRange = SpreadsheetApp.getActiveRange();
var values = activeRange.getValues();
// values that appear at least once
var once = {};
// values that appear at least twice
var twice = {};
// values that appear at least twice, stored in a pretty fashion!
var final = [];
for (var i = 0; i < values.length; i++) {
var inner = values[i];
for (var j = 0; j < inner.length; j++) {
var cell = inner[j];
if (cell == "") continue;
if (once.hasOwnProperty(cell)) {
if (!twice.hasOwnProperty(cell)) {
final.push(cell);
}
twice[cell] = 1;
} else {
once[cell] = 1;
}
}
}
if (final.length == 0) {
Browser.msgBox("No duplicates found");
} else {
Browser.msgBox("Duplicates are: " + final);
}
}
Anyhow, sorry if this is the wrong place or format, but half of what I've found so far has been from stack, I thought it was a good place to start.
Thanks!
