Insert Rows Based On Text Values In Cells?
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
ADVERTISEMENT
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
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
Jun 12, 2014
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.
View 3 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
Dec 19, 2013
a macro to insert rows based on certain cell values in column A.
I have uto 300 rows of data. Below is an example of column A.
R1
1
2
3
4
5
6
7
[Code]...
If (above the R) is an 8, I need to insert 2 rows above that R and directly below the 8.
If (above the R) is a 9, I need to insert 1 row above that R below, directly below the 9.
(Below the R there is always a minimum of 8 digits with the 9 and 10 being random).
I have excel 2007
View 5 Replies
View Related
Mar 12, 2008
I have an excel sheet that looks like this:
IMAGE 1
(The sheet holds more data but not needed for this)
I have two buttons; “BUY” and “SELL”
I need a vba-code that inserts either “BUY” or “SELL” in row 6 (buy/sell) and insert a text (code) in row 7 depending in this information. When I push either “Buy” or “Sell”
IMAGE 2
So.. When I push the button “SELL” (already made) the action “Switch” should get “Sell” and actioncode “SO”, and “Redem1” and “Redem2” should get “Sell” and actioncode “RA” and “RN”
So.. When I push the button “BUY” (already made) the action “Switch” should get “Buy” and actioncode “SI”, and “Subsc” should get “Buy” and actioncode “SA”
(When the button is pushed I have a autofilter that “hides” either all the “sell” or all the “buy”)
IMPORTANT: There is NO range!! the list goes on, and changes so I need it to work on x amount of rows. !
View 3 Replies
View Related
Jul 25, 2006
I am designing a from with a variable number of titles. The user should be able to insert a title into a column by typing in the title that he wants into a text box, and pressing a command button that I have created ( named add). The problem arises when I try to get the text to lie vertically as opposed to horizontally (in the cell, not as a text box. If this can only be done as a textbox, let me know). Is there any way to do this?
View 5 Replies
View Related
Oct 15, 2009
I have one cell (B10) that will contain either "high", "medium", or "low". Based on one of these 3 values, I'd like to shade cell J15 a color with some white text. I got 1 of the 3 keywords working, not sure how to add in the other 2.
View 2 Replies
View Related
Jul 9, 2014
one of my suppliers can only provide me with a basic .xls product list as shown in the attached test sheet.xlsx.
Is it possible to create and run a macro from test sheet.xlsm that will extract data from test sheet.xlsx, specifically from below the heading on row 64 down to and above the heading on row 123 and paste into the (new) worksheet in test sheet.xlsm
This sheet is updated daily and all the heading rows are constantly fluctuating as products are sold and added.
I can do this manually easy enough but if there's any way that this can be automated
View 4 Replies
View Related
Jan 25, 2013
I am wondering if I could have a formula be used against a certain row/cell containing a certain "phrase" or "number" for instance
I want E1 to read something like this = (row containing item "FF32105") (the given row from the previous statement) ( the input column ex. B) (the input value ex. *6)
Essential I want to be able to copy and past a sales forecast and have the formulas automatically (listed below the pasted forecast) calculate purchase needs. The issue is that if our forecast one month has a certain item and then the next does not then they will end up on different lines after the copy/paste. We have over 500 items so a manual adjustment would be time consuming..
Is there a possible way for the formulas to "find" the correct line to start calculating data?
View 6 Replies
View Related
Jun 17, 2014
I'm new to VBA and macros, using Excel 2010, and am trying to figure out how to delete all duplicate rows in a sheet where 2 or less of their values in column A is "1". I'd like have a script that is flexible enough to change to 3 or less if need be. I also have a header row that needs to be offset in the process.
A---B-
0--123 <-delete
0--123 <-delete
0--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321
or
A---B-
0--123 <-delete
0--123 <-delete
1--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321
View 5 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
Apr 21, 2014
I have different reports, some have fifty transactions, others have thousands. My goal is to: Insert a new row every time the values in the "Account" column meet a certain criteria, AND THEN add the totals for the Debit and Credit Columns.
Let's say I start with a table that looks like this:
A
B
C
D
Dept
E
Account
T
F
F2
G
Debit
Credit
Total
33010
[Code] ....
I want to group the first four rows because Accounts 33010 and 33015 are in the same department. Same with 50050 and 500060. I want to then insert a row below the last row with "33015" as its Account #. And add the values for Debit and Credit. It'd look like this:
A
B
C
D
Dept
E
Account
[Code] .........
Honestly, I have tried everything. Running a Macros with Relative reference does not cut it.
View 8 Replies
View Related
Jun 12, 2009
I need a code that will insert 3 rows between different values found in column A. I tried using this but it inserts rows between every value found.
View 2 Replies
View Related
Dec 1, 2011
I have a spreadsheet with about 10,000 lines/rows of text and I want to insert 4 lines/rows under each of these. way to do this efficiently without having to insert under every line/row?
View 9 Replies
View Related
Feb 18, 2014
I have a table (Arrears) showing a list of customers with part numbers and quantities in arrears. A second table is a customer order table showing details of what needs to be shipped for a given date.
Based on these two tables, I would like to add a column to the Order table to show the values for arrears against the order quantity for the given date. In addition if there are no order requirements but there are arrears then insert a row to show the corresponding arrears.
see the attached spreadsheet for more details. This shows the two source tables (Arrears and Order) and the expected results showing the added column for arrears and the inserted rows.
View 6 Replies
View Related
Jan 12, 2009
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.
View 4 Replies
View Related
Feb 5, 2009
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:
View 14 Replies
View Related
Jul 16, 2009
I want my macro to consider the # of rows of data on a spreadsheet and insert values into columns for those rows but then stop when it hits a blank one. For simplified example below, I have 3 columns in my spreadsheet. Row 1 has headers of Location, Status, and Effective Date. The first 5 rows of data under the headers have a value in the Location column (San Jose, Oakland, etc). When I run the macro, I want it to insert a value of "ACTIVE" in the Status column for each row that has a value in the Location column. Also, a single Effective Date value is stored in a cell elsewere in the spreadsheet, and I want the macro to insert that value in the Effective Date column for each row that has a value in the Location column. When it hits a blank row - row 7 in this spreadsheet - I want it to stop.
Location| Status | Effective Date
San Jose
Oakland
Austin
Houston
Phoenix
I currently am using an IF statement in the Status column fields to say if Location = blank, then blank, else "ACTIVE". That works, except that the # of rows populated in Location column could be 5 or 1,000, and inserting the formula that many times seems to quickly increase the file size.
View 3 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
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
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
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
May 15, 2013
I have spreadsheet with data all over. I want a macro which identifies the first cell (in Column A) which has the text "BNY" and insert 6 blank rows above the text "BNY" (First text in the Column).
Once, it is done - the macro should also assign names to the last inserted row.
Column A - Should reflect "Bank", Column B should reflect "Field1", Column C should reflect "Field 2" and Column D should reflect "Field 3".
And after the names are assigned - It should also highlight the last inserted row in Yellow.
For Example:
Raw Data (Snap):
Bank
Field 1
Field 2
Field 3
JPM
123
456
789
[Code] .....
Output (After Macro):
Bank
Field 1
Field 2
Field 3
JPM
123
456
789
[Code] .......
View 5 Replies
View Related
Apr 4, 2014
I need a macro I can run that will insert a row whenever Column A changes (from PPN A to PPN B to PPN C, etc.) and that will insert string values into the Cells as follows:
In all cases, the insert cell values into column A (PPN) will be the value from the previous cell and column B (CPN) will be 'LABOR'
Here's image of before and desired after:
Before:
PPNCPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02ADMIX
4AQ02AUTUMN BLEND
4AQ02OVERHEAD
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03ADMIX
4AQ03BETHEL BLEND
4AQ03OVERHEAD
After:
PPN CPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02ADMIX
4AQ02AUTUMN BLEND
4AQ02OVERHEAD
4AQ02LABOR -insert row and populate cells here
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03ADMIX
4AQ03BETHEL BLEND
4AQ03OVERHEAD
4AQ03LABOR -insert row and populate cells here
View 1 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
