I have to compile spreadsheets as tables of various values. One column in particular is a column of ID numbers and the worksheet is very very long. I need a method for checking this one column for duplicate ID numbers.
When I make a pivot table I wanted to know why it is showing duplicate names. Here is an example workbook below. It should group all the same names together. However it isn't doing so. Anyway around this problem?
I have a worksheet that has 3 duplicate values in a particular column, I need a macros that will highlight two of the duplicates row and then another macro to delete the entire row. The duplicate element are in column R. find attached worksheet.
I am using the following macro to insert the word "Duplicate" in the first blank column next to a duplicate row. My data is sorted by the first column. Data Example:
12345 a 12345 a DUPLICATE 11111 b 23123 b
Here is the macro I am using and it does not work. It marks the first duplicate it finds then goes into an infinite loop. Any Idea where I went wrong?
Sub MarkDupes() x = ActiveCell.Row y = x + 1 Do While Cells(x, 1).Value <> "" Do While Cells(y, 1).Value <> "" If (Cells(x, 1).Value = Cells(y, 1).Value) Then Cells(y, 3).Formula = "Duplicate" Else y = y + 1 End If Loop x = x + 1 y = x + 1 Loop End Sub
I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.
Col A 100 101 102 105 100 101 102 105
Col D 5 4 2 4 1 2 3 1
After my code is run, I need for my spreadsheet to look like this
Col A 100 101 102 105
Col D 6 6 5 5
I have some code but I still need to do a considerable amount of tweaking to it. Currently my code is only deleting the duplicate values in Col A. I am having difficulty summing the values in Col D as well as deleting the entire row.
Here is my code thus far....
------- Public Sub FindDuplicates() For RwCnt = 1 To (Worksheets(1).Cells(65536, 1).End(xlUp).Row) SrchValue = Worksheets(1).Cells(RwCnt, 1).Value If Len(Trim(SrchValue)) > 0 Then With Worksheets(1).Range("a1:a" & Cells(65536, 1).End(xlUp).Row)
I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this.
Col A 100 101 102 105 100 101 102 105
Col D 5 4 2 4 1 2 3 1
After my code is run, I need for my spreadsheet to look like this Col A 100.........................
I am using dglinfo with if statements, and I know that the spreadsheet is updated but the only way I can show the value in particular cell is by hitting the fx. Is their a way to show all the values at once instead of going cell by cell?
I have a spreadsheet with a lot of rows and there are three columns that can have a one of two values in it. These values are constantly changing from row to row, not just like a yes or no sort of thing. However 80% of the time they are the same values in all 3 columns, but I want some way of quickly identifying when there is a difference between column G, I, & K which are my three column names. So is there a way of conditionally formatting this to make a difference highlight in red or something like that?
Did you try filtering the list see Debra Dalgleish's index page [url]
--- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: [url] Search Page: [url]
"BorisS" <BorisS@discussions.microsoft.com> wrote in message news:1DD652B0-CDC0-43D4-9D46-7A3616FF7255@microsoft.com... > I am getting lots of values in my table which are zero. I have a calculated > item, and don't know if that is the problem. How, if at all, can I get any > line which has all zeros to not show up? the table gets excessively long > with them, not to mention the calculation takes forever. > > --
I made a chart to show performance above and below average for sales teams.
When team is selected with no salesperson, value should be 0.
Employee selected and will show there week by week trends above or below. Seemed like a good idea.But the zeros (team level) are being displayed as plus or minus bars which isn't right and the labels show "0".
I find the pivot table is actually showing strange values if you click in the cell even though it displays a 0.Notations positive or negative like 3.80647894157197E-16.
The underlying data is an average formula and selecting invidual criteria, I show there are no anomalies.
I Spoke too soon here, I am getting value errors, how to do it with conditional formating. I tried if cell value is =to #DIV/0! then white (dosent work). Richard this dosent seem to work mate =IF(SUM(F6:V6),"",AL6/SUM(F6:V6))
I have the Range B:F for "Values MJ" and columns G:K with "Values RT". In column A there is a letter for each row but some letters could appear more than once and I want to have only unique letters in column A and merged the Range B:F for "Values MJ" and columns G:K with "Values RT" in the same row for those repeated letters.
Original data:
A B C D E F G H I J
[Code]...
If a letter only have values in range B:F print "Missing" in range G:K for the same row. If a letter only have values in range G:K print "Missing" in range B:F for the same row.
I'd like to delete the row of the letter if the repeated letter has values in B:F, since letters for ranges B:F always appear after the letters that have values in range G:K and since I'd like the output in same sheet.
When I update a cell (change A1 from 2 to 3), any cell that references that cell correctly changes its value (B1 = 2*A1). However, the screen will show the new value in B1 (6) over the previous value (4). At first I thought it almost looked like a strike-through, but then I realized the old value and new value were simply stacked in B1.
If I scroll the screen away that cell and go back to it, the correct value will now show without the stacked values. I'm not having this issue in any other program (Open Office), and I don't seem to be having any kind of stacking issue in any other Microsoft program.
I am having problems creating a chart off of the following data. Each of the cells which have a numerical value are v-lookuping from another sheet. It needs to remain this way as when I switch brands (through data validation), the numbers will update and the chart should as well. Right now, the chart shows these values as zero. I have done this a million times in excel 2007, but now with 2010, I keep having this problem
I want cell B4 on sheet 3, to show the value of cell B4 on sheet one ... only if there is a value in B4 on sheet one then i want the rest of row 4 to be copied to sheet 3 also.
HOWEVER if the value in cell B4 sheet 1 is BLANK/EMPTY then i want the full row to be over looked and the next row to be checked (B5 all the way to B30)
Once B4-30 on Sheet 1 has been checked and only the rows showing a data/value have been copied to sheet 3, i then want the same process to start again on sheet 2 - Again only the rows showing data/values to be copied.
I'm trying to do a line chart showing two teams and their cumulative scores over the period of a game. I've attached a sample with mock data. Is it possible to change the x-series of the charts to display custom time intervals like 0 min 1 min, 2 min, 3 min, 4 min, etc? Currently the x-series only has labels for each specific time there was a change in value. I've tried to change the scale but the options that I want aren't there.
I'm trying to rank the values in cells S32:S38 in ascending order while ignoring zeros and negative values. I also need to rank duplicate values with a unique ranking, so that no ranking value is repeated. I tried the formula below, but the ranking values start at "2" instead of "1" and I can't figure out how to fix it.
How do you write an excel macro that looks at the number in the first column (day #) and finds all the duplicate id#s in the second column that are in day 1and adds the amounts together in the 3rd column then writes the first column number (day#), second column number(id#) and the third column (sum of the amounts of duplicate Id#) to an new worksheet. Then the macro would loop through day #2 and do the same thing. Notice that the values in the id column are unique in this data set below this is how I would like the data to look. I have accomplished this in a pivot table but my problem is I need a cvs file to export the final data into an external database which is why I need a macro.....
I'm trying to place a pivot table in an existing sheet in order to have a list of names next to a P&L. I've got several criteria to filter the names, but when I apply the filters to the pivot table, not all the people show up. The weird thing is that the total at the bottom of the pivot table, which is a simple sum of time in a given month per person, calculates the correct number as if all the employees are there.
When I do the same table in a fresh sheet, in the same file, it shows correctly. It's only when I try to put the table in an existing sheet that it abbreviates the list.
A1 B1 Apple Sweet Apple Juicy Apple Worm ridden Apple Round Apple Cooking Apple Grannies Pear Green
[code]......
and would like to combine all of the duplicates from A1 into one field, with all of the options in B1 combined into one cell (separated by a |). So the above table would read:
Apple Sweet|Juicy|Worm ridden|Round|Cooking|Grannies Pear Green|Tasty|Mouldy Orange Tangy|Tasty Peach Rare|Forbidden|Expensive|Squishy
I have a range of times, and with the whole range I cant have any duplicate values, if they are duplicates I would like to add 1 second until all the values are individual.
i currently have two columns (e.g. A2:A400, B2:B20) and i am trying to match all the values in B to A, displaying TRUE or FALSE (if the values from B don't appear in A) in column C.
i have tried using the various formula for duplicates, substituting what i thought would be the correct formula, but to no avail.
I have a list of values (some are duplicates) which I need to rank.
The problem arises where I have more than one of any rank, the next rank skips a number. I need to have a list of sequential numbers and therefore can't have any numbers missing.
I have an interesting problem where I am trying to display a list of top ten ranked items and I have multiple items tying for a rank, creating two or more values for one rank.
I have a table that shows the ranking, numbers 1 to 10. I'm using a vlookup formula to find the rank and return the corresponding name from my data table.
The problem I have is there are two that are tied for 3rd place, so I have two ranking at three. The current table looks something like this:
1 Warren 2 Stan 3 Mike 4 #N/A 5 Dan
I can change my table so that the numbers on the left are dynamic so it will display the number 3 twice if there are multiples of the same ranking, but when I do my lookup I'm still stuck.
Is there a way to return the second "3" on the lookup? Or is there a better way to solve the issue?
I have multiple columns / rows of data, some of which are duplicates.
Column S is a concat of columns A:R where this data is stored, and is sorted alphabetically.
I'm looking for a way using VBA to find duplicate concat rows by cycling through this list that is already sorted. I'm interested in moving down this list, 1 by 1, and if current cell = cell above, delete the data in columns A:P of that row, then delete the cell data in column R of the cell above the current cell.
So for example, if I have sorted data in S8:S14, and S9 = S8, then I would like to delete A9:P9, then delete the data in R8.