I have a list of columns filled with data, say b thru d. What i want to do is combine the cells in the same row (b thru d again) and put that value in column a like this : valueb-valuec-valued
I want to keep going down rows until there is no data left and then I want it to stop. So far I have this (which doesn't work at all, as usual)
I have to combine two value and use them as a cell name,
So far this gives me the cell name =("A"&A3) where A3 has a value of 2 and the autcome for the code =("A"&A3) is A2 but I wanna use that as the cell name for my formula and not have it projected as a value only if you see what I mean.
Basicaly the reason for this is that when I write a formula in the cells and drug it down it moves 3 numbers at the time as the colomn H is split in 3 per row, so I have for example
=(B1)
=(B4)...
and so on, where I want it to be
=(B1)
=(B2)...
Therefore I am using the colom A to assign a row number, that the reason for the formula A"&A3
but when I try to write a formula it doesnt work as it doesnt use the outcome A2 as a cell name but as a text value only
I have managed to retain leading zeros in a cell by formatting the cell and selecting Number and Custom and adding the maximum number of zeros I want to have in front of the number in the cell i.e. 000002.
I want to have another cell in the spread sheet which contains the value 35394000002 using the formula =A24&B24.
When I use this formula I get 353942. How can I retain these zeros?
I have a sheet in which there ara $ and Cents but in the bottom i want to sum them together. How can i do that?
View 8 Replies View RelatedI need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.
Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.
Book1.xlsx
I have a column say column A that holds text. If the values match say:
ABC1 or
ABC2 or
ABC3
And also match column B if it shows "Yes" then show "Correct" or "Not Correct".
i have about 100 worksheets in my workbook ..i need to combine them into one worksheet ...all my 100 worksheet has many formulas in some of the cells.
i want to combine all the worksheets one below the other with a gap of 5 blank rows after each worksheet contents....i just want the values in each worksheet to be pasted and no formulas...
I am having 3 rows with same address and want to combine rows with equal names into single and delete remaing two rows
View 3 Replies View RelatedI have to check if two items are in column A, and if yes, there values from column B shoulded be summed. I tried different combinations with IF, LOOKUP but didn't go far.
View 2 Replies View Relatedhow much excel can do and the amount of experts that is willing to help out in this forum.
Is there anyway to combine all the values to omit redundant data?
For e.g.
A B
1 Apple 28
2 Pear 55
3 Orange 35
4 Pear 22
5 Pear 15
6 Orange 18
7 Apple 25
is there any VBA codings that could automatically reduced all the above data to
A B
1 Apple 53
2 Pear 92
3 Orange 53
I have two columns of data and would like to generate a new list showing unique combinations of those values (see attached sheet).
unique list.xls
In this file i did paste special values so the links are no longer there, but in my original file alot of the values you see in the cells are pulled from other worksheets ( thats just so you know to lookin values of the cells and not the formula ).
I basically need row 18 starting from Column O to self populate.
Its based off of which has 1's in the Column. I have the completed here manually just to show you what i am looking for . I usually have to go to row 3 and Enter the data manually depending on which 1's are filled in for the column.
If i could have a method of having row 18 starting with Column O to self populate based off those 1's
I have a wookbook with serial numbers in column A and barcodes in column B. There are 51940 rows in total.
Is there a way I can search through column A, and for each unique value found , concatenate all of the barcodes that relate to that serial number in column C?
Serial Numbers | Barcodes | Combined Values
-------------------------------------------------------
abc123 abc12300 abc12300,abc12301, etc etc
abc123 abc12301
def456 def45600 def45600, def45601, etc etc
ghi789 def45600
i am using Excel 2010.
I am in need of a way to pull keywords listed in B2:B10 from text in A2:A10 and then those pulled keywords combined in cell C11.See attached document. So I have colors for keywords in column B and some random text in the column A including the keywords in parenthesis. Is there a way for a formula to find all keywords in A2:A10 and then list them in cell C11 with commas in between.So the result would be one cell C11 showing BLUE,GREEN,BLACK,ORANGE, etc.I have been trying to mess around with index and match and while I could get one to pull up I don't know how to combine it.
Keyword Range.xlsx
I've been struggling with the following problem for hours now and getting nowhere fast.
I've got '=(100/X6)*L6)' in cell M6 which gives my percentage fine but I need to round to the nearest integer with '=ROUND(M6,0)' Question is how do I combine both functions to provide the nearest whole number in M6 ??.
I have just two columns, one with numerical codes (A), the other ones with text (B).There are duplicate codes with different values, for which I would like to be able to combined their text values on a single row.
A | B
xx | text 1,
xy | text 2,
xz | text 3,
xz | text 4,
xu | text 5,
...
to be formated to:
A | B
xx | text 1,
xy | text 2,
xz | text 3, text 4
xu | text 5,
...
The resulting column B can be with/without space delimiter between the text values (there is a coma after each text value)
I am trying to combine the data in a row into one single cell.
For example,
B2= A1 & A2 & A3 .... until the end of the values in column A
I need this to be a macro so that when I enter values into column A manually and press the macro button, it will combine them into cell B2.
I would like to have cell A3 contain two different formulas. I may be doing this wrong, so here is what I would like to do:
Cell A1 will contain a number that will change from month to month, but will need to show as $0 until then. Cell A2 will contain a fixed sum number from month to month Cell A3 will contain the difference between cell A1 and A2
I wouldl like cell A3 show as $0 until a number is inputted into cell A1
A1
$0
A2
$2000
A3
$0
The formula I have in cell A3 is =SUM(A2-A1), but cell A3 shows $2000. I remember doing this before, cut can't remember the exact formula. Something like =if(A1
I am trying to combine the text values of several formulas in addition to adding some text (punctuation) in between.
Here is the data that I am trying to combine
C14: 2013
C15: Period 6
C16: Week 1
In Cell C13, I would like to have this value returned: "2013 - Period 6, Week 1"
Is there such a way to do this?
i want to creat a sheet doing the following
enter the phone numer in cell a1
enter the phone break down hour in cell a2
enter the phone break down date in cell a3
combine the cell a2 & cell a3 in cell a 4
enter the phone fixed hour in cell a5
enter the cell fixed date in cell a6
combine the a5&a6 in cell a7 subtract a7-a4 and show the result in hours
Creating a macro that is a combination of other cell values. the cell order is listed below:
C57 = contains a unique number that will change from time to time eg. 1234
H57 = 1 (this will always be the value of 1)
N57 = contains text eg. AUDUSD
V57 = contains text eg. BS
AB57 = contains a number eg. 1.8954
AH57 = 0 (this will always be the value of 0)
AN57 = contains a number eg. 0.01
AS57 = contains a number eg. 1.7954
AZ57 = contains a number eg. 1.9954
BH57 = conatins either 1 or 0 eg. 1
the cell needs to contain the above examples like this :
BT57 =OpenPosition(1234,1,"AUDUSD","BS",1.8954/0,0,0.01,1.7954,1.9954,1)
This will be needed for a total of 14 rows (57, 59, 61, 63, 65, 67, 69, 71, 73, 75, 77, 79, 81, 83)
The cell with the results cannot have any cell references contained in the end results and must be formatted as above so that the add in program can read and pass on the information.
In column A I have product names, in B I have product categories and in C:H the product categories from column B are titles and the name of the subcategory is written in the cell. Because there are mutlipule sub categories that a product can belong to the report produces mirrored lines with one sub category on each line. What I need to do is combine all of these subcategory entries into one cell separated by a comer (,).
View 2 Replies View RelatedI have a column of data that will vary in length and I'm trying to write a macro to combine it all into one cell. Basically I need to do the equivalent of entering "=A1&A2&A3..." for the entire column. I started by creating an array string variable and have the data in grp() but now I'm stuck trying to figure out some sort of loop to keep adding onto the same cell or something
View 6 Replies View RelatedLet's say there is a name (Big Bird) in cell A1
Let's also say that cell B1 has =Today()
How can I make cell C1 = Big Bird 3/13/2013?
I want to combine both an 'if' and a 'vlookup' within a single cell. So I want to say if the value in cell a1 can be found in columns c1:d100 then return a 'yes' if not return a 'no'. How do I do this
View 7 Replies View RelatedIs it possible to combine the results from 1 Text Box and 2 Combo Boxes to return to 1 Cell?
For example, i am using this code to write username to Sheet 2
Sheet2.[c9].Value = txtFullName.Value
and this code to write account number to Sheet 2
Sheet2.[d8].Value = txtAccountNumber.Value
BUT - i would really like to take the Value from txtFullName and take the Vaule from txtAccountNumber and combine that into (1 cell only) with space separators please.
Example : (username & account number combined)
Slim 12345678
I would like to combine variables and paste them into one cell. For example, I have a city stored in the variable "city", a state stored in the variable "state", and the zip stored in the variable "zip".
So I need something like...
Range("A1")=city, state zip
Column A has the following values:
au:asd
au:qwe
au:zxc
etc
I want to combine the text from each cell into a text string so that each cell's text is separated by a comma, thus:
au:asd,au:qwe,au:zxc,etc
I can do this with a formula,
=a1&","&a2&","&a3
but this may exceed the 255 character limit when the text string needs to be transferred to a query.
I have so data I am trying to combine based on count value of investment on Col A. If it appears more than once on col A then I want the col B values on col D. I have macro that works but I need make sure if Col B have any TRAC and TRL1 then I want to add that on Col D. For example so Col D1 would be EURX, TRL1 at 83.50, FTID and Col D5 would be TRAC at 87.88, Markit. Below is the macro.
View 9 Replies View RelatedI have a document that has 3 colums.
Complete Name | Surname | First Name
The Complete Name is currently blank. The others just list the users Surname and First name. In the Complete name I need it to have Surname,Firstname - for example
Complete Name | Surname | First Name
Jordan,Michael Jordan Michael
Is there a Formula (Not Macro) that I can use to combine the Surname and First name cells to populate the Complete name cell with the surname,first name???