Concatenate Duplicates + Delete + More Steps
Oct 15, 2009
I have 2 columns of data with 7,000 rows that I am trying to manipulate in the following way. The first column contains a list of car manufacturers (not really my example, but involves the same concept). The second column contains a list of different models of cars. For instance,
Ford | Taurus
Chevrolet | Avalanche
Saturn | Sky
Ford | Explorer
Honda | Accord
Ford | Taurus
What I want is a list of 2 columns with UNIQUE car manufacturers in first column with a list of UNIQUE car models (comma delimited) in second column. So for this example, the first row could read " Ford | Taurus,Explorer". I don't want Taurus to appear twice. I got a lot of help from JBeaucaire from this post. I just can't add the ability to remove car models from the list also. Thanks in advance for all the help!
View 7 Replies
ADVERTISEMENT
Apr 1, 2009
I have an excel 2007 spreadsheet that lists
items on multiple rows for the same customer.
Each sheet will list anywhere from 25 to 100 individual customers.
I need to identify the duplicates, concatenate
the "Items" to a single cell on the first row, and
delete the duplicate rows. eg:
Cust #| Item
1 | A
1 | B
1 | C
2 | B
2 | E
3 | A
3 | C
3 | E
The result should look like:
Cust# | New Item
1 | A, B, C
2 | B, E
3 | A, C, E
View 14 Replies
View Related
Oct 6, 2007
I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.
ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C
I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.
ColA ColB__ColC
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C
View 5 Replies
View Related
Nov 20, 2013
I have a table that looks like this:
ID
HostLocation
NDaysinHost
marina.silva
USA
100
[Code]....
I would like to obtain a table in the following format where I can find
ID HostLocation NDaysinHost
marina.silva USA, Norway, Bolivia, Italy, Ecuador... 100,45,67,8,9...
tatiana.gottig Venezuela, Chile, Peru, Canada 89,54,32,6
Searching in the net I found the following code:
VB:
Sub groupConcat()Dim dc As Object
Dim inputArray As Variant
Dim i As Integer
Set dc = CreateObject("Scripting.Dictionary")
inputArray = WorksheetFunction.Transpose(Sheets(1).Range("A2:C9").Value)
[Code]....
However I only obtain the first two columns concatenated. It says that for more columns repeat the loop, however I did and the result was not the expected, it returned me the same two columns again concatenated. How can I arrange this to fit my criteria?
View 1 Replies
View Related
Jun 23, 2014
I currently have a spreadsheet that contains about 1700 lines of data related to ~400 different clients which I am looking to consolidate.
The data is currently in this format (a set like this repeated ~400 times with anywhere between 3 and 7 services):
Company ID #
Company Name
Service1
Company ID #
Company Name
Service2
Company ID #
Company Name
Service3
I would like to have the data in this format (one row per company with all of the services in the third column concatenated into a single cell, all while removing the duplicate rows.)
Company ID #
Company Name
Service1, Service2, Service3
How to create the code (or describe the process) necessary to do this?
View 5 Replies
View Related
Nov 17, 2009
I've been working on a spreadsheet that is used to track the application process of numerous clients. In doing this, I have a row at the top of each client that is used as a sumarry column. Below each cell in the top row, I have up to 30 non-adjacent cells that I'm trying to concatenate in the top cell separated by new lines. I'm also wanting to not display any duplicates, as there are up to only 5 steps that could be shown for each record. I found a UDF that will search an array and return only the unique values, but it accepts the data as an array, and it returns it as an array also. I seem to be having trouble passing the array from the sheet as well as formatting and returning the array with line breaks.
In the worksheet cell: ....
View 10 Replies
View Related
Oct 14, 2013
I need to detect 2 levels of duplication.
I have a data table that is similar to below (actual table is very large 1000+ rows) but duplicate column is empty.
I don't want to add additional data (i.e. formula to perform concatenate) to other columns as it takes a very long time even with screen updating turned off.
I want to use vba to
1) determine how many rows there are in the worksheet (this updates from an outside datasource)
2) Concatenate baseno & altno and determine if the concatenated value is duplicated
3) Within those duplicates for each baseno&altno pair determine if verno is different
4) Set "Duplicate" value for each duped baseno&altno pair where verno is different
So the end resulting table after macro runs would be
baseno
altno
verno
duplicate
[Code]....
I have been for weeks now and just can't get this to work. I can get it so that I can determine duplicates on one column but I can't seem to get it to concatenate inside the script and compare without writing back to the sheet (which is too slow and I don't want to do).
View 8 Replies
View Related
Jul 25, 2011
I need to concatenate varying numbers of cells based on duplicates found in a separate column, but I'm not sure how to approach it. I have 41,000+ rows of data, so I have to find a formula.
Example:
1AB2Denton, PaulFB357D4D3OwensTest, MarcyFB539F934Brennan,
JosephFB539F935Bowser, AmyFB539F936LaRock, ChuckFB667D3B
Based on duplicates in column B, I want to combine the data in column A into one cell. The duplicates in column B could be only 2, or could be 20+.
View 2 Replies
View Related
Jun 5, 2009
I have a spreadsheet with three columns (check #, invoice #, vendor #). If a check pays multiple invoices, there is a separate row for each instance of invoice #. (ie if a check pays 10 invoices, there are 10 rows all with the same value for check # and vendor #, but different invoice #s). I need to concatenate the multiple rows to just one row per check #, with all the invoice #s in separate columns on the same row and delete the duplicate check # rows.
However, I can only use 10 columns, so if there are more than 8 invoices matching the same check #, the 10th column will need to contain invoices 9-n concatenated and separated by a space. I have over 5000 rows in the spreadsheet.
View 2 Replies
View Related
Apr 10, 2014
I've attached a test excel to this post. Column C contains several identical email addresses. I need ALL the rows containing the duplicate data removed, not leaving one remaining as Remove Duplicate Values normally does.
I'm going to be doing this with many Excels all with differing amounts of data surrounding column C. I need a reliable way to achieve this, regardless of how many extra data columns exist. If there is a duplicate of data in column C, all rows containing that data need to go, regardless of what other data may also be present in those rows.
View 10 Replies
View Related
Jun 15, 2006
I'm new to this and trying to write my own code (quit satisfying actually), I realise there's a lot of threads on duplicates but I couldn't really solve my problem. My code looks like the following
Private Sub Dubletter_Click()
Dim i As Long, lastrow As Long
Dim cell As range
For Each cell In Sheets("LR").range(Cells(3, 2), Cells(Rows.Count, 2))
lastrow = Cells(Rows.Count, 2).End(x1Up).Row
For i = lastrow To 3 Step -1
If Cells(r, 2).Value = cell Then
cell.EntireRow.Delete .....................
View 9 Replies
View Related
Feb 26, 2007
I want to create a macro that will look at different account numbers and delete those rows that have a sum of zero for that same account number. In other words, I am working with an accounting spreadsheet which shows both accounts payable and receivable. When payment is received for an account, I would like the macro to delete it, leaving only outstanding accounts. For instance: ....
View 7 Replies
View Related
Mar 12, 2014
My issue is that I have 1200+ addresses to make more readable - the first column is the street name, the second is the post code(s) relative to the street - what Ideally I'd like to achieve is one row per street, the street name followed by the post codes... i.e. take this...
A33 Relief RoadRG2 0RR
Abbey SquareRG1 3AG
Abbey SquareRG1 3BE
Abbey SquareRG1 3BQ
Abbey SquareRG1 3FB
Abbey StreetRG1 3AN
Abbey StreetRG1 3BA
Abbey StreetRG1 3BD
Abbots WalkRG1 3HW
Aberford CloseRG30 2NX
Admirals CourtRG1 6SP
Admirals CourtRG1 6SR
Admirals CourtRG1 6SS
Admirals CourtRG1 6SW
Ainsdale CrescentRG30 3NG
Alan PlaceRG30 3BW
Albany RoadRG30 2UL
to this...
A33 Relief RoadRG2 0RR
Abbey SquareRG1 3AG, RG1 3BE, RG1 3BQRG1 3FB
Abbey StreetRG1 3AN, RG1 3BA, RG1 3BD
Abbots WalkRG1 3HW
Aberford CloseRG30 2NX
Admirals CourtRG1 6SP, RG1 6SR, RG1 6SS, RG1 6SW
Ainsdale CrescentRG30 3NG
Alan PlaceRG30 3BW
Albany RoadRG30 2UL
View 5 Replies
View Related
Jun 9, 2014
How to plot a step graph?
I have attached a drawing by illustrating the graph that I wish to plot.
Attached Image : jpg Capture.JPG
View 9 Replies
View Related
Jul 15, 2009
I have written a macro, big cheers as it's my first, and it even works as I wanted it to.
Trouble is when you run it, it shows you all the steps it is making. Is there any way to stop this and purely for the end result to appear? It may well also have something to do with my PC needing more RAM to make it faster. Ever since upgrading (?) to 2007, my PC feels like it is stuck in quicksand.
View 5 Replies
View Related
Feb 21, 2010
Need advice/opinions of where to go 1st to learn all about VBA/Macro's from a novice point of view.
View 3 Replies
View Related
Sep 17, 2007
I am trying to produce a list of numbers (preferred Sheets Function and not VBA).
Every group should be of 5 Identical Numbers - starting with 2 and step of 2.
(An Example is shown in the attached picture).
I would like to produce that list with the help of ROW() and all available build-in Functions.
If possible, I will appreciate very much some explanation as for the principal of producing such a list when, for inatance, the step is 3 , starting from 3, while every group includes only 4 identical numbers and the first cell, to present the first value, is A11 and down to A26 - presented here in Horizontal layout:
(3,3,3,3,6,6,6,6,9,9,9,9,12,12,12,12)
View 3 Replies
View Related
Dec 17, 2009
I want to do, is search column A for claim numbers that match. When I do have a matching claim number, I want to concatenate the original cells ownership field with the said matching cells ownership field (or move into a column in the same row, I can always concatenate later). Once that is complete, I want to delete the row I took the information out of.
I want to join this data in ArcGIS, but as of right now, it's not a 1-to-1 relationship, so only a relate works. That doesn't help me as I want to display claims by ownership, and this can vary per claim. Company A may have 100% on one claim, and then split another claim 50% with Company B.
This causes a double entry on the claim field in this current spreadsheet I have, which requires me to clean it up by making multiple columns of ownership vs. an additional row for shared ownership.
My problem:
Column A Column B
1235555 Company A (50%)
1235555 Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%)
1235557 Company B (33%)
1235557 Company C (33%)
What I would like to see
Column A Column B Column C Column D
1235555 Company A (50%) Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%) Company B (33%) Company C (33%)
View 3 Replies
View Related
Jun 19, 2013
I have a column with text values, I would like to remove all the duplicates. How can I do that??
View 4 Replies
View Related
Oct 24, 2013
I have two columns with emails. I would like to delete the emails that have duplicates. I want to delete them from both columns.
View 3 Replies
View Related
Jun 3, 2014
how to Delete Duplicates in Column D with a macro. It would search all of Column D and look for Duplicates and if it finds an exact value match it will delete one of the rows containing the duplicates. This is running in between a much longer macro which is the reason why I can't do it with conditional formatting.
View 12 Replies
View Related
Jun 4, 2014
I'm trying to create a macro that will Delete Rows of Duplicates found in Column D. It will start with the first row it finds that is yellow and check to see if that Column D matches any other yellow Column D's. If it does then the row the match is in gets deleted. Right now it searches in between the yellow rows, but for this portion I'm looking for it to use the yellow rows only. I included a file that has the examples on sheet1 and the expected outcome on sheet2.
View 6 Replies
View Related
Mar 12, 2014
I have an excel sheet which has product list according to each site. I want to count any duplicate products and delete the duplicates.
I have attached an excel worksheet with example template to show what i want it to look like.
View 1 Replies
View Related
May 7, 2009
i'm trying to do sounds simple enough. I have 2 Columns of data, example;
1-Apr 1423
1-Apr 1426
24-Apr1430
8-May1454
8-May1460
15-May1500
15-Jun1525
15-Jun1555
15-Jun1556
I want to clear (not delete) the cells with a duplicate date in them while keeping the cell with the number next to it. So in this case they would be A2, A5, A8, A9. I've come up with a code that looks pretty logical but it doesn't work. Here's what it looks like.
View 3 Replies
View Related
Jul 2, 2009
Hi, the code in the codebox under here, which I wrote, is supposed to delete all duplicate records in the column. Which column to uniquify is stated in cell Skin!B17. I searched the internet round, and there are different solutions everywhere. I have been unable though to find the right one - so I thought, why not ask here. If you have a solution, I'd be happy to hear it ...
View 14 Replies
View Related
Dec 24, 2009
I have a list of 18k rows, and about 7.5k are known duplicates. The delete duplicates button will not delete the duplicates (except it keeps returning 41 duplicates for an unknown reason, but there definitely thousands more it is missing). I tried the remove duplicates button, advance sort, coloring cells, and some macros. The list is the combination of two lists, one older one just compiled, I was able to seamlessly delete dups in another list.
View 9 Replies
View Related
May 23, 2007
2500 or so rows
based on these 3 columns:
[A], [B], [C]
company [A] , Account # [b], Fund # [c]
need to delete duplicates but keep lowest Fund # one.
basically if company and account # are the same then delete but keep the one with the lowest fund #
View 10 Replies
View Related
Jun 6, 2006
I have a macro that Deletes duplicates in a column. But it deletes all but the last cell. Does anyone know how to change it so It keeps the first cell and deletes the subsequent duplicates Instead?
View 9 Replies
View Related
Jul 10, 2006
I am writing a spreadsheet to do the following. I have clients that submit lists of words (3 columns of data) that need to be normalised (ie - certain punctuation removing, casing to lower etc) and then all duplicates to be removed. It then pastes everything into a new sheet. Now, it all works perfectly but, runs a little too slow.
Sub NormaliseDedupe()
Application.Calculation = xlCalculationManual
Application. ScreenUpdating = False
Dim my_advertiser
Set my_advertiser = Range("G1")
Dim my_client
Set my_client = Range("J1")
Sheets("Normalised Keywords").Select
If my_advertiser = "" Or my_client = "" Then
MsgBox "Enter the Advertiser & Client at the top of the sheet."
Exit Sub
End If
Dim my_date As String
my_date = Trim(VBA.Format(Now(), "DD.MM.YY"))........................
View 3 Replies
View Related
May 9, 2007
Here in the excel sheet i have columns upto K with rows upto 3295 In these columns the F column has Duplicates in that columns
My question is how we can delete the duplicates in that F column so that i can see my excel sheet without any duplicates in that F column.
View 3 Replies
View Related
