I am trying to work out how I can assign a sequential value to sets of duplicate data within a list. i.e. where I have duplicates in a list I want each of the duplicates (that are in the same set) to have a sequential number assigned and for that to start from 1 each time it discovers a new set of duplicates.
RED
GREEN
YELLOW
PURPLE
RED
ORANGE
BLACK
GREEN
RED
Where a colour is duplicated in the list I want it to have a sequential number assigned. When a new set of duplicates is found the numbering starts from 1 and sequentially increases. So the above data list would resulting in the following output
RED 1
GREEN 1
YELLOW
PURPLE
RED 2
ORANGE
BLACK
GREEN 2
RED 3
Have tried variations of the RANK function, but not sure how to get the sequential nu,bering to restart with each unique set of duplicates. I would also like to do this without VBA if possible using the built in functions.
I'd like to do is to use loops to copy each value in "Sheet1" into every other row in column B of "Sheet2" starting on row 16. I realize that this is a simple task, but I'd like to know how to do this for the purposes of learning the correct VBA coding.
I have a column of week numbers but some gaps in the list (e.g. the job is complete and so I do not want to reference it) I am trying to create a list of jobs by week number. I need to sequentially number jobs to then use Vlookup e.g. job1-week1 job2-week1 etc to display be week.
I can work out to number the list ignoring the blanks but then resetting to 1 with each new week?
I need to lookup data based on a cells content. The answer has to output to a table as a sequential list. I have attached a sample workbook (there will be 100+ sheets in the final version!). The Register sheet lists competencies against page# and title. For each Evidence Sheet I need to populate the Competency grid (C4:D10) in a sequential manner i.e. no blank lines,and the Title (B13) based on the Page # (D2). I have looked at a great number of postings, but cannot see a solution that fits.
In short, I would like a pivot table to only count unique values, but when I click into the pivot I would like to show all instances of that value. For example:
I have a table of data that I am creating a pivot table from. There are fields for Customer ID, Task Name, Age, and Notes. There will be multiple records for a single Customer ID each time it has new notes.
I would like to create a pivot table that has Task Name in the Row Labels, Age in the Column Labels, and count of Customer ID in the Values, so that, for example, I can see how many accounts have been in the Design task for 2 days. However, when I do this it counts each record, but I would like it to count each unique Customer ID. Also, when I click into the pivot, instead of pulling up one line per Customer ID, I would like it to pull up each instance of Customer IDs in that Task Name/Age combination (similar to doing a DISTINCT in SQL).
I have a list of isometric drawing numbers ending with a [underscore]weld number e.g. 1692-SG-0040-04_05.
Some welds are repaired--in that scenario the amended weld number will be 1692-SG-0040-04_05R1, and even 1692-SG-0040-04_05R2 if repaired for a second time.
On occasion a weld may be cut out entirely and a new weld done. The weld number for that will be 6317-FG-1690-02_06C1.
And here's a wrinkle I've just verified...a cut weld may also be repaired so the weld number will look like 1698-SG-0077-01_04C1R1.
Is there a formula to count these as one weld: 1692-SG-0040-04_05 1692-SG-0040-04_05R1 1692-SG-0040-04_05R2
This as one weld: 6317-FG-1690-02_06 6317-FG-1690-02_06C1 6317-FG-1690-02_06C2
...and this as one weld: 1698-SG-0077-01_04 1698-SG-0077-01_04C1 1698-SG-0077-01_04C1R1
I am having trouble creating a function to count duplicates of duplicates.
An example of the data table 1 is:
Product 1 2nd Product 1 2nd Product 1 New Product 1 New Product 1 Flt Product 2 2nd Product 2 New Product 2 New Product 2 Flt Product 2 Flt Product 3 2nd Product 3 2nd Product 3 2nd Product 3 New Product 3 Flt
I created a new table (table 2) and made a list of all the Products on table 1 and removed the duplicates. I now have 3 columns with titles New, 2nd and Flt as follows:
New 2nd Flt Product 1 XX XX XX Product 2 XX XX XX Product 3 XX XX XX
I am trying to count the duplicates for each product (XX), but I can't seem to work it out. I've tried the MS help function, but unsure of the actual formula I need to be using.
I have my dummy data, and I have (what I think) is how I want the data to be shown. My friend uses Google Sheets, but I prefer Excel. I am trying to convert the code because I am a stickler for excel. Typically I can convert codes some easily, but this is way beyond me.
For Column A: I want to create a list on sheet 'Setup!' based on ids!D2:D="yes". If that list has duplicate entries, I would like only the first entry to show up, but for the next entries I would like the cell to be blank. (this is important for the next step) For Column C: I want to have the corresponding dates go with the name entry. For Column D: I want to have the notes go with the corresponding date entry. (I believe I can manipulate Column C's code to do Column D myself).
I am also going to upload a data sheet, and an expected results sheet.
I am working on an email marketing project and i have a small problem. I have two different email list. One (List A) is a large list of potential leads. The other (List B) is a list of leads we are not supposed to market to. I need to delete every lead on List A whose email address is also in List B, so that we do not send unwanted emails to our clients.
The best way i have to do this so far is to go through line by line, which is very impractical.
In case it matters here is out list format. Each list has 10,000 + leads. Each lead occupies a row. The row stretches across 13 columns and each column holds a different variable about the lead (names, state, email address).
The attached file (a copy of my main one) has a list of our engineers, and what stock they carry. The stock parts are the 64, 65, 66... numbers.
I need to create a list from this (as underneath the main table), for all instances where there is a 'Y' in the columns next to each engineer. So if an engineer has 3x pieces of stock, they need to appear in the list 3x times. If they have 1x piece of stock, they appear in the list once.
Name --------- Jones, Bob Jones, Bob Jones, Bob Smith, Mike Smith, Mike Smith, Mike Calai, Dave Calai, Dave Calai, Dave Etc...
What I want to do is take the entire list and create a distinct list that I can use in a named range then use a validation list to have a drop down within a different tab.
In my attachment is what i'm working with. In the first worksheet tab, "NOTBA 10 09", i need it to count what's in the other worksheets, minus the duplicates.
So C4 should be referencing to H3:H21 in the 04-Edmonton worksheet. The number it should be showing is 11, however it's counting 15 because there's 5 entries for CDAU034479. Is there a way for it to basically count what's in that column, minus the duplicates?
I am using a CountIf formula to flag duplicate entries in a column of data, in a Column I have "=COUNTIF(P:P,P1)" It returns a number equal to the number of repeats of the data in call "P1". I then sort by that column to isolate all rows which have duplicates in Column "P". The cell has "1" if there are no repeats, "2" if there is one repeat, "3" if there are two repeats, etc. What I would like is a formula which would put a "1" in the cell the first time it sees data, then something else when it sees repeats. That way when I sort, ALL the repeats will be together and can be deleted. Is that possible?
I have a column with something like 20 thousand rows. I would like a formula that basically checks the column for duplicates and numbers the sets of duplicates. So if there are 5 cells that are all "Server10" I would like for the formula to place a one next to all of those cells. And for the next set of duplicates it finds I would for it to place a 2 next to all of those cells. Essentially my end goal is to sort the sheet so that all of the matching duplicates are all together. There might be some simple filtering method here that I'm overlooking but if there is..... I'm overlooking it lol. And to be clear I don't want to eliminate the duplicates, I just want them grouped together.
I've attached a very simple example of what I would like the formula to do. The cells in the right column would be the result of the formula.
I have columns from A to AA and need to count the duplicates in at least 2 columns and accordingly need to give them values on the basis of their ratings (A, B, C, D) and type of work (book, article etc) given in yet other columns.
I tried COUNTIF, but that does not work for multiple perimeters I tried SUMPRODUCT, =SUMPRODUCT(($G$4:$G$3000=$G28)*($AA$4:$AA$3000=AA28)), but that only gives a multiplication of my input without the possibility of giving new values to the output.
I tried AND(IF( but that did not want to work either really...
I think the solution is ifsum, but I do not know how to use it in this scenario...
I know this all sounds a bit blurry, so let me attempt to make it more concrete.
Author Title Year Rating Type Desired outcome P Marx I 2005 I A I Book I duplicate A Hegel I 1923 I B I Article I - B Kant I 1674 I D I Book I - A Derrida I 2005 I D I Monograph............................
Is there anyway to make this work without having to enter a specific range. For example I want to count the duplicates in column U, but don't want it to count blank cells.
Here is my formula right now, and it works, but it counts all of the blank cells in the row as duplicates. How can I stop that?
i need to put serial no. without considering duplicate value.. i have ' N ' of value somewhere data is repeating when i am giving serial no i want to ignore duplicate value ...example given ... please see attached file..
I have teams in rows and players columns with their ID and then name so B2 would be 444 and B3 would be Johnson and B4 would be 429 and B5 would be Smith
What I want to do is run a formula that counts the duplicate ID's not names and then shows them in a list like below
Duplicate ID, Number of times in worksheet 444 , 23 411 , 21 211 , 15
I need a one formula solution to compare two columns for duplicates and count the number of matched pairs. Need to ingore blanks. Can have matched numbers or letters. Asterik denotes a blank (empty) cell. Also only consider the first matched pair if there are more than one matched pairs.
Col A Col B a b a * b c c d y x f f z f z
In this case I only want to consider a:a, b:b, c:c and f:f, resulting in the count of 4.
I have data in two columns where column A is a name and column B might be a synonym or might just equal the value in column A. I want to count duplicates if cell A2 matches any other cell in column A or B, unless the only match is with cell B2. If cell A2=B2, but also matches cell A7 then it should be counted as a duplicate. I've searched through a lot of the threads and found a lot of useful information, but nothing that seems to do what I need.
Good Morning Peps (Oh thats for users in Western Europe, got to get this right!)
I have these 12 columns (Well there is 12 Columns but this forum's HTML is not showing the last two lol, would much easier if Office Web Components was able to run on Firefox): ...
Below is a range of data i am working with, i am trying to create a formula that will count the number of different entries in column A for each different entry in column B. ie how many differnt values are there for "Packing"
008003PICKING MISTAKE 008042UNFIT FOR PURPOSE 008035PACKING 008035PACKING 008035PACKING 007960CHANGE OF MIND 007986PACKING 007986PACKING 008050UNFIT FOR PURPOSE 008070CHANGE OF MIND 008070CHANGE OF MIND 008074CHANGE OF MIND 008074CHANGE OF MIND 008074CHANGE OF MIND 008074CHANGE OF MIND 008074CHANGE OF MIND 008086PACKING 008085PACKING 008085PACKING
I would like to find the values and count duplicates on a sheet. Here is my example:
A1 A2
Organization1 Organization2
XXY BBA
ACC XXY
BBA ABC
I would do countif, but I do not know my values (this is from a report that I ran from a database) and it would take too long to find all the individual values every time I needed to run this report. So this is what I am looking for (a graph would work too).
Is it possible that, once filtered, you can count the amount a filled in cells in a column range...BUT! These cells are ID numbers for stocks, so CAN contain duplicates which represent accounts, Therefore, any duplicate will be counted as 1...
eg
12345325 12345325 435ghfdhy 5464OKff SEDDONF4
[Code]...
As we can see here there are 14 lines of data but only 7 make up the dataset
so if X was the variable assigned to this it would = 7
Is this possible, in a loop or some sort, Would VBA hold all the Instances in its memory???
I am trying to teach myself something new, say I have a list of numbers in two rows and I want to count the numbers and have it tell me if there are duplicates and highlight the duplicates. I got the part about getting Excel to tell me if there are duplicates but I can not seem to figure out the conditional formating part. I uploaded a sheet.
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
This code counts all cells with a value from column B1:B:10, C1:C10, D1:D10, E1:E10, F1:F10, G1:G10 and H1:H10. And then give the cells of the 3 lowest outcomes a blue backcolor. The problem is that the code also colors any duplicate outcomes. How can I let the code skip any following duplicate outcome(s)?
