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.
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
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:
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've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D. e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
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.
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
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.
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.
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....................................
I am looking to build a VBA to do the following action:
Look up an ID number from column F (ID numbers are separated by an empty row at current), then check whether there is a corresponding value in columns N to P,
if there are values, copy them onto a different sheet into three different columns
if there values below this particular row, i.e. if the rows below say row 9 (where we looked up the ID number) in columns N - P are not empty, also copy all these values and add additional rows as needed on the sheet this is copied to
then look up the next ID in column F and do the same until there are no more IDs
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.
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 have a dataset that i want to update, to do this I want to insert 8 rows after each row that has a value in Column C 2000. The set has 600,000 rows, comparing countries (cty, cty2) from 1980-2000. I want to make room for information up till 2008. I see lots of codes for doing this but I will also need help with actually inserting into my spread sheet.
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!
I built a Microsoft Access database for my church to track member information. I was able to import a lot of the needed information to create the records for each individual and household easily. What I can't do is import their offering donations until it is "Normalized." Which means that I need each row to be 1 record with the fields listed as the column headings. My church has a spreadsheet with several sheets one of which is named Input. I've attached the desensitized file for you to view/play with.
What I need to do is loop through the input sheet and find all the values in range B:4 to GN:387 that are greater than zero or not null (I believe they entered the default as zero so their other calculations would work). Then I need those values to be transferred to the column labeled Offering Amount on the ImporttoAccess sheet. Somehow I also need to define three variables associated with the cell that is contains a value greater than zero.
1. I need the row heading value for the envelope number (Column A of input sheet) which intersects with the amount
2. I need the column heading value (3 field merged) for the date (Row 1 of input sheet) which intersects with the amount
3. I need the column heading value (labeled: GenFund, WLA, or Missions) for the designation of the offering (Row 2 of input sheet) which intersects with the amount
Each of these I need to attach to the value offering amount and insert into their corresponding column fields for that record. I have entered 2 example rows of data into the ImporttoAccess sheet for you to see what I am after. I am sure it's possible since the data is there, But how to go about it.
The below code used to work for me perfectly but in the last couple days it's been acting up. The code is supposed to cut out 2 rows from sheet1 and insert it in order of column B in sheet2. Up until a few days ago the code was inserting the 2, cut, rows properly but now it inserts it at the top of the sheet.
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?
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.
I have to create a spreasheet monthly that has perhaps 60 transactions. The data is in columns A - G, with columns E and F having the costs figures. I need to sum the value in column E and also in Column F every few rows. The problem is that sometimes there are two rows per transaction and other times it could be 30 rows per transaction. Where I will be lucky is that there are two rows between transactions, and the sum goes in the first empty row.
Basically what I would need it to do, from a macro point of view is to go down column E, and every time it finds the first empty row, insert an auto sum. It would then need to ignore the next row, because it too, is empty.
Just Column E - I need an autosum in the "empty row 1's)
I'd like to have done is to have a blank column inserted between columns W and X(these values change so the VBA statement should reference the end of the columns) and the values that are now in column Y(April 17th values) pasted as values into the now empty column X. I would like to do this for tabs Ann-Sheet 2. I'm having a bit of trouble with setting up the loop that would go through the desired sheets.
I have another little VBA macro problem that appears to be beyond my coding knowledge.
I have attached a small excel spreadsheet, with a macro recorded (CTRL-P) of what I'm trying to accomplish. Basically, If the data in column A matches a single entry in column F, I need to copy and insert the row (columns F-L) that matches.
I am trying to insert formulas to my cells in different columns and then fill down the formulas as many rows as in the reference column. So far I have put my formulas in a macro but I seem to get problems with " and ' symbols. My macro looks like this:
