1. J

    Index/Match and Concatenate

    Hello: I have a list of first and last names and amounts in a range. I want to find the amount that corresponds to both the first and last names and return that value. I've used Index/match in the past when there was only the last name and it worked fine. Now, when I added the first name, I...
  2. N

    Concatenate multicoulmns but not display blank coulmns in the total formula

    <tbody> B C D E F 1 SURNAME FIRSTNAME MI GENQUAL CONCAT'D 2 DOE JOHN T JR DOE.JOHN.T.JR 3 DOE JANE DOE.JANE </tbody> I cant figure out how to write a formula to concatenate column B thru Column E, add a period after each column, but not display a period if the field is blank. In the...
  3. J

    Grouping based on a condition

    So, I have a list of Yes/No questions and what I am trying to do is create a string of the terms that have a Y beside them. For example in the data below, I would like the cell to return Agitated, Angry, Depressed and I will reference that in a different part of a sheet. I would like for this...
  4. U

    Concatenate: Italicize (or bold) part of output

    I have a concatenate formula to output into a cell something like "100% / 75% / (25%)" (code is below). I'd like to either bold or italicize that 25%...something to show that the (25%) is a variance and not a raw value. It needs to be something other that "red" font since sometimes the...
  5. D

    Concatenate with Offset

    Hello all, I am trying to use concatenation formula with VBA and need to use offset in order to complete it, but doesn't seem to work. The point is:I have 2 lists of names in 2 columns, which i need to add some stuff. What i need to do is select a column A item, and macro will insert 29...
  6. S

    2 sheets, Connect 2 formulas, Vlookup and Concatenate

    Hi Great minds. Greetings. I have a file with 2 sheets Values for example: <tbody> Sheet 1 Code Product Type Colour sd123 Necklace Oxidized Black hf32 Bangles Gold Rose Gold </tbody> If we input a code from sheet 1 to sheet 2 manually, we would like to get the following result...
  7. M

    Reference an outside workbook and cell location

    Hello, I saw similar functions with INDIRECT but could not get any to fit my needs (even combining with concatenate) In A1 through A20 I have file names. In A35 I have the file location. So I use concatenate(A35,A1,”.xlsx”) to get the file and location and location in the file (sheet1 C9) I...
  8. K

    Combining multiple cells into one

    Hello! This is my first post on Mr Excel :) I am trying to collate a list of numbers so they have an exclamation mark at the start and a semi-colon at the end. I wish for these to all end up in one cell so I can copy and paste them into a business intelligence system that requires this format...
  9. S

    Sum from sheets name based on another cell valu

    Hi I have Key & sheet names in Sheet1 & key(with Concatenate) need to vlookup with Key in another sheets & get the Sheet Name & sum the column values.
  10. A

    VBA: concatenate data

    I have data in Column A1:A100 and B1:B100. I need to Concatenate data in C column so that column A data appears below column B data in respective cell.
  11. L

    Formula to Find matching Absolute Values in a Range of Dates?

    Hi Everyone, I've been developing a project for work that will analyse bank accounts to determine the value of a project. The problem is some of our projects have inter-account transactions. In order to determine the proper worth of the project we have to find any transfers between the...
  12. K

    Extract list of non-zero cell values from a list AND whether Estimate or Actual AND the name of the product

    I have a list that I need to remove the zero value items from via formula to create a new list (imagine an order form). Because people will be using this second form without much excel knowledge, I need it 100% formula driven. <colgroup><col><col><col></colgroup><tbody> Qty Item Estimate...
  13. X

    Contatenate with blanks

    Hi MrExcel World, I am trying to concatenate 2 cells, which is easy enough. The problem I am having is that one if one of the cells is blank I don't want to return anything. Example below; Concatenate----------------------First Name----Last Name John...
  14. T

    VBA to Concatenate across multiple workbooks

    I'm trying to write a VBA that will concatenate the same cell across a whole folder of workbooks. Each workbook in the file is the same, just a different topic. I have 15 different workbooks, for example I have Lion, Tiger, and Panther. In sheet 3 column D of each of these books I have some...
  15. N

    TEXTJOIN unique names (columns) - ignore blanks

    I am trying to concatenate unique column text into a single cell. After spending an afternoon researching, I can now concatenate all the text in different column cells and remove duplicates. However, if one of these cells is empty, I get a blank value. as an example, I want to concatenate all...
  16. willow1985

    Concatenate formula for dates

    I have 2 columns (K and L) with the dates in the format: 08/27/2019 & 08/29/2019. I want to use a Concatenate formula to have the end result showing: Aug 27 - Aug 29. The formula I wrote is as follows: =CONCATENATE(TEXT(K3,"[$-409]mmm d;@")," - ",(TEXT(L3,"[$-409]mmm d;@"))) However this...
  17. D

    Concatenate different columns with delimiter

    Hi there, I am trying to concatenate different columns from 2 sheets, which are in different order one to each other. This would be the columns i need from the Auto sheet, and their counter from Manual sheet: <tbody> Auto Manual Column Column Q AB S T T U U AN V V W AQ X AT...
  18. P

    Concatenate & Remove Duplicate

    I have the following column values Input Account no Code 000005678 ABC 000005678 EDF 000001234 ABC 000001234 ABC Desired Output Account no Code 000005678 ABC 000005678 EDF 000001234 ABC Criteria Concatenate Column A and B (i.e...
  19. Dazzawm

    Easiest Way To Concatenate

    I have a sheet as below with several dozen columns and thousands of rows. I want to join each column separtated by a / and a space. They are set out like below but if i do a normal concatenate formula I could end up with row 2 something like 8E0145806Q/ / / / / /. Row 4 is what how I would like...
  20. R

    Query changing dates to numbers and pulling data incorrectly

    Currently I am trying to figure out why Query is changing the information and pulling on cells it shouldn't be? I have a table setup which is this: <style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style> <colgroup><col style="width: 100px"><col...

Some videos you may like

This Week's Hot Topics