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 ?
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.
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.
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.
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
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.
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?
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.
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.
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.............
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 With Indirect + VBA
1. How can i put an Indirect function nested with concatenate?
2. How can i get the formula to adjust to new data range without manually filling down. Assume the formula starts CELL Q8
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 Multiple IFS
The formula below works great for counting. I need to use the same formula to CONCATENATE all of the names in B:B. It seems as though I should just replace COUNTIFS with CONCATENATE and change Data!C:C to Data!B:B, but it doesn't work. I tried a search on the web using the title of this thread but came up with very few clues. Is it even possible to CONCATENATE with multiples IFS?
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.
IF THEN Concatenate AND Cut And Paste
I have imported .csv files into a workbook and there are instances where the data did not separate properly. When this happens, part of the text (an MCI circuit ID) that should be in column A, ends up in column B. This obviously moves the data that should be in B:I to C:J. I have the code below, which, when "*MCI*" is found in a cell in column B (and as extra validation), a "0" is found in column J, the text in column B is joined with the text in column A. This works fine. What I need is to also have it delete the circuit ID that still exists in column B and then cut the data in C:J and paste it in B:I.
If it matters, there could be as many as 25,000 rows.
Dim a As Long
Dim b As Long
Dim i As Long
Dim j As Long
Dim r As Long
Dim lr As Long
'Fancy report headings in rows 1 thru 7, so start at Row 8
r = 8
'Column in which text will be combined if conditions are met A (1)
a = 1
'Search Column B for condition one (if *MCI* exists)
b = 2
'Search Column J for condition two (if "0" exists)
j = 10
'Determine last row containing data
lr = Cells(65536, b).End(xlUp).Row
'Loop and concatenate if "*MCI*" is found in column B and "0" is found in column J
For i = 8 To lr
If Cells(r, b).Text Like "*MCI*" And Cells(r, j).Value = "0" Then Cells(r, a) = Cells(r, a) & Cells(r, b)
r = r + 1
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 & Indirect Logic
I need your help on the attached sample sheet. I have used the concatenate & indirect combination logic to get the desire comments as outcome , but my problem is that I have 300 rows & 30 sheets in a workbook with this logic , it is increasing the size of my file as I have a total of 30 sheets with same logic. I need it in every sheet. If there is any other alternative or solution.
Can't Get CONCATENATE To Work. No Results.
I have built a formula using CONCATENATE. (Built in G2) When I copy and paste it into a empty cell (E2), one that is formatted as General and is not locked, as text or value or a formula I get the same results. It always shows up as text. I can't get the formula to function and show the results in the cell (E2). Yet, when I singularly build the formula in the cell it works fine. But the moving (cut and paste) from one cell to the other doesn't work.
The concatenated formula is: =CONCATENATE("=LEFT(",G2,",",H2,")") The result is: =LEFT(B2,3) . These results look fine, yet only when built in the cell using Excel's Insert Function builder or doing it by hand it works fine. It returns the first 3 numbers/text from the left side of cell B2. But the data that should result from the concatenated formula and pasted in to the cell (E2) is not showing up as needed. Any help would be appreciated.
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)