Mark Duplicates That Are Identified In A Seperate Column
May 7, 2007
I am looking for a way to ease a 24,000 line duplicate list into showing just the files from a specific folder/subfolder and its dupes. I have marked the files that reside in that folder by adding a column and entering a "1" in it. What I am looking for is a way to add that "1" to the others of that unique numerical ID duplicates, so I can sort and trim the list to just those files. End result is to show files in one folder and the locations of duplicates of those files elsewhere for eventual cleanup.
Very loosely, if C2=C1 and B1=1, then make B2=1 (and so on)
(A is blank and only there in case a formula needs input there)
A B C
110121
10121
118134
18134
18134
124232
24232
I need to find duplicates subnet information in row F and mark the words "Duplicate" Then I need to do the same with Row K. I need to find any duplicate subnet masks.
The reason for this is that I merged two orginizations into one spreadsheet and I need to find the duplicates in Company A and Company B's subnet and mask information.
I have a simple spread sheet, as shown below. Each line is on it's own row, and all the info on each line is in the first cell. (A1,A2,A3 etc....) What I require is a macro that will look at the "Code" line, take note of the code and if it finds a duplicate code number, mark the duplicate code with (1)....say up to (5).
In the case below, it would flag the second 07edr code and change it to 07edr(1). If there was a third 07edr code it would mark it 07edr(2) etc.
It needs to look at all the codes as it's possible there will be more then one set of duplicates.
I have two columns with dates. Colum A has the date of the deadline of a document while column b has the day it was sent. Column X will display the difference if its a positive integer ( i.e. if the document has been sent after the deadline). Now i have another column Y which displays the month as an integer of when the document was actually received.
Now i need a counter which will count the number of instances a positive integer is registered in column X according to the month in column Y. I have been trying everything but cant figure a simple way to do it. Im doing this so i can be able to see how many documents are sent after the deadline per month.
In the attached file, details sheet contains multiple instances of project with associated costs for each of 2006, 2007 and 2008. What I need is a formula (preferably) or a VBA that select distinct project names and populate column B of summary sheet so I can do a sum if. The problem is the project names changes dynamically every week and they are practically in hundreds.
i have a excel file i need to markup by a 10% value. It is the L column starting at row 2 till a varying number of rows. i need to mark up all of the rows in the spreadsheet. i do not know how to make it repeat.
This is the formula =L2*1.1(i think its the right one) but i do not understand how to make it repeat. any help would be greatly appreciated.
I need the value to be replaced with the marked up value.
I have many wordlists, which have multiple entries in one column. I would like to mark up entires which appear more then once with numbers 1,2,3....The number should be put at the end of the word or ath the beginning.
Before Macro:
Column A
Love Love Long Lonely Lonely Living Living
After running the macro:
Column A
Love 1 Love 2 Long Lonely 1 Lonely 2 Living 1 Living 2
I have columns with titles at the top, and with numbers in the below cells.
What I would like is a formula for a column seperate to these that firstly, looks if there is a quantity in the relevant cell on that row, secondly takes the title (top cell) of that column, and thirdly puts the number in the cell on that row after the title:
A_____B_____ C_____ D_____ E______ F 1_____UK____ Ger___ M/E___ Rus_____ Nor 2_____80_____250___ 300___ 350_____200 3____ 60_____200____350____350____ 150
So in cell A1, I would like a formula that reads the subsequent cells and their titles, and gives a one-cell summary of the data seperated by commas. So in A1 it would say:
UK 80, Ger 250, M/E 300, Rus 350, Now 200
And in A2: UK 60, Ger 200, M/E 350, Rus 350, Now 150
I have a goal set for the year. With conditional formatting, each month will be set to either red or yellow based on where it stands in relation to the goal. I would also like to show an increase or decrease in productivity for each month. I tried to use conditional formatting with the formula:
=IF(C2=D2, "", IF(C2<D2,"↗","↘")).
The formula works on its own without the conditional formatting so I am not sure what I am doing wrong. I've attached a sample with all the conditional formatting and formulas in place.
See attached spreadsheet. I need to have all the data which is held in 1 row in column A seperated into its own column automatically. The order i need the data to be in is as follows
Date(pink), Time(red), Racecourse(green), Race Type(black), Class Of Race(purple), Going(blue), Prize Money(yellow), Distance(grey), Number Of Runners(brown). the text in bracket is a key for the info for your ease of use.
I have a spreadsheet with two sheets, 'Sheet0' & 'Sheet1'. In 'Sheet0' I have a column named 'A_Sheet0' containing a list of numbers from Rows 2 - 50. In 'Sheet1' I have 3 columns, the first containing numbers 1 to 50, the second, 'B_Sheet1' containing a list of ascending numbers and the third is the column of interest. This column entitled 'Nearest Value' must read the corresponding value of Column 'B_Sheet1' then find the closest number to that from the numbers in 'Sheet0', column 'S_Sheet0'.
I have included a sample file with this thread, and in the 'Nearest Value' column I have manually input some of the nearest values for your information.
How can I delete an entire row based on seperate column data? Example: I want Excel to look at cell A2 and cell E2. If the values are the same I want Excel to delete row 2.
If the columns A, B, and C were constant and never changed and only column H changes from job to job, how do I: SUM the TOTAL number of pieces (pcs per X qty) if the value in column B is "A"? I need to do this without creating a seperate mutliplication column for the sum of the pieces. It all needs to be done without adding any new columns.
How do I automatically insert rows given the identified quantity to insert and copy the contents of the previous item on the insert rows. Does that make any sense
a1 - abc a2 - def a3 - ghi
I need to insert 10rows below a1, and copy a1 across the inserted rows. Now I have a ton of this case currently do it manuaaly, it might take me a lifetime to complete.
I have an index page with hundreds of worksheets (poor spreadsheet design i know) I am looking for a button to go next to the sheet name on the index sheet, read the cell in the button's macro code and when pushed go to that sheet's location. I can easily make a button to go to one sheet but with hundreds of sheets this becomes a monotonous task. Example
Index Sheet
Sheet 2 (Button) <-- Button reads the text, takes you to the "Sheet 2" location Sheet 3 (Button) Sheet 4 (Button Sheet 5 (Button)
I am developing some code to extract selected worksheets to a new workbook, and provide the data in a "standalone" form where certain formulas have been converted to values etc. As part of this project, hidden columns are made visible, then data is pasted as values on a row by row basis, then the previously hidden columns are deleted. Here is the code that unhides the columns and identifies those columns as ones that it needs to delete later on:
'Expose hidden columns and keep a list For i = 1 To 200 Range("a1").Offset(0, i - 1).Select If ActiveCell.EntireColumn.Hidden = True Then colnum = ActiveCell.Column ActiveCell.EntireColumn.Hidden = False If HideCOL <> "" Then HideCOL = HideCOL & "," & colnum Else HideCOL = colnum End If
End If
Next
The macro then goes about its business doing this and that, and at the end comes back to delete those offending columns whose numbers have been stored in the variable "HideCOL".
'Delete columns that were hidden If HideCOL <> "" Then Dim colArray As Variant .............
I need to copy all the work sheets into one single work sheet (mastersheet). The source work sheets are having same column structure. The condition which i need to take care of is that after column 3 if at all there is any data till column 10 then in the destination mastersheet these should be copied in different rows with first two columns repeated. I need to do this using VBA macro.
I am trying to determine how to great a formula for cells E5, E6 of the attached (in pink) that says if the value of the cell above (E4) is equal to or Greater than the value of cell E10, the cell will perform the formula that I already have in E5, E6.
But what I'm trying to do is create a 5 year investment plan whereby the worksheet automatically inputs those values for me if the balance is above our minimum reserve. That way I can play with the default assumptions and see how it changes throughout the worksheet.
I have designed a spreadsheet and i want a seperate worksheet (sheet3 for arguments sake) to retrieve customer data from worksheet 2 - The data I required is the customer data currently contained on columns A - H and there are around 50 rows. (A2 - I51). I want the seperate sheet to identify entries that have today's date in column I and then list them in Worksheet 3.
Im having difficulties with the syntax for retrieving the data from a seperate worksheet. There may be several entries for the same date and I want to the seperate sheet to report all customer data in worksheet 3? Also, if the date falls on a weekend I would like to retrieve any data for the weekend on the Monday so all cases can be reviewed.
I love this forum, and am usually able to find the help I need without bothering anyone However this one has me stumped and I wonder if anyone can help. It feels like it should be a fairly simple solution, but they can often be the ones that are most eluding LOL!
I have two columns; in column A are incoming telephone numbers and in column B are the date and time the calls were made. (I've put a few hashes in column A just to maintain confidentiality of the numbers, but in reality the cell is formatted as text in order to maintain the leading zero, and entries will follow the format 01234567890)
Some numbers only call in once, I need to identify them as only called once.
Some numbers call twice, if they do I need to be able to show time it took between call 1 and call 2.
Some numbers call more than twice. For each successive call I need to be able to show the time since the previous call.
In my mind, the results table would need to look something like this:
NumberTime of callTime between 1st and 2nd call Time between 2nd and 3rd call Time between 3rd and 4th call 0##6270####01-Mar-2009 00:01:44Only called once0##6271####01-Mar-2009 00:03:0200:00:020##6272####01-Mar-2009 00:16:33Only called once0##6273####01-Mar-2009 00:30:49Only called once0##6274####01-Mar-2009 00:55:4700:10:3000:01:250##6275####01-Mar-2009 01:08:3600:03:050##6276####01-Mar-2009 01:13:45Only called once
I'm looking to populate my Helper Column with all duplicates from my Data Column. Please note that my helper column needs all instances of a duplicate.
Data Column Company ABC Business DEF Corporation GHI Business DEF Company ABC Business DEF
Helper Column Business DEF Company ABC Business DEF
The formula needs to be dynamic and it will need to be able to be copied down.
I need to remove the duplicates under column B for each item under column A and I can't seem to figure out how to do it.
I'm using Excel 2007; I would prefer a VBA solution, as I have to do this on a monthly basis. However, if a formula is the best way to go, then that's just fine. Either way, I'm preparing the data for a pivot table. I am unable to post the actual document in the interest of data protection.
Essentially, I'd like to turn this-
_ A B 1 Paris Bill 2 Paris Bill 3 Paris Mike 4 Paris Derek 5 Paris Derek 6 London Mike 7 London Bill 8 London Mike 9 London Bill 10 London Derek 11 Tokyo Derek 12 Tokyo Derek 13 Tokyo Derek
into this-
_ A B 1 Paris Bill 2 Paris Mike 3 Paris Derek 4 London Mike 5 London Bill 6 London Derek 7 Tokyo Derek
In other words, each city should have only one instance of any name that accompanies it; not all names will accompany each city.
I have three columns (9, 10, & 11); text, currency, and text and I am trying to move them from seperate columns on Sheet2 to one column with forward slashes "/" between them on Sheet3. The attached code does this, however I lose the dollar sign and commas.
