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:
<tbody>
</tbody>
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:
I | J | K | L | M | N | O |
USER | PST DATE | Debit | Credit | Count User? | Count | Don't Count |
1235 | 5/24/2016 | D | N | | 123 | |
1235 | 5/2/2016 | C | Y | 123 | ||
3215 | 5/2/2016 | C | N | 321 | ||
5555 | 5/2/2016 | C | N | 555 | ||
7095 | 5/2/2016 | D | N | 709 | ||
4565 | 5/2/2016 | C | Y | 456 | ||
1235 | 5/2/2016 | D | Y | 123 | ||
16165 | 5/2/2016 | C | N | 1616 |
<tbody>
</tbody>
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!