Remove Dupes From Concatenated Text?
Apr 3, 2013
I am trying to figure out if there is an easy way for me to remove duplicate text when concatenating cells together. For example if I have the following cells defined as such
A1 = red green orange
B1 = orange blue purple
when i do a CONCATENATE(A1,B1) I obviously get red green orange orange blue purple
How can i get excel to remove that duplication of the word orange without assigning each individual word to it's own cell and using the remove duplicates function?
View 3 Replies
ADVERTISEMENT
Feb 4, 2010
I have a formula that I create using concatenate and I get the right formula as below:
Cell B63
="=MATCH(B61,A"&N(1+B)&":A50,FALSE)"
The result comes like this:
=MATCH(B61,A9:A50,FALSE)
I would like to use this as a formula, however I cant figure this out
=Indirect(B63)
give me a #REF error.
View 7 Replies
View Related
May 8, 2009
I would like to compare email addresses for dupes and add missing PIN number in Column F Sheet 1 from Column F in sheet 2. Is this feasable and relatively simple? I have enclosed a test example as teh acutual scenario has thousands of entries.
View 3 Replies
View Related
Sep 16, 2009
I have a dynamic range of text data that has duplicates in sheet 1, and I would like to have a list formulated without the duplicates into sheet 2 (in a range of cells that I choose). Would anyone be able to help me with this?
View 10 Replies
View Related
Mar 8, 2012
I have two columns each having 8500 rows.
A= Zip code
B= Sales
Duplication of zips appear in column A - and I only need to return the highest sales in B. So based on the example below..I only need to return $500 in column C.
A
A2=50201
A3=50201
B
b2=$345
b3 $500
View 4 Replies
View Related
Jul 18, 2007
I would like a macro to invoke after selecting multiple rows that would be concatenated into a single row containing no dupes within each column of selected cells.
Details:
I would like to select '2 to n' rows. I would like the macro to concatenate the cells of the columns only within the selected range. However, if any of those cells are duplicates or blanks, I don't want them included in the concatenated cell. After the concatenation of all selected rows is complete, I'd like the macro to delete all the selected rows, except for the final concatenated row.
[NOTE: Rows 3 and 4 (below) would have been deleted by the macro.
Row 8 would become the final product in row 2's place.]
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1Sample*Data:*****2aaaxxxflintstone,*fredXYthese*are*notes3aaaxxyrubble,*barney*N*4aaaxyz*X*add*notes5******6******7Desired*Output:*****8aaaxxx
xxy
xyzflintstone,*fred
rubble,*barneyXY
Nthese*are*notes
add*notesSheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Oct 19, 2007
I'm using the following formula to get a count of records that have a matching function name that is in B10.
=SUMPRODUCT(--('Oct-2007_FunctionSurvey'!$T$2:$T$1498=B10))
My Problem: There are duplicate entries in my recordset based on COl A (AppID).
How can I change the formula to only count the occurences of the B10 values based on a unique AppID (col A)?
View 9 Replies
View Related
Apr 27, 2009
I have this task to solve:
a) import a txt file to excel formatting it as text
b) in column D remove the preceding space
c) find duplicates in column A and delete the entire row with the older one according to Date in column B
d) then convert data in D according to Conversion table integrated into the code and print conversion results into column J.
e) the last step is to print/copy columns A and J so that it looks like the final table in Sheet2.
Here are files attached.
sample data.xls
sample data.txt
conversion table.xls
To summarize, I need to go from a txt file like the one attached and arrive at the table in Sheet2 of sample data.xls file attached.
View 11 Replies
View Related
Aug 12, 2014
I merged about 15 adresslists from media contacts to one excel list. Each list had a name i.e. music, health, theater, etc. and the same logic in colums. I added a few columns and have 1 large list now.
As some journalists write about music & health & theater, architecture, etc. they are listed up to 10 times in the new list now. But the "genres" from the original list i.e. music, health, theater, etc. are in different columns. Some of the lines have empty fields (i.e. no address or mail)
All I want to do is have one line with all the information of all 10 lines in it, merged, dupes removed:
company - firstname - lastname - Adress - Mail, etc. : genre: music - health - theater:
example.xlsx
I atteched an example of the full list and the result i want
View 9 Replies
View Related
Jun 12, 2014
I have the following two columns, and would like to obtain for each individual Company, the corresponding Country values excluding duplicates as text in a single cell.
Company 2Country B
Company 2Country C
Company 3Country C
Company 3Country C
Company 5Country A
Company 5Country C
Company 5Country C
For example:
- For Company 2, a cell containing "Country B, Country C"
- For Company 3, a cell containing "Country C"
- For Company 5, a cell containing "Country A, Country C"
I've approached generating an array using an IF statement, as in =IF(INDEX(A1:A8="Company 5",,),INDEX(B1:B8,,)," "), which returns the following array: ={" ";" ";" ";" ";" ";" ";" ";"Country A";"Country C";"Country C";" ";" ";" ";" "}.
The question is: how do I get that array to produce, as text in a cell: "Country A, Country C". Note that the duplicate Country C has been removed.
There are a few "StringConcat" User-defined functions that I've found elsewhere on the internet, but they don't seem to be able to handle to conditionally generated IF Index array, which I would think is key to parsing between Countries corresponding to each Company in the list.
View 3 Replies
View Related
Jul 12, 2002
I have a column in which the cells contain values such as "012321 BGH YRK". Is there any way you know of that I can remove just the text from every cell and keep the numeric values? A mass operation because there are literally hundreds of these cells. I am using Excel 2001.
View 9 Replies
View Related
Jan 31, 2013
struggling with the syntax of this one. Lets say we have a variable called MYWEEK with a value of WEEK 9 and cells A1 and A2 have values of 9 and 10 respectively. I need to be able to compare the variable against cells A1 or A2 but with a prefix of WEEK .
I've tried the following:
if MYWEEK = "WEEK " & A1 then ...
if MYWEEK = "WEEK " + A1 then ...
if MYWEEK = ("WEEK " & A1) then ...
I know the answer is staring me in the face but I cant see it. Any use of the ampersand presumably invalidates the IF statement by introducing multiple conditions rather than a concatenated value. How do you compare a value against a concatenated set of criteria in VBA?
View 2 Replies
View Related
Oct 30, 2007
I have a TextBox that I want to show the concatenated value of two cells. Is that possible? Or do I need to concatenate it on the sheet and then have the TextBox = that cell?
View 9 Replies
View Related
May 13, 2009
Effectively I need to concatenate two columns in Sheet1, and then vlookup each concatenated value from Sheet1 in another concatenated column in Sheet2 and return the value from the next column
Now that is quite straight forward, but I am new to coding, and I was wondering if there was a way of doing it that doesn't involve having the concatenated values written to a column as a formula. Ideally what I would like it to do is put the concatenated value of Sheet1 together as a variable (I think thats what you call them) and then look up that variable in Sheet2 without Sheet2 creating a concatenated column too.
I'm of the understanding that this might be slower than writing the columns, but there is a reason for my madness.
Let me know what you think, or if i'm barking. Any suggested reading would also be handy, I don't like to bug you guys for the full code straight out.
View 10 Replies
View Related
May 2, 2012
How can I countif on a row/range
I tried this but this wont work. All i want to countif there are duplicate rows. This formula will be copied down
=countif($A$1:$A$11&$B$1:$B$11&$C$1:$C$11,A1&B1&C1)
View 3 Replies
View Related
Dec 7, 2006
I have concatenated the following cells:
A1, B1, C1
The data in each cell is:
1, +, 2
I can concatenate them to show "1+2".
My question is, can I then perform the calculation that the concatenation produces?
I would like to be able to enter different operators into B1 in order to carry out different calculations.
View 9 Replies
View Related
Jul 6, 2006
I have a cell that containes a concatenate statement for two named formula. The value taht the cell returns is a multiple of 10 (i. e10, 100, 1000, 10000 etc etc.) then in the adjacent cell, i have a nested if statement giviing differing text dependent upon the other cells value, i.e if less than 1000, return text string of "good" , however the formula does not seem to accept the value given in the concatenate cell.
View 9 Replies
View Related
Aug 29, 2007
I'm trying to use this function which was posted as an answer (looking up a single value and returning multiple results, concatenating those results in one cell):
Function getfiles(DRng As Range, LURng As Range)
For Each ce In LURng
If ce.Value = DRng Then
holder = holder & ce.Offset(0, 1).Value & ", "
End If
Next ce
getfiles = Left(holder, Len(holder) - 2)
End Function
What is "ce" here? Auto Merged Post;additional info:
the original question was posted by jwhite68, Feb 27th 2007, "Return Multiple Values From Lookup To Single Cell"
View 9 Replies
View Related
Jun 19, 2014
I have charts that I have to update at work, and I want to make it as fast of a process as possible. Here is an example of what I want to do.
I want to put the letter B in cell 1, then the number 23 in cell 2. I then want to concatenate those two strings, which I can do. I end up with cell 3 which says B23. I then want cell 4 to to be the value of cell B23. If the B23 in cell 3 changes to C11, I want cell 4 to be the value of cell C11.
I would use an IF(x,x,x), but I want to to take into account the fact that it could be any cell on the whole sheet which would be one hell of a nested IF statement.
I need to do this without macros if possible.
View 3 Replies
View Related
Sep 8, 2009
I have an address field that is concatenated as such that the city name is attached to the end of the street name as follows:
4 Example WayOakland, CA 94601
I have over 200 of these with different addresses and cities in my workbook so it's not always the same length. How can I grab the city out of this string? Is there a way to key on the capital letter of the city? Or is there another way?
View 14 Replies
View Related
Apr 24, 2014
I need to show sum and not concatenated values have a userform w 60 fields for numbers as follows:
a) UserForm1.TextBox34 to UserForm1.TextBox41: 8 fields-CAD currency
b) UserForm1.TextBox42 to UserForm1.TextBox49: 8 fields-USD currency
c) UserForm1.TextBox50 to UserForm1.TextBox57: 8 fields-exchange rate
d) UserForm1.TextBox58 to UserForm1.TextBox65: 8 fields-Total CAD currency => the formula here works across the board
UserForm1.TextBox58.value = Format(UserForm1.TextBox34.value+UserForm1.TextBox42.value*UserForm1.TextBox50.value, "###,##0.00")
e) UserForm1.TextBox66 to UserForm1.TextBox73: 8 fields-local taxes in CAD currency
f) UserForm1.TextBox74 to UserForm1.TextBox81: 8 fields-federal taxes in CAD currency
g) UserForm1.TextBox82 to UserForm1.TextBox89: 8 fields-total for first column in CAD => the formulas here works as well across the board
UserForm1.TextBox82.value = Format(UserForm1.TextBox34.value + UserForm1.TextBox42.value * UserForm1.TextBox50.value + UserForm1.TextBox66.value + UserForm1.TextBox74.value, "###,##0.00")
have a grand total formula pulling in all values, and this one works as well; so i have 4 simple addition formulas, and the local & fed tax formulas do not.
however, the formulas for the sub-total for the local taxes and the federal taxes do not work; the results show a concatenation of the 8 values rather thn the sum of the 8 values; the formula for the local tax is as follows:
UserForm1.TextBox110.Value = Format(UserForm1.TextBox66.Value + UserForm1.TextBox67.Value + UserForm1.TextBox68.Value + UserForm1.TextBox69.Value + UserForm1.TextBox70.Value + UserForm1.TextBox71.Value + UserForm1.TextBox72.Value + UserForm1.TextBox73.Value, "0.00")
I have rewritten the formulas and still these two tax formulas result in concatenated numbers rather than the sum.
I would have u/l worksheet but it's roughly 4MB now.
View 3 Replies
View Related
Apr 23, 2006
I need bolding and underlining cells that are "Concatenated". For example, cell format for cell K12 is already bolded and underlined. If I concatenate (C1,K12,D2) I get a result that doesn't carry over the format of those individual cells - particularly K12 which I want BOLDED and UNDERLINED.
View 9 Replies
View Related
Oct 27, 2012
Concatenated cells. My issue is how do i search through a sheet with cell values that have been concatenated?
Lets take an example:
I would like to search for a string call sales in sheet1 which would display all the employees and schedules for that department (do note that employees can be part of not just one department). but my cell formatting is as follows:
Sheet 2, Column 1
Employee name
E.g. Row 2 Employee1
Sheet 2, Column 2
Department (cells concatenated)
E.g. Row 2 Sales, Marketing (Concatenated Cell)
Sheet 2, Column 3
Work schedule
E.g. Row 2 8 AM to 5 PM
So when you search for sales, it will display employee1 and work schedule.
View 1 Replies
View Related
Oct 28, 2008
I have a dataset (20,000rows) with Grade, Region and Salary. I need to calculate the 25, 50, 70 and 90 percentiles against each concatenated Grade and Region.
View 9 Replies
View Related
Jun 21, 2007
I have a lot of seperate data that I'm trying to graph via a macro. I have one macro with a Do Until/Do While loops that runs before I import the data that creates and seperates the data into dynamic named ranges. My goal was to create a macro that graphs the different ranges using another Do Until/Do While loop. You'll see below that I have the ranges concatenated inside the loop so it does just that, graphs each set of data in each worksheet by running the same macro. Below is the code for the specific graphing macro and at the same spot, where the series values are set using the concatenated range name, the error message, "Unable to set the Values property of the series class." I have no idea what is wrong, but my guess is it's probably something simple.
Sub Graph()
Dim Counter As Integer
Dim CounterEQ As Integer
Dim Title As String
Counter = 200
Do Until Worksheets("IndustryEmployment").Cells(1, Head) <> ""
Counter = Counter - 1
Loop
CounterEQ = Counter
Do While Worksheets("IndustryEmployment").Cells(1, Explode) <> ""
GoingTo = Worksheets("IndustryEmployment").Cells(1, Explode)
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("IndustryEmployment").Range( _
"U15:U16"), PlotBy:=xlColumns.......................
View 3 Replies
View Related
Oct 9, 2007
I would like to convert the data by VBA macro from a table format into a list format. The convention of the TABLE data is as follows:
First row is Header Titles
[TABLE]
[SITE],[LOC],[PROD], [MTH1], [MTH2], to ->[MTH12]
North, Office, Cooling, 100, 125, 85
[/TABLE]
I would like the data to be provided into a list format for each month.
For example:
[TABLE]
Row 1: North, Office, Cooling, Month 1, Value
Row 2: North, Office, Heating, Month 1, Value
Row 3: North, Office, Electricity, Month 1, Value
Row 4: North, Retail, Cooling, Month 1, Value
Row 5: North, Retail, Heating, Month 1, Value
Row 6: North, Retail, Electricity, Month 1, Value
etc
[/TABLE]
View 7 Replies
View Related
May 23, 2008
I have a worksheet that is utilizing a VLookup to pull some values from another worksheet based on a composite number. I have checked the numbers for their format, length, and value (dangling spaces, etc.). I still cannot get this VLOOKUP to work correctly. I have attached the file.
Column H on the Cost Report sheet holds the formula in question. It concatenates the values in Columns C,D,E,&F to create a composite number to look for in Column B of the Original Estimate sheet. It is supposed to pull the $ value from Column D of that range.
I have tried everything. I passed the composite number into TEXT, --(Double Unary), INT, tested for both #'s length with LEN and have converted the formatting between text and numbers.
View 6 Replies
View Related
Aug 6, 2014
I have a list of skills listed as codes in column A. I would like to write in a separate column, B, the name of the skill based on the fact that A contains a specific text. Only the first condition return a correct value, the others condition returns #VALUE! as if they are not satisfied.
View 5 Replies
View Related
Feb 18, 2014
I am taking a decimal degree (longitude) and converting it to binary then to hexadecimal. The value I am using is a Binary Angle 32 bit structure that will be translated into an 4-byte hexadecimal (with two characters per byte i.e. "20" is one byte and results in spaces).
This is what I have:
Input of 359.999999916 (in cell A2)
Cells B2 through AG2 I have converted A2 into binary (using formulas like "=IF(A2-180>0,1,0)" for cell B2, "=IF(A2-180*B2-90>0,1,0)" for cell C2, "=IF(A2-180*B2-90*C2-45>0,1,0)" for cell D2, and so on)
I then concatenated all of the results into one cell using the following formula:
=CONCATENATE(B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2,Z2,AA2,AB2,AC2,AD2,AE2,AF2,AG2)
To get a result of (located in cell A4):
11111111111111111111111111111110
However, when I attempted to use the BIN2HEX conversion on A4, I get a "NUM!" error. Here is the formula that I was using in cell A5:
=BIN2HEX(A4,8)
View 2 Replies
View Related
Apr 29, 2009
I have some data a pivot table I would like them to be concatenated on next column. The X signifies the Blank cell on the pivot table. Now I can concatenate (in 1 cell) using VBA but I don't know how loop thru the Blank cells. I have about 1000 rows of data. I would like Data like this:
Investment Price Source
009451AP0 FTID FTID 009451AP0 Total x
967673Z RWP RWP 967673Z Total x
AA.CDS23 MarkIt Markit AA.CDS23 Total x
AACE.BD10BA FTID FTID, RWP RWP
AACE.BD10BA Total x
[Code] .........
View 9 Replies
View Related
