Copy Cells In One Column Based On Criteria In Another Column

Aug 17, 2006

I have an personnel file with employee info, and I want to create a macro that will look in the "Master" worksheet at the Department column ("I") for anyone in Benefits, and then copy their name from the Name column ("D") into the "Benefits" worksheet. The names should begin pasting in cell "D3" but will recognize if a cell already has a name in it and then paste in the cell below that.

Here is what I have:

Option Explicit

Public Sub RatingbyDept()
Dim Dept As Range

With Sheets("Master")
For Each Dept In .Range("I2:I1000")
With Dept

I keep getting error 92 - "For loop not initialized".

View 8 Replies


ADVERTISEMENT

Count Of Cells In Column Based On Criteria On That Line In Different Column?

Mar 10, 2014

I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.

View 1 Replies View Related

Copy Based On Column Criteria & Append To Another Sheet

Jan 18, 2008

I have a spreadsheet that is used to store statistical information on a monthly basis, some of this information may be incomplete and so will need to be acted upon month by month, What I need to do using VBA is: At the close of the spreadsheet check sheet1 and if there is any data (text) in cells N – Q on any line If there is move to sheet3 check for the next blank line then paste the data from only certain fields i.e. A,B, N-Q,R,S

Here is another problem

As the sheet will be used on and off during the month I don’t want data that is already been moved across to sheet3 to be copied again so there needs to be someway of checking if the info is already there? I have code now for the find last cell part of the problem.

Sub FindLastCell()
Dim LastCell As Range
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
If IsEmpty(LastCell) Then
'do nothing
Else
Set LastCell = LastCell.Offset(1, 0)
End If
End With
LastCell.Select
End Sub

View 9 Replies View Related

Copy Rows Based On Criteria In Single Column

Jan 30, 2008

I have on sheet1 of my workbook a number of rows of data, from column A through Column G. The Data in column G either contains the text "Yes", or "No".

I need a macro that will look through each row in this sheet, and upon finding a "Yes" in the cell in the column G, will copy that entire row (columns A through G) into a new row in sheet2. Sheet2 currently contains data, so I'd like to move everything down by one row and paste this data at the top (row 6 is the first line of data on this sheet).

I'd then like to go back to sheet 1, and delete the row I've just copied. Then continue though the loop. The loop needs to stop when a blank row is discovered.

View 5 Replies View Related

Automatically Copy Rows Based On Criteria In Column

Jul 31, 2008

I'm not sure that I'll be able to explain this clearly, so I've attached an example. There are 3 columns - Issue, Action Group and Status. Many Issues belong to the same Action Group and others do not belong to any Action Group. The is also a Status summary section that has the total Issues and Actions associated with each status. For example the Status called Resolved could have 10 issues and 5 Actions (including blanks). The problem is that I can't figure out a way to get the total for the Actions. As I said this explanantion isn't great, so the attached sheet

View 3 Replies View Related

Sum Cells Based On Row And Column Criteria

Apr 30, 2014

I am really struggling to summarise weekly budget data into period budget data by subcategory.

I have column headings A3 being Subcategory B3 being GL Code & C3 being GL Name - where column B & C are distinct values but column A are not. And then in D2 I have the period no e.g. P01, with the Week no in D3. I want to sum all cells that are in a particular subcategory and period - the data set is broken down by weeks.

View 4 Replies View Related

Copy Paste Rows To Another Sheet Based On Column Criteria

Jan 17, 2007

I have a file that has two sheets, I have some formulas in the first sheet named "Data",What i would like to have is, If column G in the sheet Data is "Closed", then i want that particular row to be cut from the sheet " Data" and pated in to the Sheet "Done".

View 5 Replies View Related

Delete Certain Cells Within Row Or Column Based On Criteria?

Nov 7, 2013

What i really need it to do is if each row in column H = "Cleared" then to only clear columns A, C to H. Theres a formula in B that needs to be left... any ideas?

Also i need this to work on all atbs as the commandbutton will on a master tab

VB:
Private Sub CommandButton1_Click()
With ActiveSheet
.AutoFilterMode = False

[Code]....

View 4 Replies View Related

Count Cells In Row Based On Criteria In Column & Row As Well

Mar 10, 2014

count cells row wise that meets criteria both in column & Row as well.

View 2 Replies View Related

Copy Data Table Seperate Sheets Based On Column Criteria

May 12, 2008

