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 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
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 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 am trying to lookup up when ppart matches spart and subtract total shipped from total produced. Here is where I am getting stuck .... because spart has multiple returns LOOKUP(2,1/((I2=C:C)),J2)
View 3 Replies View RelatedIn Row 3, starting at cell C3, I have a list of Stores in the format Store A (Town 1), Store A (Town 2), Store B (Town 1) etc
In Rows C4 to CA7 I have 1 or -1
I want to count the number of instances of 1 for Store A
I have tried the following formula, which is returning 0
{=SUM((C3:CA3="Store A *")*(C4:CA7=1))}
Which is the best way import many (thousands of) files in text format into a single excel spreadsheet. Importing files with e.g. 50 lines to create 50 columns for rows for every file
View 2 Replies View RelatedI'm trying to create a workbook that will calculate times. Currently I have a worksheet that has, for example Incident #1, Date/Time In, and Date/Time Out. At the end of the row it will calculate total time in hours.
I need to create a second worksheet that will have Incident # to correspond to the incident # on Sheet1, with Date/Time In and Out. However, on this sheet there could be more than one occurance of Incident#1, and multiple times in an out. That total will be deducted from the total of incident#1 on Sheet1.
The easiest way to explain this would be...say for the sake of argument Sheet1 calculates the duration of a vacation in hours (lets say 168 hours). Sheet2 will calculate how much of that time was spent doing business (lets say 2 hours on day1, 3 hours on day 4). In the end I want to subtract 5 hours from 168 hours giving me 163 actual vacation hours.
My problem is, I don't know how to go about using the values in multiple rows in Sheet2 that correspond to the row in Sheet1.
I need to apply filters based on 2 criteria
Filter1Dropdown SelectionFilter2Dropdown SelectionHeader1Header2Header3Header4Header5Header6
Depending on what is selected in Filter 1, lets say it applies to column F and i get a data set
Now with the filtered dataset of Filter 1, I should be able to apply Filter 2 or vice versa.
Also at the end, I need to release all filters so that i can see the entire data set.
i have created that will sort a db into dozens of sheets, now i need to be able to reset the process. There are 3 sheets i need, "balances", "trans", and "template". im unsure as to how define the sheet names so that the dSheet variable will recognize them. the way i have it now just deletes everything.
Sub reset()
Dim dSheet As Variant
Sheets(1).Select
On Error Resume Next
Do Until Sheets(1)
dSheet = ActiveSheet.Name
If Not dSheet Is "balances" Or "trans" Or "template" Then
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Else
ActiveSheet.Next.Select
End If
Loop
End Sub
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 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 134 parts and each part is available in 5 price groups - these are static items that need no calculation as they are completed on another worksheet. I have 400+ "items" each item is made up of various parts. I need to calculate the cost of each item. Hard to explain without example so I have attached an example.
View 2 Replies View RelatedI have made a spreadsheet but would love to be able to incorporate multiple 'Clear Data Buttons', (Macros)
that clear cell data.
I have attached several screen shots of my spreadsheet, with the URLs listed below.
(Images 1-6)
http://tinyurl.com/qba57l
http://tinyurl.com/od9upb
http://tinyurl.com/ovaeej
http://tinyurl.com/pctsvj
http://tinyurl.com/oc2qqm
http://tinyurl.com/qdhzge
Spreadsheet (xls & xlsm) in zip format
http://tinyurl.com/qczlap
It is a trading order sheet that I want to use, but also upload to a blog that I have just
started, relating to trading as a free download, hopefully it may benefit some users/visitors etc.
OK, regarding the macros.
I'm not quite sure how it is best to do it.
In total there are 13 'Clear Buttons.'
11 are 'CLEAR DATA' Buttons
12 is a 'Clear ONLY ORDER SHEET DATA' Button.
Finally 13 is a 'Clear All DATA!' Button
I think it's best if I give the cell location of where I would like the macro buttons located and what data they clear.
Clear DATA - Button 1 - (Is in Cell F3) - To Clear Data, Columns D,E,F & Rows 4 to 13)
Clear DATA - Button 2 - (Is in Cell N3) - To Clear Data, Columns G to O & Rows 4 to 13)
Clear DATA - Button 3 - (Is in Cell U3) - To Clear Data, Columns S,T & U & Rows 4 to 13)
Clear DATA - Button 4 - (Is in Cell F15) - To Clear Data, Columns D,E,F & Rows 16 to 25)
Clear DATA - Button 5 - (Is in Cell N15) - To Clear Data, Columns G To O & Rows 16 to 25)
Clear DATA - Button 6 - (Is in Cell U15) - To Clear Data, Columns S,T & U & Rows 16 to 25)................................
I have a workbook that query's our case tracking database and pulls data into a csv. I am trying to write something that would do the following:
1) Prompt the user to enter in a case number
2) Open up the following file:
-"C:Documents and SettingsDetails.csv"
3) Look through A:A for a match of the user entered number
4) If a match is found then pull the data from cell B in that Row
5) Output the data (from row b)into a text box on the user form along with the original entry made by the user.
i have this data.
WeekFRM CURRCY Account val val2 val3 val4
42100¢BBHBANK403522
43100¢BK200 001310
44100ABBHBANK3013
45100ABK200 9043
46100CBBHBANK10106
47100EBBHBANK903420
48100EBK200 503527
How do i get this into a chart, with week along the bottom, Amount on the left. This is starting to be a pain now as i need to seperate by currency, but a pivot chart looks really realy messy with all the drop down boxes and stuff. I dont mind using drop don boxes, in fact, its going to be essential. but the pivot one look horrible.
I have attached a report I do monthly, a duplicate ticket report.
I am able to work out the duplicate tickets and parent tickets but
I need a formula to do 1 more task.
I need to check all the duplicates in A:A and also the assignment group in K:K.
If the ticket is a duplicate and the assignment group is nz - dms* i need it to count these.
Probably simple but Im still pretty new to all this. I came up with the following but its obviously wrong or I wouldn't be here.
COUNTIF(AND('Duplicate Calls for'!A:A,"Duplicate"),('Duplicate Calls for'!K:K,"dms - nz*))
about doing the opposite of consolidating multiple sheets into one: I have a large database and want each row of columns in their own new sheet. Rather than copying and pasting each row that I have, is there a code or formula to quickly command this to happen?
View 9 Replies View RelatedAt Line 40, the program stops running. It asks for an object, but Im not sure why.
Also, I realized that there is an issue with sub twodeux copying the range selection from sheet1 of oldbook to the newly created sheet1(transfer template) of newbook.
How can I cause the form to hold that selection of values until its ready to put them into the newbook? ....
I have created a form in excel. I now want to print the information from the form in 3 different ways from a custom buttom. The form is an invoice and
1. I want to print the entire sheet with all information including custom header and footer just as if the print icon from the toolbar was selected.
2. I want to print the second sheet the same as the first with the information of cell C21 replaced with upper case Xes except for 4 digits (this is the credit card Number) including header and footer (format in cell is 1111 2222 3333 4444 / 555 want to see XXXX XXXX XXXX 4444 / XXX)
3. the last printout I only want to print the following cells in their correct locations
A6:C6
A7:C7
A9:G9
A10:G10
A26:E26
A27:E27
A28:E28
A28:E29
A30:E30
A31:E31
Also on this last sheet I want to change some items : Cell F26 to read PULLED BY and cells F27, F28, F29, F30, and F31 to be blank and no header or footer.
I would like to set up VBA codes to generate a variable number of transition matrices. I would like to know how I can define dynamic ranges instead of coding each and every range. In the codes below, I have to define each range one by one instead of using a loop.
Sub TransitionMatrix()
Dim P1 As Range
Set P1 = Cells(2, 2).Resize(3, 3)
Dim P2 As Range
Set P2 = P1.Offset(5, 0).Resize(3, 3)
P2.Cells(0, 1) = "P2"
For i = 1 To 3
For j = 1 To 3
P2.FormulaArray = "=MMULT(" & P1.Address & "," & P1.Address & ")"
P2.BorderAround Weight:=xlMedium
Next j
Next i
Dim P3 As Range
Set P3 = P2.Offset(5, 0).Resize(3, 3)........................
I've created a very large spreadsheet (4096 calculations) and I'm using array formulas for a large number of cells, which leads me to my current predicament. All the formulas are written in, but I haven't done the necessary ctrl+shift+enter after finishing all of them (there is only slight variation in each calculation so I produced them in an iterative manner) and I was wondering if there was a way besides selecting each cell individually - pressing F2 - Then pressing CTRL + SHIFT + ENTER to make all my formulas array formulas.
View 5 Replies View RelatedI 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 Related