Merging Duplicate Rows And Keeping Original Data

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


ADVERTISEMENT

Merging Spreadsheets And Keeping Duplicate Rows?

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

Merge Duplicate/Similiar Rows Keeping Data In Same Columns

Sep 1, 2008

After sorting and filtering rows with in a set range I will have several rows that are almost duplicates. This is normal and expected due to how the workbook is used. Among these rows also will be several single rows that are not duplicates. It is important that I combine any two duplicates into one row. Example:

CREATE TABLES LIKE BELOW?
ABC D E F G H I J K

1 NameA 0XX15930777PS101300PS9

2 NameA0XX15930777PS91200PS10

3 NameX1159XXP555FBX1545PS9

4 NameB0A1234P123PS101263PS9

5 NameB1A1234P123PS90512PS10


What I need is this end result:

CREATE TABLES LIKE BELOW?
ABC D E F G H I J K

1 NameA 0XX15930777PS91200PS10PS101300PS9

2 NameX1159XXP555FBX1545PS9

3 NameB1A1234P123PS90512PS10PS101263PS9


Its important that the data in each column stay with in that same column. Also of course it needs to be on the same row with the same person (NameA and NameB). The Columns that would determine if its a duplicate are D and E . I would need this to be preformed via macro or some easy way so that others will not have a hard time. It will be on a protected Shared Workbook with Excel 2003. I've enclosed a Sample. How can I sort these or accomplish this and maintain the data where it needs to be?

View 5 Replies View Related

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 View Related

Merging Two Columns And Keeping The Data From Both

Jul 26, 2006

There is one crucial feature to the 2007 Excel that has been overlooked.

Throughout all versions of Excel there has always been the feature of
converting text to table, however there is no way to do this in reverse.

There is no way to merge two columns of data and to keep all of the data
without one column overwriting the other. If only there were an automatic
way to merge two columns of data and to be able to place a delimited
character in-between, just like the “Convert Text to Columns Wizard”, except
in reverse. Currently, the only way to merge two columns of data is to
manually go row-by-row and cut and paste them together. However, for 500,000
rows of data… this is impossible. Or to use a function to merge two columns,
however this requires that the original two columns remain. This is also
unacceptable. If Microsoft really wants to make Excel more functional, how
can this vast improvement be overlooked?

View 10 Replies View Related

Delete Both Original And Duplicate Rows?

May 25, 2014

My worksheet that will have duplicate data inserted into it. Once inserted I need to delete the new duplicate row and the row it duplicated. The attachment is a copy of what the data looks like with the first tab showing what it will look like with inserted data and the second tab is what I want it to look like at the end. I will not need the deleted data again so it does not need to be on a new sheet, that is just for an example. The name of the tabs will also be different so I want to be able to run it on any worksheet. This is the code I am using to find and delete the duplicate but I can't get it to delete the original. I used "First, MI, Last, and DOB" because those are the ones that won't be duplicated where others will. This is a list of about 15,000 entries. There should never be an incident where there is triplicate data but I can't say for sure.

[Code] .....

Attached File : Practice.xlsx‎

View 2 Replies View Related

Removing Duplicate Rows, But Keeping The First One.

Feb 28, 2008

I have an excel spreadsheet with approximately 10000 rows. There are approximately 10 columns...

Column A - person's ID number
Column B - name
Column C - Street Address
Column D - City
Column E - State

etc...

It being such a large list there are many many duplicates (it was pulled from a bunch of different excel spreadsheets and combined into one via copy and pasting one document into the other).

I figured out a way to have excel highlight the duplicates in a different color... but scrolling through 10k people and deleting the duplicates is obviously very tedious (approx 40% of the spreadsheet is duplicates).

Is there a way to sort out the duplicates (via their ID number, column A) so that there are no repeats? I want to get rid of the 2nd/3rd/4th (etc) occurance of the person's information but I was to obviously keep the first occurance.

I tried using Advanced Filter but I must have done something wrong because it shrunk the list too far down.

View 9 Replies View Related

Delete Duplicate Rows :: Including The Original

Jan 15, 2010

I have a large worksheet with over 8,000 rows and about 5 columns. In several instances, I have several rows that are all exactly the same. It could be 2, 3, 4 or even more rows that are duplicate to one another.

I would like a macro that deletes ALL rows that are duplicates, including the original.

For example, let's say, rows 3, 4 & 5 are all exactly the same, I don't want to just delete rows 4 & 5, I want to delete row 3 as well!

View 9 Replies View Related

Deleting Duplicate Rows Keeping Lowest Value In Column B

Jul 30, 2009

I need the macro for deleting duplicate rows only it should keep one lowest value row.

for e.g.

Column A Column B

TS1234 100
TS1234 50
TS1234 200

Macro will compair dublicates in column A & it will keep lowest value row (i.e. 50 value in Column B), other dublicate rows will be deleted.

