Excel 2007 :: Combining Data In Duplicate Rows
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
ADVERTISEMENT
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
Mar 7, 2012
I'm using Xl 07 and need to combine two sheets of contact info and have them sorted alphabetically.
Each contact entry takes the follwing form(comma's denote new columns)
55555, Name , John, Smith, Tel: , 555-555-5555
ID , Company, XXX , , Email, xxxxxx
Each sheet has a list of 200 or so entries about 400 rows long. The entries on each sheet are sorted in alphabetical order. the names also alternate alphabetically from sheet to sheet. So when i look at the entries on my final sheet, the 1st will be from sheet1 the second from sheet2 the third from sheet1 and the fourth from sheet2 etc.
I'm not sure whether I should build a new list by pulling alternating entries from each sheet or just copy one list below the other and then try to sort it somehow.
View 1 Replies
View Related
Feb 26, 2014
What im needing is something that will duplicate each row based on Column F as shown below... However I also need the duplicate row numbers added to Column G and then the original row deleted... I will try and show you an example of the input/output i would like to achieve: (also the first row can be ignored it will all be headers)
INPUT
ROUTE
NAME1
NAME2
ADD1
DESC
3
WOOD000001
PW
ID
[code].....
View 1 Replies
View Related
Jul 4, 2014
I am using Excel 2007 on Windows 7 Home Premium 64bit.
My Workbook contains 2 Worksheets, both with the same headings in row 1. Sheet2 is initially empty, except for the headings in row 1.
The following is a sample of the data in Sheet1:
[Code] ......
I want to create a Macro to operate as follows:
In the above sample data, rows 2, 4, and 8 are identical, so I want to copy rows 4 and 8 to Sheet2, and delete them from Sheet1.
Similarly:
Rows 3 and 9 are identical, so row 9 should move to Sheet2;
Rows 5 and 11 are identical, so row 11 should move to Sheet2.
Rows 6, 7, 10, and 12 are all unique, so should be unaffected by the Macro.
New records will be added to Sheet1 periodically, so the Macro will be used each time to move any newly discovered duplicates to Sheet2.
The Macro should make no assumption about how the rows are ordered, and should not change their order.
I have uploaded two Workbooks, such that Workbook1 shows the original data, and Workbook2 shows the result I want to obtain.
Attached Files :
Workbook1.xlsm
Workbook2.xlsm
View 14 Replies
View Related
Jun 3, 2012
I want to count the number of duplicate rows where the exact text in columns A and B match. An example is as follows, where column C would be the desired result. Note that there are hundreds of different text values of column A and hundreds of column B, I just simplified the example.
Excel 2007
ABC1AX72AX
3AX
4AX
5AX
6AX
[Code] ......
View 9 Replies
View Related
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
Sep 21, 2006
In my sheet I have it search for duplicate records and give those records the same id. Is there anyway to combine data into one record. For example, I have a person with a value in column Points1, on another row a value in column Points2, and another row a value in column Points3. I want take those values from the duplicate records and put them all in one record. I've attached a sample.
View 2 Replies
View Related
Mar 25, 2014
I know about using countifs and or but utilizing sum(countif(..... [all because of this site] - but I have a statement that is quite long and is confusing me when trying to reduce it... if not then I can just use this and it'll be fine... but I would really like to reduce it if possible... here it is:
=COUNTIFS(Data!$A:$A,Sheet1!$A2,Data!$AA:$AA,"<"&Sheet1!B$1,Data!$AF:$AF,">"&Sheet1!B$1,Data!$AJ:$AJ,">"&"30")+COUNTIFS(Data!$A:$A,Sheet1!$A2,Data!$AA:$AA,"<"&Sheet1!B$1,Data!$AF:$AF,"",Data!$AJ:$AJ,">"&"30")
Basically I have a Data sheet that has 19,000+ records that have data from A to AJ... I am tasked with trying to find how many open work orders there are in our company on each given day that were submitted 30 days prior to a specific date. A = Region, AA = Date Submitted, AF = Date Completed and AJ = Days open.
The table looks like this:
1-Feb 2-Feb 3-Feb 4-Feb .........
Midwest 39 39 39 42
Northeast 119 119 120 126
Southeast 46 47 50 54
Southwest 53 53 53 57
West 53 53 53 60
Total 310 311 315 339
View 4 Replies
View Related
Feb 4, 2012
I have two line charts in Excel 2007.
One chart has the values going down from upper left to lower right
The other chart has the values going up from lower left to upper right
Is there anyway to merge these charts to see if they intersect at all?
View 1 Replies
View Related
Apr 10, 2014
I currently have a worksheet filled with data from other worksheets. I want to combine data from different columns when they share an identical row header and don't know how. For example:
1b--
1--c
1-d-
2-x-
2y--
2--z
Would go to:
1bdc
2yxz
Another specific issue is that some row headers are bold and need to be considered a separate header from the non-bold alternative.
View 4 Replies
View Related
Sep 28, 2009
I am waaaaay in over my head here. I've not done any scripting/macro/vb/whatnot work in Excel, but I'm staring down a potential all-night project manually merging data.
Here's what I have:
View 6 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
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
May 2, 2012
I'm working in Excel 2007 and need to move data from multiple rows to a single row if the ID matches.Below is sample data I would be working with. I want to move data from columns F-U to the right of the original data in the row above it. I would also like to delete the rows that had data moved.
GIDSurnameNameEmployee Number OriginalDate of birthGranting ARE
Employing ARECountry Employing ARECHCM Supplier IDVehicle
Investmt. shares / Awards at termination dateMatching Shares
at termination dateTermination
[Code]....
View 3 Replies
View Related
Feb 26, 2014
I need to transpose data from Column A to Row 2 and down.
The data in column A is in sections of 19 rows and then a blank cell and another 19 rows of data contimuously, It is a dynamic range and can contain many thousands of Rows.
The data needs to be transposed from Column A to row 2 (row 1 has the head line for each column) so the 19 lines of data is now spread accross 19 columns in row 2 and the next section from column A is spread accross the 19 columns in row 3 and so on.
My data looks similar to the below. (Test Number 0001 starts in A1)
Test Number 0001
21-Feb-2014
Kettel
Office
Demo
[code]....
I use Excel 2007
View 6 Replies
View Related
Jul 9, 2008
Problem: We have some rather large excel spreadsheets (142,000 rows, 190 columns) and some users that need to delete the data from rows of cells 5,000 rows + at a time. When doing this machines jump in CPU, Excel goes "not responding" and on average comes back to life 1 minute 45 seconds after pressing the delete key.
Note: We are not doing a right click and deleting the rows. This is a highlight of the 5000 rows and pressing the delete keyboard key to clear the data.
We've tried disabling the Auto Save and the Workbook Calculation changed to Manual.
Dell is no help, Microsoft is no help. This process can be replicated on multiple Dell machines and with Operating Systems of XP SP2, SP3 and Vista, machines with single drives, raided systems and even a machine with a solid state drive. All have 2GB of RAM or more.
View 9 Replies
View Related
Jan 11, 2012
I've already found a TON of threads about this process but nothing that matches specifically what I'm trying to do.
I have a spreadsheet that I'm using to auto fill other tabs with data that only matches specific criteria. Here's what I'm looking to do:
Columns I, J, K, and L may be marked as either Y or N (or blank). I have different sheets that require 1, 2, 3, or 4 of those columns to match Y. For example, on sheet 2 I want to copy the entire row if there's a "Y" match on column I and J. On sheet 3 I want to match "Y" against, I, J, and K. Sheet 4 I need to match only L, etc.
I need the data copied into the existing sheets to start on row 7. I have other data on rows 1-6 that cannot be moved.
I'm running Excel 2007.
View 5 Replies
View Related
Feb 13, 2012
I am trying to have duplicates and their corresponding data appear in my pivot table. Assume I have a list of over 1000 stores. Let's say store 101 appears twice in my data file.
When I create my pivot, the 101 only appears once and the data is consolidated. Is there a way to ungroup/display duplicates?
View 2 Replies
View Related
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
Jan 26, 2010
i have a spreadsheet of 1200 rows of data and i need to duplicate these rows 5 times each. is there any way to easily do this and avoid manually inserting or copying/pasting all 1200 rows? all i can find is how to DELETE duplicate rows, not how to ADD them. see below for an example:
original spreadsheet:
1 A
2 B
3 C
desired spreadsheet:
1 A
1 A
1 A
2 B
2 B
2 B
3 C
3 C
3 C
View 7 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
Mar 12, 2014
I have 2 separate excel sheets. Both contain different data about the same account. I need to combine the 2 into one spreadsheet without duplicating records. The accounts do have a unique account number that appears on both data sheets.
View 2 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
Nov 12, 2013
I have a report with about 7000 rows in it. I need a macro that will find all rows where column A and column B are the same as another rows column A and column B and delete both rows.
View 4 Replies
View Related
Jan 21, 2014
This is a sample of what I am trying to accomplish (file attached). I have information in one worksheet (called MasterList) and a second worksheet called (RecordList). I want to take information from MasterList and RecordList and combine them to produce a report (Results). Assume the user does not have access to MasterList or Results.
The user would enter the UID in RecordList, which then populates information from MasterList. There can be multiple entries for each UID and there is no set number of entries (could be 1, could be 500)
The user enters the UID into RecordList, which populates information from MasterList. The user then adds in the additional information into the fields.
What I want is all the information from the Master List must be reported whether anything exists in RecordList or not. If there is information in the RecordList, display it and on a seperate line for each entry. Each UID then needs to be totalled (which can be done through pivot table later).
View 1 Replies
View Related
Jan 22, 2013
Excel 2007
ABCDE12145101843222121028543291410388563015104796731501058178325210
685894953107839104354108841011445510985111349661101215516710013135668
981417576999151858708916195971801720607291182161738219236274902024637
59121276476872228657786232425Sheet1
View 7 Replies
View Related
Jun 27, 2012
Is there a function in Excel that allows you to combine non-numeric columns as a list?
For example, 91401 is a zip shared by three cities: Van Nuys, Sherman Oaks and Valley Glen.
The data is in a workbook as three rows (each with 91401 as the first column, and cities listed individually:
91401 Van Nuys
91401 Sherman Oaks
91401 Valley Glen
I am eventually wanting to do a v-lookup with the zip, but the duplication is causing problems. I want the associated cities listed, but only want one row. The cities could be listed, separated by commas (preferably), like below:
91401 Van Nuys, Sherman Oaks, Valley Glen
The list of zips I have is several thousand long, so it's not practical for me to do it manually. Is there a "pivot table" of text that I could use?
View 5 Replies
View Related
Jun 17, 2014
I'm new to VBA and macros, using Excel 2010, and am trying to figure out how to delete all duplicate rows in a sheet where 2 or less of their values in column A is "1". I'd like have a script that is flexible enough to change to 3 or less if need be. I also have a header row that needs to be offset in the process.
A---B-
0--123 <-delete
0--123 <-delete
0--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321
or
A---B-
0--123 <-delete
0--123 <-delete
1--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321
View 5 Replies
View Related
Feb 16, 2014
how can I write a routine to add rows in one Sheet if a condition which involves a different Sheet is met (excel 2007).
In Sheet 1 is a list of ("liquid") names (cells C4:C26) which will need to be expanded should the IF condition be satisfied. In row 27 I have a subtotal (whihc is using data from columns D onwards), and from row 29 down cells are not empty.
The condition I want to verify is in Sheet 4-column I, where it is reported whether a name is classified as "liquid" or "illiquid". If the number of "liquid" names in sheet4-column I is more than the number of names in Sheet1-C4:C26 I would like to add as many rows as the difference between the two lists. Also I would like to make sure that the subtotals that I have in row 27 (and that will be shifted down when new rows are added) will also inlcude the data in the new rows (the subtotal is taking data from columns D onwards).
Should the code need to inlcude the name of the tabs, Sheet 1 is called Summary and Sheet 4 CDS Data.
View 1 Replies
View Related
