Repeating Criteria Between 2 Values Using MAX
Jun 27, 2009
I'm having trouble delivering a MAX value within a range.
In column A I have a running list of numbers, in column B I have a yes or no answer and in column C I'm trying to write a formula that will deliver a MAX value from the A column inbetween occurancies of the Yes and No values from the B column.
I'm trying the SUMIF function but it seems to be proving problematic.
This is what it would look like.
A B C
1 Y 1
2 Y 2
3 Y 3
2 Y 3
1 Y 3
2 N 2
7 N 7
6 N 7
5 N 7
3 N 7
2 N 7
6 Y 6
9 Y 9
3 Y 9
It is almost as if I'm trying to get the formula in the C column only find the latest MAX value from where it currently is to when the last changeover happened from Y to N.
I can't find the Max dependent on a yes or no answer, but I'm struggling to make it only look at the last batch of Yes's or No's!
View 9 Replies
ADVERTISEMENT
Apr 20, 2006
sorting data I use in a workbook for athletics. I've really chopped down my workbook for upload, In the worksheet "Leaderboard" I can call up stats for different lifts, and it finds (in this case) the top 5 lifts and the names for the kids that have those corresponding lifts.
My problem is that when two or more kids have the same lift, it will only call up the name of the first instance of that lift. You can see this in the "Leaderboard" worksheet, and the name "Adams, Andrew" appears for both lifts of 75. I would appreciate any help on how to correct this, as I've searched the Internet for weeks now looking for a solution. I've tried experimenting with different things as well, all to no avail.
View 9 Replies
View Related
Jan 5, 2013
Okay, so this is what I am using:
[Code] .......
It is finding the name for a value from a table of values, the only problem is that there are more than one names which correspond to the same value. It doesnt matter to me which name it retrieves, but it lists the same name over and over again, where i'd prefer if it used a different name each time. Any way to keep it from using the same one?
Example1 (pcb).xls‎
View 12 Replies
View Related
Oct 29, 2009
I have a spreadsheet in which data is dumped from a database, there is a dump (on a new tab) for each site.
I need a summary sheet which will add up the two tabs. My problem is however is that I can not use Vlookup as the same codes are used and it will only return the first occurrence.
e.g.
444588 Prime Wages/salaries
This code appears under Production prime labour and Distribution Labour but will only ever return the production prime labour value
Another example would be
444586 Ohd Wages & salaries
This code appears under Production Overhead, Site Overhead, Sales overhead and Admin Overhead this would only ever return the production overhead value only
Is there any way that I can get the summary sheet to use a formula where it looks up the department and then the code? I was thinking index and match but I was unsure if this was possible also.
Worth noting is that the summary sheet has a list of all codes that could be used but the dumps will only ever have values if a cost has been occurred, there for the number of rows in each department is not consistent. There will be a number of tabs however in the attached example I have only provided two.
View 14 Replies
View Related
Feb 15, 2010
It has several steps and i will now just explain the least complicated one, and hopefully once i get this done i will figure out the rest myself. If not then i will need some more help
I have 500 entries (names) in column A and each entry has a coresponding category in Column B (Large, Medium, Small).
I want to see how many entries are there for each category, but i need to count the names which appear in the list only once. If the name is entered more than once then i dont need to count it.
I hope i made it clear as i am not able to provide excel table as an example do to security settings.
I am usinng Excel`03
View 9 Replies
View Related
Sep 24, 2006
How can I modify the codes below so that the results in the listbox would reflect only the actual data? Some previous information stays in the listbox making the search inaccurate. I have tried to clear the listbox first before querying another but results are the same.
On the first loop, the queried items are accurately listed in Worksheets("Search"). For example, if the data being searched on has only one result, entries in this sheet would only show one. But when it is shown in the listbox, it at most times show previously queried information.
Also, how can I dynamically adjust the array so that no vertical scrollbar would appear if the queried results fit perfectly in the listbox? The array below would automatically assign 10 rows in the listbox even if the information shown consumes only less than 10 rows.
Option Explicit
Dim MyArray(10, 5)
Public MyData As Range, c As Range
Sub FindCaseNo()
Application.ScreenUpdating = False
Dim FirstAddress As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE, fndF, fndG As String
Dim fndH, fndI, fndJ, fndK, fndL, fndM, fndN, fndO, fndP, fndQ, fndR, fndS As String
Dim head1, head2, head3, head4, head5 As String ' heading s for list
Dim i, intFind, intCount As Integer
i = 1
View 6 Replies
View Related
Nov 25, 2007
working on grouping coordinates because he we wants to simplify data. But he works manually and it eats his time. He has some sheets with data. I suppose there are 7 sheets. Each sheet has coordinates shown on example below. Also there are intervals between pairs of columns, so there will be column A with B, interval of two columns C & D, column E & F and interval of two columns G & H and so on (altogether about 10 columns of coordinates per sheet). Every coordinate begins with argument '1' and increasing gradually by 1 (it is column A). Second column shows values of arguments. But some are repeating, some not. I would like to double these coordinates, where in one argument is only one value (for example these are row 1. and 11.). If there are two the same values I would keep them. But if there will be more values than two, for example row 5. - 8., I would delete row 6. and 7. from the middle. But border coordinates should be kept (row 5. and 8.) and split by moving up lower coordinate to it with rest coordinates. But I want do it separately for every two columns which contains these coordinates, so if I delete coordinates from columns A and B and split boundary coordinates I don't want do it for columns E&F, I&J...
Examle:
Before:
A B
1. 1 1
2. 2 2
3. 3 2
4. 4 2
5. 5 3
6. 6 3
7. 7 3
8. 8 3
9. 9 4
10. 10 4
11. 11 5
12. 12 6
13. 13 6
14. 14 6
15. 15 7
16. 16 7
.. .. ..
After:
A B
1. 1 1
2. 1 1
3. 2 2
4. 4 2
5. 5 3
6. 8 3
7. 9 4
8. 10 4
9. 11 5
10. 11 5
11. 12 6
12. 14 6
13. 15 7
14. 16 7
.. .. ..
View 9 Replies
View Related
May 22, 2014
I have 1 column of pasted values that basically look like this
c
1:a
2:b
3:c
1:d
2:e
3:f etc...
And they need to look like this:
c1 c2 c3
a b c
d e f
I know how to separate the first column into two columns by separating the values by the colon. But how do I rearrange the values as above and combine all "1"s in c1 so it becomes the headers and the column is populated only with the values? The table is too big to do manually (thousands of rows).
View 1 Replies
View Related
Oct 23, 2009
Matching Multiple repeating values in one column with another.
I have a three columns of data that I need to map the requird Ids in Col A against multiple repeats in Col C. AS per data below ....
View 6 Replies
View Related
Mar 20, 2014
I have data that is something like this:
Group Assets
1 200
1 300
2 150
3 50
1 500
3 100
I want to create a scattered plot charts based on the data above but I cant get it to work for some reason. I want to have the "Group" on the x-axis and the "Assets" on the y-axis. I don't want to combine the same group number (for example to have group 1 with total assets of 1000, I want to keep it as 200 300 and 500 because I want to show which group is more represented than other group.). I have tried sorting the data by group and also with out sorting but the data doesn't plot at all (everything looks clustered at the 0 level on the x-axis instead of the respective asset level.
View 1 Replies
View Related
Feb 4, 2013
I am using Excel 2011 on a mac. I have a selection of data that lists a repeating code (say group 1, group 2, group 3 etc)and a value (-1,-2.3,3,6 etc) and need to find a quick way group together all the codes and next to that a value that counts how many values who share the same code hold a value greater than -1? I can do this myself in a pivot chat with count totals but my issue is how I can show the number of codes greater than -1.
Attached is an example of the data I want to sort.
Example of data that needs sorting.xlsx
View 1 Replies
View Related
Jul 6, 2006
I have a list of two columns. Here’s an example. The left most column provides the row number.
_ A B
1 1 0
2 2 1
3 2 1
4 3 2
5 4 2.5
6 5 4
7 1 0
8 1 0
9 2 2
Whenever there are two repeating numbers in column one, I want to reduce certain numbers in column 2 by a certain amount. The amount is determined by half the difference between the number in column B corresponding to the second repeated number and the number in column B corresponding to the row after the second repeating number. The range of numbers that are to be reduced begins with the row after the second repeating number and ends with the last row before number one appears in column A. The values in column A are integers, always starting with one. For example, 1, 2, 3, 4, 1, 2, 3, 1, 2, 3, 4, 5.
For the above example, after finding the repeating number two’s at A2 and A3, it would reduce B4 through B6 by half the difference between B3 and B4 (1/2). The values for B4 through B6 would be updated in column B. The same for the next repeated numbers, which is one at A7 and A8. Half the difference between B8 and B9 is 1.
Here’s what the updated list would look like:
1 1 0
2 2 1
3 2 1
4 3 1.5
5 4 2
6 5 3.5
7 1 0
8 1 0
9 2 1
View 9 Replies
View Related
Apr 5, 2014
What I would like to do is return the total qty of items on the 'QUOTATION' tab that are upholstery and this having been determined by whether there is a Y (for Yes) or N (for No) against the equivalent item in the 'BASIC_LIST' tab.
So in the below example, the total of Upholstered items would be 182 as items 'ST-11' to 'ST-13A - Tray' do not have a 'Y' against the matching item references in the 'BASIC_LIST' tab.
I would like this Y/N column to be the determining factor as opposed to any other criteria or filtering. (Also the Items column in the 'QUOTATION' tab is only ordered in the way it is for ease of view for this query!)
Picture 2.png Picture 3.png
SUM VALUES OF ITEMS THAT HAVE ALREADY MATCHED ANOTHER CRITERIA.xlsx
View 4 Replies
View Related
Aug 14, 2008
is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery"
Below is a small sample of data, real data is around 100 rows
Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323
View 9 Replies
View Related
Jan 20, 2014
I need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.
Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.
Book1.xlsx
View 2 Replies
View Related
Apr 18, 2012
I had a matrix like this
0 4 0 0
1 0 0 0
0 0 0 2
2 3 0 0
then I used vba to put number between 1 to 4 (since this is an 4x4 matrix) when there is a 0 in the matrix, but the numbers in row need to be no repeat??
so i need something like this for my output. Is there any code for search the number in the row?? How can I do this??
1 4 2 3
1 2 3 4
1 3 4 2
2 3 4 1
View 9 Replies
View Related
Jan 31, 2007
I have several columns of data representing a shop operation along different years. Now I try to see how will it look like if I have 10 shops like this, all summed up. The problem is that each shop will have the same numbers as the other 9, just that it's first year of operation will be different.
Now, I don't want to have different sheet for each and every shop since their data is exactly the same. I only want to have 1 type of shop which I can add to a consolidated sheet but 10 times, and each time the first year shoud be different.
Just an example: If I have this data for my shop:
YR1 YR2 YR3 YR4
Sales: 10 12 14 16
Costs: 5 6 7 8
Operation: 2 2 3 3
Tax: 1 1 1 1
profit: 2 3 3 4
And lets say I have 4 shops opening in 2000,2001 and 2 in 2002 than my consolidated should look like this:
2000 2001 2002 2003
Sales: 10 12+10 14+12+10*2 16+14+12*2
Costs: 5 6+5 7+6+5*2 8+7+6*2
Operation: 2 2+2 3+2+2*2 3+3+2*2
Tax: 1 1+1 1+1+1*2 1+1+1*2
profit: 2 3+2 3+3+2*2 4+3+3*2
But again, I don't want to open a different table for each shop and than sum them up - I want to have only my one shop, and have a possibility to say when will I open my next shop and the consolidation will be automatically.
View 11 Replies
View Related
Feb 14, 2010
my document column "M" ,this type of numbers are there.
column "M"
125468A-B
125468A-B
458962
256897
412589A-C
412589A-C
412589A-C
01.i need to remove A-B,A-C part.
02.after that any number are repeating that all the number's end part should add (A,B,C,,,,)it's like this.....
[b]
column "M"
125468A
125468B
458962
256897
412589A
412589B
412589C
View 13 Replies
View Related
Jun 10, 2009
What i need is that result of these counting is 2. Count all without repeating.
I need to find out how many different days are in these fields.
1.6.2009
1.6.2009
1.6.2009
1.6.2009
1.6.2009
1.6.2009
1.6.2009
1.6.2009
2.6.2009..........
View 3 Replies
View Related
Jun 19, 2009
it repeats by increasing range from RZ1 to RZ2, RZ3 till RZ50. For each of the range below calculation needs to be performed.
View 6 Replies
View Related
Apr 3, 2007
VBA code. My sales data is arranged as months in columns and customers in rows. I have about 36 months of data that must be formatted as a single date column for pivot table manipulation.
View 9 Replies
View Related
Jun 23, 2008
I am creating several worksheet where the first two rows are repeated so that I can see those two rows as I proceed down the sheet. That feature works on some but not all sheets. What am I missing? I even tried copying the pages that are correct to another worksheet and that format doesn't copy to the next worksheet.
View 9 Replies
View Related
Jul 7, 2008
I have the below macro that I need to have it repeat until the end of the data, I'm not brilliant with how macro's actually work,
Range("A2:Q5").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
View 9 Replies
View Related
Aug 19, 2008
Suppose I have a following set of data in Sheet1...
FT 1
DD 2
MC 1
AD 1
EE 3
FL 2
GC 1
Under the first column, I have a series of text and under the second column, I have numbers, 1 - 4.
and I want the ouput from the data to be like
FT
DD
DD
MC
AD
EE
EE
EE
FL
FL
GC
in Sheet2, and the process will continue until there is a blank cell under the first column. Can someone help me with this? I have a very little experience in VBA.
View 9 Replies
View Related
May 8, 2007
How can I have a sub repeat itself? I have a code and I want it to ask the user at the end if they would like to do it again. If so the whole sub needs to be repeated. I'm sure this has something to do with looping, but I don't know how to manage this.
View 3 Replies
View Related
Jan 31, 2013
I'm trying to sum YTD values which are in columns based on multiple criteria in order to get the data that I need. Here's a sample of my data:
A
B
C
D
1
Country
Month
Brand
Volume
2
A
Jan
Yellow
100
[Code] .....
Country YTD Brand Volume
A Mar Yellow ?
I'd like to get the Mar YTD Volume for Yellow in Country A and tried to use SUM and INDEX but can't seem to get it right.
View 3 Replies
View Related
Apr 16, 2009
With reference to a thread that is nearly similar to my own problem:
http://www.excelforum.com/excel-gene...ml#post2076546
I have a similar problem, though maybe a little more advanced. I would like to do the exact same, but when looking at the sample.xls in the referenced thread, my data can have zero values/blank in the C-column. For instance, if C6 is blank, then the formula will not add the value in the next row. In many cases this would easily be solved through sorting, but the structure of my sheet do not allow for this. So what I am looking for, is a formula for only regarding rows where the cells in column C is <>"".
Maybe this cannot be done in a single formula, and has to be solved through VBA, but having seen some incredible solutions here, I thought I'd give it a try.
View 6 Replies
View Related
Nov 17, 2005
I need a formula that sums the values with 2 different criteria and 2 different look up ranges
View 9 Replies
View Related
Feb 17, 2012
I am trying to sum values based on 2 criteria -- the date (using a fiscal month) and a Category (A,B,C,D). Here's a small portion of the source data:
Sheet3
JKL1DateValue Plant21/28/2012 50.00 A31/31/2012 10.00 B42/6/2012 40.00 C52/10/2012 35.00 D62/15/2012 8.00 A72/17/2012 100.00 B82/21/2012 50.00 C92/23/2012 10.00 D102/27/2012 40.00 A113/2/2012 35.00 B123/6/2012 8.00 C133/8/2012 100.00 D143/12/2012 50.00 A153/16/2012 10.00 B163/20/2012 40.00 C173/23/2012 35.00 D183/26/2012 8.00 A193/28/2012 100.00 B
And here's the result I want:
Sheet3
NOPQRS1StartEndABCD21/28/20122/3/20125010 32/4/20122/10/2012
403542/11/20122/17/20128100 52/18/20122/24/2012 50106
Feb Total58110904572/25/20123/2/20124035 83/3/20123/9/2012
810093/10/20123/16/20125010 103/17/20123/23/2012
[Code] ...........
View 3 Replies
View Related
Mar 25, 2009
I have a spreadsheet that is set out to look similar to a MS Project plan, with dates across the top, users in column A and Assignments in column B. In the corresponding cell that matches the user, date and assignment they will enter the number of hours they anticipate working on the assignment.
I then have a summary sheet that covers all assignments, week ending dates and users. I want to be able to calculate the hours and copy it into the cell in the summary sheet that matches the Day, User and Assignment. I have tried using SUMIF, but this requires the criteria cells to all be in columns, but with this sheet the dates are in a row.
View 9 Replies
View Related
