Insert Row (Based On Values In Range) With Sum Of Values For Other Columns

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.

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.

Count Unique Values In Column Based On Values In Other Columns

Mar 14, 2014

I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.

My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!\$I\$3:\$I\$66<\$E\$1,IF(Data!\$A\$3:\$A\$66=\$H\$1,
IF(Data!\$C\$3:\$C\$66=A4,ROW(Data!\$I\$3:\$I\$66)))),ROW(Data!\$I\$3:\$I\$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!\$I\$3:\$I\$66<\$E\$1,IF(Data!\$A\$3:\$A\$66=\$I\$1,
IF(Data!\$C\$3:\$C\$66=A4,ROW(Data!\$I\$3:\$I\$66)))),ROW(Data!\$I\$3:\$I\$66)),1))}

This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).

The results in the pink highlighted cells (Summary column D) should be:

Names starting with A - 3
All others - 2

I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.

VBA - Copy Range With Multiple Same Values Based On Other Cell Values

Aug 31, 2012

I want to build a database where the user can select a specific time period. I have a static file with the time periods and the related values. For instance I have in column A1:A15 the time periods from January 2011 to Dezember 2011, but I have some dates multiple times as there are more values attached to it. For instance I have in the first 4 rows January 2011...what I want is when someone puts in the value January 2011 in cell B1 and Dezember 2011 in cell B2 that the whole static file gets copied to another location (including the multiple dates) displaying the chosen time period. Similarly if someone puts in the value March 2011 to November 2011, I want only those values to be copied.

Sum Up Values Based On Values From Two Columns

Jan 1, 2014

I have a budget spreadsheet as follows:

Category, Sub-Category, Amount
Food, Lunch, 10
Food, Dinner, 20
Food, Lunch, 15
Food, Dinner, 30

I need to track how much was actually spent on various categories and sub-categories, as follows:

Category, Sub-Category, Budget Amount, Actual Amount
Food, Lunch, 50, 25
Food, Dinner, 90, 50

Essentially, the actual amount contains a set of formula, that sums up the spending sheet (the one on top) based the category and sub-category on the budget sheet (the one on the bottom). Tried to use SUMIF but it seems to work on a single column.

Assign Values To The Grades And Insert Extra Columns To Do The Calculations

Feb 28, 2010

I have a list of grades they are 3a, 3b, 3c, 4a, 4b, 4c, etc up to 7a. An a is one grade than a b, and a b is one grade higher than a c. In one test a student may score a 4b and in another a 4a. I need a way to calculate the difference in "grades" e.g if a student scores a 4a in test 1 and a 5c in test 2 the difference is 1.

Is there a way I can do this using custom formatting or will I have to assign values to the grades and insert extra columns to do the calculations. I need the simpliest way as I have to disseminate to my staff in my department.

Insert Row And Values Based Upon Condition

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
4AQ02AUTUMN BLEND
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03BETHEL BLEND

After:
PPN CPN
4AQ02SLAG
4AQ02SAND
4AQ02CHIPS
4AQ02PORTLAND
4AQ02AUTUMN BLEND
4AQ02LABOR -insert row and populate cells here
4AQ03SLAG
4AQ03SAND
4AQ03CHIPS
4AQ03PORTLAND
4AQ03BETHEL BLEND
4AQ03LABOR -insert row and populate cells here

Insert Values Based On Other Cell Value Reference

Mar 21, 2014

The first three columns of a spread sheet we use if function to insert values to these cells based on the next three column values. We have already done it using IF function. However, the same function should happen through macro by referring to the column headers.

Example 1:
If in column E header (E1) ‘Contract_Status’ and E2 cell value is ‘FAIL TO PAY’ then A2 value should be ‘No Owner’, B2 value should be ‘Terminated’ and C2 value should be ‘FTP’

Example 2:
If in column E header (E1) ‘Contract_Status’ and E3 cell value is ‘TERMINATED’ then A3 value should be ‘No Owner’, B3 value should be ‘Terminated’ and C3 value should be ‘TERMINATED’

Example 3:
If in column E header (E1) ‘Contract_Status’ and E4 cell value is ‘EXPIRED’ AND column F header (F1) ‘Contract_Renewal_Status’ and F4 cell value is ‘Cancelled by Customer’ then A4 value should be ‘No Owner’, B4 value should be ‘Renewal Cancellation’ and C4 value should be ‘cancelled’

VBA Loop Range And Insert Values In New Column?

Mar 21, 2014

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.

Assign Range & Interate Through & Insert Values

Jun 17, 2008

What I need to is create a VBA function with several parameters that reads data from an M x N range of rows and columns (matrix). I cannot just pick and choose certain cells within this range as the function goes into a loop through at least 200 cells in the 35 x 200 range. Obviously, I am here posting as I cannot get this function to work. I believe my troubles lie with reading this range into the function...

Application.Goto Reference:="range"
rangearray = Selection.Value

values(I, J) = rangearray(I + 1, J)

Do I need to set a function variable equal to that range, or do I do this another way? Basically, I need to have the ability to pick a given cell(m, n) out of the range and have my way with it in the function.

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

Insert Number Of Rows Based On Cell Values?

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.

Macro To Insert Row Based On Differences In Cell Values

Jan 8, 2014

a macro/code to accomplish the following:

I would like to insert a row if:

Difference in values in Column B are more than 2 .... OR ...Difference in values in Column C are more than 2

Specimen
Value 1
Value 2

[Code]....

