Counting Unique Entries From 3 Workbooks

Sep 23, 2009

I need to count the number of unique names (column A) on 3 separate worksheets. Each worksheet represents a facility that people visit. It is possible that the same person will visit all three facilities. If this is the case I want to only count them once, even though they may have numerous entries on each.

I need to get the same result as if I was to copy and paste all of the names from three spreadsheets on to one (which isnt an option) and then count unique names. To do this on one sheet I have been using =sumproduct((A1:A100<>"")/countif(A1:a100,A1:A100))

View 2 Replies


ADVERTISEMENT

Counting Unique Entries ...

Nov 2, 2009

Couting Unique Entries

I've included a sample worksheet ...

View 10 Replies View Related

Counting Unique Entries That Meet Certain Criteria

Oct 7, 2009

Every other line is the planned production and the others are filled in after as the actual production
I have a sumproduct formula right now that looks if the line is considered planned production and then if there is a number greater than 0 planned on that day. The idea is to tell me the number of Stations scheduled to run that day.

Here is my problem- if a station is running two different products it gets counted twice.
I am trying to find a way that once a station is counted it only gets counted once even though they are on seperate lines.
Attached is an example of what I have with the formula and below that is what I am looking for

View 2 Replies View Related

Counting Unique Text Entries In A Sheet With A Condition

Sep 6, 2005

Counting Unique text entries in a sheet with a condition ..

View 14 Replies View Related

Identifying Unique Entries Based On Unique Entries In Another Column?

May 29, 2014

I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.

Example List

Fruit
Color
Apple

[Code]....

View 9 Replies View Related

Unique List Would Grab All Unique Entries

Jul 30, 2009

I have a data table that repeats as follows:

CITI
SCB
SCB
SCB
SCB
SCB
SCB
SCB
RBC
RBC
RBC
RBC

From the data above I need to make a new unqie list that would grab all unique entries.
CITI
SCB
RBC

I am trying to use the following guide:

http://blogs.techrepublic.com.com/howdoi/?p=111

Get stuck on "Listing A:

View 9 Replies View Related

VBA Counting Used Entries In A Range

Jul 22, 2009

i need to count the number of used entries in a range.

so fex the range is A1:A10 but there are only 2 entries what i search is the number 2

is there a VBA function or so doing that.

ps i have this range.Rows.Count but this is counting me the length of the range even if there are no entries..

View 4 Replies View Related

Counting Entries Between Two Dates

Apr 29, 2009

Column A contains names
Column B their unique ID number
Column C the date a transaction occurred

How can I count the number of entries between two dates i.e. 1-31 April regardless of duplication or anything? I've tried a few things mentioned in other threads but none seem to work.

View 4 Replies View Related

Counting Entries Per Hour

Nov 16, 2007

My spreadsheet contains two text columns that represent dates and times; 11/21/2007 and 0935 for example. The rows are populated with the date and time for every event during a 48 hour period. A single worksheet may contain up to 3,000 events (rows).

How do I create a subtotal for the number of events in each of the forty-eight 1-hour periods?

View 9 Replies View Related

Counting Cell Entries ...

Aug 9, 2006

Colum A
Ticket No.
150258, 150268, 150269
150259
150249, 150266, 150271, 150279

Colum B
No. of Stillages
3
1
4

I have two colums. In A1:A4 are ticket numbers of products made. In B1:B4 are the number of stillages that the tickets relate to. What i am trying to work out, is a formula that will calculate the number of stillages produced based on the fact that each ticket number begins with "150". I have tried using COUNTIF, MATCH and FIND functions, but when i does work, will only return a value of 1.

View 9 Replies View Related

Counting Duplicate Entries

May 21, 2007

I am trying to determine wether there are duplicate entries within a range and have then counted also. Example:

I have a list if zip codes and I want to find out how many of each zip code are there. If I have a list with:

12345
12345
12345
12346
12346
12347

I want to have it tell me there are 3 of "12345" and 2 of "12346", possibly next to the entry?

12345 3
12345
12345
12346 2
12346
12347

View 4 Replies View Related

Counting Entries In Date Range?

Feb 12, 2014

I'm looking for a way to count entries of numbers per day of the week within a date range. Example

Start End Mo Tu We Th Fr Sa Su TOTAL
2/10 2/23 1 1 1 1 0 1 1 12
3/1 3/31 1 2 1 1 1 1 2 40

I don't know if that possible in excel or its to much

View 6 Replies View Related

Counting Entries Based On Month

Oct 21, 2009

I have a column of dates and want to count how many times there is an entry for a particular month. example:

(column D)
7/4/2009
7/13/2009
7/22/2009
8/1/2009
8/6/2009
9/15/2009

I want to be able to coun (countif?) how many times there is a July listing, August listing, etc.

