CODEWITHSUNDEEP
excelvba
HealyRT
HealyRT

Reputation: 23

How does one count the number of occurances of a string on separate worksheets?

I have a workbook that contains multiple worksheets used for data entry and status monitoring for a project I am on at work. The "Overview" Worksheet contains multiple columns that have basic information [Number, Contact Info, Page Number, Issue, Comments, Status] (from columns A to F). The "Contact" Column contains a user's Name, Phone Number, Email, and Organization all in one cell.

Each Row is a unique entry but the Contact fields are reused based on if the same person has multiple inputs.

I have another Worksheet that is named "Contacts" that has the same information from the "Contact Info" Cells in the "Overview" worksheet, but divided into individual columns [Name, Phone Number, Email, Organization] (from columns A to D).

All of the code for the data entry is in a Userform that adds information to both worksheets, but specifically, the "Contacts" worksheet when a unique contact is submitted.

There are no duplicate rows in the "Contacts" worksheet.

Here's the problem:

I want to keep track of the number of occurrences of each contact in the "Overview" worksheet on the "Contacts" worksheet. That count will also be used to keep a unique enumeration per each contact on the "Overview" worksheet. (i.e. John Doe has 5 entries, so the "Number" Column on the "Overview" worksheet will indicate "JD-1" "JD-2" … "JD-5" for each entry).

The intent is to have an aggregated list of all of the inputs but have a unique numbering methodology for each unique contact.

Any guidance will be greatly appreciated.

Problem I have encountered:

I would have used a CountIf type function in the cell but it gets out of my skill level going between worksheets.

Also, the way the Contacts worksheet is populated is to append unique entries on the next empty row, so if there are formulas in the Cells, it will not be viewed as a empty row.

This Workbook is used by multiple users and has to be functional from the beginning with a clean sheet.

Upvotes: 2

Views: 115

Answers (1)

Cyril
Cyril

Reputation: 6829

Shouldn't need VBA for this, as you can do it with formulas...

You can use CountIf for the names, where one on the Contact sheet is a simple 

B1 = CountIf(Overview!A:A,A1)

and the Overview sheet itself would be a self-counting sheet, where 

B2 = CountIf(A$2:A2,A2)

That doesn't account for needing to split your string to include the initials "JD", but it may be more appropriate to just define those on your contact sheet and index/match the output, concatenating with your countif, similar to

B2 = Index(Contacts!C:C,Match(A2,Contacts!A:A,0))&"-"&CountIf(A$2:A2,A2)

Assumed Layout for Sheets("Contacts"):

ColA      ColB     ColC
Name      Count    Initials
John Doe  5       JD

Edit1:

Since the userform is inputting new lines (assumes you have found lr (last row) and will use that variable), you can adjust the formula to support input by VBA:

' to input formula as is to the sheet:
Cells(lr+1,2).value = "=Index(Contacts!C:C,Match(A" & lr+1 & ",Contacts!A:A))&"-"&CountIf(A$2:A" & lr+1 & ",A" & lr+1 & ")"

'tweak to have VBA do calc
With Sheets("Overview")
    Cells(lr+1,2).value = Application.Index(Sheets("Contacts").Columns(3),Match(.Cells(lr+1,1).value,Sheets("Contacts").Columns(1)) & "-" & Application.CountIf(.Range(.Cells(2,1),.Cells(lr+1,1)),.Cells(lr+1,1).value)
End with

'I use cell references above, but you may want to swap .Cells(lr+1,1).value with the TextBox.Value from the userform

I dropped the "exact match" (0) from the match formula, so take that with a grain of salt, which may require more effort to make fit your exact model.

It may behoove you to have pre-determined Contact names for people to select from (think drop down) so that you don't have variance in the names that can/need to be found.

Upvotes: 4

Related Questions