Sum Some Cells In Row Based On Condition Of Nearby Cells In Same Row?

Dec 12, 2012

Basicalley I have a series of columns, each 5 letters together represent an order(a,b,c,d,e), the columns are repeated approximately 15 times, similar to the following;

Bag Size:B/G5lb2lb 16oz8ozB/G 5lb 2lb (repeat...)

Catagory 1ABCDEA1 B1 C1 (repeat...)
Catagory 2 AABBCCDDEEAA1 BB1 CC1 (repeat...)
(...Repeat)

The B/G stands for Whole Bean, or Ground, the A's are drop down lists giving those 2 options, the other cells (non-A's) are all numeric. At the end of each row, the orders need to be summed up into categories; 5lb whole, 5lb ground, 2lbs whole, 2lbs ground, 16 whole, 16 ground etc..

I am looking for a way to sum each category for all orders as necessary, in one cell adding all B's (in that row for that catagory) if its corresponding A is whole bean, in the next summing all b's if its corresponding A is Ground. The same will go for the C's D's and E's.

View 1 Replies


ADVERTISEMENT

How To Use VBA To Search Strings And Return Values Or Cells Nearby

May 20, 2009

Im needing to search through the attached document which is truck logs at a mine and use vba to search through the list for each of the different shovels eg SHVL1, SHVL2 & SHVL3, and when the code finds that string, to display the tonnage 2 colums back from it. Im about half way there a i think but am having trouble with strings, if it was numbers i could do it no problem

View 11 Replies View Related

Concatenate 3 Cells Based On Condition

Nov 2, 2009

In the workbook, 3 columns of importance Column N,S and AC. Concatenate order ("S"&"N"&"AC") - combine it and show value in column AG. Column AC has either 0 or any other number (50,60,100 etc). If it is 0 I need to insert an 'F' at the end of the code in column AG, any other numbr and i need an 'O'.

Column N has contract code, this will be in the middle, usually its alphabets like RF,EF, but sometimes numbers like 21,33, etc but when its a single digit number like 6, i need it changed to 06 (need the 0 infront). Column S has exchange codes, and this has to appear in the front of the code in Column AG. Same as Column N, if there is a single digit number then put a 0 infront of it.

View 4 Replies View Related

Fill Cells Based On Condition - VBA

Oct 4, 2011

I need to be able to fill a cell in the same row based on values in two other cells in different columns.

Current Layout. Call these columns A, B, and C

I have 3 different states I work with, OH, IL and PA. There are different tax exemptions based on whether or not the class is RES or COM.

Conditions:
State = PA, Class = RES, Tax Exempt = 1
State = PA, Class = COM, Tax Exempt = Null (not "0")
For all other states, Tax Exempt will always = 1

I need the VBA code that will autofill column B with the proper Tax Exempt status based upon columns A and C.

The code I have now just looks at the first value in columns A and C then fills all of column B with that number.

View 6 Replies View Related

Split Cells Based On Condition

Apr 30, 2014

Column A has data e.g. cat is fat xy>zzy, dog is random xyzzy, rat is a hoax.set xyz, frog is alon.23e xyz, bullseye.8 x.z
I have a look up table as below:

Col D
xy>zzy
xyz
x.z

(Note: the values in Col D do not have a set number of characters, so I cant use a Right/left Formula)

I need a lookup to look for the values in Col D within Col A and return the values in Col D. Is this possible?

All I really need is col D characters separated from the first word/words.

View 2 Replies View Related

Clearing Cells Based On Condition

Oct 3, 2008

I've created (with the help of jmthompson from here) a macro to pull an entire row of data and copy to another sheet (Sheet1) if column U has "YES" in the cell.

Now I need to clear the contents of cells in range B-U after the copy has occurred.

Here's the current macro: ...

View 9 Replies View Related

Automatically Sum Cells Based On Condition

Jul 1, 2009

I am trying to Excel VBA for doing an AutoSum function in Excel.I am struggling as the recorded macro is giving me fixed ranges whereas the number of rows would be different each time. What I need that the Delivery Qty in Column C be a Total whenever there is a text "Results" in column B?This should happen for every row with Results as a text.In Excel the Autosum feature should take care of this but I am unable to write a VBA code to do this.

