I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project Benefit Type Delivered or Enabled Benefit
PJ1 Financial Delivered Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered Financial - Enabled Tech - Delivered Tech - Enabled Green - Delivered Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Im tryng to do a SUMIF formula that has several criteria as answer but using the concatenate formula, but it doesnt seem to come back with the answer - im using AND/OR formula aswell, but have a feeling im doing it wrong.
It does work when i use the single concatenate, but not when i try a do more than one
I am looking for a way of creating the following conditioned concatenation.
I have two tables, let's call them "summary" and "detailed".
The "detailed" table is something like the following:
ID VOL
001 01
001 05
[code]....
The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:
ID (unique) VOL (concatenated)
001 V01, V03, V05
002 V01, V04
003 V06
PS: I have people using this table with office 2003, so compatibility is necessary...
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:
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.
I looking for an option to concatenate various cells but only when another criteria is complied, also to sum other value. I.E. concatenate cell "A" when the value on cell "B" is repeated, also sum the value on cell C.
I am looking to write a formula that can concatenate cell A$1$ (absolute) & B1: B245 (dynamic)and then A$2$- B1:B245 and then so on.
So for example, I need to concatenate cell A$1$ & B1, and then A$1$ & B2, all the way up until B245, and then I need to restart the sequence with A2 and B1.
Formula to preform this concatenation, or will this need to be done with VBA?
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?
I have multiple rows that I wanted to concatenate. I want the values delimited to a symbol like +. The problem using the sample formula =CONCATENATE(A3," = ",B3,"+",C3,"+",D3) is if there's a blank row the + sign will still display. I attached sample file with the desired result.
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
I have a formula, and it works okay, but I have to change it based on how many characters appear in a cell. I would like to avoid this, as there is just too much room for error.
The end result of my formula is to get a result like the following: U1C01EP or U1C01L04EP depending on which columns are completed. But the "variation" on the number of characters in the cell (1 or 2) and accounting for the leading zero is what is getting me confused.
Column V always contains only 1 character, so this is not an issue. "U" will be added before the contents of this column in the formula Column W will contain either 1 or 2 characters. If there is only 1 character, the concatenate needs to add a leading 0 following the "C" and then the contents of column W.
Column X will contain either 1 or 2 characters. If there is only 1 character, the concatenate needs to add a leading 0 following the "L" and then the contents of column X. Now this column may also be blank, if that happens, then there should be no "L" within the result, that portion would be left off.
Cell R1 contains the code that is added to the very end of my result. This code will change, so it is placed in a fixed location to be added to the end.
And I would like to keep the IF statement that I wrote accounting for Column S being Blank, yielding a blank cell completely as a result.
Say in L4 on form test i want to look for the value of A4 in Drill data range a2 to z1000 and retun a concatenated string of something like "sav 407 : 08-may to 11-may"
I've made a "Budget Calendar" so to speak.., with my bills on the first sheet, and the Months in the following sheets. What I'm trying to do is say, for instance, the day is the 11th on the calendar, (there are two bills due on this day), and I have the bill scheduled to be on the 11th, can I use a formula to return what bills are due?
On the "Bill Dates" sheet, you can see where I'm kind of going with this...
On the August calendar, under the 11th, I want it to say "Internet, gas (utility)" in cell D20, or "Internet" and "gas (utility)" in D20&21, respectively.
I can get E20 to return the total bills due for the day using "=VLOOKUP("11th",'Bill Dates'!$B$2:$D$395,3,FALSE)", and it'd be awesome if I could get it to return them in different rows as well.
I have 2 worksheets, Worksheet 1 has Customer Magic Number on it as a reference and a few customer details and Worksheet 2 has Customer magic number and contact fields.
to show the contact codes in sheet 1 however I also need to show the Notes which are located in Columns G:I, Is there an easy to use the index & match functions as above with the concatenate function to add the notes in the cell beside where I am inputting the contact codes?
I am trying to concatenate multiple strings of text where each may have composite or compound formatting. Is there any way to maintain the formatting? The Characters.Insert method won't work because the resulting string is greater than 255 characters. See an example below.
String 1: When you simply concatenate String 2: these strings, Excel discards String 3: formatting at the character level.
Required Result: When you simply concatenate these strings, Excel discards formatting at the character level.
I think this is an easy one, but i can't figure it out. I have a spreadsheeet with multiple concatenate functions programed in. All of them work. If, however, i try to change the formula inside the concatenate function it freezes and will not calculate the new cell. I have the calculation tab set to automatic in the options tab...
I have a text document disaggregated into sentences each in different cells. I need a way of selecting different cells with a mouse, and through a Macro, have the text from those cells viewed in concatenated form into new textbox generated by the Macro.
I am trying to solve a problem. I am currently using this formula = SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel MARKETING 200612 -10 MARKETING 200701 -25 MARKETING 200708 -50 ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
I am using the below formula manually but it is taking too long. How to automate this? Is there a way to create the space and comma for as long as the list is?
What is the best way to concatenate with a semi-colon across multiple cells in excel. The one kicker is I only want to concatenate where the cell is populated.
If I write a simple Concatenate or combined columns, if a cell is blank I get the multiple.
I wish to Concatenate contents of several columns into a separate column, i tried using the following "column1 & column2 & column3 etc..." however with this the blank cells get concatenated too...What should is use so that contents get concatenated but blank cells are ignored?
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
I am attempting to concatenate the dept numbers for each user in a list. For example, the first user listed below is associated with 6 depts. I would like the resulting macro to concatenate the dept's into one cell next to the Name.
Below is an example with the solution I am attempting to produce.
Sample Table
Name Dept
Abbruzzese,James L 188100
[Code] ......
Output
Name Dept
Abbruzzese,James L 188100, 231100, 600377, 600656, 600663, 600708
I have one row per day of the month in column A. each row has one or more cell entries in column B .... to column (variable) Right now I use a formula =concatenate(B2,CHAR(10),C2,CHAR(10),D2.....) for as long as it has to be, but this has its limits and requires a lot of editing as the number of filled cells for each row vary.
would it be possible with some macro code to manage the variable number of cells and then insert verything, wrapped text, in a single cell (can be cell Bx or a cell in a new column added next to the dates column) As soon as everything is in the single cell, the original data cells can be emtied. I can record a macro but that does not allow me to manage the variable number of cells . a small sample file attached.
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 ,
I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