I need to copy all the work sheets into one single work sheet (mastersheet). The source work sheets are having same column structure. The condition which i need to take care of is that after column 3 if at all there is any data till column 10 then in the destination mastersheet these should be copied in different rows with first two columns repeated. I need to do this using VBA macro.

View 9 Replies View Related

Copy Rows Where Cells In Column Match Condition/Criteria

May 5, 2008

I did a search to find a question similar to mine and I found this:
Copying data matching a criteria into another spreadsheet

This is exactly the same concept I am trying to accomplish, but don't know how to...at all. I couldn't follow the thread either.

I have a schedule which is constantly being updated (simplier version attached). I want to create a schedule, which will:
1) First ask which region to display the information for (I would like to create the same pop up menu to choose from)
2) Then, it will select only that region's information (all rows & columns) and copy them into a new worksheet and lists it all
-if you go back to the Master List, you can again choose another Region and it will create another new worksheet with that region's information
3) Don't know if this is making things more complicated, but at the bottom of the attached file, there are 3 lines under "Land".

Is it possible to list these under the same spreadsheet as the region it is pertaining to but classify it separately under "Land" (ie leaving space, so it is clear this is separate

View 9 Replies View Related

Multi-Criteria Sum Based On Multiple Column Cells

Dec 30, 2008

I have a large worksheet that lists employees and the hours they work each day in a pay period.

For Example:

Column A holds a date and Column E holds the number of hours worked for a paytype. So i want to create some function that sums the numbers in column E, but only those where column A holds a date.

Then i want to return that number to a cell, say C20. I think I've managed to tell the macro which rows i want to return values from, but I can't figure out how to say "add these numbers together and put the total in C20."

I'll check this frequently if clarification is needed.

View 9 Replies View Related

Copy Column Data From Multiple Worksheets To Single Worksheet Based On Header Criteria

Oct 18, 2013

I have a workbook with many sheets of similar but not identical data. I need to extract columns from each sheet based on 5 header criteria and paste them to a single sheet. Each worksheet contains these 5 criteria.

I've been working with the VBA script I've pasted below. It's grabbing the 5 column criteria that I have in sheet 12, and comparing them to sheet 1 in the workbook, then copying them to sheet 12. This much is good, but I need the script to also return the data from the other worksheets as well. I've tried modifying the script based on other loop functions in other scripts I've found, but I'm not having any luck.

View 14 Replies View Related

Sum Cells In A Column Based On Multiple Criteria For Neighboring Columns

Jul 1, 2013

Example data:

01/05/2013
150
Yes

01/06/2013
1
Yes

[code].....

Basically I'm trying to write a formula that will sum the total of the numbers from column B but only where the month equals June AND column 3 says "Yes".

So the answer would be 101, because rows 2 and 5 are the only rows of that table that meet all the criteria, and the sum of their column B cells would be 101.

I thought I was getting there with Sumproduct and Sumif but I'm now drawing a blank. There is a little extra complexity in that I'm actually pulling the month value from a set of cells elsewhere, but if someone can solve the puzzle above I hope I can do the rest on my own as it's just locations of data to pull from other worksheets really.

View 9 Replies View Related

Return Column Header Based On Column Criteria And Number Value

Feb 7, 2014

I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).

Here's data table:

CriteriaHeader 1Header 2Header 3Header 4
ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00

CriteriaNumberValue
DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))

View 3 Replies View Related

Adding Values From One Column Based On Criteria In Another Column?

Jan 30, 2013

If i have the following layout of data

Column 1 Column 2
January 1000
January 1234
February 1300
March 1600
January 15
March 123
April 234
January 3000

I would like a formula that adds all the January numbers together returning a result of 5249

To move this one step further i would eventually need to add these numbers based on quarters, for example if the value is January February or March in column 1 then add the numbers in column 2.

I'm sure i have done this before using a countifs maybe but my mind has drawn a blank

View 1 Replies View Related

Average From One Column Based Upon Criteria From An Adjacent Column

Jan 13, 2008

I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...

View 12 Replies View Related

Copy Multiple Cells Based On Condition In Column

Feb 13, 2008

I need the macro that looks in to say Column H sheet1, if value ="true" then copy cells in column A, B, D, F to Sheet2.

View 2 Replies View Related

Calculating Sum In One Column Based On Criteria In Another Column?

Mar 30, 2014

I have 2 columns; 1 with "dates" of incidents, the other with "number of incidents". The dates are not in any order but I want to be able to count "number of incidents" for certain time periods. e.g. If dates are between 01/02/14 and 28/02/14, what are the number in incidents. This number would be represented on a new worksheet.

