I have an amount of data split into various fields. I need to do something similar to the following:
In each row I have three fields separated by columns: name, sector and specialty. There is only one name and one sector for each record, but possibly several specialties (of which there are fifteen types in total, so they do not appear in a consistent column. specialty 'i' could appear in columns 3, 4, 5 through to 17. ie, sample entries below:
sheet 1
a1 name a2 sector a3 specialty i a4 specialty iv
b2 name b2 sector b3 specialty ii b3 specialty iv b4 specialty x
c3 name c4 sector c5 specialty i c6 specialty ii
The thing is, for every specialty in a row, I need new records created, so the above would become:
sheet 2
a1 name a2 sector a3 specialty i
a1 name a2 sector a3 a4 specialty iv
b2 name b2 sector b3 specialty ii
b2 name b2 sector b3 specialty iv
b2 name b2 sector b4 specialty x
c3 name c4 sector c5 specialty i
c3 name c4 sector c6 specialty ii
I need to create a spreadsheet that has approximately 1000 rows with the same exact information in each of them prefilled (as I use the "drag-down" method). I use this chart throughout the year to enter various bits of data in each row. I also use conditional formatting in each row. The company I work for wants to have an empty space between each row. " You can create the blank rows separately and then interleave them with the existing rows by sorting. To start, insert a new column to the left of the existing column A. Enter 1 in cell A1 and highlight column A all the way to the last row that contains data. From the Edit menu select Fill | Series and click on OK. Column A should now contain numbers from 1 to the total number of rows. Press Ctrl-C to copy these cells to the clipboard, click in the cell just below the last of them, and press Ctrl-V to paste. Now highlight the entire data area, including the new rows with just a number in column A. Select Sort from the Data menu and choose the No header row option in the resulting dialog box. Under Sort by select Column A, under Then by select column B, and click on OK. Finally, delete column A. You now have a blank row after every one of the original 1,000-odd rows."
This works great for the data that I copied (with the drag down method) to all the rows. However, this method does not insert an empty line in between all the conditional formatting I have throughout the spreadsheet. This is my conditional formatting formula I have "manage rules" which is under "conditional formatting" =NOT(ISBLANK($A6)) (*please take note that this is selected for 1000 rows.) Maybe there is some kind of change in the formula that I need in order for the conditional formatting method to be copied onto every 2nd line of the 1000 rows".
In cell b5 I have =today() which automatically updates the date on a daily basis. What I would like to happen is for each day there is a new line is automatically inserted with the date so:
Sat26/04/2008gone off sickFri25/04/2008Thu24/04/2008Wed23/04/2008Tue22/04/2008Mon21/04/2008Sun20/04/2008Sat19/04/2008Fri18/04/2008days holidayThu17/04/2008Wed16/04/2008Tue15/04/2008
I would also like any information on each of the line to move down when the new line is inserted. I am not really that good with macros or VBA but feel that is the only option to solving this problem. Also would everything still update even if the file wasn't opened for lets say the weekend. So it is constantly rolling.
I have a spreadsheet that I have people fill in. A few columns have VLOOKUPS. I want to allow people to insert lines in this spreadsheet and have the formula automatically copy in the new line. It seems to already have that behavior for formatting and conditional formatting. Is there a way to also have that for formulas when inserting lines?
Name City Assigned Salesman ----------------------------------------------------- john Mountain View (formula using VLOOKUP) cindy Palo Alto (formula using VLOOKUP) ronnie sunnyvale (formula using VLOOKUP) ---------------------------------------------------
Insert line between john & cindy. Formula doesn't copy.
I ask them to copy the formula but they often forget. If I can make it a little more robust,
1. to leave a blank line inbwteen Shop A's Apples, Shop A's Pears etc
2. I also wanted to total up the price totals....(see example below).So if the last price was in E4, the total of all those prices above would show in F5 ....
One has around 129 lines for the first years 2006 in sheet1. The other links to the first 129 rows. Then I've linked the top 129 in sheet2 130 down for 2007 and so on for 5 years.
How would I add a line in Sheet1 then would insert 5 lines in sheet2 keeping the same order.
e.g
Sheet1 Name Rev Year John £120 2006 Row 5 Jack £150 2006 Row 6
Sheet2 (linked) Name Rev Year John £120 2006 Row 5 Jack £150 2006 Row 6 John £130 2007 Row 134 Jack £160 2007 Row 135 John £140 2008 Row 263 Jack £160 2008 Row 264 John £150 2009 Row 392 Jack £170 2009 Row 393 John £155 2010 Row 521 Jack £180 2010 Row 522
Now I'm looking is when opened in Excel, it should create a two sheets excel file 1) with the First Section and the 2nd with second section . based on headers included in file.which then will be used for PivotCharts..in 2nd stage of automating..
I need a macro that duplicates lines based on the value of a Column. So in Attached excel D1 is 10 - I need it to create 9 entries for that line, so there are 10 total duplicate lines.
I had a Macro that worked that is in the document, I even had everything set up the same, which it is not now, but could not get it to work.
A Macro or a Finished document is fine, Just need this to produce raffle tickets for the church
For each unique value of Order Number [col A] + Line Number [col B], I need a program which will locate the first instance of Next Stat [col D] = 530 and the first instance of col D = 540, deleting the other lines. The output will have two lines remaining for each unique col A+B combination, one where col D = 530 and another = 540. The data will always be sorted in date/time order prior to running this program. The actual data set has a varying number of lines, usually 1000+.
I want to insert a line wherever I encounter a number. I have a range of cells in column "A" with invoices listed and at the end of each customer I have the total. At the total line (the number) I want to insert a line. Right now I can't figure out how get the cell value to recognize a number from a non-number data type (bolded area)
Basically I want to copy and paste all data in a row if the value in a cell is equal to something
I am currently using the below formula
The Value in red = Set tgt = ThisWorkbook.Sheets(Criterion.Offset(, 1).Value 'Pick the cell next to the cell containing the criterion).
But my issue is now that i will need this to work based on 37 values and having this macro 37 times with the value changed and 37 buttons will be difficult.
So I am looking for a way i can add multiple values each going to separate sheets.
I want to be able to a place a picture on a workbook based on a cell. In column A, I will run a sql query to pull a list of items. In column B, I have the items formated thus they have a the .jpg. next to item of the picture i.e Hammer.jpg. whereas column A lists Hammer and Column B Hammer.jpg. In column C, I would like the pictures to reside in this column.
So far I have this macro but I can only get two pictures to come up even though my list has 4 items on it.
Sub pic() Dim x, c, d, a As Long d = 2 c = 40 For a = 2 To 6 b = Cells(a, 2)
I have a source sheet which contains data. Within this data there is a column (column 3) called Name and I would like to take the data from this sheet and paste it onto a new sheet but in grouped format. So take all instances where it finds criteria 1, then 2 and so on and just paste it on a new line each time it finds a criteria. So far I have the following code to search
Sub loopy() Dim rngTemp As Range ' the range variable declaration Dim intCounter As Integer 'counter as integer ' the following loop goes through all the cells in column a up until the last used cell in the column For Each rngTemp In Range("a1", Range("a" & Rows.Count).End(xlUp)) 'increment the counter if the cell has value fridge If rngTemp = "Fridge" Then intCounter = intCounter + 1 Next rngTemp MsgBox "There were " & intCounter & " cells equal to Fridge" End Sub
I am attempting to create a template to make time and dollar budgets for various projects easier to create (20 plus projects a year with varying number of employees).
I want to have a template in which you input the number of employees working in a cell (IE. 3 employees), and with that the table expands to have enough rows to have 3 employees data input. However, only insert the number required but not overriding "totals" row below. With the new inserted rows I would like to keep the same format and formula for the row it was based on.
I'm working with a large report that includes ~6000 rows of data. Within the report the data is separated into a couple hundred town names - "Town of XXX" - and I need a page break at each town. Is there a way to insert a page break based on text in a cell? Should be an option in conditional formatting but... Need to avoid VBA.
I have a large data set (excel file), of "Names", "Phone Numbers", and i need to sort this based on the States that correspond with the Phone numbers. The states currently do not exist in the spreadsheet, so my current problem is trying to insert those states into the spreadsheet.
There are over 100 area codes in the data set, so i'll likely have to write a large "If" statement in VB to run through them all, but that shouldnt be a problem.
NAME, PHONE, STATE Bleh, 555-555-5555, =ChkState(B2)
I've been playing around with the VB Stuff in Excel and this is what i've come up with for trying to insert the State field
Function ChkState(pVal As String) As Long
Dim AreaCode As String Dim StateAbrv As String
AreaCode = Left(pVal, 3)
If AreaCode = "201" Then StateAbrv = "Test201" ElseIf AreaCode = "203" Then StateAbrv = "Test203" ElseIf AreaCode = "555" Then StateAbrv = "Test555" Else StateAbrv = "0" End If MsgBox StateAbrv End Function
I'm fairly new to this VB stuff, my main problem stems from trying to insert the "StateAbrv" back into the Cell for the spreadsheet.
On sheet "Create Package" in cell "AA14" I have a value (lets say 2).
I want it to go to sheet "Samples" and insert a number of rows equal to the value on sheet "Create Package" cell "AA14" (so 2 rows)
I have a header row in row 1, so I would like it to insert the designated number of rows beneath that.
Then I would like it to copy a designated number of rows (based off of the "AA14" value, so 2) from the "Create Package" sheet starting at row 66 and then paste special values into the new rows that were inserted on sheet "samples".
Each week I format a report that I receive that lists every product a particular employee closed. Its a list of each employee, the product and date. I'll have about 20-30 lines for each employee. I can easily sort by the employee name to have a nice list.
From here what I do is use a forumla to mark where each employee's name changes, then filter by that changed line and insert page breaks for each of these lines. Is there a way to macro this to save me time, having 270 employees takes alot of time still, because thats 270 manually inserted page breaks.
For each column with a header A, B (these are people), 1 in their column represents they works that day on a project. The day columns are days of the week, and people cost different amounts on different days. I need to work out the total cost for each person for the working period.
The cost for the first day for person A is =if(b2=1,vlookup(a2, f2:h8, 2),0)
How do I sum the costs for each day in a single formula without creating a cost per day column for each person i.e. sum each line of vlookups?
I have two rows of data, each row also have similar data. I need to find the number of matches that the two lines For example :
7,7,K,A,8,7 K,6,7,7,A,9
To reach the conclusion I need to build six such functions: =1*(COUNTIF($A$65:$F$65,A66)>=COUNTIF($A66:A66,A66)) =1*(COUNTIF($A$65:$F$65,B66)>=COUNTIF($A66:B66,B66)) =1*(COUNTIF($A$65:$F$65,C66)>=COUNTIF($A66:C66,C66)) =1*(COUNTIF($A$65:$F$65,D66)>=COUNTIF($A66:D66,D66)) =1*(COUNTIF($A$65:$F$65,E66)>=COUNTIF($A66:E66,E66)) =1*(COUNTIF($A$65:$F$65,F66)>=COUNTIF($A66:F66,F66))
The result is 4
Is there a single formula that will give the result ?
Another question, is there a formula that I can compare to the principle of one line against the 20 lines ?
i have a list of about 2,000 rows of text going down vertically, but out of that 2,000 there's only about 1,500 actual items - the rest are duplicates.
how would i go about eliminating the duplicate strings of text quickly?
In Column B I have some measurements. These are measurements. I chart them in a line graph. I also have my max and min over in some other cells. These aren't the min and max of column, but these are what I have set the min and max at. To make this clearer, my max is 27, but the data in Column B could be anything....20, 28, 35, 15, etc.
I want to plot my data in Column B just as I am currently doing. However I also want to plot the min and max on that same line graph and show them has horizontal lines going across. That way, if one of my numbers is above the max (or below the min), you will be able to see it on the line graph.
Here's what my data looks like. The Viscosity (column b) is what I'm charting on the line graph and you can see the min and max data that I want to plot as well.
I have a file that contains at least 35 sheets. All of the sheets have some lines that are hidden. I tried selecting all the sheets and unhiding everything but it didn't work because some sheets actually have filters in them and the filter line is also hidden.