View 12 Replies View Related

Merging Duplicate Rows To One

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

Merging Almost Duplicate Rows In Excel File?

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

Sort By Date Keeping Duplicate Data In Column A Together

Feb 26, 2014

I need to sort my data by the oldest date first in colum L (NEED_DATE), but I need to keep the rows grouped by colum A (Material No). See example of data.

Material No
Tool No
Prodn Ordr No
Curr Oper No
Curr Workcenter Cd
Next Oper No
Next Workcenter Cd

[code]....

View 2 Replies View Related

Copying Formulas Between Worksheets While Keeping Reference To Original Worksheet?

Mar 4, 2014

I have a series of formulas in Row 1 across the columns in worksheet A. I would like to copy these formulas to a single column and down rows in worksheet B while retaining the references to worksheet A. I know that I can manually enter the references in worksheet B but that would take a long time to do.

View 1 Replies View Related

Macro To Copy Formula While Keeping Original Cell References

Feb 17, 2009

I'm trying to create a VBA macro that will allow me to copy a formula from one sheet to another whilst keeping all the original references.

E.g.

If the formula on Sheet1 is:

= sum(A1:B6)

then the copied formula on Sheet2 would read

=sum(Sheet1!A1:Sheet1!B6)

You can do this by cuting the cell, but I don't want to do this, I want to leave the original cell unchanged.

I'm sure there is some simple VBA code to do this, but I can't seem to figure it out.

View 9 Replies View Related

Finding Duplicate Data And Merging Cells

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

SaveAs Worksheet To Another File / Folder While Keeping Original Open / Active?

Apr 15, 2014

I have an Excel Worksheet (let's call it "AA.xlsm") which uses VBA code to loop through column A, update column B with some results/values, and then (for each value in column A) SaveAs the worksheet into a different filename/path as a ".csv" type file.

My line of code for the SaveAs is as follows: Workbooks("AA.xlsm").SaveAs LEpath & CurrFldr, FileFormat:=xlCSV

My problem is that my original worksheet, AA.xlsm, gets closed whereas I need it to stay open/active after each SaveAs iteration.

View 3 Replies View Related

Combining Unique Data Into One Row With Columns While Merging / Deleting Duplicate Information

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

Combining Rows And Keeping Data From Both Rows

Mar 9, 2013

I have a game where people are scored on the spreadsheet, but they can join anytime, so everyday, I get a combined list of points. The list consists of people who joined previously, and the new players. How should I combine the points?

[URL] ..........

View 4 Replies View Related

Sum With Multiple Criteria A Number Of Rows And Columns From Original Data

Sep 22, 2006

I have 6 columns of data laid out as follows,

columnA columnB columnC columnD columnE columnF
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX

XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX

I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting

= SUMIF($B$2614:$B$2640,"bhree*",IF($C$2614:$C$2640,">=01/07/2006",( OFFSET($C$2614:$C$2640,1,3,1,1))))

View 9 Replies View Related

Merging 4 Rows Of Data Into 1 Row

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

Merging Multiple Rows Of Data

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

Sorting And Merging Data From Two Rows

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

Merging Columns And Rows Of Data Into A Matrix

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

Merging Duplicate Cells ..

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

Excel 2007 :: Macro For Merging Data Of Alternate Rows?

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

Macro For Merging Duplicate Entries?

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

How To Dynamically Insert Rows With Duplicate Data Of Previous Rows

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

How To Delete Copied Rows And Update Original Tab Without Empty Rows

Apr 3, 2014

In my excel I'm copying rows upon specific criteria to another tab.

The question is how I can delete copied rows and update the original tab without empty rows? (N of rows is always changing)

View 14 Replies View Related

Transpose Rows Keeping Heading In Column A And Data In Column B

Mar 2, 2011

I have an excel spread sheet with several rows of 265 (9A-IV) columns each with a heading. I would like to transpose the worksheet columns so that the heading is placed in Column A against the corresponding that is placed in column B. For example

ABCD14692571038Transpose to A1A2A3B4B5C6C7C8D9D10

View 7 Replies View Related

Highlight Entire Row When Cell Selected Without Losing Original Formats And Color Of Original Row

Sep 5, 2012

The problem is when I highlight a row with some color the original color of the row is gone, so I tried this code, and again, it's removing the original format and color for the row This is the code from McGimpsey & Associates : Excel : Highlight row with background colors

Code:
PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnNUMCOLS AsLong=256
Const cnHIGHLIGHTCOLOR AsLong=36'default lt. yellow
Static rOld As Range
Static nColorIndices(1To cnNUMCOLS)AsLong
Dim i AsLong
IfNot rOld IsNothingThen'Restore color indices

[code].....

How can I retain the range's historical color so that when I deselect the row it reverts properly?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved