VBA Copying Based On Columns Condition

Jan 24, 2014

I have three columns - as listed below. I need a script - That compares "result 1" and result 2. If there are data in the "result 1" column then input that into the "City" column. Else input "result 2" data (if any exist), else leave blank.

"Result 1" always has priority over over "Result 2".

result 1
result 2

San Fran
San Fran
New York

View 4 Replies


Excel 2003 :: Copying Row To Different Sheet Based On Condition

Feb 16, 2013

I wanted to know if it was possible for me to be able to copy a whole row into a different sheet based on a column value. For example, if B6 = CLOSE, the whole row would be coppied to the sheet name CLOSE. If B6 = OCCUPIED it would be copied to sheet name OCCUPIED. I also need if W7 = to a date thats passed today it would be added to the sheet name EXP PRD. An off topic question, i have this code for column "W":


It works just fine, but I also have "N/A" / "INDEF" in the blocks as well so it's not giving me the correct results. I'm using Excel 2003.

View 8 Replies View Related

Copying Rows From Multiple Books To One Book Based On Condition?

Jul 27, 2012

I have eight sheets that have lists of questions in, which I want to copy across to a results sheet if the answer to a question is 'Yes'. Each question takes up rows B:H inclusive, and I would want to copy them to rows B:H in the results sheet. The "Yes" value will be found in column F of each row.

How can I set up a macro to copy the entire rows (without formatting) into a results sheet properly? I've tried every solution I can find but always hit a roadblock somewhere.

Ideally I would like to have a 'populate' button on the results sheet that would find every question that was answered 'yes' across the eight survey sheets and import them into results sheet.

View 2 Replies View Related

Populate One Column Based On Condition Of Two Other Columns

Jan 17, 2014

Attached is the file & snap shot for the problem.

[table="width: 500, class: grid"]
[td]Catagories state names devices
A4-100HP A ALT

[Code] .....

Catagoriesstate names devices

[Code] ....

In above condition we want to have an automatic filling up of data in table B in column (name & device) but it should match first with state name with category codes of table B & then same with table A in order to avoid any wrong entry because in table A certain states are repeated but codes are unique so we want that filter should match two column of each table before filling data in table B.

View 5 Replies View Related

Delete Columns From All Sheets Based On Condition In X Workbooks

Mar 28, 2008

I am sure this is a very simple questions. I am using the below code to work on the worksheets on a workbook called "MF BANK EXPOSURE SUMMARY.xls" so far the code that I am using is:

Sub Commandbutton2()
Dim iCol As Long
Dim Isheet As Long
Dim Item As Worksheet
For Each Item In MFBANK.Worksheets
With Item. Range("A1:T65536")
For iCol = .Column + .Columns.Count - 1 To 1 Step -1
If IsEmpty(.Cells(65536, iCol)) And IsEmpty(.Cells(1, iCol)) Then
If .Cells(65536, iCol).End(xlUp).Row = 1 Then .Columns(iCol).Delete
End If

Next iCol
End With
Next Item

End Sub

I would like to extend the code so that it works on the worksheets of two workbooks at the same time the one being the "MF BANK EXPOSURE SUMMARY.xls" and the other being "MF CP EXPOSURE SUMMARY.xls" .

View 3 Replies View Related

Count Occurrences Based Condition In 2 Dynamic Columns

May 9, 2008

I have been trying the whole day to create a formula to solve this but I just can't get it. To make it simple, let's say I have column A filled with different people names, column B filled with Level of Complexity and column C with the Activity (work task) title. This table is fed dynamically, so the number of rows can vary from 2 to 2000.

What I want to do is count how many times a single name appears related to the same complexity, so I can tell how many "simple, medium or complex" tasks each person has in their work queue.

A simple table example would be:
colA colB colC
Dan Complex Build House
Dan Complex Build Shopping center
Dan Simple Take dog to walk
Jose Simple Clean desk
Maria Medium Paint office roof

