Sum Based On 2 Conditions
I want to have SUMIF and IF functions to be combined. i tried using the formula SUMIF( Timesheet!D2:D55,B2,Timesheet!F2:F55) but i want to edit the range everytime.
Can i have formula like if name = Ant merce and prj = Implementation support, then the sum will be 15 for the sumrange of Timesheet!F:F (entire F column)
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Sum Based On Multiple Conditions
I am trying to sum numbers based on three counditions. My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd* The yellow cell contains the formula I am using.
View Replies!
View Related
Trying To Use SUMIFS To Sum A Column Based On Two Conditions
I would like to use a SUMIFS statement to sum up a column of numbers based on two conditions. The first condition is a word. I have this done. The second condition is going to be a two week date range, which I am having problems solving. Here is my formula that works so far. =SUMIF(Data!C:C,"Office",Data!D:D) Now I would like to SUMIF a second condition is met. The second condition is a date and time in this format. 1/9/2010 9:21:49 AM I am only concerned about the date. Time does not matter. I cannot figure out how to specify a date range for the second condition. I am trying to specify between 1/1/2010 and 1/14/2010 as a test. So here is what I have so far with a SUMIFS. =SUMIFS(Data!D:D,Data!C:C,"Office",Data!A:A,"Cannot figure out how to specify dates") Maybe my logic is wrong...maybe I need to do three conditions? Where column A has a date < 1/14/2010 and a condition that specifies a date > 1/1/2010.
View Replies!
View Related
Sum Values Based On Two Date Conditions
I want to populate two cells (the yellow ones) with the start and end date values from the row where a match is found in the 'period' column for the value in the left-hand green cell and in the 'week' column for the value in the right-hand green cell. Is anyone else on the board firing on all/most cylinders today?
View Replies!
View Related
Sum Based On Multiple Conditions & Variable Range
I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached. This has been driving me nuts for a few weeks now.
View Replies!
View Related
Sum With Two Conditions
I want to calculate the sum of column with two conditions on different worksheets, Example Data Stock #QTYMonth 01335208Aug-06 01337402Aug-06 01337392Aug-06 01335400Sep-06 01337100Sep-06 01337102Sep-06 If the stock # is equal to the stock # and Month equal to month I need to get the result in another sheet as below. Stock #Aug-06Sep-06 01335208400 01337794202
View Replies!
View Related
Sum Under Two Conditions
what would be the most effective and easiest formula to use if I want to sum a range of numbers meeting 2 conditions (or more).. eg.. sum all the unpaid invoices (condition 1) due on March (condition 2).
View Replies!
View Related
Sum Between Conditions
I'm (and you are too!) still helping to streamline a charity's spreadsheet. There are two worksheets that this question refers to: "DD Tally" and "List of DD Donors" (where DD stands for Direct Debit). In the DD Tally worksheet we are trying to summarise the information contained within the List of DD Donors. We would like to be able to estimate how much, in direct debit donation, we will recieve in the financial year (currently, April 06 to March 07). We would like this to be seperated into our monthly, quarterly, semi-annual and annual donors. The challenge is knowing how to search through a column to find the, for example, "Monthly" entries and once these have been found to find the quantity of the donation(s) in the rows with "Monthly" in them, and then, to determine the number of months they have been paying for during the financial year (their first payment dates are listed as 01/MM/YY) - i.e, the difference between the end of the financial year (03/07) and the start (04/06) or between the time they joined during the financial year and the end. Then to multiply the number of months the direct debit has been active during the year by the amount they donate.
View Replies!
View Related
Sum Unique With Conditions
I am looking for a function or group of functions that will: Match Column C and for every unique item in column A, it will sum column D. Example: Column A: Column B: Column C: Column D: 12346 Level1 ACME 1 12346 Level1 ACME 1 12346 Level2 ACME 1 12376 Level2 ACME 2 13255 Level2 ACME 1 13548 Level1 ACME 4 12356 Level1 WIDGET 5 12356 Level2 WIDGET 5 12376 Level2 WIDGET 6 12378 Level3 WIDGET 2 12378 Level1 WIDGET 2
View Replies!
View Related
Conditions To Sum Every Other Row
Although I am not getting any errors, I am not having success returning proper answers with the formula below. Perhaps I cannot do what I am attempting. =SUMPRODUCT(IF(AND($K$22:$K$169="A&B",YEAR($G$22:$G$169)-YEAR('Info'!$F$9)=0),(MOD(ROW($R$22:$R$169),2)=0)*($R$22:$R$169),0)) where: K22:K169 are user entered categories from a drop down list G22:G169 are user entered dates F9 is a user entered update date column R are monetary values with first row being a revenue$ second row margin$ I need to summarize every other row (one row is revenue, one is margin) if the two conditions are met.
View Replies!
View Related
Sum If Two Conditions Match
I need a formula that returns the sum of cell N12 from multiple worksheets where cell A9 matches a particular month and cell K7 matches a dept name (such as Scientific). To explain further, in say 50 worksheets, I want to pick out all the March sheets, for Scientific, and sum all the N12 cells from those sheets. I've been trying to alter an array formula i've used previously, but gotten completely lost.
View Replies!
View Related
Sum Formula With Conditions
I'm having toruble developing a formula to allow me to add all the values in cells A1 through A100 if their respective quantities are more than 0. What i mean is if i have the following columns of data: A B 6 2 2 1 4 0 A being the values added and B being the quantity then i only want A1 and A2 Added together. Therefore the value would be 8.
View Replies!
View Related
Reporting With SUM And IF Three Conditions
I have been trying to create a report that involves three conditions, but so far I have had no luck using SUM and IF conditions to do this. I have attached a file with an example of what I would need. Basically, I would need the "Resolved" and "In-Progress" quantities filled in below the "Country Report" for each respective country.
View Replies!
View Related
Sum With Multiple Conditions
see attached workbook. I need to create a conditional sum in the report tab in the yellow cells that will sum all the values from the list sheet, based on the following conditions. 1) The name is in the list 2) The code in the list is one from the codes tab. I saw somewhere that I need to put an 'OR' statement in the conditional sum but I can't get it work. It seems to add up values for the code 'HOLIDAY' and I don't want it to.
View Replies!
View Related
Finding The SUM Using Various Conditions
I'm not sure if this is the best way to accomplish this task, but I've been trying to use countif to calculate the total of column C in my data based on the conditions that its respective cell in column A must be found and that column B must be yes. I can't use countif as it doesn't allow criteria relative to cells. Is it possible to use SUMIF?
View Replies!
View Related
Sum: Many Conditions-Criteria
I have recently started using formulas in Excel therefore cannot really crack this problem down. In my worksheet, I have a column (A2:A200) which stores dates starting from Jaunuary 1st 2006 to January 1st 2007. The second column (B2:B200) stores the expense type (i.e: Maintanence, gas, lease payment), and the third column (C3:C200) stores the amount spent on the given date and expense type. What I would like to do is to create a formula which calculates the amount spent on maintanence, gas, lease payment during January, February, etc:
View Replies!
View Related
Macro Sum Activesheet With Conditions
Group the same cells in each row, if found, in Column 2 that refer to same cells in Column 1 and sum the cells in col 3 and 4 that refer in column 2. Col 1 Col 2 Col 3 Col 4 1 272 0125 200 10 2 272 0125 100 5 3 273 203 500 85 4 274 ................
View Replies!
View Related
Sum If With 2 Conditions And A Wild Card
My live sheet is a lot more complex but this will help me well on my way. Ill let the example say everything: Sheet3 ABC1Condition 1Condition 2Condition 32red lorrybig £ 92,621.98 3yellow carbig£ 145,498.33 4yellow vanbig£ - 5pink carsmall£ - 6red carsmall£2,552,404.50 7pink lorrybig£ 862.37 8green carbig£ - 9green lorrybig£ 707,083.31 10red vansmall£ 30,010.73 11green carbig£ 2,276.13 12 13 14Total of all cars that are big: 15expected answer £ 147,774.46 Excel tables to the web >> Excel Jeanie HTML 4
View Replies!
View Related
Sum To Unknown Rows With Conditions
Need to sum through an unknown number of row generated by advanced filter at run-time Formula at C16 Data starts at row 57 (from copy/paste - advance filter) Last row unknown Criteria to match in A16 Data to be evaluated in B57:B (row unknown) Found this formula on microsoft.public.excel.misc =SUM(A57:INDEX(A:A,MATCH(9.99999999999999E+307,A:A))) Which I modified formy start row
View Replies!
View Related
Sum Ranges With Multiple Conditions
# of Project Names that are under Status=Contract (Amount in $’s ) & # of Trks # of Project Names that are under Status=V Verbal (Amount in $’s) & # of Trks # of Project Names that are under Status=No ($ Amount lost) & # of Trks lost # of Project Name that are Lost to (Grandview) that are under Status=No ($ Amount lost) & # of Trks lost Must be able to add more lines & have instructions so I can do it myself for the future.
View Replies!
View Related
Check Sum Then More IF Function Conditions
=IF(H10=0,IF((R10-I10)>$AH$1,(R10-I10),0),IF((R10-N10)>$AH$1,(R10-N10),0)) I have a small issue with the formula above. Basically the sheet is used to input start and end time of shifts and create breaks and lunches accordingly. The formula above resides in column S. Basically it warns us if the end of the shift is over 4.00 hrs past the end of the last lunches. A shift can have no lunch, one lunch or two lunches. H10 would be the start of the first lunch, R10 is the end of shift, I10 is the end of the first lunch, AH1 = 4:00 or 4:00:00 AM, N10 is the end of the second lunch. What happens is that if there is no lunch given in cells H, I, M, N or in this case H, I, N then I get a strange number. For instance, I put in 6:00 for the start time [E10] (actually we input military time and the result is displayed in AM or PM) and the end time as 8:00, then I get a result of 8:00 in the S column [titled +4hrs warning). 6:00 to 9:00 results in 9.00 hrs., etc, When we input a six hour shift, a lunch is automatically starts in H10 and ends in I10. Once this begins, the formula works correctly.
View Replies!
View Related
Sum The Range Of Data If Two Conditions Are Satisfied
My data is as follows MonthMilesAmountLocation June 8£3.20 Loc1 June 8£3.20 Loc1 June 8£3.20 Loc2 July 8£3.20 Loc2 July 8£3.20 Loc1 July 38£15.20Loc1 July 4£1.60 Loc2 Aug 38£15.20Loc2 Aug 4£1.60 Loc1 Aug 8£3.20 Loc1 Here is what I want to do - Avoide filtering and manually calculating data for the total expense in the month I want to SUM all the (3)AMOUNT in the (1)MONTH of JUNE for (4)LOCATION LOC1 I know its too much to ask for but can you suggest if i can combine SUMIF or SUM(IF) or any other formulas ???
View Replies!
View Related
Count And Sum Cells Meeting Two Conditions
I'm working out a schedule for work. Row 1 contains 31 days(columns), Row 2 28 days, Row 3 31 days...and so on for the 12 months of the year. I've formatted each Friday, Saturday, Sunday and Holiday with color. Fridays are blue, Saturdays are green, Sundays are yellow, and Holidays are red. Monday-Thursday are no color. Next, I fill in each day with an employee name. Now the hard part...I want to count the number of times an employee name falls on a Monday-Thursday, Friday, Saturday, Sunday and Holiday. At the bottom of the worksheet I'd like to see something like this: Jones: Friday 4 (total number of days jones is in a blue box) Saturday 5 (...on a green box...and so on...) Sunday 3 Holiday 2 Monday-Thursday 50 For each employee name. Sounds easy, right? I can't get it to work!
View Replies!
View Related
Determining A Sum Total When There Are Multiple Conditions
below the following lists (Name, Week, Amount) I am trying to calculate a sum of the Amounts column (Column D) for two conditions: a particular week # (Column C), for a particular color (Column A). Is there a simple formula to SUM numbers when it is based upon two conditions (color and week #)? For example, there are two separate RED amounts that should be received in Week 4 - how do I write a formula for the cell that correlates RED and WEEK 4 that states "sum the amounts for the Name: Red and Week: Week 4"? NameWeekAmount GreenWeek 1 5,000 RedWeek 4 13,000 BlueWeek 1 1,500 GreenWeek 3 3,500 GreenWeek 2 4,200 PurpleWeek 5 1,200 YellowWeek 4 500 GreenWeek 3 25,000 RedWeek 4 13,000 RedWeek 3 12,500 RedWeek 1 10,000 BlueWeek 5 9,000 BlueWeek 3 14,500 YellowWeek 5 3,300 GreenWeek 2 1,000 YellowWeek 4 30,000 Week 1Week 2Week 3Week 4Week 5 Red Yellow Green Blue Purple TOTAL
View Replies!
View Related
If ValueA Appears In Different Sheet, Sum ValueB With Conditions
I’m having difficulty writing a formula to solve the following dilemma: I have “Sheet1” with staff details and each staff member may be listed more than once. I need to use columns C (StaffID) and L (Load). Load values may only be 1.0 or 0.5. Then I have “Sheet2” which is being used to compile data from a range of sources – this is where I need to put my formula to get my answer. “Sheet2” has column A (StaffID) and each ID will only appear once on Sheet2. The formula required needs to solve “if the StaffID from Sheet2 appears in Sheet1 (keeping in mind it may appear multiple times), sum the Load values for that StaffID provided the Load value on each line only equals 0.5". If the StaffID doesn't match, the cell return is to be 0. I will then need to do this again in another cell where the Load value only equals 1.0 (obviously just amending the condition value in the formula).
View Replies!
View Related
Sum By Conditions & Rolling Monthly Total
I have a pivot table that summarizes expenses (cash advances, cashe remitted, etc.). The issue that I'm having is the way the data is displayed on my pivot table. When I adjust the custom calculations to "show data as Running Total in MONTH" I get the desired outcome on my row totals, but I do not get the correct figures on the actual data within the Pivot Table. When I remove this custom calculation and just "sum by value" then the data is correct, but the row totals are not. In a perfect world I would need the values to sum by value, while the row totals are set to "show data as running total in MONTH". I'm not smart enough to figure out how to produce both.
View Replies!
View Related
Sum Multiple Conditions & Subtract Result
Attached is a section of a spreadsheet containing many columns and about 20000 rows..these rows are made up of events.. named in column a. In column F I want A FORMULA to print the difference between each rating(in col C) and rating of THE ROW identified by a 1 IN the favrank col(col e) in that event. I did the first event manually to show what I mean.
View Replies!
View Related
Lookup Based On Two Conditions ...
I know that there are bunches of threads concerning lookups with multiple criteria, but I just can't figure out how to translate one to my situation. I want to return a value based on an item name which is in column A, and an operation which is in row 1. The array from which I need to look up the value contains part number in column B, operation in column G, and the actual value I need returned in column H. So I need to return something like this: ...
View Replies!
View Related
Get A Result Based On Several Different Conditions
New to the forum and in need of a bit of help. Friend of mine is in here regularly looking for assistance and tells me this is the best excel forum around. Thought I would put it to the test with a problem that is doing my head in. The following is a table which shows a salesmans sales. He or she has sold to 8 customers. What I need to do is show in the commission column how much they should get paid for the additional products based on this criteria. 1. If the penetration is equal to or over 41%, and he has sold the additional product for max profit of 250 then he should get £40 2. If the penetration is less than 41%, and he has sold the additional product for max profit of 250 then he should get £25 3. If he hasnt sold the product for full amount then he gets 10% of the profit, regardless of the penetration ...
View Replies!
View Related
Display Value Based On 2 Conditions
I have a workbook with 2 worksheets. The first sheet provides a summary showing the last date a person attended a specific course. The second worksheet is a list of all courses, attendees, dates, and status. I need a function that will match the attendee and course from the Master and display the date from the master in the corresponding cell on the summary sheet....
View Replies!
View Related
Lookup Based On A Few Conditions
I am wondering if I can do a formula that would tell me if the name that appears in column J or K appears more than once at the time/date slotted in columns A and B. Basically, I want to make sure that the name(s) in column J and K aren't assigned 2 different places at the same time slot (column B) on the same say (column A).
View Replies!
View Related
Lookup Based On 2 Conditions
In the sample that I have attached. I am trying to compare Control ID and Business Date in Sheet[Test] and Sheet[Perform], If they are the same, then populate in Sheet[Perform] Column Test Status with the corresponding row in Sheet[Test]. note that Control ID and Business Dates are not constants.
View Replies!
View Related
Summing Based On Multiple Conditions
I have been trying to search an array and cannot find the correct combination of functions to get the information I want. I might need a macro to do this but I will try anything. Here is an example: 1 A 1 1 B 2 2 A 3 2 B 4 3 A 5 3 B 6 I want to search for 3 and A and return the value in the 3rd column. I have tried several functions but cant figure out how to make sure I get the correct value in the 2nd column that corresponds to the value in the 1st column.
View Replies!
View Related
Cell Value Based On Multiple Conditions
I need to create a macro that will calculate a value based on the contents of multiple cells. Looking at the example attached, if columns A and/or B (employee ID and name) are empty, then allowable OT (G) should be 0. If either have data, then if Stage (F) is CAN, G should be 20. If Stage is FAS or FAR, G should be 10. I'm not very savvy with IF statements, which is how I imagine this can be done, and I don't know if this would be easier to do as a macro or as a formula within G. Since the contents of the of the cells will be changing on a weekly basis, I'd prefer the formula to only be there if there is content on the line, so we don't have nulls showing.
View Replies!
View Related
Reset Cell Value Based On Two Conditions
I am trying to build a spreadsheet that tallies hours worked and hours available for the next day. The key to this spreadsheet is the number 70. A worker can accumulate no more then 70 hours in an 8 day period, however after any day with no hours worked the rule resets to 70 hours available again. I have attached a sample spreadsheet so you can see how this should work.
View Replies!
View Related
Selecting Values Based On Conditions
find a book with two sheets. One contains data and the other is the report that I must generate. In sheet one ull find the list of all the trucks received. I would like to see all trucks that I have received in the month of say June in the report sheet as per its arrival date when I change the month on the top of the report.
View Replies!
View Related
Extract From List Based On Conditions
I want to know how I can make Excel look at a list of information on one worksheet, extract certain items from the list, and place it on another workshet For example, the following list would be imported from an inventory program as text and pasted on worksheet 1: P+green beans d-2.99 c=2 cd+10258 p+cherry tomatoes d-3.99 c=1 cd+11204 p+red apples d-.69/lb c=3 cd+08902 p+tissue d-1.99 c-2 cd+20023 How can I have Excel take this information and create a list like the following on worksheet 2? ...
View Replies!
View Related
Calculate An If Value Based On A Number Of Conditions
I am trying to create on excel order form. I want customers to be able to input the item # (a range from 1 to 12), then I want the to price to be calculated based on the item # they input. For example. If they choose item #2 in A6 then the price in F6 will be recorded as $8.00. (the price would change for each item # they input). the formula I started out with was: =IF((A6=1),"$8.00") this worked for me if A6 did in fact equal 1. So I tried adding this equation to the formula. =IF((A6=1),"$8.00")*OR((A6=2),"$7.00")....this would continue on. I even pressed "command return" after the statement as if I was entering an array formula. I got the error #VALUE!
View Replies!
View Related
SUMIF Based On 2 Conditions From 2 Columns
Have had a search around the web forums for an answer to this and am not getting anywhere. In column A i have text - apr, may, jun etc In column E i have general format text - a 6 figure number in column M i have text - eg 886.97 (a currency, but in text format) i want to: where column a=apr and column E=212130 sum the contents in column M
View Replies!
View Related
Returning A Text Value Based On 2+ Conditions
I'm trying to lookup values that are a combination of text and numbers. The difficulty is I need to use two criteria to look it up. I've been trying to use a combination of Sumproduct and Index,Match but it doesn't seem to be working. I think I need to add some sort of text function in there. Below is some data for example purposes. A B C E F G M S Get value from G M S 5G M T M T 6M M U M U 7H M V M V 8K
View Replies!
View Related
Average Based On Multiple Conditions
Im currently trying to report on cycle times for a certain task that is carried out on a daily basis. I have used NETWORKDAYS to calc each rows cycle time in Cell R14:R512 the code is:- =IF((NETWORKDAYS(O14,N14)-1)*($AH$11-$AH$10)+IF(NETWORKDAYS(N14,N14),MEDIAN(MOD(N14,1),$AH$11,$AH$10),$AH$11)-MEDIAN(NETWORKDAYS(O14,O14)*MOD(O14,1),$AH$11,$AH$10)=0,"",(NETWORKDAYS(O14,N14)-1)*($AH$11-$AH$10)+IF(NETWORKDAYS(N14,N14),MEDIAN(MOD(N14,1),$AH$11,$AH$10),$AH$11)-MEDIAN(NETWORKDAYS(O14,O14)*MOD(O14,1),$AH$11,$AH$10)) This is returning the cycle time providing both the start date/time and end date/time are filled in, or it returns blank if nothing has been filled in. I have then tried to calc the average overall cycle time in Cell R12 by using the below formula = SUM(R14:R513) / (COUNT(R14:R513) - COUNTIF(R14:R513,0)) This seems to be working proved i have two rows of data filled in. My problem lies when all the cells are blank, it then returns the DIV error. I have tried using an if statement to ignore if the cell is blank but it still returns the DIV error. Im really at a loss on this and now im not even sure if i have gone about this in the right way or if there is a better way to approach the whole task? I have attached a sample workbook to try and better explain.
View Replies!
View Related
Extract Certain Data Based On Conditions
method to extract certain data from a column of values. The attached workbook has two sheets. From the worksheet named "Data" I would like to be able to extract any values >0.00% and then display the corresponding "model" (C:C) on the other sheet called "Results". I need to be able to achieve this for each day in the month and disregard all other values so I am left with a concise list. I have manually entered the info for the 1st June as an example.
View Replies!
View Related
Add Sheets Based On Conditions
i am not able to solve this problem. 1- I have 3 tabs in this worksheet with the data - Tabs A , Master Data and refernce Table 2- Suppose column K in the tab " Master Data " has a record or number, they can be repeated as the new data us added every month. 3- Reference table has the list of the same data record ( numbers ) . Now the situation is if there is a new record in the column K of the master data sheet the Macro should do the following. a- Check for the record in the reference table , if doesnt exist then add the record in the reference table b- create another sheet same as the sheet " A" (duplicate of A) and name it the new record number b- Plug the new record number in the cell B2 of the new sheet c - Copy the row 8 from ( D8 to O8 ) in the new sheet from (D8 to O8)from sheet A
View Replies!
View Related
Return Data Based On Two Conditions
I have very limited vb experience and I believe it’s the only way I can do what I need to get done. I am working on creating an invoice template. I want the price to populate based on two conditions. The two conditions are invoice line quantity and month. Let me provide data ranges. The user will enter a quantity for each invoice line moving down B17, B18, B19, B20 etc up to 24 lines. (through B40). The user will enter a current date in F13. Away from the main body of the invoice: Range T5 thru T16 will be pre-populated with each month of year 2007. So T5 = 1/1/2007, T6 = 2/1/2007, T7 = 3/1/2007 etc. Range U5 – U16 will be pre-populated with the line price for each month if the line quantity is less than 10 Range V5 – V16 will be pre-populated with the line price for each month if the line quantity is 10 or more The price should autopopulate in G17 - G40 where a quantity is placed in the corresponding column range B17 - B40. The number of lines varies per invoice so I'm setting the template up with sufficient lines. As an example, the price should populate for each invoice line beginning in G17 to correspond to the month entered in F13 and quantity entered in B17 based on the corresponding month in T5-T16 and the price in either U5-U16 or V5-V16 given the quantity. So if the Month in F13 was February and the quantity in B17 was 25, the price that populates G17 would come from V6. If the Month in F13 is March and quantity in B17 is 5, the price that populates G17 would be from U7. I just don’t have enough experience to write this myself.
View Replies!
View Related
Copy Range Based On Certain Conditions
I have this macro to copy a range to another worksheet: Sub transpose_UPCID() ' Hace el copy y paste transposed en BAUCS de los numeros de UPC"s y ' las cajas ATR Dim rng1 As Range Dim rng2 As Range Set rng1 = Sheets("Sales-Inventory").Range("UPC", Range("MK_ID").End(xlDown)) Set rng2 = Sheets("Sales-Inventory").Range("Figure", Range("Figure").End(xlDown)) rng1.Copy Sheets("BAUCS").Range("C11").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True rng2.Copy Sheets("BAUCS").Range("C37").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True Application.CutCopyMode = True End Sub The only problem is that this code copies all the items and I need this code to look into Column A in the Sales-Inventory worksheet. There I have a data validation with a total of six options. I also have six differents worksheets where I need to copy the values based on that data validation. To give you and example, all items containing XX in column A copy to XX worksheet. Something like that.
View Replies!
View Related
Flagging Duplicates Based On Multiple Conditions
Flagging Duplicates based on Multiple conditions.. I have attached the file for reference with Dummy Data as the actual data runs in thousands... What I require is to flag Duplicates with some formula so that I can filter and then later delete those specific rows from the records... The Duplications has to be considered on Multiple criterions: 1. If the FNAme and LName is appearing for one Company more than once. 2. Sometimes there would be inconsistency in Data Entry, so the FNAme would be typed in the Column for LName and vice-versa.The code needs to understand the same. 3. Alongwith the above, if the Designation is different then the record with the Lower Designation based on the Weightage should be Flagged as "Duplicate". Ex: - 4. If there's a tie between the same designation then the record with the Maximum information across all the columns having Max info should be Unique. Ex:- The amount of details mentioned in the following columns would determine the record to be termed as Duplicate or unique 5. If still the records are identical then the first occurence can be unique... I have made this data to give an example as the actual data is very confidential. Please allow room for adding New Designations as the Designation and their Weightage needs to be Dynamic. Please refere the Word doc Duplication Criterion Explanation for the explanation.
View Replies!
View Related
Populate List Based On Two Selected Conditions
I have a worksheet which contains all the details of the medical equipment in the county that i'm responsible for. This worksheet posesses amongst others; two columns titled location and sublocation. I need a technique which will allow me to select a location from a dropdown list, select a sublocation again from a list and for these two conditions to then populate a list containing data such as asset code and description of all the items in the location and sublocation (possibly a little like an advanced filter, but only for the selected conditions) This list will then be used to provide an engineers report on equipment holdings at various locations. I'm open to any ideas fellas.
View Replies!
View Related
|
TRACKER
