Populate One Column Based On Condition Of Two Other Columns

Jan 17, 2014

15.png
Attached is the file & snap shot for the problem.

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

[Code] .....

TABLE B
Catagoriesstate names devices
A4-100HP
A4-101MP
A4-102AP

[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


ADVERTISEMENT

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

Auto-populate Sheets Within One Workbook Based On Condition

Dec 20, 2012

I have a single registration sheet that lists names, contact information, and the class someone is in on a single sheet.

(Name, Number, Email, Class 1, Class 2)

I am hoping to automatically populate other sheets in my work book based on classes... essentially making automatically populated rosters. For example, everyone who has Math listed in either Class 1 or Class 2 would have their entire row (with contact information, etc) copied to the "Math" sheet.

View 2 Replies View Related

To Populate Three Columns With Text Based On Text Of Another Column

Dec 16, 2009

I am having a trouble in Excel sheet.My column A has a drop down list with text- possible, not possible, not required.Based on the text, i need to populate texts in columns B, C and D.

For example

Column A drop down selected is "possible"
then B coulmn should automatically populate "1-3"
C should populate with "3-5"
D should be "5-7"

I am using MS excel 2007.

View 9 Replies View Related

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".

City
result 1
result 2

San Fran
San Fran
New York
Tampa

View 4 Replies View Related

Auto Populate A Third Cell - Based On Data From Two Columns

Mar 24, 2013

I have data in three columns

A B C
4.5 9.50 4.657
4.5 11.60 4.789
4.5 12.50 4.654
4.5 13.50 3.930
4.5 15.20 3.826

I need to be able to auto-populate the cell in column G based on values in columns A & B. If I type 4.5 in E2, and 13.50 in F2, G2 should auto-populate with the value from column C

E F G
4.5 13.50

View 2 Replies View Related

Get Data In Column A To Populate Into Multiple Columns

Jul 15, 2014

I have single values in each cell in column A. I have 100 rows.

I want to take six rows at a time and get them to populate columns B, C, D, E, F, G.

Is there a simple formula for this?

View 2 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

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
Set MFBANK = Workbooks("MF BANK EXPOSURE SUMMARY.xls")
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

Populate Column Based On Change In Data Of Another Column

May 30, 2014

I am trying to find a way to populate a column based on the changes in the data of another column...

For example, I want to fill in the code column....so when the customers cost changes, I want a numeric code to populate & change. So for the first 5 lines, the code could be 21, and then when the cost changes to $37.51, want the code to change to 22.

CodeCustomer's Cost
$34.10
$34.10
$34.10
$34.10
$34.10
$37.51
$37.51
$32.50
$32.50
$33.48
$33.48
$34.10
$37.51
$37.51

View 7 Replies View Related

Condition Paste Formula In Column N Based On Column A

Sep 7, 2009

I am wanting to paste formula from N1 till N X (X = varaiable row) where X reliant on Column A Row X. If Column A Row X has character "=====" it should paste N1 Formula all the way down till N Row X which is equal to A Row X containing "=====" .

I might be sounding complicated over here but it is a simple equation.. I have tried to approach this in the capacity i could by condition if Column A row x is blank delete the row.

View 2 Replies View Related

Concatenate Column Based On Condition?

Aug 13, 2013

I need a formula to be placed in cell Col A, Row 1, that concatenates any and all cells in Col A, with a ';' separating each item. I only want this concatenation performed ONLY IF an adjacent cell in Col B contains the letter 'X.

Additionally, if the cell in Col A is null, then I the formula to ignore it, and not perform the concatenation.

Is there a way to do this formulaically?

View 1 Replies View Related

Sum Values In Column Based On Condition In Another

Oct 7, 2007

II want to sum the values under 'amount' column only if the corresponding value under 'Balance' column is "Paid"

NameAmount Start Date End Date No.of DaysInteresetBalance
Sat1 250 1-Aug-07 20-Aug-07 19 - "paid"
Sat2 550 4-Aug-07 20-Aug-07 16 -
Sat3 250 10-Aug-07 20-Aug-07 10 - "paid"
Sat4 450 6-Aug-07 20-Aug-07 14 -

Total PAIDAMOUNT(B1:B4)

For the above, the sum value should be 500. I have written the following function to calculate the same. On uncommenting the line 'paid = CStr(paidvar)' the value is displayed as '#VALUE!'. On uncommenting all commented lines, the value is displayed as 'Paid'.

Function PAIDAMOUNT(amountRange As Range) As Variant
Dim count As Long
Dim paidvar As Variant
Dim paid As String
Application.Volatile True
PAIDAMOUNT = 0
For count = 1 To amountRange.Cells.count
paidvar = amountRange.Offset(count - 1, 7 - amountRange.Column).Value
'paid = CStr(paidvar).................

View 2 Replies View Related

Copy Cell Value To Different Column Same Row Based On Condition?

Oct 13, 2012

I've schedule header date 1-oct,2-oct,3-oct.....etc and have two cells Last Date and Hours I need from those cells once i add date and hours to copy the hours and paste in schedule header in the exact date..

for example
#
Last date
Last Hours

[Code]....

but was too slow takes long time.

2nd VBA code to copy last hours to schedule date based on condition on last date cell.

View 3 Replies View Related

Finding The Maximum Value In A Column Based On A Condition

Nov 7, 2008

This should be simple to do but I can't figure it out. I have a database that lists operating room numbers in one column and the length of the surgeries performed in those rooms in another column.

I need a formula that will give me the longest OR time for a given room. For example the room numbers are in column A and the OR times are in Column B. I've tried something like

View 6 Replies View Related

Selecting Rows Based On A Column Condition

Jun 4, 2009

I have a worksheet which is created monthly by one of our company's employees which reports results for that months operations and successful audits. These audits are separated into various service lines and on the report which is submitted it takes on the following format:


1|Service Line|Data A|Data B|Etc.
2|___CR_______| Data | Data | Etc.
3|____________| Data | Data | Etc.
4|____________| Data | Data | Etc.
5|____________| Data | Data | Etc.
6|___DD_______| Data | Data | Etc.
7|____________| Data | Data | Etc.
8|___MS_______| Data | Data | Etc.
9|____________| Data | Data | Etc.

What I want to do is use a macro from another sheet that has the effect of: While ServiceLine = "CR" copy the row of that line and move it to my monthly summary workbook, sheet 1. Then when the Service line changes to DD have the macro copy that information to the monthly summary workbook sheet 2, and so on. The problem is, if I test the Service Line column each time it will be an empty string the majority of the time and I'm not sure how to combat that with my If, Do, For, and While statements. As I've thought about it there are two solutions that I can think of: someone knows of a way to test the row only if it has information in it, or if there is a way that I can copy the service line information down until the change in service line so I can test Service Line on each Row.

View 3 Replies View Related

Filling Column Data Based On Condition

Nov 20, 2012

I have an excel sheet with 2 tabs. 1st tab provides the data for downtime of a manufacturing line. The last column (shift) is blank and needs to be filled based in shift schedule in tab2

On the 2nd tab I have the shift schedule, which tell which shift is working on particular days

Month
Date
7:00am- 7:00 pm
7:00pm - 7.00am

Feb
1
Shift A
Shift C

[Code] ........

Is it possible to write a macros that will look at the Date and time from tab 1 and assign the correct shift number in the shift column. For example the 1st row of tab 1 reads (Month=Feb, Date = 3:55:59 AM) so according to shift schedule it would fill C shift.

I have lots of data and to assign every event to shift # manually is not possible. So i wanted to know if there is a macros i can use to make life easier and learn at the same time

View 2 Replies View Related

Exclude Rows Based On Condition In 1 Column

Feb 7, 2009

I have a worksheet that I want to export to a csv file. However, there are multiple rows that I want excluded in the export. These lines are recognized by the word "No" in column E. Is this possible?

View 3 Replies View Related

Match Column And Colour Based On Condition

Feb 22, 2007

i am trying to do is match 2 columns data based on a condition ie i have data in column "b" of sheet called "balance" data is variable this column has unique indentifiers i want to look at another column and match the uniques identifiers in another sheet if the cell value in column "D" = "please investigate" otherwise do nothing, the (column f) it will be looking up too is in sheet called "hi- port" so if cell value in column "d" of sheet"balace"= "please investigate" match values that are same from colum "b" of sheet "balance" to that of column (f) sheet called "hi-port"

i would like to colour only entire row of sheet called "hi-port" when the data matches to other sheet ie from columns "f " too " b". colour can be any colour
igonre my ranges they are wrong just used as example

Sub start()
Dim oWs As Worksheet
Dim oRngCheck As Range
Dim oCl As Range
Dim cCola As Range
Dim cColb As Range
Dim rngIRd As Range
Dim rngACs As Range
Dim Match As Boolean
Set rngIRd = Range("a1", Range("a65536").End(xlUp)) 'define the rang
Set rngACs = Range("b1", Range("a65536").End(xlUp)) 'define the rang
Set oWs = Worksheets("balance") 'change to suit
'starts at b2 to llow for header row.......................

View 4 Replies View Related

Delete Rows Based On Condition Of 1 Column

Oct 19, 2007

I would like to Use my Excel VBA program to search each row in a csv document for a name (located in a cell ) if the name exists then I would like to delete the entire row.

Whenever I try to do the above in excel, even when I save in csv format the file formating or something gets changed and the file which has to be procesed through another program then process incorectly.

Is there a way to do what I need while retaining the formating which must obviously be changed when the file is opened in excel?

View 9 Replies View Related

Vlookup Using 2 Condition One Based On Devices And Grouping Column?

May 27, 2014

Attach below are 2 sample file and inside it consist of device,grouping and lb1,tb1,ab1. How do I use excel formulae based on 2 condition and set from vlookup with 2 condition file to vlookup with 2 condition 1 file like a vlookup.

View 11 Replies View Related

Counting Unique Values Based On Condition In Different Column?

Aug 21, 2014

Formula which will count unique values in column A based on condition in Column B which "y" .....

Show ranges as A:A instead of A1:A100 as I dont know the size of the table, it can be thousands rows .....

View 3 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

Populate New Sheet With Data From Multiple Sheets Given Condition?

Dec 16, 2011

I am trying to populate a blank sheet with data from multiple sheets given a certain condition.

Scenario: I have multiple columns on each sheet, but only three of interest to me. The first column has a header "Part ID", the second column header is "Description", the third column header is "QTY". I want to pull the data from all three columns to the new sheet when the QTY for that column IS NOT 0. The three columns of interest are always in Column A, B, and C.

The only thing that may make it tricky is that the data doesn't necessarily start on the first row of each sheet. For example, the headers for the first sheet are on row 17, and the headers for the second sheet are on row 5. So on and so forth...

View 3 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

Average Column Based On Another Columns Value?

Jul 29, 2014

I would like to average a column based upon another column value. Namely, if one column is a duplicate, I would like to take the column to its right value and average all the duplicates. For example

comp 1409 99.7
comp 1409 99.5
comp 1409 100
comp 1308 65.7
comp 1509 99.5

I would like that to become

comp 1409 99.73
comp 1308 65.7
comp 1509 99.5

View 6 Replies View Related

Sum Column Based On Two Criteria From Two Columns

Nov 7, 2012

I'm not sure if this is the correct way to display my sample data, but here it is (how to change it. I looked in the guidelines, but I can't download anything onto this computer, so I can't get the HTML maker)

A
B
C
D
E
F
G
H
I

1
Descriptor
Group
Identifier
Inventory
Total Vaue
Invetory Type

Group
Total Incomplete Inventory Value

[Code] .......

Here is what I would like in:

The sum of the total value of half finished and unfinished inventory for each group.

So, for group one, it would be 145. For group two if would be 38, and group three would be 316. (displayed in column I)

I think an array formula would do this, but I'm not really sure how this would work.

View 9 Replies View Related

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).

View 9 Replies View Related

Code To Hide Columns Based On Value Of Another Column

Jul 31, 2014

with a simple code that could:

If column K2:k466 is blank or says "No" then columns L & M are hidden. If it says "Yes" it is not hidden.

Also,

If Column N2:N466 is blank or says "No" then Columns O,P,Q,R are hidden. If it says "Yes" they are not hidden.

Finally,

If Column V2:V466 has a zip code (or number) then columns T,U,W,X,Y,Z,AA,AB,AC are present. If there is no value in any of V2:V466 then they are hidden. .

View 12 Replies View Related







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