Based on the current month (which I planned on typing in) on the MainList worksheet I need to count how many blank cells are in the table above. The information above is on a separate worksheet but I can change that in the formula. Every month gets a new column.
This is what I have but its not working correctly: =SUMPRODUCT(--(BTES!AD1:BQ18=F2),--(BTES!AD1:BQ18" "))
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.
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?
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).
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.
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.
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 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.
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.
I receive multiple worksheets containing transaction data with multiple numbers of row and columns. The data columns contain varying types of information like: price, quantity, location1, location2, loading dock, transaction date, buy/sell, purchaser, seller, currency unit, etc.
I have a need to concatenate into a single cell in a blank column, data from a number of cells in each row -- sometimes two cells, sometimes more. In some instances, I'd like to concatenate data for location1 and location2 with a delimiter like a semicolon-and-a-space to create a cell with: "location1; location2" Or in another instance, I'd like to concatenate multiple cells in a row, like: "location1; price; quantity; transaction date", etc.
I'm looking to create a macro that, with the cursor in a blank cell in a blank row, would open an input box, ask a user to select with the cursor the cells in the row to concatenate and then concatenate them into the blank cell in the row.
Ideally, the macro would populate the entire blank column with the concatenated data for the group of selected cells (from the same columns as in the row where the cells to be concatenated were selected) for each row where there is data. It is also possible that some cells in some rows of the selected columns would contain no data. In that case, I'd be looking to return just the delimiter, like: "location1; ; quantity; transaction date"
Right now, with no macro, I do it by hand like this:
To concatenate into cell I6, the contents of cells A6, B6, G6 and H6, I do a "=CONCATENATE(A6,"; ",B6,"; ",G6,"; ",H6)" in blank cell I6, but I'm looking for a way to do it fast in a macro that would fill in all of Column I.
I am trying to concatenate multiple lines of comments into one string and place that string in the comment field corresponding to the start of each record set. I believe the code below is close, but I am receiving this error
Run-time error '91': Object variable or with block variable not set
I have two columns - A and B. In column A there are blank cells and filled cells. There is a blank cell and after it - a variable number of filled cells, then a blank cell again and a variable number of filled cells. I am trying to concatenate the filled cells from column A into a single cell in column B, which is corresponding to the blank cell in column A. Values should be comma delimited.
For example: A2-blank; A3-27; A4-63; A5-blank; A6-31; A7-blank .. and in column B should be B2-27,63; B3-blank;B4-blank; B5-31,B6-blanc and etc.
I am trying to figure out how to Concatenate a group of cells if they contain a certain text. Basically I have a list of Architects in column A and the account name in column B... and I want to say Concatenate column A, IF column B=Specific Account (ex: Coca-Cola). The list will be added to frequently so the formula must refer to the entire columns, not just a certain range.
As of right now, only the first architect associated with each account will appear. I have attached a workbook with the example..
In the workbook, 3 columns of importance Column N,S and AC. Concatenate order ("S"&"N"&"AC") - combine it and show value in column AG. Column AC has either 0 or any other number (50,60,100 etc). If it is 0 I need to insert an 'F' at the end of the code in column AG, any other numbr and i need an 'O'.
Column N has contract code, this will be in the middle, usually its alphabets like RF,EF, but sometimes numbers like 21,33, etc but when its a single digit number like 6, i need it changed to 06 (need the 0 infront). Column S has exchange codes, and this has to appear in the front of the code in Column AG. Same as Column N, if there is a single digit number then put a 0 infront of it.
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.