Merging Duplicate Rows With Unique Data?
Jun 5, 2013
I have attached a before and after image of what I am looking to accomplish.
In the before image, you can see that there are 3 rows of data
- a header
- a repeating model number (in column F) with accompanying data (values in columns G - J are the items of interest)
In the after image, you can see that I took the 3 rows of data and turned it into simply two rows of data
- a header
- the repeating model number
What I did however in the after image, as can be seen, is I took certain data values that appeared on the repeating row (columns G - J), and included them as PRICE, COST, BEGIN, and END values in new columns on row 2.
Is there a way to accomplish this via some functions or maybe even a VBA script?
View 3 Replies
ADVERTISEMENT
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
Feb 18, 2014
I need to merge rows with duplicate values in column A (Patient Name being the most important one), with columns B, C, & D usually having different isolated values as well. Columns E, F, G, H, & I are date columns, but the data is always going to be the number 1, meaning a patient was seen once that day (if they were seen two times that day for different reasons, information would be in an unmerged second row [same patient name listed in two separate rows], where columns B & C would be different). Column J is an autosum of columns E through I if that makes a difference. Column K is a notes column. The data that needs to be merged is always added to the bottom of the spreadsheet in order to show that a patient was seen on any given day, with columns B through K almost always being blank. Example:
Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes
Alice Alpha--------AB----------PT-------1---------------------------------------------0-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3----------------------------------------------0
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2---------------------------------------------0
Alice Alpha-----------------------------------------------1
Carl Carlisle--------------------------------------1--------------1---------------1
Ernie Elephant-------------------------------------------1---------------1
This is what I'm hoping it can look like:
Column A---------Column B---Col C----Col D--Col E--Col F--Col G--Col H--Col I--Col J---------Col K
Patient Name-----Therapist---Shared--%P----2/3----2/4----2/5-----2/6----2/7---Total Visits--Notes
Alice Alpha--------AB----------PT-------1---------------1-----------------------------1-------------blah
Boris Beta---------BC----------SELF----2----------------------------------------------0------------blahblah
Carl Carlisle-------CD---------PTA------3-------1--------------1---------------1------3
Carl Carlisle-------AB---------SELF-----2----------------------------------------------0
Donny Delta-------DE---------PT--------1---------------------------------------------0
Ernie Elephant-----EF---------PTA-------2--------------1---------------1-------------2
In this example Carl Carlisle is being seen for two different things, however how would it be written so the macro would know which Carl Carlisle row to merge with? I'm thinking that before running the macro I could manually enter the information into column B so it knows which Carl Carlisle row above to merge with.
Data always starts at row 14 (row 13 is frozen pane header column), and extends to a row that is different every week depending on how many people happen to be in the list.
I found something from this link that looks very similar to what I need, but with no knowledge of coding, I have no idea how it should be tweaked: Merge Duplicate Rows Keeping Data In Same Columns
I know I'm asking a lot, but the amount of time this takes to manually go through hundreds of rows of patient names every week is incredibly time consuming, and I have too many other things to stay on top of at work for this to drag me down day in and day out.
View 5 Replies
View Related
Oct 18, 2005
I have a list that contains multiple data that I would like to merge in to one. There are varying duplicates.
Currently it looks similar to this:
Repid, Lname, Fname, States
000001, Doe John, CT
000001, Doe John, NY
000001, Doe John, GA
000050, Jones Mary, NJ
000050, Jones Mary, NY
000025, Presley Elvis, PA
000025, Presley Elvis, NY
000100, Doe Jane, NJ
I want to return the following:
Lname, Fname, Repid, States
000001, Doe, John, CT, NY, GA, NJ, CA, MA
000050, Jones, Mary, NJ, NY
000025, Presley, Elvis, PA, NY, GA, NJ
000100, Doe, Jane, NJ
View 9 Replies
View Related
Aug 6, 2013
I have an excel file which has many rows that are almost duplicate but some columns would differ, I would like to merge them, I will try to explain what I mean by "merge" .
Code :
Sub Remove_Duplicate()
Dim LASTROW As Long Dim I As Long Dim J As Long Dim K As Long Dim MyVALUE As Variant
[Code]....
Since I am very new to this language and my tinkering doesn't work at all. Whether or not a row is "almost duplicate" is based on columns A to G.
View 1 Replies
View Related
Aug 15, 2014
I have a spreadsheet with only 80 rows in it and I need data from another spreadsheet with over 200 rows. One of these rows in each spreadsheet contains a matching field (Job #). I only want the 80 matching rows data from the over 200 row sheet.
Is there an easy way to combine these two. Even adding the 80 to the 200+ will be fine as I can just remove the blanks. I can't figure out a way to combine them and match them up.
View 2 Replies
View Related
Nov 5, 2007
What I have is a massive table of data (15k+ entries), which consists of 4 different tables merged together. I have a lot of duplicate entries. However, most of these entries have information in one or another columns that its duplicate does not.
What I need to do is:
Identify the duplicate entries (by part number, which is one of the columns).
Merge the duplicate entries. In the merging process, I need to carry information over, taking data that is currently in 2 or more rows and merging it into one row.
Here's an example:
column1 column2 column3 column4
row1 a . . d
row2 . b
row3 . . c
needs to turn into:
column1 column2 column3 column4
row1 a b c d
If I'm not explaining this well, I will do my best to clarify. My main question is, is there any way to automate this process (even partially)
View 14 Replies
View Related
Aug 29, 2012
I am using the following code to copy a unique list, but it gives a duplicate in the first 2 rows(col Q). There is no headings in the columns.
Code:
Sub CopyUnique()
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "P").End(xlUp).row
ActiveSheet.Range("P11:P" & lastrow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("Q11"), Unique:=True
End Sub
View 4 Replies
View Related
Jan 27, 2014
I have a spreadsheet that lists employees and their certifications. If an employee has multiple, then they will show up on as many rows as they have certifications.
The macro I have merges them into one row with a line break, but only the first column's unique value has been merged while the other columns containing their own unique values are duplicated when I want them to show up only once. Example: Jane Doe shows up 2 times on the report. Her name should only show up once on the row, not 2 times with a line break.
Here is the code. I have also attached an example of what I need. Because the attachment is a simpler version of the actual report, is it possible to specify which rows have the unique values and which ones don't?
View 2 Replies
View Related
Feb 4, 2014
I am looking for some code that will use A,B,C as filters to find duplicate cells, and if duplicate found, there should be deleted the duplicated row (but not only the row from a,b,c column, but the whole 8 cells from that row - A,B,C,D,E,F,G,H).
As filter I would like to be used A,B,C columns.
EXAMPLE:
BEFORE
A B C D E F G H
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar01 1
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar001 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1
AFTER
A B C D E F G H
Kristijan Markovski 26,2,1992 1389 Prilep Prilep Mice Kozar1 1
Bojan Smileski 5,2,1992 1356 Prilep Prilep Borka Taleski 1
View 1 Replies
View Related
Jun 27, 2014
finish up a macro. The first part of the macro that is already working copies and paste 4 cells content from each worksheet found in a closed workbook thus generating 4 rows of data for each worksheet.
How to merge those 4 rows of data into one row. I have attached an example with the original data and the results data I am looking for but here is an explanation:
The column that is a constant and that I want to match is column B (Worksheet name). The 4 rows generated per worksheet will be showing on the first row data in Column C and columns D,E,F will be blank; then the 2nd row of data will have column C blank, column D with data and column E,F, and so on for the next 2 rows. What I would like the macro to do is merge all 4 rows so that the data found in cell C,D,E,F can be found in one row.
example.xlsx
View 9 Replies
View Related
Mar 20, 2008
I have several worksheets in a single workbook.
I have the following
employee paymethod earningcode earningtype rate hours amount
jane doe H CR R 5.00 80 400.00
jane doe H CO O 7.50 2 15.00
jane doe H HL R 5.00 8 40.00
jane doe H SE R 10.00 10 100.00
john smith H CR R 6.00 10 60.00
...
I need to have
jane doe CR 5.00 80 CO 7.5 15.00 HL 5.00 8 SE 10.00 100.00
I will have multiple employees. Not all employees will have all 4 earning codes (some will only have one, some will have two, and some will have three).
View 9 Replies
View Related
Apr 26, 2007
I have two rows of data that I need in one row and then sorted, it is possible that the data will be difffering lengths depending on input conditions. I also need the option to sort it either ascending or descending. The data in the two separate rows depend on other parts of the system and in turn this data will be used in another part. So I need it sorted automatically. I have tried ranking the data and then sorting it by rank but for some reason this does not appear to be working.
View 9 Replies
View Related
Jul 13, 2009
i would like to paste multiple duplicate values from unique values. Is there any way to do it in excel??
Ex:
Table 1:
3MG_HUMAN453
3MG_HUMAN44
3MG_HUMAN953
A1AT_HUMAN285
A1AT_HUMAN76
A1AT_HUMAN234
A1AT_HUMAN653
A1CF_HUMAN96
A1CF_HUMAN23
A1CF_HUMAN765
A4_HUMAN 944
A4_HUMAN 23
A4_HUMAN 755
Unique table
3MG_HUMAN100
A1AT_HUMAN78.89
A1CF_HUMAN90
A4_HUMAN 98
I wanted to add new column with these values for table 1 including duplicates. this is sample data and i wanted to replace thousands of this type.
View 7 Replies
View Related
Aug 3, 2014
I've been trying unsuccessfully to find a way to merge data into a matrix, but can't quite figure out a way to manage it. I have a list of employees in column A, in the list there are employees, many with multiple instances, and in columns C onwards there are dates in dd/mm/yyyy format.
Each line represents the number of nights in a trip, from 1 to 35 nights and the start to finish dates. Where as some employees may only have one row with one date others may have 20 rows with between 2 to 35 dates.
I'd like to merge this into a matrix with one line for each employee showing which nights in the year they were away (tab matrix) which has a day of the year to illustrate which employees were away on which nights of the year.
I've uploaded an example to highlight what I mean.
I've tried a pivot and an index match formula but couldn't get either to work.
View 7 Replies
View Related
Mar 25, 2008
how would i run a macro to merge duplicate cells in a column
For example, i have lots of data in columns A, B and C
however some of the data in columns A and B is the same. Column C data never changes. so to make it easier to read, i would like to merge these cells (which are the same) together?
-ColumnA-
Cat
Cat
Cat
Dog
Dog
Dog
Dog
Mouse
Mouse
Mouse
Mouse
Mouse
would become.... (with all the other data the same)
-ColumnA-
Cat
Dog
Mouse
I dont want to lose any of the rows, as the data in column C is always different. I just want to merge the similar ones, so its easier to read.
Its a bit like this, but i cant even come close to getting this to work:
[url]
View 9 Replies
View Related
Jul 7, 2014
Currently I am using Excel 2007. I want to merge data of alternate row of a particular column in Excel.Also i want to remove merged row. E.g.
Name City Pincode
John Mumbai
John 400009
Carol Delhi
Carol 110001
Carol Pune
Carol 411079
I want output to be ----->
Name City
John Mumbai-400009
Carol Delhi-110001
Carol Pune-411079
macro/formula for the above requirement..?
View 11 Replies
View Related
Mar 5, 2014
I am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).
What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.
Picture:
Column C Column D
111111 M
111111 M
111111 M
[Code]....
Currently calculates: M=9, F=2
Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.
Equation currently using to count only filtered values (in this case "males" and "females"):
="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))
View 9 Replies
View Related
Apr 3, 2013
I'm trying to get data totals for multiple entries and haven't been able to figure out how to do this using a pivot table.
For example:
A B
Ben 2
Bill 1
Jane 3
Bill 4
Dave 7
Jane 4
Would make it
Ben 2
Bill 5
Jane 7
etc.
this list is 3700 cells long.
Is there an easy macro or another way of doing this? I want to generate a list that gives all data totals but just lists each persons name once. I treid attaching the file but it wont let me! here is the link... [URL]
View 1 Replies
View Related
Jun 7, 2014
I thought this would be very easy to achieve in Excel but is proving difficult. Basically I have a number of excel worksheets with the headers as below. What I would like to do is to use the staff number from each worksheet to populate and sum a master worksheet. Some staff numbers will be in each of the workbooks while some are only in one. While the employee name should also be the same in each of the workbooks there are some differences in spelling etc.
Mon - Fri Mon - Sat Saturday Sunday/Public Hols OVERTIME
Staff NoEmployee Name6 am - 11 am5pm - 12 Midnight12 Midnight - 8am8am - 12 Midnight8am Sun - 8am Mon
View 6 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 22, 2010
Im having some problems writing a macro,
I have a spreadsheet with 2 columns. The first column is the index number (unique identifier) of the second columns data that will repeat itself as many rows as needed. Let me try to exemplify it:
Column A (index)Column B (text)
1 AAA
1 BBB
1 CCC
2 DDD
2 EEE
3 FFF
3 GGG
3 HHH
3 III
Im trying to write a macro that will merge the text of each identifier in the first row it appears. The result Im looking for is:
Column A (index)Column B (text)
1AAA
BBB
CCC
2DDD
EEE
3FFF
GGG
HHH
III
I already know how to make the merge, but I cant get the logic to make it go through each row and merge the contents, since the number or rows for each identifier varies.
View 9 Replies
View Related
Jul 25, 2012
I have a table (is populated in a macro already) that has two header rows (1 & 2), inputs a blank row on row 3, then starts data input on row 4. The data can potentially run to row 1000. The columns that are used are B through M. The part name is in column B and the quantity is in column C. The part name can repeat with a different quantity and be put in a different row than a previously entered value. I need to combine those x number of rows based on the match of the part name and sum the quantities then delete the remaining matches. Columns D-M are not unique.
I have attached a sample. The sample is very simple with just two matches but the matches can more than two. The matching rows are highlighted. duplicate rows.xls
View 9 Replies
View Related
Jan 27, 2010
I need to combine rows with that have the same column A value into one row. The duplication will be no more than 3 (ie 3 As, 2 Bs, 3 Cs, 1 D etc). I am looking for one of two options. 1) Ideally I'd like to be able to take 2 columns and turn them into up to 4 columns total. 2)Alternately, I can format the data to be in the correct columns to start, but will still need to merge the duplicate rows. The data can have any number of rows to start.
I've found something close, but it puts the new column data into ascending number order, which won't work for me because I need it to be in the specific order I input.(It also put all the data into one column, which I don't mind because I can do text to columns, but the order was the bugger.)
View 5 Replies
View Related
Jul 28, 2013
with deleting duplicate entries in a spreadsheet that contains two names, a code number and a date. All rows with a duplicate number in column “B” should be deleted. Write a micro code? I have a few thousand of entries that need to be knocked out.
A sample file is attached : Example.xls
View 5 Replies
View Related
Mar 5, 2009
I require a formula that will show unique data in a specific rows or cells. Here are the data:
Raw Data
Col1
Row 1 A
Row 2 A
Row 3 B
Row 4 C
Row 5 C
The result should look like this (Note that the unique data are displayed in rows 6 to 8:
Col1
Row 6 A
Row 7 B
Row 8 C
View 5 Replies
View Related
Oct 27, 2008
I want to be able to count unique rows of data.
I have three columns (A) has a date (B) has the name of a person and (C) has an audit type.
I know I need an array but not sure how to structure it for 3 columns
DateNameAudit type01/04/2008DavidCDM01/04/2008John1 in 1030/04/2008Bill1 in 1001/05/2008DavidVehicle01/05/2008DavidVehicle01/05/2008JohnCDM
The answer for these 3 columns is 5. Rows 4 and 5 are the same so are only counted once.
I can get the arrray to count unique items in the whole range and in a column but not for each row.
I have a feeling this is going to obvious but I can't work it out.
View 9 Replies
View Related
Aug 15, 2008
I have a long (about 180 000 rows) list of part numbers, descriptions, prices and quantities. There are many duplicate part numbers. I need to go through and find the duplicates, add all their quantities into one cell and delete the remaining duplicates. I have used conditional formatting to find the duplicates easier but cannot use the remove duplicates button as it will delete the whole row and I need the quantity information in each row.
Any formula or macro I can set up to search the part number column (F) for duplicates and add the figures in the quantity column (J) and THEN delete the duplicate part number rows?
Maybe I need to put the new combined information in another sheet?
View 8 Replies
View Related
Jan 13, 2009
I have a spreadsheet that has thousands of rows of data with maybe 6 columns.
I would like to remove the duplicate rows but the problem I am facing is that I would like to use column A as the criteria.
There are some duplicates in column A but the issue is that the data in column B, C, D, etc may not be the same for each duplicate.
Easier to see in this image:
http://img127.imageshack.us/img127/6364/datajv6.jpg
So A1 is the same as A6
A1 has data in B1 and C1, no data in D1
A6 has data in B1, C1 and D1
I need to delete either row A1 or row A6, it is irrelevant which row it is but one of them has to go.
View 2 Replies
View Related
Jun 4, 2013
I have attached a before and after image of what I am looking to accomplish.
In the before image, you can see that there are 3 rows of data
- a header
- a repeating model number (in column F) with accompanying data (values in columns G - J are the items of interest)
In the after image, you can see that I took the 3 rows of data and turned it into simply two rows of data
- a header
- the repeating model number
What I did however in the after image, as can be seen, is I took the data that appeared on the repeating row (Row 3 from the Before image), and included the PRICE, COST, BEGIN, and END values in new columns on row 2.
The model number is the same of course, which is why I did it that way.
If the model numbers were different, I would have simply left it alone, as-is.
Is there a way to accomplish this via some functions or maybe even a VBA script?
By the way, if we have to dump all of this onto a new sheet, then so be it.
View 5 Replies
View Related
