I receive a monthly report containing a list of people, and how much is being paid for certain services. The company that sends me this list is preparing to adjust their rates and it will be retroactive back a few months.
The way they plan on doing it is by means of taking a credit back several months, then "re-paying" the correct rate. The main data will include the month for which the payment (or credit) is being made, the person's unique identifier, as well as the amount.
Here's a sample of what it would look like:
Name, ID, Month, Amount
John Doe, 123, 04012008, 25.00
John Doe, 123, 03012008, -20.00
John Doe, 123, 03012008, 25.00
John Doe, 123, 02012008, -20.00
John Doe, 123, 02012008, 25.00
So basically the above shows they paid $25.00 (correct rate) for April 08, then they took back $20.00 the prior two months (the old rate) and paid the correct rate right afterwards.
In what I need to do, this is going to be a lot of work. Is there a way to programatically merge the amounts given the member's unique ID as the "key field" as well as the same month? So it might look like the following:
Name, ID, Month, Amount
John Doe, 123, 04012008, 25.00
John Doe, 123, 03012008, 5.00
John Doe, 123, 02012008, 5.00
Just giving the sum of the amounts for a the given people in the same month?
I'm pretty good with VBA but this one is stumping me.
In my userform I have a list of check box's that can be selected. Currently if more than one is selected, they will appear in the spread sheet in the same line one after another with a space between them. How do I make it so they either appear with a comma appearing after each, so the next value appears on the next line below or most perferably the cell turning to a drop down list with the values?
Current code
VB: If CheckBox1.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox1.Caption If CheckBox2.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox2.Caption If CheckBox3.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox3.Caption If CheckBox4.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox4.Caption If CheckBox5.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox5.Caption If CheckBox6.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox6.Caption
Spreadsheet current cell appearence if all 6 are selected ' Chinnook EH101 Lynx Puma Sea King Fixed Wing'
Required appearence: Chinnook EH101 Lynx Puma Sea King Fixed Wing
I'm having trouble combining three lines into one. I'm trying to do a daily weight chart and the x-axis is days of month, and the y-axis is the weight. I have 3 rows for the weight (one red line for weight going up from previous day, one green if the weight goes down from previous day, and one yellow line for equal). I want to make the three into one showing the colors. Right now all they are are vertical lines and are not connected.
I have a report that I ran that gives me all the customer we have. Unfortunately, my system gives me a separate line item for any thing that my be different within the account like the account has multiple services. I need a way through excel or vba to take these multiple rows of the same acct # and site # and combine it into one line. Each account and site could have multiple services so I would need each consecutive service to be moved to last empty cell of the first such account.
A transportnumber can have multiple instances of itself, such as 106200. This transport number can also have multiple instances where the postcode is the same. These are the rows i want to combine
So shortly: Transportnumber double/triple...+ Postcode double/triple...= combine these rows.
if only transport number is double, or only postcode, then dont combine. I want it to combine 2 columns of values, one will b the kgs, other one a price.
So example of end result for 106200 would be 106200 8500 value+value (these 2 rows matched so it combined the 2 values i want it to) 106200 8600 value ( this was unique so it stays unique)
I have the below macro which is failing to insert text into A1 of the Header Sheet, followed by the values in the designated cells that are from Sheet1. I would also like to have the values in cells J2 and K2 enclosed in single quotes.
Sub Header() Worksheets("Header").Activate ActiveSheet.Cells(1, 1).Select ActiveCell.Value = "create or replace" & " '" & Sheet1.Range("J2").Cell.Value & "' " & " '" Sheet1.Range("K2").Cell.Value & "' " End Sub
how do I get it into the nicely formatted version most of you are using? The Mr Excel HTML add-in?
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
I have a column of cells with Customer info and address in each row, that I need to move into multiple columns. ie Cust name, acct #,address, city,state, zip the problem is ALL of the info for each customer is in ONE cell.To be clear, cell B1 has complete name address etc for one customer, B2 has the info for another, B3... on and on. To complicate matters as in the example, there is not a pattern in the lines. the first example shows the info beginning on the 2nd line of the cell, but the 2nd example shows info in the first line. I have tried the text to column fixed width. Didn't work because names and addresses are of varying lengths. Then I tried delimited, but there isn't a common separation between different data. I am using excel 2007. FYI I did go through many many threads and found some that were close to my problem but not close enough to work.
" JOES TOWING SERVICE [0123451] 123 STREET
CALEDONIA FL 12345 " "BUBBA / SHOP ACCURATE AUTO SUPPLY INC [0234567] 21234 HYDRAULIC DR
I have a challenge where in have a list of about 4000 rows and each cell have mutiple lines. ( i know there is a thread about this but that did not address my issue).
So my data is as follows - (I have attached teh sample file)
"Pari bom - GP and CFO 02547895452 pari@gmail.com
papa bom - GP and CFO 23365789541 papa@gmail.com
mama bom - CFO 2587412589 pari@gmail.com
nana bom - CFO 2587412589 nana@gmail.com
masi bom - CFO 2587412589 masi@gmail.com
"
I want to be in a position to seperte each line in exclusive rows and then seperate teh name, number and the email ... the later eing easier but cant get my head around seperating the cells.
share a macro to copy cells from range C20:C300 and paste them in C2 on multiple lines. If possible two lines gap between each cell's value when they are pasted in C2.
I'm having trouble combining some values in cells. For example in cell A1 i have the value 372-25. In cell B1 i want to make A1 part of another value (it is an APN if you must know). So that cell would be 011-372-25-11. I would be adding 011 as a prefix to each cell and 11 as an ending. I'm sure there must be an easy way of doing this.
I have tried =011-A1-11 ="011-"A1"-11" ="011-A1-11"
I have the follwoing example. Basically, there is an "X" for each posession of Andrew (cells: B1, C2, D3, E4). What I'm trying to do, in another sheet, is centralize the below information in only one row for "Andrew", with an "X" under each of his posessions (all the 4 "X"-es are now in cells B1, C1, D1, E1).
The company I work for has kept a list of the types of objects sold with their houses and how many were bought for each customer, via an Excel document. Here's a snapshot - You can see the name of each type of object on the left, with each column afterward representing an individual customer (Each vertical row is one customer) and how many of each item they bought.
[URL]
We're hoping to make a bar graph of how many of each type of item has been sold. I selected all the titles and columns of numbers and ended up with this, which is almost what we're looking for:
[URL]
The problem here is that each individual customer has their own line on the graph. The Recessed Can Light one I highlighted is a good example of a lot of people who bought varying amounts of that one item. What I'm looking to do is combine the individual sales for each item and make a single bar for each representing the total sales overall, so we can gauge what the best and worst-selling items are. Is there any way to do this?
I have a User Form in which users can enter R,G & B values in 3 seperate Text Boxes in order to change the colour scheme of a worksheet to suit their personal taste.
I have tried combining the 3 values into a string to give, for example, RGB(255, 182, 45)
But, of course, because it is a string variable it is enclosed in quotaion marks and so, when add to a .Interior.Color function, the qutation marks are added too and the macro fails.
I have tried altering with Left, Right, Mid and Trim but cannot get rid of the quotation marks. I also tried converting to Hex but got totally confused there!
code to convert the values in the 3 list boxes (called LBRed, LBGrn and LBBlu) into an RGB value that can be appended to .Interior.Color
I am trying to report on race with splits that involve hundreds of secs, secs and minutes. I wish to rank individual splits and overall. Problem is I can't put in values along the lines of 3:09:87, it comes up with something odd?!
I need to have two area charts combined, but they have different x and y values. Is there another way to do this.When I try to combine them in one graph, I can only use one x-axis value.
I have attached a sample spreadsheet for your review. I think I need to combine multiple IF statements into one single statement. See column I:
There is currently a formula in Column I that looks at Column H and computes a ROUND formula based on this information. Unfortunately, this formula only works if Column J says "MB". If Column J says "Minutes" then I need Column I to compute with this formula: =IF(H2<30,0.5,ROUNDUP(H2/60,1))
How can I combine this with the current formula to get what I am looking for? Here is the current formula (for MB): =IF(H2<0.125,0.125,ROUND(H2,4))
I have a list of data elements listed row by row with headers
last 4 of SSNFull Nameschool_codeterm_beginterm_endSchool Rep NameEmail 1111 Jim smith 00104600082012 082016 school reptestemail@hotmail.com 2222 Jane Doe 00104600082012 082016 school reptestemail@hotmail.com 3333 justin justin00104600082012 082014 school reptestemail@hotmail.com 4444 Joe Joe 00104600082012 082016 school reptestemail@hotmail.com 5555 Jake Jake 00104600082012 082016 school reptestemail@hotmail.com
What I need to do is to get all 5 rows into one row and duplicate the headers so row 1 has all fire rows of information one column after the next comlumn. If I can't duplicate the headers easily and can just aggregate the data into one row in cell by cell that fine.
I need to combine multiple sheets in to one sheet. Each sheet has a column with unique identifiers but other columns have different data. The model is attached.
I have a userform that has the same type information on 3 different entities (name, address, phone, etc). The same research is required on each one i.e. you need to look up the zip code for each). Therefore I have a bank of buttons on each page of the multipage form. Each button does the same thing depending on which page you are on. The following is my code to copy some information and open another application depending on which page you are on and which type entity.
My question is how can I combine these into one so my module is not 3' long.
A member of my team has created numerous templates in Excel 2007 for various customers. Each template has some columns that are identical for each (Cust Name, for example), and some columns are unique to each customer. I want to combine all of these individual templates into one Excel workbook and be able to click (select from a dropdown list) the customer name, and have only those columns that pertain to that customer appear. Select another customer name, different columns appear, and so on. Is this possible to do in Excel 2007? [This is my first post, so please forgive any offenses if my question is too long.]
I have multiple WB's all with the same format in a single folder. I need a button to copy all the text from each WB into a single Master WB that has the same format. There are 3 sheets in each client WB corresponding to three sheets in the Master WB. Each client WB has a number of rows (or none) on each sheet and when they are copied to the Master they need to paste consecutively and into the corresponding Master sheet.
Something like this:
Copy rows from [WB1].Sheets 1, 2 and 3 (starting at a:4, columns A-Q). Then paste to corresponding [MasterWB].Sheets 1, 2 and 3 (at a:4, columns A-Q) then repeat with Next Book.
I hope thats clear enough. The Client WB's are all named "stats [name].xls" with 1 hidden sheet (to populate lists) and 3 sheets named "POC", "ISS" and "ECS" repectively. The Master WB is named Stats.xls with the same sheet names as the Client WB's.
The following code was written for me by a helpful member of this forum but it only copies the first sheet of each Client WB. When i tried to duplicate and modify it to copy the second and third sheets I could not get it to copy from the second/third sheets and it meant 3 buttons/3 steps/3 times the confusion.
Sub Report() a = 1: st: If Sheets(5).Cells(a, 1) = "" Then GoTo endd Path = Sheets(5).Cells(a, 1).Text If Dir(Path) = "" Then w = MsgBox(Path + " Is Not A Valid Path / File", , "REPORT") a = a + 1: GoTo st End If
If there is anyone who could help me with this I would very much appreciate it. I am only a basic user of Excel and VBA is still new to me. Adding modules and understanding basic commands is as much as I know at the moment.
Each one consists of user data for each time they logged onto our systems. The problem is that there is one row for each login. There are anywhere from 1 to 20 (or so) rows per user. I need to turn that into a spreadsheet with one row for each user.
In theory, the problem shouldn't be that difficult. Each user has a unique user ID, and the only math that's required in the merge is a simple adding of numbers from three columns to give a single total for each user.
So far, the only workaround I've been able to come up with is using the subtotal function based on the unique user ID which will at least isolate each user and total up their usage statistics. But it still leaves me with the problem of deleting the now uneccessary extra rows by hand, and then pastin the data back into a new sheet. Using vlookup, I think.
I want to go dorn column B, starting at B26:B, and if the value in B equals any of the values in C2:C25 than delete that entire row(not just delete the word, because there are values in column C and D that I need gone with the C value).