Then, on another cell I'd like to have a formula automatically telling me that Dan has 2 Complex tasks and 1 Simple, and so on - considering number rows is not a fixed number. What I have so far is this: =SUMPRODUCT((Data!$I$2:$I$457="Dan")*(Data!$L$2:$L$457="Complex"))
But I need to tell a specific last row, and if the data worksheet gets updated with less data (say 450 rows) the formula breaks...

View 3 Replies View Related

Macro To Segregate One Column List Into Three Columns Based On Condition

Nov 21, 2012

Lets say we have data in Column A, B, C and D and no row left blank. In the column A no cell left blank however in B, C, D any one cell only contain a value in that row. If B10 has any value in it then C10 and D10 are left blank (not empty). I want to segregate the data in Column A based on the value in B, C or D. So this one column data ( that is Column A) will split into three column. this segregated data to be put in E, F and G.

Wherever Column B has any value that's greater than zero content from the column A from the same row should copy to the E, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the F, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the G.

View 4 Replies View Related

Copying Columns For N Times Based On Value In A Cell On Same Sheet

Oct 8, 2013

With reference to attached file. I want to copy column D&E for 'n' number of times of value based on B1. If value in cell B2 is 0 then hide cell D&E and if there is any other value, excel to copy E&F to next column (leaving one column blank after each paste).



Product A




View 5 Replies View Related

Copying Certain Columns From One Sheet To Another Based On Value On One Column (with Feedback)

Mar 2, 2014

I have a master data sheet with the structure as below: I need a few of the columns (i.e. not all) to be copied to another (filter) sheet (starts from Row 20 say). The "notes" column should be fed back to the master list. If the condition changes in the master list, the entire row (including the data entered in notes) should be removed on the filter sheet.

The filter sheet structure is as below:

I initially used Index, Small, Countif to pull data to the filter sheet but could not "input" into the cells as they all held formulas in order to copy to the new sheet.

View 1 Replies View Related

Automatically Copying Cell To Other Sheet On A Condition

Aug 14, 2007

I have 41 Sheet in my Workbook.

Sheet1 Contains all the Data

Now For every Staff I have Four Individual Sheet of "Seg"

What I want is to Copy Data From Sheet1 to each sheet of the Staff according to there Respective Details.

1st record to be copied automatically to Sheet7 (CAS B) in Bought Side Column,
2nd record to be copied automatically to Sheet6 (CAS A) in Sold Side Column
with name of toys in both the sheet.

View 9 Replies View Related

How To Count Condition Based On Having Another Condition

Apr 12, 2014

So I have one sheet that needs to pull data through to another sheet (which is a stats summary)

I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.

For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.

View 9 Replies View Related

Count On Condition From Two Columns

Jul 2, 2009

I have a column with different cities that customers visit (Barcelona, Lisbon etc) and another column with their booking requirements (All inclusive, breakfast only etc)

I'd like to set up a count on the total number of customers who are going to Barcelona and having all inclusive.

I've tried using nested if statements with vlookups and a countif but can't seem to get that working.

View 7 Replies View Related

2-Condition Statement - 2 Different Columns

Oct 28, 2009


Each row contains a new client (a new entry)

Columns:Clients first and last name.
Case number
Case type - Case type can be AAA, BBB, CCC, DDD, EEE, (and many more).
Case outcome - Case outcome can be Granted (G) or Denied (D)

A case can be of type AAA, and be Granted.
A case can be of type MMM, and be Denied.
A case can be of type ZZZ, and be Denied.

I am trying to keep track of how many cases of each type have been granted and denied. If you look at the file i attached, you will see that the table is a list of clients, with different case types and different outcomes to their cases.

The problem i am having is that i need a way to have a two-condition statement as such:

if case type is "AAA" and case outcome is "G", then add 1 to a "AAA GRANTED" column on another sheet.,....................

View 3 Replies View Related

Hide Columns On Condition

Jun 12, 2014

Using VBA or by other means, I want to disable user entry in cells G1 to X1 if the user places a value in A1, similarly if the user places a value in A2 then disable user entry in cells G2 to X2, and so on.

View 7 Replies View Related

IF Condition Formula - Take All Columns In Result

Aug 13, 2012

I'm trying to complete my if condition formula, wherein I've stuck between, how I need to complete, here is the attached one.

The IF formula should take, all 'A,B,C and D columns in result.


View 7 Replies View Related

Match Columns To Check If Condition Met

Aug 29, 2007

If you open the attachment.. then you will see that Column A has all the names and Column B has it's Value.. likewise.. Column D has all the names and Column C has it's values...I'm trying to first match the names from Column A to Column D and then see if Column B matches to Column C..

View 8 Replies View Related

Copying Several Columns

Sep 29, 2009

Hello. I download several spreadsheets monthly that have a large number of columns (A to EZ). In every excel spreadsheet I need to copy only 18 different columns of data from the sheet which are the same columns each month, and put them into a new spreadsheet. It is very time consuming to highlight and copy a few columns at a time throughout the whole sheet.

I am not very experienced in excel, but is there a simple way to copy all of these columns at once out of the spreadsheet and paste them at once into a new one? I would think there must be some way to enter the column letters I need to pull them out.

View 9 Replies View Related

Count Multiple Columns Meeting Condition

Dec 21, 2004

I have a column (A) in sheet1 with these values:

a1 04800128
a2 04800178
a3 04800128
a4 04805555
a5 04800128

And in Sheet2 - Column A and B has these values

a1 04800128
a2 04800128
a3 04805555
a4 04800128
a5 04800128

b1 Y
b2 Y
b3 Y
b4 Y
b5 N

I need to count in sheet1, where the code of sheet1 will be matched with sheet2 code and its status should be equal to "Y" .. I do not want to hard code these values as I have a huge data.

View 4 Replies View Related

Copying Columns Of Data

Jan 7, 2013

I'm currently making a workbook of annual sales lists for my small business. There are separate sheets for each year (2007, 2008, etc.) where column A consists of a list of customers for that year and the next 12 columns include sales for each respective month.

The issue I'm now facing is in trying to create a new "master" sheet of sales (month-to-month) for every year (2007-2012). The problem I'm having is transposing the sales data for each month from the previous sheets. Why I'm having trouble is this new sheet will be for every customer we've ever had whereas the past annual sheets were merely for customers in those specific years.

Thus I have annual sales sheets of 800 some customers while the final sales sheet will be nearly double that. What I've been attempting is to match the customer name from the master sheet with a specific year and if matched, transpose the monthly data associated on that yearly sheet. The formula I came up with works to a certain extent and then seems to fail because the customer difference gets lost in translation. I'm doing something wrong so that it's not looking for a match out of the entire 1600.

Here's my formula I'm trying: =IF(ISNA(VLOOKUP($A2, '2007'!$A$2:$A$883, 1, FALSE )), "", '2007'!B$2:'2007'!B$883)

View 2 Replies View Related

Copying Columns From One Tab To Another Via Macro

Dec 17, 2013

I have 2 tabs (tab 1 is "Data", tab 2 is "Compare")

Data tab is filled with results from tests I do in a lab, each test has its own row, each 'test subject' has its own column.

In the second tab I want to have the ability to pick from a drop down list one of the other columns from the first tab.

So I have 2 columns (D and F) in tab 2 with drop down lists that I have populated with the names of the test subjects (using data validation grabbing the top row in tab 1).

I have formulas set up in Column E of tab 2 for the actual comparison.. What I'd like to do is when I select a test subject (for example SubjectA), from the drop down list in $D$1.. I want to populate $D$3:$D$155 with rows 3-155 of the matching column in tab 1 (so if for example SubjectA is in column X, it would copy from tab 1 X3:X155 to tab 2 D3:D155.

So so far I have the validation part done with the drop down list.. and I have the following VB code for tab 2.

View 4 Replies View Related

Copying Columns From One Sheet To Another

Mar 5, 2014

I'm trying to copy some columns from one sheet to another using column numbers. Why this doesn't work?

Dim leftColumn As Integer
Dim rightColumn As Integer
leftColumn = Sheets("Hidden-Summary").Range("B3")
rightColumn = Sheets("Hidden-Summary").Range("B4")

Sheets("Hidden-Summary").Range(Columns(leftColumn), Columns(rightColumn)).Copy _
Destination:=Sheets("Summary").Range(Columns(leftColumn), Columns(rightColumn))

View 2 Replies View Related

Checking Columns Then Copying To New Worksheet

Sep 16, 2012

I have 4 columns E,F,G,H which each column has the variant purchase cost, I need to sort between E,F,G,H and highlight which is the lowest cost on sheet 1, I then need sheet 2 to display any item that was the lowest figure from column E, then the same with sheet 3 for column F and so on to H.

View 1 Replies View Related

Printing Columns Without Moving Or Copying

Jan 15, 2013

Without moving or copying columns I need to print Column A & Column I on the same piece of paper and not print Column B-H.

Every time I try this though it is printing column A on one sheet and Column I on a second sheet. Yes I've checked margins, etc.

This is actually possible, right?

(I didn't want to move or copy columns as the spreadsheet is for someone else's use, plus my formulas buggered when I tried to so I didn't want to add that complication for the other person of double checking the formulas every time she wanted to print this two column version of the sheet.)

View 3 Replies View Related

Copying Out Data Columns Of Different Lengths?

Jun 25, 2013

I am half way through writing a bigger macro, but am stuck at this stage.

I need to copy from a block of data as shown:

However, this column varies in length each time, and I only want to copy down to where the entries stop.

I also only want to copy W1:X6, and columns Y, AA, AB, AD (but only down to the end of the column)

I've tried using the End(xlDown)).Copy function, but obviously this doesn't work with the block up the top and the gap between the block and the columns

View 4 Replies View Related

Copying Columns To New Workbook And Add Headers

Aug 26, 2013

I am trying to create a macro that will create a new workbook, copy certain columns to the new workbook and add new headersEaxample is:

In workbook 1 copy columns A To Workbook 2 Coumn D
BTo Workbook 2 Coumn F
CTo Workbook 2 Coumn O
Also add headings to the Columns in workbook 2
And last populate some columns with text all the way down tothe last row that contains text in workbook 2. A = Y
B= 5
C= Pass

View 3 Replies View Related

Automate Copying Columns From One Sheet To Another?

Nov 16, 2013

I want to copy certain columns from Sheet6 to Sheet1. For instance, Column G in Sheet6 would be copied to Column A in Sheet1. I have over 20 columns that need to be copied. There are over 5,000 rows in Sheet6. Thiomething that I have to do daily.

View 2 Replies View Related

Copying Specific Columns Into A New Worksheet

Aug 20, 2008

I have a file that has various numbers of columns, some of which i need, some of which i do not. Under these columns, there are various rows, sometimes 50, sometimes 400. I need to write something that copies only the specific columns I need and pastes it into a new worksheet.

For example, in the data file there is are columns property type, loan balance current, and amortization type. I need them to be copied with the information in the rows below them to a new worksheet, to eliminate the unnecessary information in the data file.

View 9 Replies View Related

Sum Based On Condition

Dec 15, 2009

I want to total column C, but only where column B contains certain data, like add column C where column b contains abd so that i get the answer 7, Hope ive explained it right.

abd 1
abd 2
bmr 3
abd 4
crc 2
crc 3
bmr 2

View 2 Replies View Related

Automatically Copying 9 Rows And Converting To Columns

Dec 17, 2012

I need to take a list of about 1,500 rows of data (single column) and convert it into columns of 9 entries. I know that I can select nine rows and then paste using transpose... But any way of "automating" the process?



becomes 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 etc....

View 5 Replies View Related

Copying Formula With Fixed Rows But Different Columns?

Nov 27, 2013

Im using this formula in a spreedsheet =(B$3*0,5)+(B$3+(B$4*0,5))+(B$3+B$4+(B$5*0,5))+(B$3+B$4+B$5+(B$6*0,5))+(SUM(B$7:B$28)*4)

What i want is to be able to drag this down for multiple rows but i want the formula above to only change columns i.e.

Next row gets C$3 and so on.

View 3 Replies View Related

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