=COUNTIF(D3:D20,">=7/1/2009")+COUNTIF(D3:D20,"<=7/31/2009")
=SUMPRODUCT(--(D3:D20>="7/1/2009")*(D3:D20<="7/31/2009"))
I have made them arrays.

View 4 Replies View Related

Counting Entries If Adjacent Box Matches

Sep 17, 2013

Im trying to make an excel spreadsheet for work for working out how many of each product and individual staff member has sold

I have 2 columns one with staff and one with what they sold e.g.

Staff Member - Sales
Staff 1 - prod A
Staff 2 - prod B
Staff 3 - prod A
Staff 2 - prod C
Staff 1 - prod A

and have another table, the one that i want to populate, that looks like this

Staff 1
prod A =
prod B =
prod C =

Staff 2
prod A =
prod B =
prod C =

etc

I cant seem to find a function that will work out how many of prod A staff 1 has sold and how many prod B he has sold etc

i have tried things like countif but i cant seem to get them to link the staff member and sales boxes together, the only way i can think of is to write down an equation for each line, but i have hundreds of sales and that would takes ages

View 3 Replies View Related

Counting Data Entries In Rows

Apr 11, 2007

I run an online golf tour and I need a little help with the coding of my handicap excel sheet. (For any golfers out there this is a custom handicap system, not the R&A version).

First I'll explain what I have, then my problem.

I have from left to right the following, Name, Scores, Worst Score, 2nd Worst Score, Average, Hcp. (There are other columns but are not important here)

The Scores columns record each round played, so at the moment we only have 10 columns with data as its the start of the season. I currently find the "Worst Scores" from the 10 columns, as they are all relevant at the moment. My problem will arise when I get over 12 columns of data. From that point on I need to find the "HIGHEST 10" from the "LAST 12" entries.

Easy you might say, BUT the last 12 rounds played could be spread over 100+ columns and every row could be different.

Example, Player 1 could have scores in column A to AB and then in AD to AE. Where as Player 2 could have scores in every alternate column.

I'm assuming I'll have to set up some form of count to count the cells that have data in them until I get to 12. Then extract the ones I dont need.

My question is, How do I find the last 12 column cells used in any row AND extract the highest 2 from those 12?

View 9 Replies View Related

Counting Entries Using Time Criteria

Jun 6, 2006

I need to generate a report that indicates how long it took a vendor to respond, and indicate if the time was less than 2 hours.

I'm having trouble with a formula that will indicate how long it took for the vendor to respond during business hours (8-5 monday through friday). I can easily calculate the total elapsed time, but I'm at a loss on how to account for the after hours time period.

Would this be better handled with VBA?

View 3 Replies View Related

Counting Entries In A Validation Field

Apr 17, 2007

I need to count entries in a validation field

I tried using =COUNT(3,E3,M3,U3,AC3) but this does not work.

View 3 Replies View Related

Counting Number Of Multiple Entries

Jun 6, 2007

I am trying to count the number of indentical entries in my spreadsheet. I got the conditional formatting to color indentical entries that have more than 2 entries. I need to know how multiple entries are in my spreadsheet. I am using =COUNTIF($A$1:$A$10000,A1)>2.

View 9 Replies View Related

Sum Unique Entries

Apr 23, 2007

I have attached a sample workbook to try and explain what I'm after.

Basically I want to sum the amount of occasions a person has had checks and each occasion could have 1,2 or 3 individual checks.

View 9 Replies View Related

Counting Additional Entries On A Linked Spreadsheet

Sep 29, 2009

I have two worksheets, 'monthly budget' and 'summary sheet'.

Summary sheet has been set up so that any additional records added to 'Monthly Budget' will be copied using the formula below (kindly provided by Cheeky Charlie yesterday):-

=IF(ISBLANK(INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A))),"",INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A)))

I have set up a unique entry count on 'summary sheet' which is currently showing 5. If I add a new name 'Jim' to the 'Monthly budget' sheet, this is copied on the 'summary sheet' however the unique entry count remains at 5.

View 5 Replies View Related

Counting Cell Entries In Protected Cells

Apr 25, 2006

I'm trying to create a worksheet with only one unprotected cell (simple enough!) and I want to base calculations in other protected cells on a running total of how many times a value has been entered in my unprotected cell. Re-entering the same value would need to be counted.

First enhancement would then be a means of resetting the counter.

Second enhancement would be to check for a valid entry and only then increment the counter.

View 4 Replies View Related

Extract Unique Entries

Oct 4, 2006

I have a list with many names, several of them appearing more than once. I would like to extract the unique entries an then make the list of them.

I can do that using the filter. But can I do it with formulas ? (In order to, whaterever change is made to the first list, get the extracted one immediately updated).

View 9 Replies View Related

Delete Unique Entries

Jun 12, 2008

