I have a workbook where i am trying to find duplicate accounts. I need to get a count of these duplicate accounts and i need to find the accounts as well. One last thing the workbook covers an entire year and i would like a formula that will cover the 12 sheets to provide the results.
I have two spreadsheets, one named Tracker, the second named Data. Data is a central database spreadsheet with account #s and Tracker pulls information from it. A team is assigned an account based on the last two #s of the account #, called term digits.
Goal: A VLOOKUP to where if a column equals the term digit range (example, any accounts ending in 00 to 09), it will pull the account # from Data to Tracker.
That part is easy, the tricky part I'm having issues with is that I need it to do the search mentioned above AND search for account #s that end in 60.
So: search for 00 to 09 and 60, pull the account # from Data to Tracker once found.
Edit: Term Digit is on column B and the account # is on column A - both in the Data spreadsheet
I'm looking to count the number of customer accounts in a report based on the branch number. I generally need to know, how many accounts has been opened per each branch without double counting the same account number (as it happens that the report may have duplicate number.
finally I need to know the sub total showing the total per branch and subtotal of all branches together.
formula to find duplicates and count only on instance of them.
Uploaded with ImageShack.us
As you could see on column A i have several entries,some of them duplicated. In column B i have a formula to check weather the value on column A is duplicated or Not. From this values i want to count only one instance of duplicated entries. I want to have one instance of Oscar to be counted as original and regardless the number of duplicates i want to count only 1. i want this not only for Oscar but for all the duplicates with more than 1 duplicate result.
I'm trying to find customer loyalty by rep. So I need to find all the accounts that have a loyal relationship with a rep and separate them. All the other reps that sold to those companies are removed. Then I need to find the accounts that still did business with us from year to year, but not with the same rep. Any not loyal accounts are also removed.
The ultimate goal is a pivot table that shows reps on the vertical axis, years on the horizontal axis, with the data being total number of accounts per rep per year. All of the 2008 accounts will be counted, and of the 2009 accounts, only those that were loyal - retained either by the same or a different rep - will be totaled. For this purpose there is no difference between accounts retained either by the same or a different rep, they will be added together. So a rep may have had 10 accounts they sold to in 2008, and of those 10, 5 bought again in 2009, and the rep also picked up 1 account that bought from another rep in 2008. So the rep's totals would be 10 for 2008, and 6 for 2009. I've attached an example workbook to show you what I mean. A "1" in the 2008 or 2009 column indicates the account bought something that year.
I have a spreadsheet of over 15,000 lines of student information, sorted by student number. I want to count the number of rows which have a duplicate student number, up to 15 duplicates in a row, and show the total number of duplicates in a Separate Column. I.e.
I've been trying to use a Countif formula, but I found I had to use so many ANDs and ORs that the formula became too long. I don't know how to use programming code, only formulas in Excel. Is there an easier solution using some type of SUMPRODUCT code?
I want to check with the vlookup function and some other form of either index or other function where if I check (enter an ID) an ITEM ID and then it will tell me how many different products have been assigned to that ID ITEM. In some cases the ITEM ID has only used One Product, whereas other ITEM ID's have used muliple products.
I have attached an example of what I am trying to achive (its possible the same ITEM ID could have several products used against it.
Reptile Dog Red Reptile Tiger Purple Mammal Tiger Stripes Mammal Tiger Spots Mammal Racoon Black Mammal Cat Plaid Mammal Dog Brown Mammal Dog Purple Reptile Dog Red Reptile Tiger Purple Tree Dog Orange Tree Pine Green
I would like to use this data to populate within a seperate worksheet that reads:
I have a template whereby it show agent hourly performance. I unable to use vlookup formula because duplicate id with different interval. If I select id 1977 it will auto update agent performance it the table according to the interval.
I have a sheet using VLookup to find EMail and Web addresses. I can get the address to show up but not as an active URL address. Is it possible to have the address "active" so I can click on it and activate the EMail or Web Site?
Writing this workbook in 2007, but it will be used in 2003. Have searched but can't find answer to this. I have a 5-column (A to E) table array and I'm using VLOOKUP in several other cells to return values in col2, 3, 4 & 5 with my lookup value being col 1. However, col 1 will contain numbers which occur more than once. How can I get VLOOKUP (or something else?) to pick the last occurence (lowest row) and use that one? Also, the array will be sorted - smallest to largest - as the rows go down - how will the SORT work on duplicate entries? I'd like, if possible, for the entry made most recently to be the lowest of the duplicate entries so that VLOOKUP (or whatever) will pick that one to use. Will any kind of SORT do that?
I have attached a spreadsheet where I need the cells B2 to B8 to return the number of Y/P's (in column D) by Unit (in column E). There are duplicate entries in column D, however, if entry is duplicated, it is only to be counted once. Is there a formula to be able to do this? I don't know anything about VBA so would prefer a formula if possible.
there are unique entries like AU0896 etc. that are repeated in my list. my job is to find how many unique entries there are and add the count at the end so, basically if there are 6 AU0896 entries, then I must create a AU08966 value.
how to count how many times a word or text is displayed in a column.
Say this is Column A Can any formula be used to tell me that T1409 was duplicated 3 times and T1527 was used 2 times. The rest of the numbers only triggered once so I dont care for those numbers. I only want T1409 and T1527 to pop up and tell me how many times it was used.
macro that takes the number data and if it is the same number as in the previous row(s) just delete the extra number(s). It also needs to put in a factor in the next column of how many times the number was the same. For example:
I often need to merge multiple occurences of data (such as account numbers or names) and to sum or count the values associated with each invividual instance (eg cost or number of entries). Data can often be thousands of rows and varies every time. For example:
Col A Col B Ken 5.9 Ken 12.6 Brian 5.5 John 6.4 Fred 9.9 Fred 11.6 Fred 2.0
I'm trying to create an aging accounts table from transaction data. I am tracking loans. Here's the layout of the data that I have. Positive entries mean that a new loan was taken out. Negative entries mean that a loan payment was made.: ...
I have a situation where I have about 20000 lines to populate and looking for a quicker alternative. I have duplicate numerical values in the lookup_value. I want the same number to appear for all lookup_value's but there is only one instance of that value on the other sheet I am searching. My table looks like this:
I have two columns of data, and I need to create a third column to count the number of times that same line appears in the document (and then remove all but the first copy of that line). my data looks as follows (and it is sorted so all duplicate rows appear directly next to each other):
Adam1998 | Jan Adam1998 | John Adam1998 | John Adam1998 | Paul Adam1998 | Peter Adam1998 | Peter Adam1998 | Peter Adam1999 |John Adam1999 | Paul
I need this to look as follows: Adam1998 | Jan | 1 Adam1998 | John | 2 Adam1998 | Paul | 1 Adam1998 | Peter | 3 Adam1999 |John | 1 Adam1999 | Paul | 1
I have a tab (All Data) full (column A - S) of daily data, the date is in column A, there's a unique identifier is in column D. The daily data can be repeated multiple days. I need to remove the duplicate data for the unique identifier for the week. I'm having problems with the removal of the duplicate data. This is what I currently have for the weekly (2nd week of the year) data totals:
I'm trying to make a combo box that lists only unique entries in 2 columns but also has a count in brackets beside the entry. For example:
Apples (8) Bananas (13) Grapes (2)
The code I have to populate the box is:
Sub RemoveDuplicates() Dim AllCells As Range, Cell As Range Dim NoDupes As New Collection Dim i As Integer, j As Integer Dim Swap1, Swap2, Item
Set AllCells = Range("e3:f370")
What can I add to count how many times an entry is listed in my range? Also, the filter works off text from the combo box, how can I remove the counts prior to filtering? Here's what I'm using now to filter:
Private Sub SrchBtn_Click() Worksheets("sheet1").Range("b1") = ComboBox1.Value Range("A2:J1000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("L1:M3"), Unique:=False ActiveWindow.SmallScroll Down:=-5 Unload Me End Sub