GingerMermaid
New Member
- Joined
- Nov 2, 2015
- Messages
- 8
Hello Amazing Excel Gurus,
I have been struggling with this one for far too long and I still can't figure out what I'm doing wrong. I will try to be as clear as possible:
My spreadsheet has a list of user numbers in Column I. The length of data varies each month and there are duplicate user numbers listed.
Column M is filled with a "Y" or "N" in each row, indicating whether the user number should be counted or not.
Column N has a formula dragged down to populate it with the user number (minus the last digit) if the user should be counted: =IF(M2="Y",LEFT(I2,LEN(I2)-1),"")
This means that Column N appears to have many blanks, since not all users should be counted, but it also has some duplicates, since some users should be counted more than once.
Example:
[TABLE="width: 531"]
<tbody>[TR]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M
[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]USER
[/TD]
[TD]PST DATE [/TD]
[TD]Debit [/TD]
[TD]Credit[/TD]
[TD]Count User?
[/TD]
[TD]Count
[/TD]
[TD]Don't
Count
[/TD]
[/TR]
[TR]
[TD="align: right"]1235[/TD]
[TD="align: right"]5/24/2016
[/TD]
[TD] D [/TD]
[TD][/TD]
[TD]N
[/TD]
[TD]
[/TD]
[TD]123
[/TD]
[/TR]
[TR]
[TD="align: right"]1235[/TD]
[TD="align: right"]5/2/2016[/TD]
[TD][/TD]
[TD] C [/TD]
[TD]Y
[/TD]
[TD]123
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3215[/TD]
[TD="align: right"]5/2/2016[/TD]
[TD][/TD]
[TD] C [/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]321
[/TD]
[/TR]
[TR]
[TD="align: right"]5555[/TD]
[TD="align: right"]5/2/2016[/TD]
[TD][/TD]
[TD] C [/TD]
[TD]N[/TD]
[TD][/TD]
[TD]555[/TD]
[/TR]
[TR]
[TD="align: right"]7095[/TD]
[TD="align: right"]5/2/2016[/TD]
[TD] D [/TD]
[TD][/TD]
[TD]N[/TD]
[TD][/TD]
[TD]709[/TD]
[/TR]
[TR]
[TD="align: right"]4565[/TD]
[TD="align: right"]5/2/2016[/TD]
[TD][/TD]
[TD] C [/TD]
[TD]Y[/TD]
[TD]456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1235
[/TD]
[TD="align: right"]5/2/2016[/TD]
[TD] D [/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD]123
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]16165[/TD]
[TD="align: right"]5/2/2016[/TD]
[TD][/TD]
[TD] C [/TD]
[TD]N[/TD]
[TD][/TD]
[TD]1616[/TD]
[/TR]
</tbody>[/TABLE]
I am trying to find a way to select all the Counted users and copy the unique ones over to Column P. I have searched for answers and I came up with 2 methods that seemed to make sense to me, but the first is giving me a Runtime Error '1004' and says that it requires at least two rows of data and the second says the extract range has a missing or illegal field name:
TRIAL METHOD 1:
With ActiveSheet
.Range("N1", Range("N1").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("P1"), _
Unique:=True
End With
TRIAL METHOD 2:
With ActiveSheet
.Range("N1:N" & Range("N" & Rows.Count).End(xlUp).Row).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("P1"), _
Unique:=True
End With
Any assistance would be much appreciated and thank you in advance for even taking the time to consider my problem!
I have been struggling with this one for far too long and I still can't figure out what I'm doing wrong. I will try to be as clear as possible:
My spreadsheet has a list of user numbers in Column I. The length of data varies each month and there are duplicate user numbers listed.
Column M is filled with a "Y" or "N" in each row, indicating whether the user number should be counted or not.
Column N has a formula dragged down to populate it with the user number (minus the last digit) if the user should be counted: =IF(M2="Y",LEFT(I2,LEN(I2)-1),"")
This means that Column N appears to have many blanks, since not all users should be counted, but it also has some duplicates, since some users should be counted more than once.
Example:
[TABLE="width: 531"]
<tbody>[TR]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M
[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]USER
[/TD]
[TD]PST DATE [/TD]
[TD]Debit [/TD]
[TD]Credit[/TD]
[TD]Count User?
[/TD]
[TD]Count
[/TD]
[TD]Don't
Count
[/TD]
[/TR]
[TR]
[TD="align: right"]1235[/TD]
[TD="align: right"]5/24/2016
[/TD]
[TD] D [/TD]
[TD][/TD]
[TD]N
[/TD]
[TD]
[/TD]
[TD]123
[/TD]
[/TR]
[TR]
[TD="align: right"]1235[/TD]
[TD="align: right"]5/2/2016[/TD]
[TD][/TD]
[TD] C [/TD]
[TD]Y
[/TD]
[TD]123
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3215[/TD]
[TD="align: right"]5/2/2016[/TD]
[TD][/TD]
[TD] C [/TD]
[TD]N
[/TD]
[TD][/TD]
[TD]321
[/TD]
[/TR]
[TR]
[TD="align: right"]5555[/TD]
[TD="align: right"]5/2/2016[/TD]
[TD][/TD]
[TD] C [/TD]
[TD]N[/TD]
[TD][/TD]
[TD]555[/TD]
[/TR]
[TR]
[TD="align: right"]7095[/TD]
[TD="align: right"]5/2/2016[/TD]
[TD] D [/TD]
[TD][/TD]
[TD]N[/TD]
[TD][/TD]
[TD]709[/TD]
[/TR]
[TR]
[TD="align: right"]4565[/TD]
[TD="align: right"]5/2/2016[/TD]
[TD][/TD]
[TD] C [/TD]
[TD]Y[/TD]
[TD]456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1235
[/TD]
[TD="align: right"]5/2/2016[/TD]
[TD] D [/TD]
[TD][/TD]
[TD]Y
[/TD]
[TD]123
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]16165[/TD]
[TD="align: right"]5/2/2016[/TD]
[TD][/TD]
[TD] C [/TD]
[TD]N[/TD]
[TD][/TD]
[TD]1616[/TD]
[/TR]
</tbody>[/TABLE]
I am trying to find a way to select all the Counted users and copy the unique ones over to Column P. I have searched for answers and I came up with 2 methods that seemed to make sense to me, but the first is giving me a Runtime Error '1004' and says that it requires at least two rows of data and the second says the extract range has a missing or illegal field name:
TRIAL METHOD 1:
With ActiveSheet
.Range("N1", Range("N1").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("P1"), _
Unique:=True
End With
TRIAL METHOD 2:
With ActiveSheet
.Range("N1:N" & Range("N" & Rows.Count).End(xlUp).Row).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("P1"), _
Unique:=True
End With
Any assistance would be much appreciated and thank you in advance for even taking the time to consider my problem!
