Not sure even how to ask this one

D

dgerdes

New Member
Joined
Jan 17, 2012
Messages
1
I am a teacher. I am scheduling the faculty's classes for next semester.
I need to create a method to track names. As I add the name in the column of faculty names, I need to be able to track it either below or under another tab. So...
Roberts
Smith
Smith
Jones
Skinner
Hendrix
Smith
Jones
Thompson

I need this column of information to somehow reflect that I used smith for three classes already, Jones twice, and the rest once (so I do not over-schedule one faculty member and under-utilize another). Does that make sense? I am just not sure how to do it.
 
Sort by date Sort by votes
Highlight range in column A.
Insert-Pivot table

65234919.jpg
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
If you really need the VBA solution, try...

Code: 
Sub CreateUniqueList()
    Dim srcLR As Long
    Dim lrSrc As Long
    Dim lrDst As Long
    
    Const wsSrc As String = "Sheet1"
    Const wsDst As String = "Sheet2"
    
    Application.ScreenUpdating = False

    With Sheets(wsSrc)
        lrSrc = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A1:A" & lrSrc).AdvancedFilter Action:=xlFilterCopy, _
            CopyToRange:=Sheets(wsDst).Range("A1"), Unique:=True
    End With

    With Sheets(wsDst)
        lrDst = .Range("A" & .Rows.Count).End(xlUp).Row - 1
        .Range("B2").Resize(lrDst).FormulaR1C1 = "=COUNTIF(Sheet1!C[-1],RC[-1])"
        End With
    
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank u Jeffrey for the code.

But iam getting the run time error"1004"

and one more help
the programme should run automaticlly when i paste the names in the sheet 1

and when i delete or add names in sheet 1 than it should automatically run

please help
 
Upvote 0
You don't need a macro, you just need to learn to use a pivot table. A pivot table will do the same thing and all you have to do after pasting data is right click the table and click refresh.

There is no one formula that will give you all the names like you are asking for. Macro's are powerful but pivot tables are even more powerful in a situation like this.

What would be nice is if excel would make it so that you could make pivot like tables which would be automatic and call only certain data from a master list but this isn't available yet.

For pivot table

In A1 type Names, Enter your names in column A under the header.

Click column A(to highlight all of it) and then go to insert pivot table. Drag Names to row labels and values as shown before.

Then whenever you want to see the new results after changing the data just right click the pivot table and click refresh.

There is no one formula that will allow you to
 
Upvote 0
Try...

This is Worksheet code >> right click on sheet tab >> View Code >> Paste on right side of screeen.

http://www.rondebruin.nl/code.htm

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim srcLR As Long
    Dim lrSrc As Long
    Dim lrDst As Long

    Const wsSrc As String = "Sheet1"
    Const wsDst As String = "Sheet2"

    Set rng = Target.Parent.Range("A:A")

    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, rng) Is Nothing Then Exit Sub

    Application.ScreenUpdating = False

    With Sheets(wsSrc)
        Sheets(wsDst).Columns("A:B").ClearContents
        lrSrc = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A1:A" & lrSrc).AdvancedFilter Action:=xlFilterCopy, _
            CopyToRange:=Sheets(wsDst).Range("A1"), Unique:=True
    End With

    With Sheets(wsDst)
        lrDst = .Range("A" & .Rows.Count).End(xlUp).Row - 1
        .Range("B2").Resize(lrDst).FormulaR1C1 = "=COUNTIF(Sheet1!C[-1],RC[-1])"
    End With

    Application.ScreenUpdating = True

End Sub
 
Upvote 0
Thank u Jeff, it is wroking now

but small changes need to be made

the result iam getting is

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64>B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Jayanthi</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Murali</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Sridhar</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Jayanthi</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25>Ravi</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>1</TD></TR></TBODY></TABLE>

i.e., The first name it is not working the Cell A1 is not working

Second if i paste the name it should go to the second sheet automatically

Third if i try to delete the names in the first sheet iam getting run time error"1004"

fourth I want the i want to be according to the numbers that is if Sridhar is 4 it should be 1st and Jayanthi should be second and Murali third and Ravi last
 
Upvote 0
The first name it is not working the Cell A1 is not working
Click to expand...

One particular when using advanced filter, the column being filtered for unique records requires a header. If you do not use a header then you may get at least one duplicate (based on my experience).

The example I am using…

Before Macro
<TABLE style="WIDTH: 54pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=72><COLGROUP><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 54pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21 width=72>Name</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21>Jayanthi</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21>Murali</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21>Jayanthi</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21>Murali</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21>Ravi</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21>gayau</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21>Sridhar</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21>Sridhar</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21>Sridhar</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21>Sridhar</TD></TR></TBODY></TABLE>

Second if I paste the name it should go to the second sheet automatically
Click to expand...

This is worksheet code which will work with any change to column A on Sheet1.

Worksheet code >> right click on Sheet1 >> View Code >> Paste on right side of screen.
Click to expand...

After Macro
<TABLE style="WIDTH: 108pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=144><COLGROUP><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 54pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 width=72>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=72>Count</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=21>Sridhar</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>4</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=21>Jayanthi</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>2</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=21>Murali</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>2</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=21>Ravi</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=21>gayau</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1</TD></TR></TBODY></TABLE>

Third if I try to delete the names in the first sheet I am getting run time error"1004"
Click to expand...

I do not get any such error. If you do get this error then you need to debug and then tell me which line is causing the error.

fourth I want the I want to be according to the numbers that is if Sridhar is 4 it should be 1st and Jayanthi should be second and Murali third and Ravi last
Click to expand...

I added a line to the code to sort the results as you asked.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Dim srcLR As Long
    Dim lrSrc As Long
    Dim lrDst As Long
    Const wsSrc As String = "Sheet1"
    Const wsDst As String = "Sheet2"
    Set Rng = Target.Parent.Range("A:A")
    If Intersect(Target, Rng) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    With Sheets(wsSrc)
        Sheets(wsDst).Columns("A:B").ClearContents
        lrSrc = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A1:A" & lrSrc).AdvancedFilter Action:=xlFilterCopy, _
            CopyToRange:=Sheets(wsDst).Range("A1"), Unique:=True
    End With
    With Sheets(wsDst)
        lrDst = .Range("A" & .Rows.Count).End(xlUp).Row - 1
        .Range("B1").Value = "Count"
        With .Range("B2").Resize(lrDst)
            .FormulaR1C1 = "=COUNTIF(Sheet1!C[-1],RC[-1])"
            .Value = .Value
        End With
        .UsedRange.Sort Key1:=.Range("B2"), _
            Order1:=xlDescending, Header:=xlYes
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
VBA to show and change list of names depending on selection
Replies
7
Views
733
mumps
mumps
A
  • Question Question
How to auto-populate a number entered in one cell to another depending on date range
Replies
9
Views
439
awoohaw
awoohaw
S
  • Question Question
Macro to add sum total to track percentages
Replies
0
Views
333
spenserallen7
S
P
  • Question Question
Copy a worksheet from one workbook to another workbook based on conditional statements
Replies
5
Views
357
HighAndWilder
H
O
  • Question Question
Count only one occurrence of all criteria being met
Replies
8
Views
1K
KRice
K

Forum statistics

Threads
1,223,723
Messages
6,174,113
Members
452,544
Latest member
aush

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top