In the above table, rows would be inserted after specimen A, B, D, and E.

Insert Rows & Repeat Values Based On Corresponding Cell Value

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
Peter Andrews 0
Claire Burrows 6

After macro:

Joe Bloggs
Joe Bloggs

Copy & Insert Range If Column Values Match

Mar 24, 2008

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.

Insert Pictures Based On Cell Values From One Specific Folder

Mar 14, 2014

where i have a products list and respective pictures in a folder. What is should do is to get the relevant pictures appear on the screen when i select the relevant name.

Insert Extra Rows On Data Based On Values From Another Cell?

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.

Excel 2007 :: Macro To Insert Rows Based On Cell Values

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

Finding Value Based On Two Values In Columns And One Value In A Row

Mar 19, 2014

A
B
C
D
E

[Code]...

In the example above I am trying to look up a value from columns C-E. I need to be able to search/index using 2 criteria to figure out which row to match with the given column. for example: If I want to know the invoice qty. for R&D for Jan-2012, so the returned value would be 13. I have tried several different combinations of match and index to get this to work but have had no success. Ultimately what I want to do is have a drop down for the month and year that our VP can select and it will give him the given values for that month.

Delete Columns With No Values In Range?

Oct 10, 2011

In .Range("A8:CJ8") I want code to delete all columns within that Range that do not have any values in the cells.

Excel VBA To Sum Column Based On Values In Two Other Columns

Jul 9, 2014

I have successfully populated a website forms using Excel VBA. As a continuation of this task, i need to calculate the total of a column based on two other columns in the same worksheet.

Column A contains Date, column B contains 3 fixed alphabets (I, L and K) and column C contains time. I have attached a sample sheet here. sample.xls

How can i write a VBA code to find the sum of effort values of column C for each values in column B for a given date (value in column A).

Capture Values Based On Number Of Columns?

Jun 7, 2012

My data comes from 3 different sources

Source A = 4 columns (3rd one is important)
Source B = 8 columns (3rd one is important)
Source C = 5 columns (4th one is important)

Is there a slick way to capture values based on the number of columns in that row?

Highlight Duplicate Values Based On 3 Columns?

Nov 8, 2012

I just want to colour duplicate values but want to do it with this Dictionary method

Code:
Sub highlight_Dups()
Dim cell As Range
Dim rng As Range
Dim dict As Dictionary

[Code]....

VBA - Hiding Columns Based On Cell Values

Mar 20, 2014

the support this board has given me as I learn VBA. I have three columns - Q, R, and S. I only need to see columns R and S if the cell values don't equal those in column Q or each other. So if I have cell values like the ones listed in the example below, then I don't need to see columns R and S.

50
50
50

[Code]....

Sum Of 3rd Column Values Based On Nested If On 2 Other Columns

Apr 28, 2007

I have three columns as follows:

ItemsWeekEnding Count
a14/7/2007 10
a34/7/2007 3
a14/14/2007 11
a14/7/2007 5
a44/7/2007 6
a14/21/20078
a44/14/20072
a34/21/20071
a14/28/20077
a24/21/20074
a94/28/20079

I want to Sum Count values (column 3) for matching WeekEnding (Column 2) and Items (Column 1), example:

a1 4/7/2007 15
as a1 for this weekend is occuring twice.

I need the formula to do it in excel.

Delete Columns Based On Cell Values

Feb 6, 2008

I have a standard data import that populates data in columns A through DC. I am trying to delete those columns from BX through DA where the value in row 2 equals 0.

Insert Values If Values Duplicated Elsewhere

May 2, 2007

I'm working on a spread sheet tracking conference presenter so I'd like to come up with a formula and find any duplicate names. I'd be grateful to learn how to do this without hand sorting.

If value in cell A1=y
and a lastname that appears in G1, H1 or J1 appears more than once in the range of cells in three columns - G1:g10, I1:I10, J1:J10
insert in cell B1
the list of lastnames (up to 3) seperated by a colon in G1, H1 and J1 that are duplicated elsewhere.

Compare Values In Range Of Columns In Excel VBA

Jul 10, 2013

So I have values in Column L3 to AH3 and I would like to use if condition to see which values are less than 10.

I also have other rows where this comparison needs to be done but for now I can't even get simple if condition to work.

this is what I am doing

For Each cell In Range("L3:AH3")
If cell.Value < 10 Then
ActiveCell.Font.Color = vbRed
End If
Next cell

I am not getting any error but for some reason only first cell condition is compared and not the other cells..

Return Unique Values From Columns/Range

Oct 24, 2006

I need to know how to read all the criteria1 brought by the autofilter (in the dropdown box) from one column one by one and then print the table. Is that possible? I've read that it's hard but never read how to do that.

Filter A Table With An OR Logic Based On Columns Values

Feb 10, 2010

i have a table and have some columns.

i want to filter the table so that i just see the records which have "text1" in ther column "F" OR they have "text2" in ther column "H", for example.

How can i implement an OR filter ?

Extract Values From One Column Based On Two Criteria From Two Other Columns?

Feb 14, 2013

In the attached, sheet 2 has a formula that pulls the Item (column B) from Sheet 1 based on Instocks (column F) being less than the value in N2. Would it be possible to add a second criteria to the formula in sheet 2? In short, can I pull the item from column B of sheet 1 into sheet 2, where in sheet 1, column F values are less than N2 AND where column G values are greater than O2?

I'd love to just vlookup the fill rate value and then filter it down, I know at some point I will be asked to weed it down a bit via a formula instead.