Insert Rows Based On Numbers In Column
Apr 4, 2008
I have a huge spreadsheet with addresses in column A and number in column B. Each address needs to have a ceirtain amount of lines inserted below, depending upon the number in column B. for example:................
With the new space inserted. the numbers i have range from 34 -2. does anyone have any code that will insert the number of rows depending on the number in column B? i HAVE had a look through the forums, but there are so many topics!
View 5 Replies
ADVERTISEMENT
Oct 31, 2009
I want to insert rows based on the coloumn F (has 6.00 on it)
206101.08.200901.08.20096.00
Since the coloumn F has value 6 on it, i want to insert 5 rows below the current row
View 9 Replies
View Related
Apr 6, 2012
I have to create 1000 dynamic ranges and do not want to do this manually. See the list below for the syntax I am using, I am only showing the first 10.
Name Range
KPI_01=OFFSET(Date,0,1)
KPI_02=OFFSET(Date,0,2)
KPI_03=OFFSET(Date,0,3)
KPI_04=OFFSET(Date,0,4)
KPI_05=OFFSET(Date,0,5)
KPI_06=OFFSET(Date,0,6)
KPI_07=OFFSET(Date,0,7)
KPI_08=OFFSET(Date,0,8)
KPI_09=OFFSET(Date,0,9)
KPI_10=OFFSET(Date,0,10)
I have tried the following code which works to an extent...
Range("=OFFSET(Date,0,1)").Name = "KPI_01"
..but the final result of the named range is an actual range, i.e. "A1:A13".
How can I keep the named range as a dynamic range?
View 5 Replies
View Related
Jun 9, 2014
I would like to insert rows into column A from column D based on the count values and without affecting subsequent values in column B and C. For example. Count of a value in column A =1 and count of that same value in column D=5, THEN 5-1 (count of D - count of A) insert 4 rows in column A (shift cells down) without affecting data in column B AND C. Basically making the counts in column A and D equal.
COLUMN A
COLUMN B
COLUMN C
COLUMN D
G250001
[Code]....
The above example should look like this -
COLUMN A
COLUMN B
COLUMN C
COLUMN D
[Code]....
View 6 Replies
View Related
Jun 16, 2014
I have a row of numbers that change as they go up. I will have 2 rows of numbers that will range from 1's all the way to 9's (B through BO) I will have different letter sequences that I will be entering as the criteria in column F either on the Up and Down sheet or the Even and Odd sheet. When I say different sequences. This is 1 group of letters. The next group of letters in column F will be in a different order. They will always be a U and D in one and E and O in the other but in row 13 it may have a U or D and it will be the same all the way down. The object is for me to be able to place the letters in either column F from rows 13 to 60, place 2 rows of numbers in rows 58 and 59, press the advance cell in G1 and it it populates all across the spreadsheet from I to BO. I will get all different number sequences since I am starting with a different number in rows 58 and 59. I have all the letters color coded and they move up based upon the color of the letters in row 1. If there is a green E in column M then I will be the column that the number advances from 1111 to 2111. If there is a green O then column K will advance up from 1111 to 1121.
I made a short video if you wish to view it.
The conditional formatting is very important so I need to have the green font with gray shade and red font with yellow shade [URL]....
View 2 Replies
View Related
Oct 12, 2010
I have a list of numbers in sequential order. There are numbers missing. Is there a way to have Excel insert rows for the numbers that are missing in the series.
example:
1
2
3
4
8
9
becomes
1
2
3
4
8
9
View 7 Replies
View Related
Jan 7, 2009
I 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 Related
Feb 19, 2008
The excel file has industrial information arranged by sector. The sectors are represented by six digit codes that are not strictly sequential. For example, a typical example of consecutive codes is 120011,120012,120030,120040,130011. There are upwards of three hundred sectors.
The data I am using is missing certain sectors - sometimes more than 30 at a time. I would like to find a quick way to identify the missing sectors and insert rows there, preferably with the first cell in the row being the missing sectoral code.
I have a similar problems with columns as well. But I can always just transpose the data and use whatever solution works for the rows.
View 9 Replies
View Related
Mar 15, 2014
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).
View 6 Replies
View Related
Aug 3, 2006
I am trying to insert the missing numbers/rows on a numerical acsending column. I have a spreadsheet that has a list of 8000 parts that I want to put into numerical acsending order, however numbers are missing. For Example:
Column A is a numerical part number and column B is the description for the part number in column A. My spreadsheet used to be in perfect numerical order however over the years some rows have been deleted which makes my spreadsheet look like the below example.
ColumnA ColumnB
1 -------Part A
2 -------Part B
3 -------Part C
5 -------Part E
6 -------Part F
As you can see part number 4 is missing is there a way to insert the missing part numbers into column A and keep column B in order associated with column A? My goal would have the result look like this below.
ColumnA ColumnB
1 -------Part A
2 -------Part B
3 -------Part C
4
5 -------Part E
6 -------Part F
I am looking for a quick solution for this as there are 8000 rows. I have tried the MATCH function and can't seem to get that to work.
View 4 Replies
View Related
Sep 11, 2008
I want to delete rows in whole list and numbers of rows to be deleted I have in for example C column. How to do it?
View 9 Replies
View Related
Jun 24, 2014
I'm working on a large table which has one unique product number followed the number of rows that I want to add below it (for other variants of the same product number).
The Data looks like this:
Column B Column C
12543 2
13456 2
19543 1
I want it to look like this:
Column A Column B
12543
12543
12543
13456
13456
13456
19543
19543
View 2 Replies
View Related
Jun 19, 2014
I'm trying to create a macro that will ultimately insert rows based upon a particular value in a range of cells. What I would like to know if there is any way to make active only those cells that contain the letter "X"? Then I would be able to insert rows at that point. I hope I'm making this clear. An example of data is listed:
X ANDY 57.00
X DAVE 43.93
DAVE 92.00
X FRED 4.66
X GREG 23.55
GREG 84.21
GREG 8.69
X MIKE 83.50
X SETH 41.33
to look like:
X ANDY 57.00
X DAVE 43.93
DAVE 92.00
X FRED 4.66
[Code]....
View 3 Replies
View Related
May 16, 2011
Want to run a script to add rows beneath each row based on the value in one of the cells in that row and then move to the next row of data and do the same.
For example:
Cell E5 = 7, I want to Insert 7 rows beneath row 5, then move to the next row and insert the number of cells equal to the value in column E of that row.
View 6 Replies
View Related
Jul 14, 2009
I need to create a macro that will insert exact number of rows based on the value in certain cell. e.g. value in cell F2 of sheet1 is 3...so I need to insert 3 rows down from cell F9 in sheet2 copying data in cells D9 and E9 to inserted Cells / rows.
View 4 Replies
View Related
Nov 8, 2006
I need to insert a row based the value of in column L. For example, beginning at L10 and down, are values. These are subtotals that are dynamic (as in they move based on how much data is inputted to the worksheet monthly.) I need to insert a row above the value. So if L22 has (2,961.25) in it, I would like to it down and insert a blank row. So on down the sheet.
View 2 Replies
View Related
Mar 22, 2007
Following up from Insert Blank Rows Where Date In Column D Changes from the first populated row after the inserted rows, and paste it in the 1st blank row above the populated rows (as a kind of title). How would I build this into the code below?
Private Sub CommandButton1_Click()
For x = Range("D65536").End(xlUp).Row To 7 Step -1
If Range("D" & x) <> Range("D" & x - 1) Then Range("D" & x, "D" & x + 3).EntireRow.insert
Next
End Sub
View 7 Replies
View Related
Aug 26, 2009
I am trying to write a macro which will insert a blank row at the end of each year(A column) (Last cell i.e Dec 95 and so on).
I have attached the file for the same ....
View 13 Replies
View Related
Feb 3, 2009
I need to insert one or two rows depending on the criteria of two different columns.
We have two shops (A and B)...and the sales are expresed like this: ....
View 9 Replies
View Related
Jan 1, 2007
Inserting Rows and Headings. Is it possible to automatically insert Rows and Headings based on the Cell value of a particular column ? For example column B consists of a field called, 'Assigned Group'. Column A consists of a field called, 'Fault description'. Column A needs to have a heading depending on the value of Column B. One row also needs to be inserted above the heading.
View 6 Replies
View Related
Feb 9, 2007
I have three columns - "Start Time", "End Time", and "Elapsed Time". Elapsed time is just end time - start time. What I would like to do is insert rows based on elapsed time.
For instance if start time is 6:00 and end time is 6:10, then elapsed time is 0:10. In this case I would like to insert 10 rows below. Basically I want to insert 1 line per minute (based on elapsed time).
View 9 Replies
View Related
Jan 31, 2008
I have a spreadsheet which contains data needing to be re-formatted into a suitable format for my database. So far I have been doing this by hand, but want to make it quicker as I have over 2000 lines to sort out. I am trying to write a macro that determines whether there is data in the column next to the active one (D2), and if there is counts how many columns there is data in. Once it has done this it needs to insert the same number of rows as there are columns, then copy the column data and transpose it into the empty rows. My macro seems to work at first, but it exits after around the 4th row, but I can't work out why.
Sub split_For_Database()
Dim No_Of_Cells As Integer
Dim Start_Cell As Range
Dim Cell As Range
Dim LastRow As Range
Dim i As Integer
Dim lRows As Long
Set LastRow = Range("C65536").End(xlUp)
i = 0
Set Start_Cell = Range("C2")
Set Cell = Start_Cell
Do While Start_Cell.Address <= LastRow.Address
Do While Cell.Offset(0, 1) > 0
i = i + 1
Set Cell = Cell.Offset(0, 1)
Loop...................................
View 8 Replies
View Related
Dec 7, 2010
I am trying to perform a simple insert macro. That will search column A for a value "Jack".
Once the value has been found insert a blank row, 2 rows up from that value. Not below.
View 9 Replies
View Related
May 5, 2006
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.
View 9 Replies
View Related
Apr 23, 2014
I have an excel sheet that I've automated for my shop ... Basically it copies a set of rows from one sheet, asks for which cell you want to start the paste at, then pastes it... Here's what I have so far:
[Code] .....
So I tried to use a variation of strReturn to indicate number of rows wanted but that didn't work ... Basically I'm going to create the "data" to include 50 rows, but have it selectable for the user to say they only want 5, 7 or 29 rows ...
View 2 Replies
View Related
Feb 20, 2014
I regularly run a report that comes out in a slightly different format each time depending on whether or not there is data for specific criteria. for instance (sample attached): criteria a, b, and c are in rows, 1, 2, and 3 in columns. a, b, and c are expected to repeat themselves several times. if there is no data in one instance of b then you may see something like abcacabc.
I'd like to loop through each row in column a, check to make sure that it is the correct value (either a, b, or c) and if not, insert a blank row so I can then copy the data to my final project without having to worry about formatting.
the example above would then become abca cabc
View 3 Replies
View Related
Feb 20, 2009
Column B contains geographical Areas. Column C contains a list of business departments.
North Env
North Env
North Ops
North Sales
North Sales
North Sales
South Env
South Maint
South Ops
South Sales
South Sales
South Sales
Etc.
The values and number of these departments will vary. I want to insert lines to sum the totals at the bottom of each geographic area based on the number of different departments. So, for this example, for the North three lines would be inserted. For the South, 4 lines would be inserted.
View 4 Replies
View Related
Dec 20, 2006
I have a bit of a curly one - I have a sheet with 9,000 records that I need to expand to 24,000 + on a separate sheet records using the following method:
1) Select and Copy entire row 2 from sheet(1)
2) Insert a number of new rows into sheet(2), based on the value of cells(2,8) or cell H2. Column H contains quantity values for each of the 9000 rows which are variable.
3) Repeat for row 3, copying and inserting into sheet(2) based on the value of the cell in H3.
4) loop through all records until complete
I have tried writing the code in VBA but am not even getting close!
View 9 Replies
View Related
Jul 7, 2009
I have a list of 130 names in column A. I have a number value between 0-10 in column B (next to the name). I need to insert the number of rows defined by the value in column B, below each row that I already have (if the value is 0, then the row needs to be deleted). The inserted rows have to be filled with the name value from the row above.
For example - before macro:
Joe Bloggs 2
Adam Wilson 10
Peter Andrews 0
Claire Burrows 6
After macro:
Joe Bloggs
Joe Bloggs
Adam Wilson
Adam Wilson
Adam Wilson
Adam Wilson....................................
View 6 Replies
View Related
Mar 12, 2007
There are many columns in Sheet1 and more will be added for titles of the columns remains unchanged. One of the Col. in Sheet1 is 'Part Number'. Part Numbers are defined as 'Configurations', and each Configurations has additional parts and peices which are broken down in Sheet2. Back in Sheet1, I also have a Col. for QTY. So, if Sheeet1 has a part number that matches in Sheet2, then I would like Excel to copy all 'parts and pieces' and QTY from Sheet2 to Sheet1. Please make sure that I want the QTY to be multiplied if applies. Please see below for an example:
Sheet1 looks like this:...................
View 2 Replies
View Related
