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


ADVERTISEMENT

Filling Empty Cell Value Based On If Else Condition And Delete Row More Than 2 Cells Empty

May 23, 2014

Here find the excel file

My requirement

1) 4 values contains in each row based on the values from those cells the max value will display.

2) if more than 2 cells have empty,NR or NA text means the entire row has to delete.

3) if 2 or more that means 3 cells having values the empty cell,NR or NA cell will place value with the condition of macro that is 75% of other values which is maximum among them.

View 1 Replies View Related

Filling In A Column Based On Certain Criteria

Mar 5, 2008

I am trying to auto-fill a column based on certain criteria - in other words, fill in blank cells based on a cell above, just a little more complex version. I have added an attachment to give everyone a visual of what I am describing and then maybe this explanation will make a little more sense.

First, if you open the attachment, the left hand side is a small example of what I have. The middle is the condition. And the right hand side is what I'd like to see happene.

In the example, I am matching up a time and when I hit that time, I am adding on a certain number of miles per hours based on the condition and the cell above. Meanwhile, I am filling in the blank cells with the condition until the condition changes. So a cell is incremented based on the condition and gets its value from the cell above.

View 14 Replies View Related

VBA For Filling Column Based On Reference Column?

Feb 2, 2012

If column B is not empty, I want the corresponding cell in the same row of column A to contain the value "1".

View 9 Replies View Related

Filling In A Column Of Data

Apr 8, 2008

I have a column of data with various values and a bunch of blank spaces. Essentially I want to leave the values as is but fill in the blank cells with a number. I’ve written a loop to do this in VBA, which grabs the value in the cell above, but it’s somewhat slow. Is there a more efficient way to do this?

Sub Downfill(Max)
'
'This count variable is used to run the loop
Dim i As Long

i = 1
Do While i

View 9 Replies View Related

Validation And Filling By Condition

Oct 13, 2009

I have to validate cell text are (1, Absent, 2,leave, 3 present, 4 late 5 no report)
at the same time Absent should filled by, red leave by pink, present by green, late by yellow and no report by blue.

Preferably I dont want to use VBA. I am using excel 2007 but I am not excel expert.

View 7 Replies View Related

Filling Data Down A Column With Fixed Values?

Dec 22, 2008

I have two worksheets. One that contains material pricing and another that has misc data, formulas, etc. I have 100 rows of data with 2 columns. Column A I have numerical values. Column B I have a formula =(A1/36.4)*sheet1!C7. I try to drag down the formula but the value for sheet1!c7 changes as I go down (sheet1!c8 then sheet1!c9 and so on). How can I drag down the formula and have the SINGLE value from worksheet 1 carry to all rows?

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

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

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

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

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

Printing Data Based On Condition

Mar 31, 2008

a way to select which ones to print based on the given condition. I have a store list, and on this list I provided the store number and their authorized vendors. I have a weekly spreadsheet that has all our vendors cost/retail changes. On this spreadsheet, the vendor numbers are on column A. I was hoping for something, like maybe provide the store number to print, and it will print only the vendors that that particular store carry... We have a total of 106 stores, so automating this process would definitely save us a lot of time.

View 9 Replies View Related

Duplicating Data Onto Another Workbook Based On A Condition

Nov 15, 2013

How to open a fresh workbook and have information automatically being pasted in, based on a checkbox trigger. I don't know if I have explained well or not but essentially it is this:

W5 is the checkbox with a tick and a cross. When the cell is ticked I want to specicy cells on this record to be brought over to another tab.

View 1 Replies View Related

IF Function To Retrieve Value From Set Of Data Based On Condition

Mar 28, 2012

I have huge set of data listing almost 20 columns and 400 rows..i am trying to apply a formula for one of them..

Condition: Onshore or Offshore

Data to be retrieved: list of locations at onshore and offshore

Result expected: If a particular cell is selected as onshore then allow user to select only Onsite locations same for offshore

View 1 Replies View Related

Data Compare And Update Based On Condition

Jan 29, 2009

I have a spreadsheet with ~35k rows of data. This isn't too easy to explain I have the following data (example).

User Name Employee ID Data Level Jan 08 Feb 08 March 08 etc.
Jo Bloggs 12345 A1 1 1 0
Jo Bloggs 12345 A2 1 1 0
Mark Jones 89101 A1 1 1
Mark Jones 89101 A2 0 1

For each user with data level A1 I wish to add an X if the same user has a corresponding data level of A2 so the result should be,

User Name Employee ID Data Level Jan 08 Feb 08 March 08 etc.
Jo Bloggs 12345 A1 X X 0
Jo Bloggs 12345 A2 1 1 0
Mark Jones 89101 A1 1 X
Mark Jones 89101 A2 0 1

I would really love a formula or macro to be in existance that would save me from trawling through all this data and going blind.

View 9 Replies View Related

Equal Distribution Of Budget Data Based On Condition

May 6, 2014

I have written a code for equal distribution of Budget Data based on condition.

The user will enter annual budget number in Column K (highlighted in Green) and based on condition in Column N (highlighted in Yellow), the data will be divided by 12 and it need to be equally distributed from Colum P to Column AA. The condition for distribution is 'Y' (Yes)/ 'N' (No).

I have also attached the sample file.Budget_Input Form.xlsm

View 2 Replies View Related

Macro: Copy Data Based On Criteria/Condition

Oct 16, 2006

Attach is a sample of my project. I have to loop through B9:B80 in sheet("Raw Data") and capture the value of each cell. EG:

* if Value captured is 120A --> check with sheet("SP14F") from the row which contains the same value.

* i have to check that the aperture, outer and inner values of Layer 120A and so on..of the Raw Data sheet is the same as that of in the SP14F sheet.

* if the values are not the same, change the font to red

i understand that this task requires looping and arrays.

View 4 Replies View Related

Split Data In Multiple CSV Files Based On Condition

Jun 11, 2008

I have a few CSV files that sits in a folder ie C:/Data and I want to split the data in those files into different sheets. Each of those files contains rows of data with an account number. For example, I have 3 files that contains transactions and they have accounts "Orange", Apple, pear, I want to put all transations with account apple together and all Orange transaction together etc etc.

View 3 Replies View Related

Filling Down A Column Using VBA

Sep 25, 2007

I want to be able to have a macro that will compare one cell to the rest of the cells in a collum. I have used the COUNTIF() formula and it's getting on my nerves a bit because it seems as though everytime i sort the data, it totally messes up the formula (as in it changes the cell refernces to seemingly randomly generated numbers, but that is a different matter! lol). here is the formula, and it will be followed by the formula that is in the cell directly below it: =COUNTIF(F2:F65536, F2) and the one below it: =COUNTIF(F2:F65536, F3). Only one number needs to change, but when i fill down in excell, it wants to add one to every number, even when i click COPY cells... oh well. So i want to write this macro that will do just that. That way, it has fixed reference parameters e.g. F2:F65536, and one variable parameter e.g. F2, F3, F4 and so on. I'm not too savvy with this whole VBA stuff... the university insisted that everyone uses C++ nowadays, lol. I would like this macro to output its data into an adjacent cell ex. if F2 was compared, the data will be placed in H2.

View 10 Replies View Related







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