Opposite Of Concatenate
I have 2,000+ cells containing text that I need to break out into multiple cells. They are names (ex. John M Smith MD) and I need each part of the name in it's own column. I need the opposite of concatenate.
View Complete Thread with Replies
Related Forum Messages:
Opposite Of Sumproduct
what is the opposite of sumproduct? I want to divide the quantity in column A by the opposing value in Column B to get the answer in B5. I made this example simple but it would be used on a much larger scale. if there is a function that would divide and sum ?
Concatenate Duplicates: Concatenate Results Of All Equal P/N's From Any Given List
I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.
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.
Change Value Based On Opposite Cell
In Column A I have a number of differnet country codes and for 2 of them I want to change the value in Column B. For Example if cell A1 is = to "SHA" I want cell B2 to be "SHANGHAI". If cell A1= to "SGN" I want B1 to be = to "HO CHI MINH". If cell A1 is = to anything else I want the value to B2 to remain unchanged.
Duplicate Every Nth Cell In Opposite Column
I have a worksheet, example data below, it has 150+ columns and varying amounts of rows 1 -> 2500+ per column. The row data is as follows:
Row 1: Has title of worksheet
Row 2: Date of the column
Row 3: Total of completed for the day
Row 4 - n: Serial number
I need to intsert a new column to the right of each column and then populate it with the date at the top of the column from the left, I need the new date value to go down until it reaches the end of the data in the left column. So effectivly each serial number will have date in the column to its right.
There are no blanks in the rows, or blank columns.
Example of existing:
**Note date is in second column and taken from top of first column. Does not need to update if date is changed.
Summing Up Opposite Numbers In A Column Based On Criteria
Imagine column A sorted by invoice numbers. Column B has payments applied but due to movement between unapplied cash, some numbers appear as negatives and positives for the same invoice number.
I want to have a formula which says that for each invoice number, look in column b and eliminate opposite numbers and post only unique values. Keep in mind that there may be multiple lines with the same invoice number.
For example, starting with line 5, for invoice 123, payments 400 and -400 in line 5 and 17 and payments 315 and -315 in lines 7 and 9 will zero out but any other unique payment will be picked up and displayed in column C. Lines 5 and/or 17 will be blank in column C.
Deleting Rows Where Values Are Opposite & Equal
I have a spreadsheeet with values in columns F:K from row 10 onwards
I need VBA code that will delete the rows where there are values opposite and equal in columns F:K from row 10 owards for eg if F10 is 10000 and K10 is -10000, F 25 is 150000 and G25 is -10000, G29 is -175000 and H29 is 175000, then rowa 10,25 & 29 must be deleted
Delete Opposite Rows Based On Multiple Criteria
I have a Macro that I use daily on a spreadsheet I get with raw line-item transaction data on it. Basically, it deletes columns I don't need to look at, applies column headers, deletes certain rows that I don't need to look at, applies an auto-filter and filters to the first group of data I need to look at, and puts a subtotal on a couple of columns at the top so I can see totals on filtered results.
I need an additional function in this Macro. At some point in the middle of the actions above, I need to evaluate all rows and delete those that are 'opposites' based on a couple of criteria. Basically, I need to delete any transactions that have been refunded in their entirety, because for my purposes those are zeroed out and I don't want to see them....
Copy Cells To Worksheets With Worksheet Name In Opposite Cell
I am developing a Workbook where I import data to one worksheet and then disseminate it to other worksheets. I am not sure if I am overthinking the process, but I am having difficulty writing the macro and getting it to run through the entire list of rows.
There are 4 Columns in the one worksheet, named "Update":
Column A supplies the name of the worksheet that will be updated
Columns B-D contain the data that will update the sheets.
The process that I am trying to write the macro for is a follows:
1. Select/copy the range, starting with B1:D1 on "Update"
2. Navigate to the appropriate worksheet to be update, determined by A1
3. Identify the first blank row, starting with A5
4. Paste the data B1:D1 to A5:C5 (or next available line)
5. Navigate back to "Update" and begin the process again with the next row B2:D2
This process should continue until there is no value in Column B on the "Update" worksheet.
Delete Rows If Opposite Column Cell Matches
I have a spreadsheet that is updated daily with data that falls into one of two categories: a) completely new to the sheet or b) existing from the day before but not taken care of yet. The items which fall into category a are evaluated, and notes added to the sheet. The items in category b must be compared to the new data, and duplicated lines deleted but notes from previous evaluation must be kept on the sheet. In addition, anything on the sheet that is not included in the new information needs to be deleted. I've set up a sheet with all old and new data with dates the data showed up as well as the notes associated with the old data, and have concatenated all the info from each row into column V (from another suggestion I got on a similar but not exactly the same problem which I also still haven't figured out). The sheet has been sorted by col V so any matching rows should be within 3 or 4 rows of the original. The problem is that my code does absolutely zilch!
' goes through coord. sheet and identifies duplicated lines, copies the date from the old row
' copies it into the new line, then deletes the old line
Dim rCell As Range
For Each rCell In Selection
If rCell.Value = rCell.Offset(1, 0).Value Then
If rCell.Value = rCell.Offset(2, 0).Value Then
Concatenate With If Criteria
I created a concatenate on a co-worker's spreadsheet so I could utilize the data, and it works fine. But then I realized when reviewing the results, that there are MANY instances where not all of the fields that the concatenation pulls from contain data. I verified the source, and found out that any of these fields may or may not contain data. Now I need to account for this within my formula.
The cells being concatenated are in columns G-K, my first formula (in cell L4) is:
=IF($A4=3,CONCATENATE(G4,"; ",H4,"; ",I4,"; ",J4,"; ",K4),"")
Now I am using a "; " to separate between each cell's text. I need to adjust this formula to account for the fact that any of those cells MAY not contain data. And if it does not, I do not want the separator ("; " to show), because otherwise, I get entries that display like this:
up to 10.; ; 1.NS.1.1
If necessary, I can easily place my separator in a specific cell (M1 would be good) and access it from there rather than the way I initially wrote the formula.
CONCATENATE Not Working
Create a column of concatenated values to serve as a "primary key" for a compare and merge tool (Synkronizer). For some reason, the CONCATENATE function isn't being recognized.
If I enter
instead of showing the strings located in A2 and B2 it displays
in the cell.
I tried creating a new spreadsheet and it works just fine. Does anyone have an idea of what might be happening? I have compared all of the options between the two spreadsheets and don't see any difference in them.
Concatenate A List
I have a list containing 3 the abbreviations of states in A1:A3 (one in each cell), and I would like to concatenate the list in cell B1 to display the list like this: "CA, TX, FL". At times, the list will only have one state (always in A1), but at other times it may have 2 or 3.
I tried: =IF(ISTEXT(A),A1&", "&A2&", "&A3,A1)
But, if A2 does not list a state then it returns "#value!"; and if A2 lists a state but A3 does not then it returns only the value in A1.
What can I do to get it to list all states listed in column A whether there are 1, 2, or 3 states listed?
Reverse To Concatenate
a way in which to split the following line into say 5 separate pieces
George Julius Aaronson (81) 12th Feb 1945 D 9th Jan 1948.jpg
George Julius Aaronson
12th Feb 1945
9th Jan 1948
so that they are in 5 separate cells on a row. just to make things harder no name in the first colum has the same number of forenames or the same number of digits.there are possibly 600+ of these lines of which the next one is below
(Hanna Auerbach (73) 2nd Oct 1949 D 13th Feb 1955.jpg)
From cell A1 to A10 I have the figure 5412587
In cell B1 I want the figure in Cell A1 to concatenate with the figure one but in cell B2 instead of figure one with figure two etc. etc.
So B1 will be 54125871, B2 54125872, B3 54125873 etc. etc.
the formula would be Concatenate(A1&1). I want the "one" in this formula to be increasing so in B2 will be Concatenate(B1&2) et.c etc.
The problem is that I dont want to add an extra column to replace the 1, 2, 3 etc. in the formula.
i need to merge several columns together with a comma seperator. but there are some columns with only set of data in and sometimes 6 columns with data. so i end of with something looking like WC,,,,, or GAU,MAJ,CHEM,,, etc.
i have over 6000 lines so don't feel like cleaning up manually.
A1 contains "=2+3+4" which shows 8 as a result. I would like to add a word "Lbs" with it so it would display "8 Lbs" on the same cell. If I entered =1+2+3 on cell A6000, it should give me "6 Lbs".
Concatenate And Bold
I have a simple concatenate in Excel...
=CONCATENATE(A1, ", ", A2)
This is, my text
Now, I want 'my text' i.e. the contents of A2 to be in bold.
I've played with the TEXT() function before, as per this page:
To get Excel to play nicely with date formats and stuff, however, my research has come to a dead end with Bold/Italic - with everyone suggesting that VBA is the only solution.
As I don't feel overly comfortable using VBA is there any other solution? (If not, is this a VBA 101 task or an advanced task?)
Concatenate With Delimiter
to concatenate across columns, say J through Q, (some of which are blank) and have the results delimited by a comma without returning anything for columns that might be blank. So R660 would read "0263B001A,FX9,FX-9,FX10,FX-10" and R669 would ...
Concatenate (two Different Cells Into One)
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..
If / Concatenate Into One Cell
I have 6 columns:
A1 = LastName
B1 = FirstName
C1 = LastName2
D1 = FirstName2
I need to concatenate into 1 cell so it looks like this: FirstName LastName;FirstName2 Last Name2. Now the challenge is that there are many instances where C1 and D1 are blank (don't have values).
So here was my attempt at a formula:
=IF(A1>""&B1>"",CONCATENATE(B1," ",A1))& IF(C1>""&D1>"",CONCATENATE(";"&D1,"",C1),"")
Now this works, but it ALWAYS leaves a semi colon at the end (even when there is no C1 and D1 to concatentate. Thus I put in my IF statement the "".
Concatenate 2 Column
i trying to merge column A1 (ABC) with column B1(XYZ), B2(LOL), B3(ROF) ..etc
with a simple =CONCATENATE(A1,"-",B1) , i can get "ABC-XYZ" , but when come to column B2 , i only can get "-LOL" , any solution ?
File Name In A Concatenate
Can I put the file name into a concatenate formula?
What I've got is seats for lots of different settees that I want to show up on a cutting sheet as ie:
10 Chatsworth Seats
15 Abbey Seats
At current its is reading 'Seats' from the file Chatsworth but if I can add filename to my concatenate it will say "Chatsworth Seats"
Increment Using Concatenate
I want to write formula in cells(many cells!) using concatenate. The desired output:
In Sheet2!D1 - i will write concatenate of Sheet1!C6 and Sheet1F6
in Sheet2!E1 - i will write concatenate of Sheet1!C7 and Sheet1F7
in Sheet2!F1 - i will write concatenate of Sheet1!C8 and Sheet1F
Copy and paste formula would not do so i created a macro stated below. The problem is a can not change the ActiveCell.Formula correction.
Item No amountinvoice no
2050377 14,870 105
5000085 120 1102
5000085 15,000 90197361
5000139 16,000 12346
5000237 31,000 56789
2050377 20,000 10111213
I have a spreadsheet with 3 columns. when I'm reimbursed money (see 2050377 for ($20,000) i'm trying to use VLOOKUP to search the spreadsheet and find out the invoice that relates to that reimbursement. but as you can see VLOOKUP finds the first incidence of 2050377 and gives me inv no 105 when it should be 10111213. my formula in C8 is =VLOOKUP (A8,$A$1:$C$8,3) but I need for the VLOOKUP to look at the item number as well as the amount. i've been told that I need to utilise concatenate, but I don't know how to do this.
What I am trying to do is to have the concatenated content of two different cells seperated into paragraps.
For example, if A1=Blue and B1=Red I want the content of C1 to look like
I use =A1&B1 in C1, but I'm missing something to split them.
Concatenate Use On Chart Title
I'm using concatenate to name a chart title. My problem is that when I concatenate two dates I lose the date format. I'm after a title which looks like 06 December 2007 - 13 December 2007
I'm actually getting 39422 - 39429
The cells have been formatted in date format but lose this when concatenated.
Is there a sollution?
Concatenate Formula Amendment
I am trying to merge cells A1:GR1 in to one cell by using the concatenate formula and the copying and pasting the values in to the same cell.
Is this possible? I've tried to use the formula below with no luck,
i need to create a macro that is dynamic.
i have attached a example on here.
My main idea is that i have to select the rows/column to compare that they are the same for the selected column then concatenate and merge.
In my example, i wan Column B be like Column D... but in the end i wan it to be like Column F if they are the same.
Fixed Width With CONCATENATE
I want to CONCATENATE 3 columns
column 1 will have names
Columns 2 and 3 will have numbers
All will be of different lengths
Is there any way to set a fixed width when I use the CONCATENATE funstion so everything looks more uniform (left justified)
Concatenate Column With Row Value
I have created vbscript that references a particular column and row. This cell is referred to many times in the scripting but the row may change from time to time. I would like to know how I can define the row value at the top and reference this row within my vbscript.
Dim row As Integer
row = 1
'vb script coding referring to cell
ws.Range("B&row").Copy Destination:= Sheets("Summary").Range("B1")
I tried to concatenate the row to the column but it doesn't seem to work. When I would like to change the row value I would like to only make the change where row is defined rather than within each line of coding.
Concatenate The Cell Values
I am trying to merge three cells using the following formulae =CONCATENATE(A3,": ",B3,", ",C$1,", ",C3,"") where C$1 is a title (header) I will use if C3 is populated. However, I would like to leave C$1 out if C3 is not populated.
CONCATENATE ATE MY DATE
I am using the concatenate function, however my date field, instead of maintaining its 08/07/07 state, changes to the annoying 5 digit 33935 number.
Even if I cut and paste it as special / values, it stays as the 5 digit number. Can this be avoided somehow?
Concatenate With If And Copy Then Sum
I'm trying to Concatenate E4 through E60 then Copy Column B,C,D,F,G to Single row then Sum H Through M. I've got My code Concatenating E But have not been able to get the rest.
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LR As String, i As Long, j As Long
Dim MyStr As String
Set ws1 = Sheets("Count Sheet Esman Greenhouse's")
Set ws2 = Sheets("Count Sheet Print")
j = 4
LR = .Range("C" & Rows.Count).End(xlUp).Row
For i = 4 To 60
If .Range("C" & i).Value = .Range("C" & i + 1).Value Then
If .Range("D" & i).Value = .Range("D" & i + 1).Value Then.............
Concatenate Date With Text
I have a date, 03/17/2007, in column A
I have the word, Cashiers in column B
I want to concatenate the 2 and use this in a vlookup. Can this be done? If so could someone explain please?
I keep getting the N/A error on this.
Concatenate Values Per Conditions
I'm trying to Concatenate values as per a condition.
The condition is being a member of group: 1
The result should be ACE but I need it to be presented in one single cell (like in C9).
(Pls see attached picture)
I tried to use SumProduct but it seems that this function handles only Numeric values.
I was able to present the result in a Range(!) of 6 cells with the Array-Formula:
=IF(B2:B7=D2,A2:A7,"") but this is not what I need.
I know how to solve this with VBA but as it is sort of an excercise I need it to be solved with sheets build-in Functions only.
Concatenate Two Columns Of Text
In Excel 2007 I want to concatenate two columns of text. In Column A all the cells contain a single statement that I want to prefix the statements in the cells of column B (the statements in column B differ from cell to cell) I have used the formula =A1&" "&B1 and this is fine for that row, when I use the fill handle and pull it down the page the formula changes accordingly i.e.=A2&" "&B2, =A3&" "&B3 etc. But when I make the text appear using control+ I only get the concatenation of the first row repeated all the way down, irrespective of the contents of other cells in Column B.
CONCATENATE Use LEFT, RIGHT Or REPT
I am trying to concatenate the formula below so that the data lines up properly but I can't figure out if I use LEFT, RIGHT, LEN, REPT or what is the proper combination.
=(D12&" "&TEXT((FP12*100),0)&"% Cmpl"&" "&IF(FU12=1,FU12&" Hr Rmng",FU12&" Hrs Rmng"))
What I want to do is this.
D12 in the formula is always 13 characters, after that I want three spaces and then the next data which will be a one to three digit number (FP12 is 0 to 100) then "% Cmpl" (without the quotes), then three spaces and the next data which will be one to three digit number (FU12 is X to XXX) then a space and "Hrs Rmng".
I count that I need the data to be a max of nine total characters for the second part of the formula (100% Comp) and 12 for the third part (XXX Hrs Rmng) plus the three spaces between.
I want the data to be right justified so if the data is less than 9 or 12 character respectively, I want to add the spaces to the front/left of the data (xxxxxxxxxxxxx-----0% Cmpl-----2 Hrs Rmng).
I am guessing that I can also just increase the number of characters to be added to the front of the data by three and then I can remove the &" "& part of my formula?
Concatenate Returned Value With Its Corresponding Match
I have a 2 column array of numbers. Column A has duplicate values, column B has unique values.
I have a lookup which is counting the number of occurences of any given value in column A, but now need to populate a cell with a concatenated string of the values from column B that correspond with all instances of each unique value in column A.
Concatenate Multiple Cells
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
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.
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-10],RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-1])& ActiveCell.Offset(0, 0)"