In cell B1 I have the number 1
In cell C1 I have the number 1031000
I know how to combine the cells in A1. I am getting a result of 1-1031000
What I would like to happen is that 1 becomes 001.
Ending result is 001-1031000
problem is that cells in B could be one, two or three digits.
On a small scale I could fix manually but i have over 15,000 rows to deal with
I need to combined all non blank cells in a given row into 1 cell. But within each row i have 5 phases that the values fall into, which is denoted by the 1st charter 1, 2, 3, 4, or 5. for example, in a given row i have 1-a, 1-c, 2-d, and 1-f and these values occurs in non consecutive columns starting from G to ALR. I need a macro that sorts these values in one of the 5 phases. So in another sheet the macro would combined [1-a 1-c 1-f] in Phase 1 and [2-d] in phase 2. and if there are other phases it would put them in the appropriate cell.
See attached workbook : Work Order Summary Sheet.xlsx
However, in addition to what the macro already does, it is possible to add another work sheet in the same work book that outputs the values in separate cells? It also has to output the data in order: for example, in the first work sheet the data is inputted at random and has spaces but the macro will have to remove all the blank cells and output them in order based on the first value in the test string, 1, 2, 3, 4, and then 5;
input:
blank 3-x Blank 1-y blank 2-z 1-k
output:
1-k 1-x 2-z 3-y
i m working on a spreadsheet and i need cell A+B=C..
Example
Cell A has a first name (John)
Cell B has a last name (Smith)
Cell C has last name then first name seperated with comma
Is it possible to combine three cells into one? Like for example, column A is the first name, column B is the middle name, column C is last name.
Is it possible put it on a format like this: Last, First M.
(middle name on column B is a name, not an initial.)
And can I make it only the first letters are capped. Last, First M. Or all small letters. last, first m.
I have a data set that I am generating from a SQL database then putting into excel format. In this spreadsheet, I have a one to many ratio. I want to combine all notes associated with the same applicant_id into one cell. I also, only want the first Received Date and first Entry date that comes across. Is this possible to set up a formula/macro to do this manually for me? Each day I generate this data set, the number of entries can vary. I provided a smaller set, but I'm dealing with hundreds of entries.
ReceivedDateapplicant_identry_datenotes_comments
12/27/13 0:0788695812/27/13 9:07Primary Call
12/27/13 0:0788695812/27/13 9:07Left Note
12/27/13 0:0788695812/27/13 9:08Left Voicemail
12/27/13 0:09886959NULL
12/27/13 0:13886960NULL
12/27/13 0:14886961NULL
[code].....
I want to combine two cells and add text. Then delete column D.
View 4 Replies View RelatedI am making a report template in excel. I have written a macro to format the report based on the contents of two other sheets in the work book.
I am attempting to combine two cells in separate sheets as a linked cell in a third sheet.
i.e. in sheet 3, A1 I want to combine the values held in sheets(1).range("a1") with sheets(2).("A1") as a link which will update as the parent cells values are changed.
The cell has to be a link of the other cells as their contents will change based on the report requirements.
How to combine cells with a similar value inside them into a single cell. Here is what my data looks like.
10'X14'{&}SNM8981-1014{&}16
2'6"X10'{&}SNM8981-2610{&}9
4'X6'{&}SNM8981-46{&}6
[Code]....
For example, I need to have every cell with SNM9012 to combine into the same cell. If I could figure this out it would save me hours vs pasting the similar lines into textmate and removing the enters.
I am having a hard time with this one. I have 3 cells.
A1 Dallas, TX.
A2 Chicago, IL. (Sometimes Blank)
A3 New York, NY
I would like this info to transfer to another cell like this
Dallas, TX / Chicago, IL. / New York, NY (All in one cell with the "/" to divide.
When the "Chicago, IL." is missing it would look like
Dallas, TX / New York, NY
I need to search and combine cells based on a specific set name. I would be just as comfortable if it automatically found similar names without me entering the names I would like combined. This is not required though.
EX: Search for John in Column A
Find 2 rows with John
Add each column from row with name John together individually
EX:
Name----Number 1 -----Number 2
John------ 1 -------------3
David----- 55----------- 65
John------ 5 -------------2
In the end I would like:
Name -----Number 1----- Number 2
John------- 6 --------------5
David-------55 ------------65
Then I'll be recalculating fields and repeating the macro for another name (this part I can handle). The current quick macro I made just combines highlighted cells, which is OK but time consuming. I took VB in highschool, but remember none of it!
am trying to code a loop but I have no idea where to start.
With the help of you guys I have already got some code from a previous thread which gives me a list of hierarchical data now I want to be able to loop through this and actually have the 'parent' next to it.
For example:
1 Shops
2 Butchers
3 Meat
2 Bakery
3 Bread
I would like to be able to say
1 Shops
2 Butchers Shops
3 Meat Butchers
2 Bakery Shops
3 Bread Bakery
I have a list of transactions on accounts that have the account name multiple times. I want a count of just those account names but without the copies for the different transactions. I have like 5 entries (transactions) on say Bob Smith's Towing I need it to combine all five entries into one or delete all but one so I have a true count of how many accounts I have.
View 14 Replies View RelatedIs it possible to combine datas from over 200 cells (all cells are from the same row) in one cell? I have tried CONCATENATE function and e.g. =A1&B2&... but is there shorter formula for this?
View 4 Replies View RelatedCreating 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.
ItemNew or UsedQuantityDescriptionScrap ReasonActionitem1new1000item1 description1item1 reason1item1 action1item2new1200item2 description1item2 reason1item2 action10000item2 reason2item2 action20000item2 reason3item2 action3item3used3500item3 description1item3 reason1item3 action1000item3 description2item3 reason2item3 action2
The above was imported to Excel from another software and somehow those unwanted rows with "0" were created.
Would like to delete those unwanted row and combine the reason on those rows into their respective rows above.
Please note: this is just a small part of the file. The file is big and row with "0" doesnt occur in a prodictable patten.
Let'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?
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 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???
I have a data output from citrix that looks like the top half of the attached file. Each ID has 2 rows of data; open orders & recommended orders. Is there a macro that can sum up the two rows of data on to the first row (the one with ID and description in Collumn A & B) and delete the row afterwards, leaving only one row of data (format can be seen in the bottom half of the attached file). The original citrix report has hundreds of product IDs, which may vary from month to month. Original Format.xls
View 2 Replies View RelatedI have 2 cells that dump data in a column format. I need to combine these two cells with a ' after the data in the column. This macro will loop down until both column J and K are empty,
I need a Macro that
1. Will combine both cells
2. Data will be value format not formula
3. Macro Will run as soon as workbook is opened
Ex.
F J K
1 Prop MD MD Objective
2 1' Test Data 1 Test Data
3 235' next data 235 next data
I Need To Combine 2 Fields Into 1 Field Without Spaces Or Commas Between Them.
View 4 Replies View RelatedI have 5 columns, each with some data. On the 6th column I need to combine the data from all 5 columns into column 6 to be as: Data in A2-Data in B2-Data in C2-Data in D2-Data in E2. With one exception. column D is the date in this format 27-Aug-09. I need the date to be modified on the fly from 27-Aug-09 to be 270809 which will be the info in D2. See example attached.
View 3 Replies View Relatedhow do i make a number list. i want to start at 8000 then i want excel to auto generate up to 9000. it will take ages to sitt and punch 8000 8001 8002 ......
Next problem
how can i sett that cell A1 is connected with B1. so when i sort by number it follows. Example:
I need to combine a first name with last name, combine them with a domain to
create a useable email address.
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.
I need a macro, that will merge the cells and the data in cell A1 with data in B1, and the data in cell B1 will paste to the end of data in cell A1.
Column A Column B
KL8CB6S90DC505233
3GYT4MEF9DG331323
I need the final result to look like this:
Column A
KL8CB6S90DC505233
3GYT4MEF9DG331323
I need it to complete this for all data in Column A and Column B.
21045
21022
21054
20214
20114
I would like to know how to combine many cells in a column - sometimes 100's - into one cell separated by a comma without having to type each cell name using.
combine 2 cells containing text the convert all to lowercase
i.e. in say A1 John, B1 Smith
convert this to c1 johnsmith
this is to me used as a user name generator
then in cell d1 create a 5 digit unique number (password)
I'm importing some data from an HTML table into excel, it all formats pretty well except that some of the data appears on multiple rows.
A B C
1 01.02.06 John
2 Bob
3 Sam
4 02.03.06 Jim
What I need:
a b c
1 01.02.06 John, Bob, Sam 3
2 02.03.06 Jim 1
What I need to do is get all the items in the colum B which are listed under the same item in column a, in the first case John Bob and Sam and put them all into the same cell. I then want to delete the empty rows. It's important that this is done by testing to see if column A is blank, rather than using the date.
then in column C I want to count the items in column B,
ideally then repeat the script for the whole sheet.
Not a clue of the type of syntax I should be using though!
In fact there's probably a better way of importing the data from the HTML so it doesn't even need to be done this way! of course I'm oblivious to it. Auto Merged Post Until 24 Hrs Passes;Thinking maybe I didn't make myself properly clear....
If a cell in column a is empty then it means the item in column b needs adding to the end of the item in the row above in column b.
I then want to count the items in column b.