+ Reply to Thread
Results 1 to 2 of 2

Counting Frequency per hour

  1. #1
    Registered User
    Join Date
    05-13-2010
    Location
    Marion, Illinois
    MS-Off Ver
    Excel 2007
    Posts
    43

    Counting Frequency per hour

    I am trying to create a specialized daily report. I need help based on the following workbook. Sheet 1 my data, sheet 2 has a list of all the numbers (column a) that I need to do a reference to from sheet 1.

    I need the following results.

    1. Remove the duplicates from column B in sheet 1 based on a hourly basis. We find this on column A.
    2. Count in sheet 1 how many numbers are left minus duplication per hour.

    A sample of what I am looking for is on sheet 3

    I know how to manually do this but I want to create formula or macro to help.

    Thanks for this
    Attached Files Attached Files

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Counting Frequency per hour

    Here are some steps to use

    In your Sheet1
    • Insert a row above the data
    A1: Time of Day
    B1: Station #

    The times are still text, not numeric, so they won't sort properly.
    Let's fix that:
    • Select the "times" under the Time of Day heading
    • Data.Text-to-columns...Click: Finish
    (Now the times should be numeric)

    • Insert a new sheet in your workbook
    A1: Time of Day
    B1: Station #

    • Select A1:B1 on the new sheet
    • Data.Advanced
    ...Check: Copy to another location
    ...Check: Unique records only
    ...List Range: (Select your data range on sheet1...Sheet1!$A$1:$B$130)
    ...Copy to: (Select A1:B1 on the new sheet)
    ...Click OK
    Now you have a list of the unique pairs
    (Note: Advanced Filter cannot push values to another sheet
    BUT it can pull them from another sheet)

    • Select that list on the new sheet, including headings
    • Insert.Pivot.Pivot_Table
    ...Drag: Time of Day to the ROWS section
    ...Drag: Station # to the vALUES section
    ...Click the dropdown arrow on Sum of Station #
    ......Select: Value Field Settings
    ......Choose: Count
    ......Click: OK
    ...Click the dropdown arrow on Row Labels...Click: Sort A to Z
    Done

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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