Conditional Copy & Count in Excel

Microsoft Excel 2007 home and student
October 26, 2009 at 11:07:36
Specs: Windows XP
Part of my database is a column for Referents. I'd like to create a Referent page that when a new referant is entered on the Database that Referant's name will be copied to the Referent pages as well as the number of referrals of that Referant.

I need their name copied and counted to see how many times it turns up the the Database. I have is set up now to count all the existing referents. But, problem is everytime there is a new referent I have to manually copy their name and apply the Count formula to that individual. In the main database there is only one referent listed per row. I will show what I would like below:

	Referrents:	                # Referred:
1	Roberts, Rob	2    
2	Roberts, Tom	3
3	Roberts, Lisa	8
4	Roberts, Benjamin	20
5	Roberts, Zachery	1

Thanks for all the help!

See More: Conditional Copy & Count in Excel

Report •

October 26, 2009 at 12:29:37
I'm confused:

You said: "when a new referant is entered on the Database"

Then you said: "I need their name copied and counted to see how many times it turns up the the Database."

If the referant is "new" how could it be in the Database more than once?

Report •

October 26, 2009 at 13:54:22

I couldn't think of a way to do all of this with formulas.

Maintaining a list of unique referent names on the referent worksheet seems to require a user defined function.

The function is FINDUNIQUE() and it takes three arguments:
1. Search_Range, the range of cells containing the names to be checked.
2. Item, a number or reference to a cell containing a number which is the Nth unique item (name) to return from the search range
3. An optional No_Error value which defaults to FALSE. If false and the Nth unique instance does not exist it returns the #VALUE error,
If TRUE then it returns an empty string ""

Here is an example of what the formula looks like with real values (formula on Referents worksheet, data on Database worksheet range E4 to E21, and there is a column of numbers (1,2,3 etc.) starting at cell E13 to the left of the formula.)

In cell F13:
=findUnique(Database!$E$4:$E$21,E13, TRUE)

To enter the UDF, from your workbook enter Alt + f11 (both the Alt key and the #11 function key at the same time), or Tools-Macro-Visual Basic editor.

This opens up the VBA Editor

In the left side Project Explorer pane, look for the name of your workbook (all workbooks are preceded by VBAProject).

Right click on the name and select Insert.
Insert Module (not class module)
A new module (Module1) will be added.
Double click on it.

Now in the main window paste the code,
then Save the project (File - Save)

Option Explicit
Public Function FindUnique( _
    rngSearch As Range, _
    intItem As Integer, _
    Optional blnNoError As Boolean = False _
) As Variant

Dim rngCell As Range
Dim varFoundArry() As Variant
Dim dblItems As Double
Dim strThis As String
Dim m As Double
Dim n As Double

On Error GoTo ErrHnd

'get size of range
dblItems = rngSearch.Cells.Count
'resize array to number of cells in range
ReDim varFoundArry(dblItems, 1)

'put text values in array & 1 in counter
n = 1
For Each rngCell In rngSearch.Cells
    varFoundArry(n, 0) = rngCell.Text
    varFoundArry(n, 1) = 1
    n = n + 1
Next rngCell

'mark duplicates '2'
For n = 1 To dblItems
    'no need to look at an entry already flagged as duplicate
    If varFoundArry(n, 1) = 1 Then
        strThis = varFoundArry(n, 0)
        For m = n + 1 To dblItems
            If varFoundArry(m, 0) = strThis Then
	       'flag duplicate
                varFoundArry(m, 1) = 2
            End If
        Next m
    End If
Next n

'get requested nth unique item
m = 0
For n = 1 To dblItems
    If varFoundArry(n, 1) = 1 Then
        m = m + 1: If m = intItem Then FindUnique = varFoundArry(n, 0): Exit Function
    End If
Next n
'No required Nth unique instance
'return error unless 'no error' flagged'
If blnNoError Then
    FindUnique = ""
    FindUnique = CVErr(xlErrValue)
End If
Exit Function

'if Program error return #NA
FindUnique = CVErr(xlErrNA)
End Function

The formula in cell F13 should return the first unique item in the selected column of referents.

Drag the formula down and subsequent unique names will show.

To get the number of referrals for each referent, in cell G13, i.e to the right of the first unique name, enter this formula:

Drag this down alongside the unique names.

This formula, the UDF FindUnique formula and the numbers will need to be extended down sufficiently far to be able to respond to new referent names in the main database.

The countif() function will return zeros for the blank cells below the last unique name. Wrap it in an IF() function to suppress this:
The above formula has been split onto two lines for ease of viewing.

In the example, the reference to the database column with the referent names is in a standard A1:A1 format. It would be better if this was replaced with a named range, so that the range can be expanded without having to change any of the range references in the formulas.


Report •

October 26, 2009 at 14:10:10
My apologies for the confusion.

My data set currently has 200 or so referents. If that list was final then it would be easy. I would just use the Count formula from my current list. The problem is we are constantly adding referents. So right now I have a page that lists the referent and counts how many times his name shows up in the database to give us thier number of referrals. Then when we add a new referent I have to then add his name to this list and then apply the formula to their name. Since I am not the only one adding data this makes it very difficult to stay on top of.

What I would like is everytime a referent is entered that referent (if new) is copied to a seperate sheet with the number of referals next to their name. If that referent is not new then their referral is just added to their tally.

Here is a sample of what my database looks like, and below that a sample of what i'd like the conditional copy and count to look like.


Name of Student	Student's Age	Parent's Name	Referent
Roberts, Rob	16	Roberts, Rob Sr. & Jane	Smith, John
Roberts, Tom	17	Roberts, Tom Sr. & Julie	Taylor, Blake
Roberts, Sam	16	Roberts, Sam Sr. & Sarah	Smith, John
Roberts, Hank	18	Roberts, Hank Sr. & Tina	Samuel, Josh

Referent:	# Referred:
Smith, John	2
Taylor, Blake	1
Smith, John	1
Samuel, Josh	1

I made sample in excel then tried the pre tabs so hope this works. Let me know if you have any other questions.

Report •

Related Solutions

October 26, 2009 at 15:50:21
Okay, so I was able to apply the Macro...Not that scared of VBA anymore thank you. Fairly easy, given the instructions. A prob I can forsee happening is barely mispelling a referents name and having it counted twice, i.e. Roberts, Rob and Robert, Rob being counted as two seperate referents. Is there a way to have my Referent list copied in alphabetical order vs. order in which they appear? This would allow me to easily recognize those names that were counted twice. I tried using a filter and it messed up the data. Thank you.

Report •

October 26, 2009 at 17:24:55

Go to your database and select the whole database, including column headers, then Toolbar - Data - Filter- Auto Filter.

This will add a filter / sort option to the whole database.

Click on any drop-down button and you have options to filter your data OR sort it based on any one of the columns.

This would let you look at the Referents in alphabetic order.

Another option is to copy the referents list on the Referents worksheet, Copy - Paste Special - Values and then sort the pasted list.

You might consider using data validation on all inputs to the cells in the Referents Column.

Create a list of all Referents and use it as a data validation list

In the referents column input cells select: Data - Validation, then select List under Allow: and enter the range of the list.

This forces users to only use 'approved' names. If a new Referent is identified, the name has to be entered in the validation list. A bit of a nuisance, but maybe worthwhile to reduce those typo errors.

PS Glad VBA isn't so bad after all!

Report •

Ask Question