Before
DeliveryAct.goods issue dateDelivery Qty
800205573906/05/20094
Results
800205657806/01/200921
Results
800206322306/02/200910
800206322306/02/200915
800206322306/02/200942
800206322306/02/200940
Results
800206322806/02/200941
800206322806/02/200910
800206322806/02/2009455
800206322806/02/2009410
800206322806/02/2009485
800206322806/02/2009785
800206322806/02/200944
Results.................................

View 2 Replies View Related

Merging Cells Based Upon Condition In Different Cell

Feb 26, 2013

I am trying to automatically merge cells based upon a condition in different cell. Below is the example of what I would like to achieve.

Apple
Red

Apple
Red

Apple
Red

[Code] ......

View 9 Replies View Related

Copy Only Part Of Cells Contents Based On Condition?

Jul 6, 2014

I will have 2 columns. In column B, will be a list of invoice numbers. Now each one starts with the "job number" (Ex. 51APGC01) then a dash and unique number per invoice. (Ex. 51APGC01-01)

In column A, will be a list of Purchase Order numbers sent by the customer so we can bill. Now, sometimes we get one PO for multiple invoices, (Ex. 03, 05, 06, 07.....)

What I am looking to do, is when I get these PO's, I enter them in and then I print a report for our billing Dept. The way the output line (cell) will read, is Job Number, then each unique number: (51APGC01-03, 05, 06, 07)

There are multiple jobs and each one has it's own report, so the job number will be dynamic and defined else where in the workbook. I can define that. It's just getting the numbers after the "-" and putting them in only.

View 5 Replies View Related

Select Certain Cells Based Upon Condition And Putting In Sheet 2

Mar 25, 2009

I have attached the file with this post.

What I like a macro to do is:

Whenever a 'D' ends in column "Step 5" the last value of 4 cells to the left to be put in sheet 2 in reverse order.

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

Copy Range Of Cells Based On Condition To Another Worksheet

Apr 17, 2008

"copy cell range based on conditions" and it didnt really get an answer. There was one that copied the info the next blank cell on that line, but im not smart enough yet to figure out how to copy it over. ( getting there though with lots of staring at code).

2 sheets. I have already created auto modules to fill in data and code,and sorted the columns so they are in line.