I've looked at COUNTIF/S, SUMIF but just cant get there.

View 8 Replies View Related

VBA To Sum Values In One Column Based On Criteria Of Another Column

May 10, 2014

The below sample is an accurate example of a much larger set of data. Column H will be my desired result.

I need a macro that will look at the matching values (numerical value of month) of Column F, then enter the sum of the corresponding values (rows) contained in Column D for that month. The sum of each month should be placed in Column H at the last value of the row for each particular month.

A
B
C
D
E
F
G
H

1
Item#
Description
Quantity
Month
Year

2
1001

[Code] ........

View 9 Replies View Related

Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.

Mar 13, 2009

I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in.

I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.

View 2 Replies View Related

Copy And Paste Range Of Cells Based On Number Of Cell Values In Column A

Mar 17, 2014

Please see attached sample worksheet. Column A will be generated by the user manually.

I'm looking for a way in VBA to have A1:D20 in Sheet2 copied and pasted in the "Bank Reconciliation" Sheet based on how many "Markets" there are in Column A. Then, once that's complete to have A22:D30 (the smaller box in Sheet2) copied and pasted directly below those results.

I have what the macro would hopefully generate to the right in "Bank Reconciliation" (B6:E54) as an example. So if there's a market in A1, copy and paste the box to B6. If there's a market in A2, copy and paste the box directly below the first (B26) etc. etc. until it's done, then paste the smaller box directly below whatever the macro generates.

Book2.xlsx‎

View 3 Replies View Related

Setup A Macro To Copy Text From A Column Of Cells To Another Column

May 22, 2008

I have been working with a few people on here to setup a macro to copy text from a column of cells to another column and then print this in to a text document but it seams to have got stuck in a loop ....

View 9 Replies View Related

Copy Cells From Column To Adjacent Column If Bold Or Empty

May 20, 2008

I have a worksheet on which the data is already grouped. At the top of each group is a row that contains only the group name. Since the rest of that row is blank, I want to use a blank cell on that row as a reference, then copy the group name to a newly created column, then fill that column down to the next group.

The goal is to create a column that contains the group name, rather than just having the group name as a " header" at the top of each group.

View 5 Replies View Related

Calculate The Average Of A Group Cells In One Column Based On The Condition Of Another Column

Oct 2, 2008

I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days

17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7

On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.

View 2 Replies View Related

Copy Range Column Based On Other Column

Aug 7, 2009

in column B of sheet1 i have every 3rd-4th row a name. i need to copy the name and the the data on the row below the name to sheet2,
the list of names can be min 2 max 30.

so sheet 1 has row 14- name in column B and C. in row 15 in columns d:z data for the name.

i need to copy this to one row in sheet 2 copied for each name up to 30.

View 9 Replies View Related

VBA To Copy Non-blank Cells From Column And Paste Into Another Column

Oct 4, 2013

In sheet "diary" I have data in certain rows in column A8:C10000 that contains values if a certain condition is met. I need a vba to copy and past only non blank cells in column D8:F10000. I first wrote a formula with index but it takes too long to caculate.

View 9 Replies View Related

Count Filled Cells In One Column Based On Date In Another Column

Apr 24, 2009

I think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example:

AB
1-May
1-May777
1-May
1-May
1-May
2-May999
3-May
2-May
1-May
2-May111
1-May

If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.

View 2 Replies View Related

Combining Cells In Column Based On Matched Value In Another Column?

Apr 5, 2013

I have a problem which requires a vba routine that is currently outside of my level of expertise...

Here is an example of the data:

TESCO GARAGE 3729
59
REF 177 1092098207 BCC
59
CO-OP GROUP LTD
60

[Code]......

View 3 Replies View Related

Summing Cells In Column Based On Data In Another Column?

Feb 27, 2012

I have a statement from an account (which happens to be the government) in which they list every invoice they are paying and each item on that invoice. But they don't have an invoice total. I'd like a way to add up the item totals for each invoice and put the total in column D. Each invoice could have 1 to 10 different items on it.

A(invoice#) B(Item) C(total) D(invoice total)
111 widget 1 $5
111 widget 2 $10
111 widget 3 $8 XXXXX
222 widget 1 $5
222 widget 5 $15 XXXXX
333 widget 2 $10 XXXXX
444 widget 5 $15 XXXXX

I had thought an IF formula would be the way to go.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved