Remove Multiple Data By Multiple Conditions?
Aug 21, 2009
1. I have 4 columns (let's say D,E,I,J);
D E I J
Account1 Sum1 Account2 Sum2
151 93,79 BF2479 30,00
1476757 93,79 BF7 22,00
BF2479 30,00 151 93,79
86041 84,03 23235 30,00
534 6608,45
2. I need to clean the multiple identical data in the list by both Account no. and Sum (of course the list is much longer!) so that the list should look like this:
D E I J
Account1 Sum1 Account2 Sum2
1476757 93,79 BF7 22,00
86041 84,03 23235 30,00
534 6608,45
I must say that I am a beginner in Excel, so I tried to use this:
=IF(OR(E5=$J$5:$J$430);IF(INDEX($I$5:$I$430;MATCH(E5;$J$5:$J$430;FALSE);1)=D5;"";E5);E5)
for column E, and
=IF(OR(J5=$E$5:$E$452);IF(INDEX($D$5:$D$452;MATCH(J5;$E$5:$E$452;FALSE);1)=I5;"";J5);J5)
(which is reverse of the first one) for column J; (The ranges are the ones used in the real sheet), not for the ones in the example above!); The formula does seem to eliminate a dew unwanted data, but does not give me all the wanted results!
View 4 Replies
ADVERTISEMENT
Oct 8, 2007
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.
This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.
The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.
Here is a working formula for only one page.
=COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))
Here's 2 problems with this formula:
1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.
2. I don't know how to make it work across several sheets.
This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.
SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))
View 11 Replies
View Related
Dec 29, 2009
hope the below example is clear to understand and makes sense .....
View 10 Replies
View Related
Dec 27, 2012
What I would like to do is combine all data (from sheet: Page1,Page2, and Page3) into one sheet named 'Consolidated'. Unfortunately, as you will see from the attached file, my programming and understanding of VBA programming is pretty naive.
What I am after is, when I combine the data, from all sheets into the one, the data should be
1. Automatically Sorted by Patient Name
2. Group, results of the same patient from all sheets one below the other in seperate rows
3. Delete any other rows that may have a patient name and ID, but rest of the rows (New Mole,Clinically Suspicious......Benign Naevus) are blank. (This happens because of the way I have designed my userform. For first visit the details are entered in Page 1, then for second Visit Details are entered in Page2, and for third visit in Page 3. So when I enter the first visit details, the Patient Name and Patient Number are copied into Page 2 and Page 3 even though the other details remain blank. I have done it this way because, if say the Patient comes for visit the second time, the clinician may or may not be aware which visit this would be for the patient. So as soon as they enter patient number, it will give them a message that Patient already exists. So when they click Open Existing Patient Record, the form autopopulates with any notes from their previous visit into the respective page (i.e Page 1,Page 2 or Page 3).
Page 1 (Page 2 and Page 3 are exactly the same)
Patient Name
Patient Number
New Mole
Clinically suspicious
Changed from mapping Photo
Level of Suspicion: Score
Monitor
[code].....
View 3 Replies
View Related
Jun 16, 2009
I am far from advanced in excel, but I have been tasked with creating a macro which includes filtering.
1) I have a workbook called Workbook1
2) I need to copy Sheet 3 from Workbook1 cells A1-J5000 into a new workbook called Master.
3) Column D is labelled Status and the two statuses are Enabled and Disabled.
4) I need to copy anything labelled Enabled to a new worksheet in the workbook already created called Enabled and then the same for Disabled. I then need to put it into date order which is column F labelled Date.
I have been trying to work on this all morning, but have found that Excel doesn't like Macros with filters and I don't know the first thing about VBA
View 4 Replies
View Related
Dec 15, 2007
I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet to help.
The suggestion of an Index with a Match Array worked but the problem is that my spreadsheet has over 5000 rows and my data has to meet three criteria = 15000 cells to cross reference. And I need to set up a spreadsheet that would do about 300 arrays. So obviously my excel is soooo slow I can't even get 10% complete.
Any other suggestions? I've read (from Dave I think) that database is a powerful alternative but have not been able to find any info on this and it's not an Excel function. For your reference I'm attaching a demo spreadsheet of what I'm trying to do with the two Index + Match arrays that work for small files, not mine.
I looked into Pivot Tables but it can't handle all the row and column headings.
View 4 Replies
View Related
Jul 26, 2014
Want to be able to select data from Column C (3 data validations/drop down menus) and have the corresponding values display in the appropriate fields in column H. Not sure if this is possible?
View 4 Replies
View Related
Oct 16, 2009
I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions.
So, overall here is what I want:
If DL2=3 and CK2=1, then I want this to be labeled as '1'
If DL2=3 and CK2=2, 2
If DL2=1 and CK2=2, 3
If DL2=2 and CK2=2, 4
If DL2=1 and CK2=1, 5
If DL2=2 and CK2=1, 6
These 6 conditions cover all possible combinations of numbers in the two columns.
View 13 Replies
View Related
Apr 4, 2013
This is a very SMALL sample of the data I need to sort through. We have been using filters but the data is growning and becoming far too time consuming using the filter method.
The output I am looking to achieve is something like this, (any format is fine; whether in rows or columns)
SVR1
1.99.99.254
(2) CAT I (display how many CAT I for SVR1)
CAT I: 2011-B-44
CAT I: ST-5546
(2) CAT II (display how many CAT II for SVR1)
CAT II: 2011-B-52
CAT II: 2011-A-21
SVR2
2.5.2.333
(1) CAT I
CAT I: 2012-E-55
(1) CAT II
CAT II: 2011-A-21
COLUMN A, varies from SVR1-SVR1400 (cointains duplicates)
COLUMN B, IP matches SVR name (contains duplicates)
COLUMN C, will be either CAT I, CAT II, CATIII
COLUMN D, will contain duplicates
I have some history of using formulas and nested formulas but this one is really throwing me for a loop. It feels like Inception to me and I'm not really sure where to start!
A
B
C
D
SVR1
1.99.99.254
[Code] .....
View 9 Replies
View Related
Nov 29, 2008
My data looks somewhat like this :
X Y Z
DOU Quarter Sum
0 Q1 = sum of 1's in Q1
1 Q1 = sum of 1's in Q2
1 Q2 = sum of 1's in Q3
0 Q2 = sum of 1's in Q4
1 Q3
1 Q1
1 Q4
0 Q2
A cell in the SUM column must contain the sum of 1's in column DOU if under column Quarter the value is Q1. Similarly for Q2, Q3 & Q4 if only the corresponding value under DOU is 1.
Its like.. count the number of 1's per quarter and come up with the sum in 4 distinct cells for each quarter.. i.e. quarter-wise sum/count of 1s
View 9 Replies
View Related
Aug 9, 2009
If ActiveCell.Value < -25 And ActiveCell.Offset(-2, 0).Value < -25 And ActiveCell.Offset(-3, 0).Value < -25 Then
Range("C4").Select
End If
End Sub
I am looking to do something similar on excel 07 as follows:
Range("O16").Select
IF ActiveCell.Offset(0,-1)Value ="1" And ActiveCell.Offset(0,-2).Value = "1" Then
ActiveCell.Value = "1"
Compile error expected: Then or GoTo At the First value (which I have highlighted in red)
Also would like the If formula to repeat for range O16:O36.
View 9 Replies
View Related
Jan 29, 2007
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 3 Replies
View Related
Nov 29, 2013
I cant seem to rack my brain around how to go about making a formula in order to achieve my desired effect using the IF Formula. The data is as follows:
Worker Status %
S1 75% and higher
S2 42.8571% -74.9999%
SH Anything below 42.8571%
My sheet pulls the workers status from a preset data table, lets say S2 for Joe. I want the cell beside his status to compare what his percentage is and display what the status should be (based on the conditions of the above table). If hes working 73% it will turn up S2 and I can compare the value from the table and the value generated based on his working percentage (I would use conditional formatting to color the cell red or green to indicate if it matches or not.)
View 4 Replies
View Related
Dec 17, 2013
Is VLOOKUP suitable for if one wants to lookup a value from a defined range based on two assumptions?
Enclosed is an example file.
I want to lookup the values in sheet1 in the defined range in sheet2 and write enclosure and density to sheet 1
what method to use?
nicolaiBook1.xlsx
View 6 Replies
View Related
Mar 1, 2014
My spreadsheet looks like this:
Column A: Individual
Column B: Not used in formula
Column C: Not used in formula
Column D: Location
Column E: Duration in Minutes
Column F: Pay (Column E)*(Column G)
Column G: Formula
(simplified version listed below)
[Code].....
To be clear of what I'm trying to achieve:
When in location 1 & 2 (Column D), the pay for each individual is a different rate depending on the duration in Column E, but when in Location 3, the rate is consistent no matter what the duration.
My question is:
How do account for individuals who aren't individual1? That is, my understanding of the above formula is that I need to list each individual that will appear in Column A for it to work. What if I just want to apply the above to individual1, but apply some other calculations to all other individuals?
I had tried adding these lines (with their corresponding closed-parentheses at the end)
[Code]....
But it doesn't work. I'm guessing it's some sort of OR, but I don't yet grasp how.
I should note that my formulas are all on one line in my spreadsheet, I just have them on separate lines to make it easier to find my way around.
View 14 Replies
View Related
Aug 4, 2005
I have a table in Excel:
The first row is time in years.
The second row is method name,say,"A","B","C".
I want to count the number when the time is less than 5 years AND "A"
method is adopted.
I tried this:
count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work.
how to revise the formula?
In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as
countif(C2:Z2,"<5")
View 9 Replies
View Related
Jan 4, 2007
I'm trying to get a sum only if 2 conditions are met.
Column A has dates (ex. 01/02/2007) in a random order. Column B has a location in a text format (ex. London). Column C has a series of values formatted as [h]:mm.
What I'm trying to do is get a sum of the values in column C where the corresponding values in columns A and B = the date and loction I specify. I've tried...
=SUM(IF(('Jan ''07'!A$2:A$750="1/2/2007")*('Jan ''07' E$2:E$750="London"),'Jan ''07'!J$2:J$750))
but it's returning zero values regardless of the criteria being met or not.
View 12 Replies
View Related
Nov 16, 2008
I have a table like this:
Name (A).....Date-in (B)....Date-out (C)
Name1........27/12/2008....3/01/2009
Name2........25/11/2008....28/11/2008
...
I want to count the rows (in the entire table) that B and C dates intersect with a reference dates (say J1 and J2).
It is a booking table so I want to know if the apartment is available for the reference dates (i.e. no bookings for that days).
I tried first a simple double conditional to know if a date is inside two dates but it didn't work:
=SUMPRODUCT(--(B:B<J1),--(C:C>J1))
View 9 Replies
View Related
Jan 13, 2009
I have the following formula entered into cell O4: =IF((AND($K4="1",$B4="1 - C",$I4="open")),(1),(0)). When the three conditions are met in the corresponding cells, I get a "0" returned in cell O4. I would like the formula to return a "1" when all three conditions are met.
View 2 Replies
View Related
Jan 15, 2009
I want cell B to be blank if 1 of 2 different conditions are met. If Cell A is blank then so too should be Cell B. If cell A contains data, then I want cell B to perform a formula $D$1-E6.
However, if Cell C contains data then Cell B should return to a blank status.
View 6 Replies
View Related
Apr 14, 2009
In my Sheet "List" I have list of persons working on different projects.
I prepared graph after putting conditions on Project Type, Project Size, Project Year & Position (PM Project Manger). Every thing was done a in a nice manner with the help of below formula.
PJ TYPE , PJSIZE, PJYEAR, POSITION are ranges names.
=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"))
But the problem was occured that in a year if a person work on small project more than once then he will be counted only once. But if he has worked in same year on Medium or Larage project then they will be counted separately. I tried to oversome the problem with the help of Pivot Table and put manually some legend P1, P2 & P3 against the person name if he is working on same type of project in same year. then count only P1 in my formula to count how many Project Manager worked on Project. like
=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"),--(PMCOUNT="P1"))
Now i am trying that in a separate columm of # of PM there must be a formula which only put P or 1 for a person if he is working on same project in a year but i want that p or 1 only appear against his first entry i duplicate. for other persons it automatically enter 1 or p if they are appearing only once. i have tried a lot while using countif with multiple conditions but all in vain.
View 3 Replies
View Related
Aug 28, 2009
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 4 Replies
View Related
Oct 27, 2009
I currently have this equation: =AVERAGEIF($B$2:$B$900000,">="&L5,$C$2:$C$900000). Where L5 = 100. I'm interested in having the formula averageif B column value is between L5 & L6. Where L6 = 200. So I want it to find all values in B$ that are between >= 100 but < 200, and then average their C$ counterparts.
View 3 Replies
View Related
Jan 28, 2009
I'm having some problems getting a simple Do While loop to run. Essentially I'm trying to get it to run as long as one of two conditions is met, so I used an Or function in the loop condition. This then produces the "Run time error 1004: Application defined or Object defined error" error on that line.
I'm certain my syntax for the two conditions is correct (one is a simple check value in a cell, the other is check the value of a VBA variable) so it would appear the error is caused by the inclusion of the Or function. So I guess my question is this, have I done something wrong? If so what and if possible why is what I did wrong (I like to understand these things)? Is this something that has to be done in a different way or even is it something that cannot be done? I'm using excel 2003 on XP SP3.
View 5 Replies
View Related
Dec 3, 2005
way to return a value from a data table by specifying 3 conditions to be met.
Eg. My data table is in cells W1:Z100
(Column names = Track, Distance, Class, Time)
I want to be able to return the time value, based on track, distance and class values.
View 9 Replies
View Related
May 31, 2006
My book is telling me that I can't use multiple conditions with a SUMIF
statement but other sources are telling me I can. Either way, it's not
working!
My situation:
Look at data in $V$2:$V$144 (range = "group"). If cells in "group" =
"Broth" then look at $Y$2:$Y$144 (range = "bldg"). If "bldg" = "1" then sum
corresponding cells in $R$2:$R$144 (range = "fcst").
This is one of my attempts - feeble...
=SUM(IF(group="Broths",IF(bldg="1",fcst,0)))
View 9 Replies
View Related
Dec 22, 2006
I need to set up something like an IF formula that will populate 1 cell based on which of 11 possible values will be represented in another cell. I can do this up to 7 with no problems using the following IF statement:
= IF(G23="1","a", IF(G23="2","b", IF(G23="3","c", IF(G23="4","d", IF(G23="5","e", IF(G23="6","f", IF(G23="6","g",IF(G23="7","h", "OTHER"))))))))
But if I try to put more than 7 conditions in there it tells me I've entered too many arguments. Is there a way I can either use more than 7 arguments or some other formula
View 9 Replies
View Related
Nov 10, 2008
My worksheet contains a database of systems that have been installed since 6/08. Each system has its own unique serial number, however, this serial number appears more than once.
I'm trying to count the number of systems that have been installed since 9/08/08 but I do not need Excel to count each s/n entry, I only want it to return a count per s/n once to get a total of all installations since this date.
I have attached the sheet in pk zip format.
View 8 Replies
View Related
Jan 8, 2009
ABB
MetricsCategoryJan 2008VisitsTravel50view_offerTravel10
Above is the data I want to lookup on. I need to be able to do a lookup for travel visits and a seperate lookup for travel view_offers.
Is there someway to do this with a vlookup or maybe match index?
View 9 Replies
View Related
Jan 25, 2012
I am trying to use multiple "ifs" and then summing the cells that obey my conditions.
Let me give an example:
Column A - place
NY
Chicago
Los Angeles
San Francisco
Column B - object
gate
keyboard
pencilcase
cellphone
[code]....
I want the ifs and sumifs to:
Check if place = NY
Check if object = keyboard
Check if condition = failure
and sum the prices when all of the above are true.How do I do it??
View 4 Replies
View Related