I was wondering if there might be a better way to write this macro. What it does is clears unique items from a Range( leaves duplicates ) I've looked all over the net I can find all kinds of function and subs to remove duplicates but haven't been able to find anything that just removes single entries. I"ll bet there's a more elegant way to write this maybe using a Collection or a Dictionary.

Sub Dummy()
Dim MP1_Rnge As Range
Set MP1_Rnge = Range("A1:A100")
For Each Cell In MP1_Rnge
If Not IsEmpty(Cell) Then
If Cell.Row = 1 Then..........

View 9 Replies View Related

Unique Entries & List

Dec 23, 2008

I have the below table of data, in Column A there is a list of entries and there may be duplicates in them, what I want to do in column B is be able to list all the unique entries in there and basically remove any duplicates.

AB1DETAILED LISTUNIQUE LIST2Holden 3Ford 4BMW 5Mercedes 6Ford 7Ford 8Audi 9BMW 10Holden 11Ferarri 12Holden 13BMW 14Toyota 15Toyota

View 9 Replies View Related

Identifying Unique Entries

Mar 24, 2009

I have a worksheet, where in C3:C100 (or even higher) i will have text strings. A lot of these will be repeated, and i want to pick out just one instance of each text string and display it in E3:E10 (or higher).

So, for example, if i had the following values in column C:One
Two
Two
One
One
Three
Seven
Two
Four
Seven
One
Three

I would want the following values in column E:One
Two
Three
Seven
Four

View 9 Replies View Related

Find Sum Of Unique Entries

Dec 29, 2009

I am using excel 2007.

I am trying to find the sum of unique entries in a table such as below for each respective date.

DateClient1/01/2010ABN1/01/2010ABN2/01/2010BNP2/01/2010HSBC3/01/2010ABN4/01/2010BNP4/01/2010HSBC4/01/2010ABN5/01/2010BHP

The results should appear as

Count Unique1/01/201012/01/201023/01/201014/01/201035/01/20101

What formula would I use to calculate this unique count?

View 9 Replies View Related

Filtering Unique Entries

Jul 20, 2006

I have this 2 columns, A(region) and B(location), and I need a macro to filter the unique entries in Columns A and return both the unique values in A and their corresponding values in B. I've attached a file on it with sheet 1 having the data to be worked on and sheet 2 being the output that I require.

View 7 Replies View Related

Choosing Unique Entries

Oct 7, 2006

I'm trying to write an Excel program to calculate golf handicaps. In column A, I have 20 unique dates. In column B, I have corresponding 20 scores. In column C, I'm trying to create a corresponding notational mark, "*", indicating the 10 lowest scores that are also the oldest. However, I'm running into problems as I may have duplicate scores with different dates, i.e. 3/22/06 will have a score of 85 and will be the 10th lowest score, but 4/1/06 also has a score of 85.

View 9 Replies View Related

Count By Unique Entries

Feb 24, 2007

I am hoping this can be done with formulae. Starting at C7 and continuing down the C column there is a list of names which could potentially run from C7 to C5000. This list of names will contain duplicates. For each name there is a corresponding 'reason' in the F column which will contain the word 'Truancy' or 'Late'.

I need a formula that can count the number of UNIQUE names in the C column which correspond to the word 'Truancy' or 'Late' in the E column.

An example,

[Name].....................[Reason]
C7 ...........................E7
John Potts.................Truancy 2
Matt Jones................Truancy 10
John Potts.................Truancy 4
Matt Jones ...............Late AM
Pete Burns................Late PM
Pete Burns ...............Late Both
Steve Lopez..............Truancy 6

Count of unique names with the word Truancy in the corresponding E column = 3 [John Potts has 2 instances of the word truancy in column E but this is only counted once]

Count of unique name with the Word Late in the corresponding E column = 2 [Pete Burns has 2 instances of the word late in the E column but this is only counted once].

I have also included a sample workbook.

View 9 Replies View Related

Compile Entries From Multiple Workbooks

Feb 6, 2008

I need to compile entries from several workbooks (one for each day of the month), from Column B every time, into a master workbook which will display all of the data from the columns B, laid out in individual columns in the master workbook. i.e. if Workbook1 contains the numbers 1, 2, 3 in cells B2, B3 and B4, and Workbook2 contains the numbers 4, 5, 6 in cells B2, B3 and B4, then I want the master workbook to run a macro which will put Workbook1's numbers into cells B2, B3, B4 and Workbook2's numbers into cells C2, C3, C4and so on for each day of the month. The idea is to be able to compile the month's data so as to plot charts per row.

Each month has it's own folder and each day has it's own workbook. File names are in the following format: YYYYMMDD.xls
I've tried several times, including the use of loop code found here: http://www.ozgrid.com/VBA/loop-through.htm but to no avail.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved