Concatenate Cells Then Determine Duplicates?
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
ADVERTISEMENT
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
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
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 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
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
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
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
View Related
Aug 11, 2013
Sampling table :
one
two
three
four
one
two
three
one
two
one
Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")
one , two , three , four
one , two , three
one , two
one
Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?
My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,
View 9 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
Nov 10, 2006
If I have A1:A1000 in a column, and I know that A1000 is the sum of 3 cells within A1:A999 but not sure which, what kind of functions can I write to trace out these 3 cells?
View 8 Replies
View Related
Oct 19, 2007
I need to create a macro that will make a cell only run its formula when an associated cell has something in it that will trigger the function.
View 2 Replies
View Related
Mar 27, 2008
Below is the code I used in order to calculate the price of an item depending on the number of items ordered, where the items are in column "I" and their corresponding prices in column "J"
The "If" and all of the "ElseIf" statements work fine except for the last "ElseIf" statement. It's not any different from the ones above it though.
Private Sub cmdCalculatePrice_Click()
Dim strDish As String
strDish = Range("B21").Value
Dim intQuantity As Integer
intQuantity = Range("C21").Value
Dim intPrice As Integer
View 4 Replies
View Related
Dec 11, 2008
I'm having a rather difficult time getting this one.
I am concatenating (is that a verb?) a bunch of cells to create an item code.
The combination of two different cells need to be able to give different values so I can dump the appropriate value into the CONCATENATE formula.
A picture is worth a thousand..
View 4 Replies
View Related
Apr 11, 2013
On tab 2 of a spreadsheet, I am trying to concatenate several cells on tab one! Everything comes together in the cell on tab 2, however it does not hold the date format. It turns it into general text so just random numbers that do not even tie to the date. Any way to lock the date format for this idea?
View 7 Replies
View Related
Apr 27, 2007
I have 7 consecutive cells which contain a %
e.g. A1 120%, B1 35% etc
I am trying to put them into one cell using concatenate
but am getting 1.20.3529411764705880.705882352941177
I would also like to separate them by comma,
View 9 Replies
View Related
Apr 11, 2007
I'm feeling pretty inadequate at the moment ... I'm trying to determine something that (I think) would be relatively simple ... How do I determine if a single column , for example, (B:B) is blank (or null)? Alternatively, how do I determine if a range of cells in a column or columns, for example, (B1:B30) or (B1:D30) is blank (or null)?
View 2 Replies
View Related
Jun 5, 2007
The compliment of a set is those elements not in the set. Excel doesn't do compliments well.
In Automatic Filter, one can easily see the rows that match criteria, but selecting the compliment of those rows (the ones that don't match the criteria) is difficult.
Similarly, there are the range functions Intersect and Union, but no Symetric Difference.
(Def: the symetric difference of two sets, AB, is the set of those elements in A that are not in B. {1,2,3,4,5}{2,3,4,10,11} = {1,5}.)
Other than by looping through cells:
Given two ranges, aRange and bRange, how would one code for those cells that are in aRange, but not in bRange.
View 9 Replies
View Related
Apr 30, 2009
I concatenate value of the several cells at one. (A1&"/"B1&"/"&C1 and etc. The length of characters may be different and I want it be looking neat by adding spaces. Something like that:
400/ 25/ 30
40/250/300
View 5 Replies
View Related
Jun 1, 2009
I've been trying to use IF OR and CONCATENATE to read 4 cells and give me a list of parts needed based on the data loaded in the 4 cells. I attached an example showing.
View 2 Replies
View Related
Sep 29, 2009
I know how to do the "normal" type of concatenation (concatenating?), but I was wondering if there was a way to concatenate a range of cells.
If my first three cells look like this:
A1 = Adam
A2 = Bob
A3 = Chris
I'd like cell A4 to say "AdamBobChris". I'm not worried about punctuation at this point. I thought I could just do =concatenante(A1:A3), but that's not working.
View 4 Replies
View Related
Apr 23, 2014
I have a Requirement where we need to concatenate the cells based on conditions
i have the attachment for the reference . please download the file from the link.
View 1 Replies
View Related
Jan 29, 2009
I have data in cells A1:A50. I want to combine all of them into cell A51. I know that I can achieve that by using =concatenate(A1,A2,A3,A4,........,A50) or =A1&A2&A3&A4&......&A50 but it would be very tiring to click on each cell. Just imagine if the data in cells A1:A1000? function that work like say =combine(A1:A50).
View 2 Replies
View Related
Feb 6, 2009
I cant quite get this, I have 4 cells in one row (a selection and can be any row), I need to string the values together and add some simple text.
Example:
Assume row 1 and columns A, B, C, D.
496 | 6d7 | Minor | Ø.375 +/-.010
What I need this to read as one long string:
#496, 6d7, Minor, Ø.375 +/-.010
Adding the text in Red.
Column A may be a single digit or up to 10 digits in length. Once the string is configured I want to copy it and manually paste in another application. If the string is placed any where else in the worksheet for formatting, it can be deleted after the copy/paste or macro end.
View 5 Replies
View Related
Apr 14, 2014
I'm trying to concatenate a few cells but seem to be missing the mark with my code. show me where I went wrong.
Code:
Dim counter As Long
Dim b As Range
Dim e As Range
Dim g As Range
Range("E1").Select 'Set the range for the loop
Selection.End(xlDown).Select
[code]....
View 5 Replies
View Related
Jul 17, 2007
I have the following formula in cell N4:
=CONCATENATE(O4,CHAR(10),"EBC Briefing: "&TEXT(P4,"dd-mmm-yy"),CHAR(10),"Site: ",Q4)
As long as 04:Q4 are ALL non-blank cells, the formula works. However, if ANY of the cells are blank, it displays the green rectangle in N4, but it doesn't offer the Trace option.
View 9 Replies
View Related
Nov 25, 2009
I have a macro to concatenate multiple cells but unfortunately it is not giving the result,
It has to concatenate below mentioned cells and put the result in column N
Sub ConcatColumns()
Do While ActiveCell "" 'Loops until the active cell is blank.
'The "&" must have a space on both sides or it will be
'treated as a variable type of long integer.
Range("N1").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-10],RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-1])& ActiveCell.Offset(0, 0)"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
View 9 Replies
View Related
Jan 6, 2007
I have a spreadsheet of inventory items that I am trying to prepare to import into Quickbooks. There are items in this sheet that are subitems of a parent item. The subitems have the same ID number (col. C in the attached sample), different quantities and different descriptions than the parent.
I need to to concatenate the Item Lookup Code (col. D in example) and the description, separated by a colon. Then I need to copy everything else in the parent record, except quantity and description, into the child records. There are close to 20,000 items in this sheet.
View 4 Replies
View Related
Mar 22, 2007
I want agglutinate some text values in the same cell. Something like that:
A1="John"
A2="Elton"
A3="Mark"
A4="AND"
B1=A1+A2+A3 =(John Elton Mark)
B2= A1 +A4 +A2 + A4 +A3 = (John AND Elton AND Mark)
View 3 Replies
View Related
Jun 8, 2007
I am attempting to concatentate several cells (10 to be exact). I only want to include cells that are not blank in the output. I searched the topics and found a similar problem. I attempted to modify that solution to fit my needs but I obviously do not quite understand exactly how it worked. I have attached a sample. The expected results are that if in row 1
columns a through j have any value they will then be concatenated or joined in some other fashiion seperated by a comma. I only want to include non blank values. I have placed the code that I attemted to use in this post.
=IF( COUNTBLANK(GL41:GU44)=0,GL41&", "&GM41&", "&GN41&", "&GO41&", "&GP41&", "&GQ41&", "&GR41&", "&GS41&", ">41&", "&GU41,IF(ISBLANK(GU41),""&GL41,GL41&", "&GU41))
Some of these cells are merged as indicated in the range of countblank().
View 6 Replies
View Related
