I need to do a vlookup with the array starting on a row determined in another cell, so I've tried concatenating the number into a formula but this doesn't seem to work. Is there something else that I can do?
E2 is 134
=CONCATENATE("=VLOOKUP($B2,$C$", E2+1, ":$F$2571,3,FALSE)")
returns: =VLOOKUP($B2,!$C$135:$F$2571,3,FALSE) but not as a formula, as text and I need the formula...
I need VBA that will make those two columns:
1 A
1 B
2 C
2 D
2 E
2 F
2 G
3 H
3 I
3 J
into those two columns:
1 A,B
2 C,D,E,F,G
3 H,I,J
I am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx
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.
Example is in the attch: Book11.xls. This formula works but if there is no number 4 then I don't want comma after 1, or 2, or 3, How avoid it:
results
1,2,3,4
1,2,
1,2,3,
3,4
1,
4
2,3,
(red , need to be removed)
I need to cocatenate anything between and including column 4 and col (unkown until macro is run)
is there a way to do this?
Basically need to add all the cells from 4th column to whatever col is. Example if col = 12
then I need columns 4 to 11 all rows together
I have a sheet where some data is entered in A1 to C1 and i use concatenate to string them together. The problem is in cell C1, where I used cell format to customize the number of zeros in front of the number.
EG:
A1: Photos
B1: 2013
C1: 00001
the concatenate result is Photos2013\1, instead of the desired result of Photos2013\0001.
what i am trying to do is use concatenate in a vlookup to search for a resource number and date, then return another column in the array.
the formula looks like:
=VLOOKUP(CONCATENATE(D7,$H$6),Roster_Allocation,7,FALSE)
but only results in NA.
if i search for the resource number only, i get the correct result.
also, the res# and date are concatenated in the table array. could this be related to the way excel is storing the dates (40241?) even though both concatenated fields look the same?
i have also tried adding a new coumn which has the res# and dates concatenated as the lookup value but still all NA.
How do I get my number in one cell to show all possible combinations of it in other cells. (e.g. cell B4 = 123, now i need cells C4:H4 to display all 6 combinations of the number as follows: 123, 132, 213, 231, 312, 321). Attached is a sample file.
View 3 Replies View RelatedI have a data set that shows numbers for categories over time. I would like to add the data label to the numeric value and then sort the data by the original number. Is this possible?
Here's an example
A 1 2 3 4
B 5 7 4 1
C 8 0 2 1
D 3 4 9 5
I would like each column sorted independently based on the data in that column, but I want the results to have the label from column 1 concatenated with the value in the other columns such as this:
A1 C0 C2 B1
D3 A2 A3 C1
B5 D4 B4 A4
C8 B7 D9 D5
Each column is sorted by the numeric value, but has the name label from column 1 attached to it.
I'm trying to use a macro to write a formula within a column of data.
Here's the
I am using CONCATENATE formula to make summary of my data, but i m using long CONCATENATE formula. I want to use short formula instead of CONCATENATE
formula is here :
[Code]....
How to short this formula and accuracy is first ...
Is this possible because when i try to use the Substitute formula my Concatenate formula is doesn't work. This data in cell A1 is linked from another sheet
PHONE
WIRE
CANDY
INDIA
When I use the Concatenate formula only, it works fine but when i try to add the Substitute to this, it looks like this
PHONE WIRE CANDY INDIA
I want it to look like this, removing any blank lines within cell A1
PHONE
WIRE
CANDY
INDIA
Something wrong with the formula? how come it doesn't do both?
=SUBSTITUTE(CONCATENATE(A1),CHAR(10),"")
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,
=Concatenate(A1:GR1)
I have a DDE code that returns a stock symbol value. The code is as follows, where @ES# is the symbol:
=NeoTicker|q!'@ES#,last'
I am trying to create a cell so when I put in a symbol (H10), it automatically gives me the quote. So I split up the code and then concatenated it as follows:
G10: '=NeoTicker|q!'
H10: '@ES#
I have been able to create concatenate formulas for numbes (% and $), but have been unable to create one that will display a date, I. E. for the number formula, I used, =(A8&" "&TEXT(B8,"$0")). What would be the formual so the second cell show the value as a date?
View 2 Replies View RelatedI am writing a macroI have three variables named "name", " book", and "count". In previous statements these variables have all been defined as String, String, Integer respectively. I am trying to uses all of these variables in the same formula and am encountering issues. The formula I wish to use it in is:
ActiveCell.FormulaR1C1 = "=COUNT('[" & book & "]" & name & "!'""B6:B" & count & ")"
Where if book = Book1 ; name = "Sheet1" ; and Count = 100 I would want the following result: "=COUNT('[Book1]Sheet1!B6:B100)"
I have a time in cell C10 in hh:mm format, in cell D10 I want to add the text "DFL" in front of the time, remove the ":" and add "HRS." at the end of it. For example "DFL0715HRS." I have been able to get the following formula to work:
=CONCATENATE("DFL",(TEXT(C10,"hhmm")),"HRS.")
However sometimes it may not be a time, it could also be "Rest Day" in cell C10, if it says "Rest Day" I need it to add the text "DFL" in front as before and then change "Rest Day" to "RDFLEXI." For example "DFLRDFLEXI." I have been able to get the following formula to work but only if it is "Rest Day", if it is a time such as 07:15 it doesn't work.
It shows "DFL07157R0.": =CONCATENATE("DFL",IF(C10="Rest Day","RDFLEXI.",(TEXT(C10,"hhmm"&"HRS."))))
I think it is trying to continue doing something with the text function
I'm looking for a formula that utilizes an "if/then" condition. I'm attaching a sample workbook of what I'm dealing with. I need it to end up so that column "E" holds: if a equals c, then e = b, "and", d, c Does that make sense? I manually entered my desired end result into column E in the sample workbook.
View 3 Replies View RelatedI 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.
View 4 Replies View RelatedI receive daily reports from the people that work for me. The files are
saved in a known location and a known naming structure (initials - date.xls)
In a summary report I CONCATENATE their initials and the date along with a
path but cannot get the INDIRECT command to recognize the text line.
Is there a way to make the CONCATENATE formula put all the text on different lines? It keeps combining some on the same lines and I want them all on separate - like a list.
View 13 Replies View RelatedLooking for a little help with the following formula =CONCATENATE("August 07 -",TODAY()," Evening Instructor") After entering this formula today is displayed as a serial number. I'd like it to display as current month/current day/current year (11/28/08)
View 2 Replies View RelatedIs there a way to nest IF & And statements. I'm looking to concatenate a number of cells and seperate them with a space and/or comma but only if they contain data so need something along the lines of
View 3 Replies View Relatedway of joinng the contents of cells to create a formula?
path month subdept
x:path oct [dept.xls]sheet1!a1
x:path dec [dept.xls]sheet1!a1
is there any way of joining these to create a cell reference such as x:path oct[dept.xls]sheet1!a1
i've tried the concatenate and "& &" method to no avail.
Is there a faster way to combine all the P.O# with a formula automatically, no matter how many P.O# there are? Its unpredictable on how many invoices will get pay per wire.
Log # Amount P.O. Numbers 1-a 45,678 1234 1-b 89,173 5678 1-c 66,526 9123 2 22,113 2345 3-a 66,654 6789 3-b 88,650 2468
1 201,377 1234/5678/9123 2 22,113 2345 3 155,304 6789/2468
I would like to reference information from another workbook. Instead of writing the Name of the other workbook I would like to reference through a Concatenate formula (e.g. CONCATENATE("Test"&G2&".xls")). I always get an error message. Is there any way I can reference from another workbook by using the Concatenate or any other formula?
View 5 Replies View RelatedI want to "calculate" a worksheet name by concatenating 2 cells. Then based on this concatenated word perform a " lookup". Basically I have a form for people to fill in and want to extract their answers automatically.
View 4 Replies View RelatedI have a list of P/N's that are used in more then one location. and it's sorted by P/N's.
ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C
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 use concatenate to combine two cells of data, but to only display the information if both cells contain information.
Currently using the following simple formula:
=concatenate(a1,a2)
However, I only want it to work if both a1 and a2 cells contain data. If only a1 contains information, but a2 is blank, I want it to calculate a blank cell.