+ Reply to Thread
Results 1 to 5 of 5

Repeat text count based on unique ID number

  1. #1
    Registered User
    Join Date
    06-29-2015
    Location
    CT, USA
    MS-Off Ver
    Standard 2010
    Posts
    30

    Repeat text count based on unique ID number

    I am new to VBA with some experience in writing formulas in Excel. I need to wrote code to count occurrences of text that are associated with an ID number, sum the types of text (i.e, under a column header of Blue write 4 if 'blue' is used four times, etc), and then repeat the process for the next ID number. The tabulated data of text data would be pasted into the same row as the corresponding ID number (but obviously in a different column). The issues I'm having are starting and stopping the counting - starting at first cell in row A that contains a number, counting the number of rows to next number (& subtracting one so that first text of next number is not counted), and then starting next count at the next number . The code I have for counting the text is: =COUNTIF($G3:$G14,"Blue"), where in this example data begins in cell G3 and ends in cell G14. How can I automate this for a very long list of ID numbers? I've tried to paste in a picture of a type of data I'm trying to work with - just imagine it continuing for several hundred ID numbers. There are intervening pieces of data in columns that I am not analyzing now but I don't want to loose that data either (in this example, hire date, eval start and stop dates, etc). I think that what I want to accomplish needs to be done using VBA, but I'm open to any suggestions. Thanks a bunch for the help.

    Spreadsheet.PNG

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,256

    Re: Repeat text count based on unique ID number

    The simplest way is to not use VBA at all, but to use a pivot table. Copy your sheet, select from A3 down to AXXX, then use Go To... Special / Blanks OK. Then type =, press the up arrow key once, press Ctrl-Enter, and then copy column A and pastespecial values. Then select columns A:G and insert a pivot table. Use Employee ID as the row field, Service as the column field, and service as the data field, set to count. And you will have a nice summary of your values.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,981

    Re: Repeat text count based on unique ID number

    Click here to download a sample file. I used some dummy data to test the macro. The macro is in Module 1. Change the colorArray to suit your needs.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #4
    Registered User
    Join Date
    06-29-2015
    Location
    CT, USA
    MS-Off Ver
    Standard 2010
    Posts
    30

    Re: Repeat text count based on unique ID number

    Thank you for the quick reply - it worked! I think I'm going to like the power of the pivot table. Now onto the next problem ......

  5. #5
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,981

    Re: Repeat text count based on unique ID number

    Good luck with it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count Consecutive Days Based on Unique Number
    By Kimston in forum Excel General
    Replies: 5
    Last Post: 11-23-2014, 04:46 PM
  2. Replies: 2
    Last Post: 11-21-2014, 12:59 PM
  3. Formula for count excel text & number except repeat
    By emran06 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-13-2014, 04:06 AM
  4. Replies: 4
    Last Post: 11-30-2010, 05:14 PM
  5. Repeat values based on number count (without macro)
    By ibexcel in forum Excel General
    Replies: 7
    Last Post: 12-17-2009, 06:35 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1