Merge Duplicate Entries For Count Or Sum
Jan 16, 2008
I often need to merge multiple occurences of data (such as account numbers or names) and to sum or count the values associated with each invividual instance (eg cost or number of entries). Data can often be thousands of rows and varies every time. For example:
Col A Col B
Ken 5.9
Ken 12.6
Brian 5.5
John 6.4
Fred 9.9
Fred 11.6
Fred 2.0
I need to be left with either a sum
Ken 18.5
Brian 5.5..............
View 4 Replies
ADVERTISEMENT
Sep 14, 2006
there are unique entries like AU0896 etc. that are repeated in my list.
my job is to find how many unique entries there are and add the count at the end so, basically if there are 6 AU0896 entries, then I must create a AU08966 value.
View 7 Replies
View Related
Nov 2, 2011
I have a column that is filled with different names. Some names appear more than once. There is one name in fact that appears four times.
I am looking for a formula, or VBA if that's necessary, that will look through the range Y2:Y50, and return a value of 4, because the person that has the most duplicates, her name appears 4 times.
If everyone's name appeared only once, except for a person whose name was in that range twice, then the formula would return a 2.
View 6 Replies
View Related
Sep 24, 2009
I've been trying to sum merge, and delete duplicate rows in this massive spreadsheet (over 15,000 rows). Currently I've gotten to the point where the duplicate lines are eliminated but the quantity is recording the number of times the line has moved, not summing. Below is an example of what I'm looking for
EX:..............
View 3 Replies
View Related
Jul 30, 2007
I want to make a report wherein the macro has to merge cells based on duplicate cell values in column A and to put corresponding data in columns into one cell which we do manually by pressing ALT+Enter.
How this can be done through a macro or other tricks as I need it for a large report and need to use repeatedly.
A sample WB is attached which explains more.
View 4 Replies
View Related
Mar 28, 2013
I was given 6 spreadsheets combined into one (the reason is immaterial-but valid) it is what it is... unfortunately! (42 columns, 14,000 rows of which 450 names are duplicated as illustrated below)
I want to merge duplicate data.. but not the way I see it shown in all the 'merge' and the 'delete duplicate' threads I have seen so far.
I manage a homeless shelter and someone duplicated the spreadsheets and started using the duplicates to enter fresh data.... several times!
As a result I now have somewhere around 8 instances, (or 7,5,3 etc) of every homeless guest, but it has duplicate data in some columns, and new data in others, for instance:
I want to turn this....
Name SS# 1st entry date, 2nd entry date, 3rd entry date, fourth entry date, etc...
John Smith, 123-45-6789, 1/5/2010, 7/13/2010, 3/30/2011, 5/16/2012
John Smith, 123-45-6789, 1/5/2010,
John Smith, 123-45-6789, 1/5/2010, 7/13/2010, 3/30/2011,
I tried de-duping based on duplicate rows and that worked with the pure duplicates get out of the mix, but if I try to to de-dup on only the first two columns of data it may remove date I want to keep.So I need to de-dupe and merge at the same time.
View 3 Replies
View Related
Sep 27, 2008
I have a new project that needs macro code. Your help is very much appreciated. We have a spreadsheet with duplicate accounts meaning two or three rows with the same account but different information. We want to use only one row for one account and move the new data from the same account to one row only to the right and delete the duplicates. Can someone please help me with this?I read so many post and I tried some of them but it only delete the duplicate row and not copying the new data from that row to one row only. Also, the other code I tried was retaining only the current or old data. Actually, to elaborate more, I want to get the new data in each cell of the same account in multiple row and move it in one row to the right only and delete the duplicate in that same account.
View 14 Replies
View Related
Oct 28, 2009
i have to clean a worksheet from duplicat rows. The list looks like:
A---------B-----------C
a---------s-----------b
a---------f------------b
a---------s-----------b
a---------e-----------d
a---------d-----------v
Those rows have to be deleted where duplicates exist in column A and C. In our example the first three rows would meet the criteria and two of them have to be deleted. Content of Column B has to be merged with ";" but without duplicates. The result would then look like:
A---------B-----------C
a---------s;f----------b
a---------e-----------d
a---------d-----------v
View 2 Replies
View Related
Oct 16, 2008
Through a query I extract data from an oracle database. This database creates a record for every unique Article_Batch_Pallet_Faultnumber combination. Through a filter macro I create a new format on a different worksheet which creates a record for every uniqe Article_Batch_Pallet combination. I need a searchfunction to get the faultnumbers in the same row.
View 2 Replies
View Related
Jun 2, 2012
In a big data sometimes I have identical rows (maybe the 'Quantity' column has different value). I would like to merge them into one and add the quantities together. I have to use B and C (I need both) to find out if these rows are identical.
For example:
A1=date B1=111222 C1=ABCD ... and G1=quantity (1)
A2=date B2=111222 C2=ABC ... and G2=quantity (1)
A3=date B3=111222 C3=ABCD ... and G3=quantity (2)[code]......
View 3 Replies
View Related
Sep 24, 2009
I have been trying a number of different functions!
I have the following countif function that is searching a worksheet (Cases Closed) for the name John in Column O and excluding Solutions in column x. The problem I have is there are duplicates cases in Column C that are being counted two and three times.
Is there anyway to have the following function exclude duplicates records in Column C? Just count unique records in Column C?
=(COUNTIF('Cases Closed'!O:O,"John"))-(COUNTIFS('Cases Closed'!O:O, "John", 'Cases Closed'!X:X, "*Solution*"))
View 9 Replies
View Related
Jul 28, 2008
to prepare an excel spreadsheet for a mailmerge but as all of the info for 1 recipient needs to be in columns instead of rows. I need to convert 2 columns' data into columns but only when there are duplicate invoices, see below;
View 9 Replies
View Related
Apr 23, 2013
excel.jpg
Basically I've made this up myself because what ill be working with has 100s if not 1000s of rows with many different product numbers that's quantities are different. What I've been able to do up to now is sort the spreadsheet by the product number so all the same rows are next to each other. My problem is however I need a speedy way of making these duplicate rows become one but add the total quantity basically everything in the left screenshot into the one on the right. What I've tried up to now is sorting them so there together and manually adding them up and putting them into one of the rows quantity, then delete the rest. takes to long. Another was to make a row underneath the rows I need into one but that takes more time than manually adding and deleting the rest.
View 9 Replies
View Related
Jun 13, 2008
I'm working on a employee evaluation form. I'm using a Pass/Fail system.
I have a cell designated for PASS & one for FAIL. In these cells, I would put
"YES" if they passed, "YES" if they failed that section.
How can I prevent someone from mistakingly entering Yes in both cells?
View 9 Replies
View Related
Jul 14, 2008
I am trying to find a macro that will clear duplicate entries in a column of data, and then move to the next column and repeat the process until it gets to a blank column.
There are blank cells between entries in a column. I need to keep the data unsorted as the cells are linked to others in the row, therefore I dont want to delete the cells, just clear the contents.
View 11 Replies
View Related
Nov 24, 2009
I would like to check column B and if any of these values match values in column J hightlight the cell on the same row as the match on column E green. I don't know much about excel but this would be a tremendous help because i'm looking through quite a bit of data. I've tried using conditional formatting and i can't seem to get that to work for my needs. Let me know if you need anymore information.
View 13 Replies
View Related
Feb 8, 2010
I have a table of data like this:
A , 1 , 1
B , 2 , 1
C , 3 , 4
D , 1 , 3
E , 3 , 3
I need to eliminate duplicate entries WITHIN a row. So, I need the table to become:
A , 1
B , 2 , 1
C , 3 , 4
D , 1 , 3
E , 3
Is there an easy way to do this for a large sample? Also, there are actually more like 8 columns of this data, if thats important.
View 9 Replies
View Related
Dec 11, 2012
I've generated a sheet where, based on the total run hours of a generator set using a given fuel type, we can determine which engine to run on what fuel when. The problem I've run into is that if more than one engine has the same runtime, the chart returns just the lowest numbered engine and eliminates the remaining engine(s). What I'm trying to do is make it so that when this occurs, the suggested engines show up in numerical order. For example instead of 1, 1, and 1 showing up on the chart, something like 1, 2, and 5 will show when they all have 0 run hours. I've attached my chart below with some engines having the same runtimes. RunOrder.xlsx
View 5 Replies
View Related
May 8, 2009
New here, I wanna do a "Prevent Duplicate Entries" into my column i try the following: http://www.mrexcel.com/articles/prev...duplicates.php
http://www.theexceladdict.com/_t/t040818.htm
They all work well but if I'm using copy and paste It doesn't work! why is tat so?
Second issue will be i'm wanna do prevent duplication for the whole column not just one cell.
View 3 Replies
View Related
Feb 5, 2009
I have a spreadsheet that has account numbers listed multiple times. I need to eliminate all of the duplicate entries...Is there a formula for this...?
View 9 Replies
View Related
May 25, 2009
=COUNTIF($A$1:$D$100,A1)=1
however I don't know how to change that to suit my needs (or even if it is at all possible to change it to suit my needs)
I will try my best to explain how my spreadsheet is set up as unfortunately I am at work and I can not use the method to show you the spreadsheet.
My spreadsheet:
I already have Data Validation in use from a list that I created. I use the same validation list in Columns C, E, G, I, K-BA
I also use validation list in other cells, and it is OK if these are duplicated throughout the row.
The only cells that should not contain duplicate information are the ones from C, E, G, I, K-BA
Basically I want to stop someone accidentally chosing the same option from the list within the same ROW.
Just in case it makes any difference I should let you know that I have already had help from the forum regarding macros for this same spreadsheet, here is the macro that is currently running just in case it makes a difference if there is an answer to my current problem.
Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address).........................
View 9 Replies
View Related
Aug 24, 2006
I want to use (VBA) data vaildation to restrict duplicate entries.
Worksheet Name = "Metal Type"
NoDupe Cell = B15 (allowing text and numbers)
Range to check = (Sheet)="Metal Type" (Range)=A21:A100
Error = Message box "Enter Unique Name in Cell B15"
Then Exit Sub
View 4 Replies
View Related
Jan 22, 2007
In my spreadsheet, on ( sheet A) I pick up a value to a range (O2:O22) from other worksheet (sheet B), the value is validated when a cell in the same range but other column (A2:A10) reach a specific criteria.
But in the same column if the criteria is typed again I gone a have the same value on range( O ), and I dont want that, because this value represents a total for a day, and is to be added to other cell.
I'm using this, to pick up the data
Ex:
(A2:A10) Criteria
(O2:O10) value picked up
on SheetA, Sumif(sheetBA10:A40,A2,SheetBK10:K40)
How can I count only one of the values picked up from sheet B?
View 5 Replies
View Related
Feb 14, 2007
Got a workbook with 4 worksheets and sometimes we need to cut and paste a row from one worksheet to another, now thats easy now as we are all human and sometimes it gets copied and we have duplicate logs.
Now each sheet has usually not got that many rows in it, but that spreadsheet is getting sent to the highest people in the company and any mistakes look poor.
What i'm looking for is a way that i can highligh any rows that a duplicated in each worksheet. I can do it in each worksheet using conditional formatting with something like = COUNTIF($A8:$A$20,A8)>1. but don't know how to check that is not on any other worksheet. On every worksheet only need to check Col A for the duplicate information
View 9 Replies
View Related
Mar 7, 2007
Wihitn a large SS I have a column which has a number of entries that may include an amount of duplicate entries. Till now I have been using Sort and them manually deleting the duplicates is anyway that I can select just the Column and then without sorting go down and if a duplicate is found, then the duplicate would be deleted. Meant to say the dta is numeric.
View 2 Replies
View Related
Mar 27, 2007
I have a spreadsheet which has data in column A & B. I trying to do an if statement to state "YES" if data in both A and B has been duplicated. EG 111 in (A) & 222 in (B) are a PAIR, im need an if statemnet in Column (C) to say if this PAIR are duplicated in these two columns return "YES". I have tried THREE different methods to do acheive this which are in the attached document. The main issue I am having is the iff statement is returning yes for some of the number when thye are completely different. I have used concatenate, & and tried to multiply the two values to try to make it unique. I have an example of each in the separate tabs.
View 7 Replies
View Related
May 21, 2007
I am trying to determine wether there are duplicate entries within a range and have then counted also. Example:
I have a list if zip codes and I want to find out how many of each zip code are there. If I have a list with:
12345
12345
12345
12346
12346
12347
I want to have it tell me there are 3 of "12345" and 2 of "12346", possibly next to the entry?
12345 3
12345
12345
12346 2
12346
12347
View 4 Replies
View Related
Feb 22, 2007
I have been using the wrong formula to count total entries in columns and only just found this error. The MAX formula in cell B4 is: =MAX($B$12:$B$36). If the all the rows are full within range F12:F36, then the MAX formula is fine to count the total within range B12:B36 (25) so I thought. But sometimes there are omissions between F12:F36. If there are 2 blank cells anywhere within F12:F36 for example, then B4 needs to show 23 respectively. In the sample WkBk B4 needs to show 8
View 2 Replies
View Related
Apr 7, 2014
I have a huge document that looks like this
Column A______Column B_____Column C
100/12__________B___________$
100/12______________________@
100/12______________________€
250/13______________________€
250/13______________________$
I want to keep in ColumnA all three rows of 100/12, because it has a value in Column B in one cell-which is the criteria, and remove the 250/13 because it has no value in cell B.
I was assuming that merging duplicates in column A, and than remove empty from ColumnB.
View 2 Replies
View Related
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
It’s 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 it’s 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
