Unable To Merge Duplicate Data?
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
ADVERTISEMENT
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
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
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
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
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
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
Jun 2, 2006
I can choose some cells in a sheet to protect them and prevent any one to change.
it's supposed to change and edit in the other cells.
the problem which I face when I try to use " merge and center" or to insert picture or autoshapes ..etc. in unprotected cells!
View 8 Replies
View Related
Feb 28, 2014
I've Created Connectionstring for excel which is in database format and trying to get the data by comparing 2 sheets using he below code.. But not working..
Set ws = Sheet1
sSQL = ""
sSQL = "SELECT [Company Code], "
[Code].....
View 1 Replies
View Related
Jun 15, 2006
One of my workbooks contains 6 worksheets. Quite often I need to copy a row of data from sheet 5 to sheet 1. I right click, copy, select sheet 1, right click and find that the paste options are greyed out and so unavailable. If I wanted to, all the other sheets would allow me to paste the data into them but not sheet 1. It did work until recently so I suspect that it is something that I have inadvertantly done. I have checked things like security, protection and the like but find myself now going around in circles.
View 3 Replies
View Related
Feb 9, 2014
I am trying to graph the amount of calls that come in on each day and at what hour. I am trying to put the days of the month along the x axis and hours along the y axis. I then want the number of calls to show as a scatter at certain times on each day. Is this possible? I have attached a sample of a table I will be working with that has made up data on it. I have tried using the select data function to try and change the axis, but I do not think I am writing the correct things in.
View 4 Replies
View Related
Nov 30, 2006
I am having trouble getting this list of data sorted the way that I would like.
Here is the data:
View 13 Replies
View Related
Jun 1, 2006
I have a function written in excel written in VBA. The function reads data from a worksheet and uses it in an array to solve based on the variable passed with the function. Now the issue is if I change variable that the are passed with the function it works fine. If, however I update values in the worksheet that the function is using as an array (sort of a look up table) the function doesn't see the change. I have to exit Excel and reopen it to get it to re calculate the function. Is there a better/faster way than continually closing and reopening excel?
View 3 Replies
View Related
Jul 14, 2006
I have tried using the below mentioned macro. However, it only work on one cell. I need to copy and paste a range from A1:Q200.
Sub retrievedata()
Dim wbResult As Workbook, wbSource As Workbook, CopyRng As Range, Dest As Range
Dim FileName As String, Filt As String
Set wbResult = ThisWorkbook
Set Dest = wbResult. Sheets("Data").Range("A1:Q200")
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Filt = "All Files (*.*),*.*"
FileName = Application. GetOpenFilename(Filt)
Set wbSource = Workbooks.Open(FileName)
Set CopyRng = wbSource.Sheets("Summary").Range("A1:Q200")
Dest = CopyRng
Application.ScreenUpdating = True
Application.DisplayAlerts = True
wbResult.Activate
wbSource.Close
End Sub
View 3 Replies
View Related
Apr 21, 2014
I just copied a table from the web which contains numbers in the last column.
I pasted the data into Excel, but when I try to run any type of calculation i receive a #VALUE! error.
The annoying 'workaround' is to re-type the same numerical text into it's own cell, which allows for calculation.
I've tried changing the cell formatting to 'general', and 'text', but I still receive the #VALUE! error, when trying to calculate.
View 1 Replies
View Related
Apr 19, 2008
Very simple Excel 2007 spreadsheet. It consist of four columns with either numeric or alpha data in the. No formulas, no macros etc...
She emailed the spreadsheet to me and it works fine, I can enter data without problems. Whenever she tries to use it she just can't enter any data. She clicks in a cell and it highlights but she doesn't get a cursor and can't enter data. If she tries to click in the area underneath the toolbar to enter data she also does not get a cursor!! It used to work just fine for her but has suddenly stopped!
View 5 Replies
View Related
Jun 4, 2013
I have a pretty simple spreadsheet (because I'm not an expert on excel) to track temp staffing requirements each week over a year. It only has about 150 rows and a bunch of columns but when I try to make it bigger (add more rows so I can put about 70 staffing positions instead of 19) it will not save (cannot save all of the data and formatting). I have read on the microsoft site that there are limits to data formatting, but it describes over 2000 rows?
View 3 Replies
View Related
Sep 4, 2013
Error in data validation: "A named range you specified cannot be found." However, the named ranges are in the Name Manager, and if I go to the sheet and select the range, I can see it is already named.
Full description:
I created a spreadsheet model (no VBA, just formulas, etc) in a workbook that another coworker took and added VBA to. However, he was not working with the most updated version. So last night I checked the sheets which had the same names, made sure there were no differences except the changes I had made since that version, and then moved the ones from my current workbook into his workbook.
On the sheet where the user was to input details of the report, there are 4 cells that each contain different lists (non-dependent). I want the user to only be able to choose one of the 4 drop down menus - that is, if any of the 4 cells are filled in, I would like the other 3 to default to N/A (or somehow not be allowed to be chosen). This was solved in a previous thread:
Using A1: =IF(COUNTA(A2:A4),NA(),G2:G6) from the other thread solution, I used a named range instead of G2:G6 and it worked well.
When I merged the files together, the 4 cells no longer worked at all. It was suggested that I look at the Name Manager, and some of te named ranges I had were in there multiple times. I cleaned them up.
I tried to enter the same formula again, and it gave me the specific error: "A named range you specified cannot be found." However, the named ranges are in the Name Manager, and if I go to the sheet and select the range, I can see it is already named.
I am unable to load the spreadsheet due to sensitive data, and removing the data would make the workbook make no sense.
View 6 Replies
View Related
Feb 7, 2014
Excel 2013 on a surface tablet and attempting to create a data form. I've followed the necessary steps to try and add the "Form" button to the quick access toolbar, but "Form" is simply not a listed command. Have looked in "All Commands", "Commands not listed in the ribbon" and "Data Tab" and it's nowhere to be found. Not greyed out, just not there.
Frustrating because it's so easy to create a form on past versions of excel. All the tutorials I've seen online explain how to add "Form" to the quick access toolbar
View 1 Replies
View Related
Jul 12, 2009
I am putting together multiple worksheets with dumped data that should sort themselves by the press of a button. Each entry has a 'code' and a value and they are sorted by the 'code'.
At the moment i have the first worksheet sorting correctly and i am trying to program the second worksheet to sort data into the existing worksheets if they exist or create a new worksheet if the data doesn't have it's own worksheet.
Here is the code i am working with.
View 12 Replies
View Related
Aug 2, 2013
I have three source workbooks that needed to be consolidated into a master workbook pasting the data with values & formatting.
The Master workbook also contains 6 additional worksheets that link to the data pulled in from the source files.
My question is how do I write the code so the source files populate the master in a specific order.
For example, the 3 source files are named "Central" "NED" and "WEST" and I need them to populate the master workbook in that order.
This is the code I am using to consolidate the data:
Option Explicit
Sub ConsolidateSheetsFromWorkbooks()
'Author: Jennifer Starr
'Date: 7/12/2013
'Summary: Open all files in a folder and merge data (stacked) on all
[Code] .....
View 1 Replies
View Related
Mar 29, 2014
I got the following data and I like the results to be:
Data
Company | Country
ABC | USA
ABC | London
BBB | USA
CCC | USA
Results:
Company | Country
ABC | USA, London
BBB | USA
CCC | USA
Is it possible to this under Pivot? as I need to consolidate lots of data..
View 2 Replies
View Related
Jan 23, 2013
I have a large spreadsheet where some data are similar. for Example
Robert $1000
Louis $600
Glen $350
Doreen $2300
Robert $500
Louis $600
Glen $750
Doreen $300
Robert $3000
I want to merge all Robert together, all Louis together and show as one Robert $4500. How can I do that ? Should I use a Look up.
View 11 Replies
View Related
Jun 7, 2013
I have 2 data sets which i want to add together. Here is an example.
Data sheet 1
A B C
SAC
L
1
INPUT
L
4
BLIN
L
12
[Code] .......
What i want to do without inserting every line manually.
A B C
SAC
L
1
FIX
L
2
[Code] ........
View 2 Replies
View Related
Dec 4, 2013
I have an empoyees first name in A1 (Joe) and Surname in B1 (Bloggs), however, I want "Bloggs, Joe" in C1?
View 2 Replies
View Related
Oct 23, 2007
I'm quite proficient in Excel but am struggling here, would really appreciate any help
I basically have 80 tabs with IP addresses in Column A and then information in Columns B, C and D related to this.
What I want to do is bring all this together in one sheet
Possible?
View 7 Replies
View Related
