I need to loop down a column of data containing branch numbers and whenever there is a new branch, I need to insert a row both before and after the last instance of the previous brance. For example:
If the first three rows in the column contain branch 0001 and the forth row starts with branch 0002, I need to insert a row both before and after the 0001 in row three...
I have a large file that has the following setup
2003 Zipper X
2004 Zipper X
2005 Zipper X
2003 Zipper Z
2004 Zipper Z
2005 Zipper Z
How can I insert rows at changes in the product description
I'm not really all that familiar with a lot of things in Excel. I do know how to make a macro, which this question is to help with, but I kinda skipped a lot of learning in between.
I am wanting a shortcut that will insert a new row after each change in column A. I don't think Subtotals is the answer because after I enter the row to seperate them I will want to enter another row and seperate again by column F when there is a change that is not a blank row.
Column A of my spreadsheet consists of Subcase 1000 to Subcase 1010 for example, with up to as many as 100 repetitions of each subcase in consecutive order. See Below:
Subcase 1000
Subcase 1000
.
.
.
Subcase 1001
Subcase 1001
.
.
.
Subcase 1002
Subcase 1002
.
.
etc
I would like a macro to find where Subcase 1000 finishes and Subcase 1001 begins and then insert 2 new rows between them. These 2 new rows have to be inserted between any change found in the column A field. The searching of Column A should start at Row 15. I have uploaded an example file. This file contains 2 sheets, 'Before Macro' and 'After Macro'. Hopefully it outlines what the outcome of the macro should look like.
I've posted this thread in http://www.excelforum.com/excel-prog...ml#post2038238 but since the thread has been solved, I think nobody would look into my problem, so I'm creating a new thread for the purpose.
My previous thread:
Hi there,
I'm looking for the same idea only that I wanted the macro to select the data in the cells selected. Based on the code given by StephenR above, I tried to do some modification but I don't know how to make it to start from the first cell of my selection, not from row 15 as R_S_6 wanted. The range selection can be in any column. Here are the code that I used:
I have rows of data with each row going from column A to column P
They are sorted by column A (account No.)
I need to insert 2 blank rows between each change in account No.
I have a spread sheet with values in the area of A1:H834
In column H, I have number values from 1-7.
Essentially that number value means that the values in the row are duplicate.
So, for example, if H2 has a value of 4, that means that $A$2:$G$2, really should have an additional 3 rows underneath with the EXACT same data in each cell, however, the way the sheet was created, was to remove the duplicate values and just indicate in column H, the number value of how many duplicates $A$2:$G$2 really is.
I need to unpackage this and create what it was originally. What type of formula can I use, to look at the value in H2, and then insert underneath that number of rowes with the exact same data as A2:G2 and do the same for the remainder of the table all the way down to A834:G834
I have spreadsheets I generate weekly that have 100s of rows. Everytime there is a data change in a certain column, I need the same row entered. I found a macro from this site that got me sort of half way.
I am trying to paste the macro that I found, but I am such an idiot I can't figure out how without it losing its formatting. Regardless, it only enters a blank row at every data change. Is there a way to enter a row with the same labels already entered in it?
i have a list of numbers in column A and in column B every now and then a * will appear next to a number. The first number will always have a * next to it. Basically i'm looking for a way to say...that either by copying to another tab or inserting rows on this tab, do the following for every number.
Insert a row above that says
[pf6]
put a " next to the number so that it reads as below
"408425
and then the following in new rows as below.
[enter]
[tab field]
[tab field]
[tab field]
[tab field]
"1.5
[field+]
[pf5]
I need code that can work down a column and insert a blank row when it notices the data value has changed. i attached a file to show what i am looing for.
View 4 Replies View RelatedI have a column with intervals(every 30 min) and with some values. My problem is that if there is that if the value is 0, the interval is deleted. Is there a way(maybe macro? ) for the excel to see that an interval is missing and add it with value 0?
Attached File : intervals.xls
i've got supplier list on the excel file and need to insert two blank rows under each supplier name. the two blank rows need to be inserted no matter whether the supplier may be occupying one row or 100. for example
from this
a ltd
a ltd
b ltd
b ltd
b ltd
x ltd
m ltd.........
i have numbers in column A1:A556, i want to insert a row below every column that has a number so that they can have double spacing.
View 1 Replies View RelatedI have a string of numbers ex. 1234, 5678, 91011 in 400 rows. Before each string I would like to add 88.
View 4 Replies View RelatedI need to insert a blank row between each row of data under my headers (not the immediate row) until I reach the end of the particular table. I then move down to the next header and repeat the process.
My starting data looks like this:
HEADER
DATAROW1
DATAROW2
DATAROW3
-emptyrow-
HEADER
DATAROW1
DATAROW2
DATAROW3
I need it to look like this:
HEADER
DATAROW1
-emptyrow-
DATAROW2
-emptyrow-
DATAROW3
-emptyrow-
[Code] .....
I deleted the data and made up some figures but kept the format to protect privacy. Highlighted blue and green because it's possible to have the same name have 2 different types. I put in dashed lines to more clearly divide months.
1. For each UNIQUE "Name" and "Type" add a new row starting from last entry
2. Copy A,B,C to new row
3. Copy LAST (most recent) "End Balance" from column "I" corresponding to last person
4. Copy column "I" formula into new role
So Name, ID#, type, column D and the formula in "I" (excluding "Name" and "Type" duplicates) should be added to the end of the sheet with their new row
Unsure if possible:
5. Make old amounts (columns D,F,H,I) not count towards the totals
6. Totals reflect new amounts only so values aren't counted multiple times
exampleforforum.xls
I'm new to Macros and below is my requirement.I need to split my data into multiple rows based on count and the first row should have the value but the other rows should have a value as zero.
Input
Count Value
1 400
2 101
3 300
4 450
Output
Count Value
1 400
2 101
20
3 300
3 0
3 0
4 450
4 0
4 0
4 0
Original
01012014 DAVE JOHN
02012014 MAT
03012014 CHRIS MIKE PHIL
What I want
01012014 DAVE
01012014 JOHN
02012014 MAT
03012014 CHRIS
03012014 MIKE
03012014 PHIL
I am working with a spreadsheet generated from software that keeps track of fuel usage for a large fleet of vehicles. The data comes out looking like the snapshot below.
******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=ABCDEFGH3 Transactions for CUSTOMER ID: 0000CUST7 Sales 4 5 6Product summary for Vehicle ID 00001080 7 8Product Description Transactions Quantity9 101 Unleaded 3 57.60 GL11 12Hose summary for Vehicle ID 00001080 13Site ID HoseGradeProductTransactions Quantity140001 2113 57.60 GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am trying to get the data into a more convenient format for analysis. I need a macro that will:
1) Take the text "Product summary for vehicle ID 0000****", extract the last 4 digits of the text, and paste it where the 1 is under the Product heading (a10). Those digits are the actual fleet number, and I need to separate them out from the rest of the text. The digits will change for each vehicle, so the macro should just move down the spreadsheet doing the same thing for each instance (the setup you see is repeated for every vehicle).
2) Once the first goal is accomplished, I would like the macro to then go back through and delete every row except for the rows with the pertinent data in them. So this means I would only want one row per vehicle and all rows would line up directly below each other like demonstrated below.
******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA10=ABCDEFGH101080 Unleaded 3 57.60 GL111081 Unleaded 6 84.70 GL121122 Unleaded 5 47.00 GL131182 Unleaded 8 95.80 GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I need to create extra rows of duplicate data. The number of duplicate rows depends on the number of semi-colons in the 15th column (column O).
Sheet 1 is the original working file. Sheet 2 is the result of how the executed macro for the first 2 lines of data. The first line in sheet 1 has 7 semi-colons, so there should be 7 rows of duplicate data created in sheet 2. The second line in sheet 1 has 6 semi-colons, so 6 duplicate lines, etc.
I need to insert blank rows at the end of the each data segment to add totals for each individual. Is there a macro that I can use for this? I'm attaching an example file.
View 8 Replies View RelatedI am looking for a macros VBA where a user insert or update a data the date and time should be insert in column I and save the workbook.
Note: If the column I already have the date and time inserted before then it should give message record already have date and time.
I am using office 2010.
I have a chart in a workbook that works fine by looking at the following data series:
=Workings!$A$2:$C$35
In the data series above...where the numbers are 2 and 35 I have named ranges "CHART_Start_Row" and "CHART_End_Row" which will change dynamically as appropriate and can replace these static numbers - but how do I incorporate these named ranges into the data series reference?
I have a multiple rows of data, but one column which is K has data like:
1020
1020
1020
1020
1020
1030
1030
1030
1030
1050
1050
1050
What i want my macro to do, is whenever that number in column K changes to copy all the data in row A thru K to a new sheet.
The best way to explain my problem is to look at the table below:
How it looks now: ApplePrice 1
Price 2
Price 3FruitDeliciousPearStore 1
Store 2FruitVery DeliciousHow I want it to look:ApplePrice 1FruitDeliciousApplePrice 2FruitDeliciousApplePrice 3FruitDeliciousPearStore 1FruitVery DeliciousPearStore 2FruitVery Delicious
I have 5 pivot tables on 5sheets, all looking at the same source data in sheet 6
On the source data there is a filter on the headers, if you change the filter, is it possible for all 5 pivot tables to update according to the filter?
My starting point is the below, but there probably is a better way but i would want the sourcedata to equal visible rows on the source data sheet headers run A:K and up to row 10000 .
I want to show which rows of data have been changed by incrementing a revision attribute. For example, if a user changes the contents of a cell anywhere between rows 2 and 13 and col 1 and 9 then the revision attribute in col 10 would increment from 1 to 2 (for the affected row). If another change affects the same row then the rev attribute would increment to 3, and so forth. I don't care which cell was changed only that something on that row was touched.
I thought the CHANGE event was a dead ringer for triggering some VBA code to control this but, since part of the change event code writes the revision value, this triggers another CHANGE event causing an endless loop until something (??) kicks in and stops it after 220 iterations. Is there a way to inhibit the change event just prior to updating the cell containing the version attribute?
Rather than post the code here I have submitted the workbook that includes the whole setup and code. I should also mention I looked at all the other worksheet events and I do not see any "triggers" that would fire each time a cell content is changed. As a side note, is there a way to step into the code of a change event? F8 does nothing.
I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:
Supplier
Invoice Date
GL Date
Invoice Amt
[Code].....
I'm a macro novice and have been trying to teach myself how to write the correct one for a task I need to do, but I cannot seem to get it right. Basically, I have bunch of data and for one of the variables, different values are separated by commas. What I want is to create a row copying the info below for each piece of data after the comma.
Sheet1
A
B
C
D
[Code].....
I suspect there is a fairly easy way to do this, but I cannot figure it out from searching the forums (or rather, I can't get it to work right).
i have this code which inserts blank rows in alternate rows,
Code:
Sub insertrow()
' insertrow Macro
Application.ScreenUpdating = True
Dim count As Integer
Dim X As Integer
For count = 1 To 20
If activecell.Value "" Then
activecell.Offset(1, 0).Select
[code].....
what changes should i make in this code to insert rows only when ther are now blank rows. So first time i run, blank rows are already there, and when i update some data at the bottom and re-run it inserts blank rows again.