Duplicate Information To Be Removed From Different Rows?
Sep 24, 2013
I'm busy cleaning information from an excel spreadsheet. Not all the columns are populated. e.g. Row 17 will only have an email address in column E whilst row 18 will have a name, surname, company and email address (same as in row 17).
Using the remove duplicate function, removes row 18 and thus leaves me with the row that only contains the email address.
What would it take for me to be able to remove the rows that do not have the additional columns populated?
View 2 Replies
ADVERTISEMENT
Nov 24, 2009
I have a sheet with over 1000 rows showing me every employee's benefit choices. It has multiple rows for each employee - one that indicates their health option, one that indicates their dental option and so on.
I'd like to combine them so I have one row that has columns for health, dental, etc...and then their options would be listed all in the same row. I've attached the original with the solution I'd like pasted below. I'm sure this isn't difficult but I'm having trouble figuring it out.
View 2 Replies
View Related
May 24, 2006
I have a worksheet with duplicate but necessary information. in column A there is a Name and in column B there is a number. like...
John 43
John 6
John 23
Karen 54
Kathy 12
Kathy 23
I need to add all the numbers up for one name and then delete the un-needed rows. so it ends up
John 75
Karen 54
Kathy 35
View 5 Replies
View Related
Mar 4, 2014
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23
Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
Attachment 301726
View 4 Replies
View Related
Dec 12, 2009
I have a module that imports data from a text file (120000 rows), filters and removes some unneeded (85000)rows and then puts the data in a designated workbook.
Problem: when filtering by some criteria it for some reason removes a row off the final file for each criteria. I have a total of 23 filtering criteria. I have attached a file for exemplification. You can run the module and import the file, it will be without first 23 rows, even if non of the deleted rows contained the filter criteria.
example.txt
View 11 Replies
View Related
Feb 27, 2009
I have pasted an excel report that I need to find any duplicate information. For example they are seperated 2 sets of 4.
For example:
Column A and B is one set
Column C and D is one set
With column E is where I need a way to flag duplicate information with "yes" or "true" on that set
The same would need to have happen on the next column G thru J then M thru P finaly S thru V
******** ******************** ************************************************************************>Microsoft Excel - compare-v1.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutW1=ABCDEFGHIJKLMNOPQRSTUVW1Internet & DMZ Idns db-whipNamed.confInternet & DMZ Idns db-metroNamed.confDuplicates Internet & DMZ edns db-whipNamed.confInternet & DMZ edns db-MetroNamed.confDuplicates Integration idns db-whipNamed.confIntegration idns db-metroNamed.confDuplicates Integration edns db-whipNamed.confIntegration edns db-metroNamed.confDuplicates2zone 0.IN-ADDR.ARPAfile db-whip/db.0.0.0.0zone 0.IN-ADDR.ARPAfile db-metro/db.0.0.0.0 zone 0.IN-ADDR.ARPAfile db-whip/db.0.0.0.0zone 0.IN-ADDR.ARPAfile db-metro/db.0.0.0.0 zone 0.IN-ADDR.ARPAfile "db-whip/db.0.0.0.0"zone "0.IN-ADDR.ARPAfile "db-metro/db.0.0.0.0" zone "0.IN-ADDR.ARPAfile "db-....................
View 9 Replies
View Related
Feb 8, 2014
I have two different spreadsheets with customer leads on them. I want to ensure that I don't duplicate my effort by contacting the same customers again, so I want to remove duplicates, but not in the usual way. If I simply use the remove duplicates function I will be left with a combined list of customers, some of which I have contacted and some of which I will not have contacted. I want to be able to search the second spreadsheet so that it removes the information that also exists in the first spreadsheet without combining the two so that I am left with a list of only customers I have not contacted already. I understand I may have to copy and paste, but I need a way to ensure the two lists are able to be separated once the duplicates are gone.
Someone said something to me about an "IF" function
View 1 Replies
View Related
Jan 7, 2014
I am trying to separate the data in my spreadsheet. I will attach the spreadsheets and explain below. T
The column labeled number has entries that include commas. I need each number entered separated and put into its own row and I need the remaining data from the original row duplicated to the new rows.
Here is an example using the first entry.
Original entry
November Wiseman 1,2,11 Bluen Medical CA
How it needs to be separated
November Wiseman 1 Bluen Medical CA
November Wiseman 2 Bluen Medical CA
November Wiseman 11 Bluen Medical CA
View 7 Replies
View Related
May 27, 2014
I have a piece of code that consolidates data onto one sheet.
Once the code has copied everything over, I would like it to also duplicate the Category field.
There is no option to add the duplicated column in the source information.
Here is the consolidation code:
[Code] ......
View 1 Replies
View Related
Jul 3, 2012
I have a file with 2 worksheets:
The "On" worksheet represents ALL of the students that LIVE on campus (freshman, sop****re, juniors, seniors)
The "JS" worksheet represents all the juniors and seniors going to school here
I need to know which of the JS live OFF campus.
So.....I need a macro that will compare the JS worksheet to the On worksheet and create a new worksheet called Off and populate it with those JS that are not in the On worksheet.
The "On" worksheet contains:
A B
Last First
The "JS" worksheet contains:
A B C D E F G
Last First Street Street2 City State NY
The new "Off" worksheet should contain the same columns as "JS"
View 3 Replies
View Related
Dec 11, 2008
I have a spreadsheet with 3300 rows. In column A there is a list of company names and in column H there is a corresponding Sales Rep name.Column A has many duplicate company names. I would like to run a macro that will find the a company name and then delete all the rest of the rows that contain that same company name.
Attached is a sample of that spreadsheet.
View 5 Replies
View Related
Nov 1, 2007
I feel as though I have spent enough time searching the previous posts to ask this question.
I have a 4 column sheet, column B has many cells with identical data. I want to delete all the rows that that have duplicate data in column B.
COLUMN A= Car Makers
COLUMN B= Models of cars
COLUMN C= color
COLUMN D= owner
I want to end up with rows that each contain unique info in COLUMN B.
View 9 Replies
View Related
Apr 8, 2014
I have used a vlookup code from a past post (Vlookup multiple values in one cell) in VBA and successfully retrieved my information to one cell.
However, I would now like to remove any duplicates in this cell; preferably while running the same code.
--vlookup code
Public Function mVLOOKUP(mlookup_value As Range, mtable_array As Range, mcol_index_num As Long)
Dim r As Range
[Code].....
View 2 Replies
View Related
Jan 5, 2004
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)
[Code]....
View 9 Replies
View Related
Jan 5, 2004
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.........................
View 9 Replies
View Related
Jun 13, 2014
I have data in excel that has some information the same with a unique field. See below:
Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
[code]...
I want it to consolidate all of the like information but add on the codes in separate columns like this:
Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
Code 2
Code 3
Code 4
[code]...
Also, sometimes the same code is used multiple times but i want any duplicate codes to show as separate codes.
View 5 Replies
View Related
Apr 14, 2014
I am new to macro and just trying to learn. I have a spreadsheet with 20000 rows and 8 columns. I am trying to identify equal rows based on the values of columns C, D, E and F. then I need to separate equal bunches with a blank row. Then I need to copy the ID number from the first cell of column B of each bunch and paste it for the rest of the rows in that bunch. I have written the following code but this does not put the ID of the first cell in a bunch for the rest of the rows in that bunch.
[Code] .........
View 10 Replies
View Related
Oct 30, 2013
I have a spread sheet with values in the area of A1:H834
In column H, I have number values from 1-7.
Essentially that number value means that the values in the row are duplicate.
So, for example, if H2 has a value of 4, that means that $A$2:$G$2, really should have an additional 3 rows underneath with the EXACT same data in each cell, however, the way the sheet was created, was to remove the duplicate values and just indicate in column H, the number value of how many duplicates $A$2:$G$2 really is.
I need to unpackage this and create what it was originally. What type of formula can I use, to look at the value in H2, and then insert underneath that number of rowes with the exact same data as A2:G2 and do the same for the remainder of the table all the way down to A834:G834
View 1 Replies
View Related
Feb 2, 2014
The below code compares the Data in a Field that must be set and collect the duplicate Values in a second Worksheet.
The thing I want it to copy the rows, when a duplicate is found in Col A. editing the code below:
Original Sheet:
"A" "B" "C" "D"
Teil1A11000
Teil1B21001
[Code]....
View 7 Replies
View Related
Oct 21, 2008
I have a string which contain spaces in between. I want all the spaces to be remove.
Example: Say i have a string "IN DI A" and i want it to set as "INDIA".
Can some help me on this using VB.
I have 100 string to which spaces are to be removed and are to be kept in the same column.
View 11 Replies
View Related
Feb 20, 2008
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.
My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.
So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
[Code]....
My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.
View 4 Replies
View Related
Feb 20, 2008
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5
Should be processed and come out like this:.......................
View 2 Replies
View Related
May 2, 2008
Is it possible to check if a password is used to unlock a worksheet.....
View 9 Replies
View Related
Jun 26, 2013
Everyone always want to get rid of duplicate rows.
Is it possible to create duplicate rows of all the existing rows?
View 5 Replies
View Related
Jul 3, 2014
Please find the attached file : <removed repeated characteristics with 1 criteria.xlsx>
I want to remove the repeated characteristics and it is to be plotted once also `XXX' should be removed and should not present anywhere.
View 14 Replies
View Related
Feb 21, 2007
I have MS office 2003.
When I open new excel sheet, It opens with " Getting started " office online help page on right side of sheet.
I removed it by unchecking ViewTool barTask pan. but next time I open new sheet it is still there.
View 9 Replies
View Related
Nov 17, 2009
So this forum has helped me in the past develop a standard deviation that removes max and min from the data set.
=STDEV(LARGE(E5:AB5,ROW(INDIRECT("2:"&COUNT(E5:AB5)-1))))
Would if be possible to adjust this formula to ignore N/A's?
View 6 Replies
View Related
Feb 15, 2009
I am importing an existing HTML table into excel, using the Data > Import Extrenal Data > HTML Query.
The data table is being imported correctly, however, the first column is a set of claim numbers (alpha-numeric).
Excel is stripping away the leading zeroes.
e.g. Claim numbers are of the form:
Actual: 000005847161 Excel Conversion: 5847161
Actual: 020000008760WC01163 Excel Conversion: 020000008760WC01163
The second one (which is alphanumeric) is being treated correctly, the first one is not.
The purely numerical claim numbers can of variable length to each other e.g. one could be 15 digits, another could be 12 for example.
Is there any way to preserve the original values including to have the claim numbers being imported as their EXACT values?
View 9 Replies
View Related
Jul 4, 2007
I'm some cells with validation but when I copy some data (whose cells do not have the validation), and paste onto the cells with validation, the validation is no longer there.
How could I ensure that the validation is still there after the Copy-Paste action? Since the spreadsheet is used by multiple users, I can't force the users to do a Paste Special.
View 4 Replies
View Related
Apr 16, 2009
I had to remove a lot of pictures in the xls file in order to attach it, (since the original is about 5.3mb) but it worked out. When you move your arrow over the titles in column A a picture shows up thanks to a code. When you change the status in column G, the color changes together with the title in column A, as it should.
View 4 Replies
View Related