Sheet 1. Info : has 9 columns. So if column = 9 and the value = new.
Then i want to copy the range on the columns (A:G) and then paste it on the other sheet ( Card info) as long as Column A is empty ( as in next available blank cell) ( something like a DO while worksheet("Card info").column(A) <> ""

View 9 Replies View Related

Copy Selected Cells Of Rows To Another Worksheet Based On A Condition

Feb 4, 2010

I am self-taught (arguably by a pretty shoddy teacher), and am determined to figure *some* of this stuff out. I need a code to look at the rows on one worksheet and based on the value of column A for each row, copy cells A through E in that row to cells A through E of a blank row on a corresponding worksheet. I found a code from another post that does just what I need it to with one small exception. The code was posted by DaveGuggs and is as follows:

View 2 Replies View Related

VBA Copy / Paste Specific Cells From A Sheet To Another Based On Condition (reference)

Nov 7, 2012

Basically, I need to copy and paste upon certain condition (references) and paste related-datas in another sheet corresponding with these references. Every references are organized by lines with indicators on columns.

In attached file, see:
- worksheet ANALYSIS:
* datas in E2, G2, D18, D19, D20, D21, D22 that needs to be copy based on reference in C2,
* then need to find this reference in worksheet called GROUP SAVE,
* and based on this reference, paste above cells value in corresponding "Indicators" column number

THEN, I will have to do pretty much the same with subgroup data. But I can figure out for this second step that request same manipulation.

View 3 Replies View Related

How Do You Protect Cells On Condition Of Other Cells Having Data Inputted

Dec 4, 2006

way of protecting an array of cells on the condition that information is entered in another array of cells or vice versa. So, put simply, if data goes into a1 then a2 becomes protected (or vice versa).

View 14 Replies View Related

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

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

Copying Merged Cells (3 Cells) Based On Contents Of Any Of 3 Cells To Right

May 29, 2014

I wish to copy a merged cell (3 cells) based on if only 1 of 3 cells to the right contain "X". if the top cell does not contain "X" than the merged cell is not copied. Also, is therea more elegant to copy 3 columns at a time rather than do one at a time as my code shows:

Sub CopyICUCAPU()
'
' CopyICUCAPU Macro
'
Dim i As Integer

[Code].....

View 14 Replies View Related

Return Values Of Adjacent Cells When Condition Is True Omitting Values Where Condition Is False?

Jan 15, 2014

Here's a simplified example:

ColA
ColB
ColC

Row1
A
Y
A

Row2
B
N
D

[Code] .........

I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.

The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.

View 7 Replies View Related

Date Cells In Project Plan To Change Based On Other Cells Including Other Date Cells

Oct 31, 2008

This is a project plan with tasks and dates. Column A is the activity number. (Example 1, 2, 3" etc). Column B is the task (Ex. "Complete Report"). Column C is number of days required to complete the task. Column D is the dependency column. (Ex. Cell D2 =1 in other words Task 2 is dependent on task 1). Column E is the date.

I would like to have a seperate start date cell and a go live date cell.

The objective is to enter a start date, and have each column E date increase based on the number of days entered in Column C. If a task is dependent on another and I change the number of days in Column C I need the dependent task to change the same amount of days.

View 9 Replies View Related

Add Certain Value To All Cells When Certain Condition Met

Feb 26, 2012

I want to add a certain value to all cells when a certain condition is met.

I'll try to explain:

I have the following table. What should happen is by pressing a button it would automatically increase the value specified (in this case 50) to all values in the column "Frete 20" that meet the condition of the "Pais" being, in this case, "Angola".

The same should happen for each column (Frete; BAF; CAF;...)

Pais Frete 20 BAF 20 CAF 20 PSS 20 Angola 20 30 40 50

So when clicking the button it would add the corresponding value to each column

POL Pais Frete 20 BAF 20 CAF 20 PSS 20 Cape Town Africa do Sul

[Code] .......

View 1 Replies View Related

Cells On Condition

May 8, 2008

I have a progress column of cells with a data validation dropdown list (the list has 3 values).

If the 'complete' has been selected from the dropdown list and a date has been suppield in the column next to it, i want that cell in the progress column to 'lock' - be uneditable.

How do i do that. I don't know who a macros works and i don't understand VB scripting stuff.

View 9 Replies View Related

Lookup Range Of Cells And Populate Specific Cells Based On Matching Data?

May 23, 2014

I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.

I have attached the worksheet so you can see what i am trying to achieve.

View 2 Replies View Related

Excel 2007 :: Conditional Formatting Empty Cells Based On Full Cells?

Nov 17, 2011

Working in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A

Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).

there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.

View 5 Replies View Related

Count Cells Containing Dates (greater Than 2014) Based On Filtered Cells

Jun 24, 2014

I have spreadsheet with different 100s of columns of dates with 600 rows. The first row identifies which zone the data belongs to (North, South, East, West. NE, SW, SW1, etc...)

I want to write a formula to check how many dates in each column fall in 2015 or later years; This can be accomplished by writing a countifs formula.

Where it gets complicated is once i filter on the Zones;

I want the formula to give me the desired result - count of all CELLS where the year is 2015 or greater - WITH FILTERS ON.

I stumbled upon following sumproduct formula that gives count for visible cells, however when i apply the date criteria, i get incorrect result -

=SUMPRODUCT(SUBTOTAL(3,OFFSET(IJ3:IJ999,ROW(IJ3:IJ999)-MIN(ROW(IJ1:IJ999)),,1))*(IJ3:IJ999>DATE(2014,12,31)))

View 8 Replies View Related

Blink Cells If Condition Met?

Mar 19, 2014

How to make blinking effects vba on the cell, if cell A1:A300 have FAILED value?

View 3 Replies View Related

Concatenate The Cells On Condition?

Apr 23, 2014

I have a Requirement where we need to concatenate the cells based on conditions

i have the attachment for the reference . please download the file from the link.

View 1 Replies View Related

Delete Cells On Specified Condition

May 10, 2008

I have some data in numaric form from range a2:f20000 which I have got as a sum from a sheet and I want if any cell (a to f) from row 2 to 20000 has sum up value blank or 0 then it deletes shift xlleft.

View 9 Replies View Related

Lock Cells On Condition

May 1, 2009

W3:W300 is a protected range containing formulas returning numbers from one upwards, with two decimal places.

I need some vba to check this range and, if a number greater than 1 is found, to make the corresponding cells in Columns X and Y blank and lock them.

View 9 Replies View Related

Condition Formatting In Cells With No Value

Mar 1, 2010

I have a workbook (column K) set with the following format conditions:-

Cond.1 - =(I3="declined")
Cond. 2 - =(G3>0)
Cond. 3 - =(K3

View 9 Replies View Related







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