Sort SCADA Data And Finding MAX And MIN Values?
Apr 27, 2013
I have a small challenge with the following parameter
2 columns of data, the first A contains sets of calendar dates:
A1 27-4-2013
A2 27-4-2013
A3 27-4-2013
A4 28-4-2013
A5 28-4-2013
Op to 5000 lines but normally around 140 of them for each date in April 2013
Colum B contains wind speed data from a met tower
B1 15.0
B2 5.3
B3 7.9
B4 3.2
B5 32.4
What I need to find are the Highest, Lowest and Average wind speed for a specific date.
I have a NOW -1 function in field C1 I would like to use as the parameter to search for in column A so I get the last full calendar days full data set (10min values), so writing it up it sounds something like this: Look in column A line 1:5000 and if you find any date the same as in C1 then move the content of B to a new separate column D line 1:140 and when all 140 lines are extracted for the specific date then find the Highest, Lowest and Average wind speed between those extracted data.
View 3 Replies
ADVERTISEMENT
May 8, 2013
I have a large amount of data and am trying to find certain values.
Firstly - I am looking for any cell which has the number 0 in it, as I need to edit that specific cell. However, when I search for the number 0, multiples of 10 (e.g. 10,20,30,etc) come up in my search. Is there anyway to find the number 0, without also finding the multiples of 10?
Secondly - I am also looking for values over 100, is there a search function which allows me to do this?
View 3 Replies
View Related
Jun 6, 2013
Any way to search for a value down column A, another value across roW 1, and tells what value lies in the cell at their meeting point. 4 example, I'm looking For "47" somewhere in column a, and also the number 53 somewhere in row 1, and I need to know what value is in the cell where those to meet.
View 1 Replies
View Related
Oct 28, 2009
I have about 3000 of these values per column. I need to sort the data so that column B and E are the same values. But the cells to the left and right of each B and E need to sort with it. Plus where there is a missing consecutive value I need a blank cell added...
View 8 Replies
View Related
Mar 12, 2009
In a column I have loads of numbers like this except there are 5000 of them.
3
2
0
0
0
0
2
0
3
0
0
2
0
9
how could i sort it into another column so it stays in the same order but counts the 0 values so it would look something like this.
3
2
0 (x4)
2
0 (x1)
3
0 (x2)
2
0 (x1)
9
View 9 Replies
View Related
Oct 8, 2012
I have a spreadsheet that track orders. I want to be able to add a bit code to my current VBA script that sorts the data based on the values in a particular column (smallest to largest).
Current table looks like this:
Order Number
Date
Quantity
Colour
Priority
548562
12-09-2012
56
VBA formula
2
[code]....
I want to add be able to sort the data, smallest to largest based on Column E (tittle Priority)
View 1 Replies
View Related
Jan 28, 2010
I have a spreadsheet with 7000 lines exported from a database. I'm looking for lines that exist with an @@2 that don't have a corresponding @@1. Let me explain.
SV10000000@@1
SV10000000@@2
SV10101000@@2
I want to keep the first two lines because there is an @@1 associated with an @@2. I'm looking to single out and delete lines that have @@2 that don't have a corresponding @@1 associated with it.
View 9 Replies
View Related
Apr 21, 2014
What I want to do is place a lap time into 2 columns (representing each lap), then excel chooses the lowest number in the two columns on the same row, paste it into another column, and then automatically sort, while keeping the first column static for 1, 2, 3, 4, 5 etc
So what I want is
Column A | Column B | Column C | Column D | Column E
1 | Driver Name | 14.004 | 14.102 | 14.004
I want Column A to remain in tact, never changing, 1-50. I want Column E to hold the fastest lap time calculated from C and D. Then I want excel to automatically sort Column E from lowest to highest, while keeping Column B with a specific driver name in line with their respective lap times. Column A is not a 100% solid requirement, but auto sorting Column E after it's calculated the minimum value of C and D is.
View 11 Replies
View Related
Mar 4, 2010
I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.
Sub SortMeetings()
Dim iCTR As Integer
Dim yCTR As Integer
Dim zCTR As Integer
zCTR = 11
For iCTR = 12 To 23
For yCTR = 1 To 10
If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then
Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value
zCTR = zCTR + 1
End If
Next yCTR
Next iCTR
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
View 9 Replies
View Related
Jan 31, 2012
Here's an example of what I need..
I want to
A B
2.5 corn
4 corn
7 green
2 green
I'd like a macro to find all corns, add up the column A for each corn and place the sum beside the last corn in column C
Then do the same with green, etc.
View 2 Replies
View Related
Apr 18, 2014
I have this massive spreadsheet, with the maximum rows excel will fit on one sheet. In the interest of simplicity, this is what the raw data looks like:
Last First Amount
Jones Jim $1000
Jung Joe $700
White Jon $100
Jones Jim $200
Jones Jan $300
Jung Joe $800
White Jon $200
What I want to do is automatically get excel to group all the same names together. Then I want it to sum all the values for each name, then order by largest total value for each person, then order that by name alphabetically. In other words, the above would look like this after the sort:
Last First Amount
Jung Joe $800
Jung Joe $700
Jones Jim $1000
Jones Jim $200
Jones Jan $300
White Jon $200
White Jon $100
Or, if necessary, there could be sum total rows under each name...although I don't have spare rows, so IDK. I could delete some rows if I had to, but would rather not.
View 1 Replies
View Related
May 11, 2009
I have 10 columns (A-J). Column B is the Date & Column C is the Time, I have the data sorted 1st by date then by time. The 5th Column, E, is Size. The 10th column, J, is "Position."
This 10th column, J, is in binary format. A 1 equals a Position & a 0 equals no Position.
1) I need to find the Max Sum of the Size in which the Max consecutive set of 1s has occurred.
2) I need to also do this on a day by day basis, using the Date in Column B, without having to manually go through & modify all the formulas to reference the date.
I have a Sumif formula for the 2nd but it won't copy down to update the formula for each unique date. I have to manually change the date values & I have ~957 unique dates to do; so it would be a rather painstaking process if only done manually. Moreover, it doesn't count the Max Sum of all consecutive 1s in Column J...
The length of the non unique columns (all columns w/o unselecting the duplicate entries) is 19,068.
If you can be of help, I'd be very thankful...my brain is overloaded w/googling & using trial & error.
View 6 Replies
View Related
Apr 29, 2014
Basically what I need is to find a row (I'm doing it by matching the row title that is 'BALANCE'), then I need to find the max value in this row (the problem I'm having is that the row number is not constant), copy that value in another cell and copy the name, that is located in the same column, next to the previous copied value.
View 2 Replies
View Related
May 27, 2014
Actually i have to two sheets. In (1st file) I have the data of customers. And in (2nd file) I get the approved sales. So i have to find the numbers.
In my sales sheet, there is data of customers like name, address, phone number etc.
In my sales report sheet, i got multiple numbers in a notepad file which are rejected sales.
So basically, i have to copy every number one by one, and then have to find it in my excel sheet with CTRL+F and then i change the color of that cell which is rejected.
I want magic by which i simply add all the rejected numbers in one column of my file, and then do Abra Cadabra by which all the numbers which are rejected get in red colour automatically in the column in which there are all the numbers (approved as well as rejected).
View 13 Replies
View Related
Feb 5, 2009
I have a spreadsheet in which one column I'm trying to find a value in another table that has 3 columns (A, B, C). I need to find all the values in A, that fulfill the parameters of less than 1.5 in B, and great than 5% in C.
They all need to be listed in column H while skipping the records that do not meet this criteria.
View 9 Replies
View Related
Feb 18, 2013
I have people who wrote down that they attended a location on a certain date at a certain time, and I have their start and end time.
So for example my person data has something like this:
Arrive: 1/1/12 7:00 AM,
Leave: 1/1/12 10:00 AM
Arrive: 1/1/12 3:00 PM
Leave: 1/1/12 4:00 PM
Arrive: 1/4/12 8:00 PM
Leave: 1/4/12 10:15 PM
I also have those fields formatted as text and as decimals so I can work with whatever I need.
The next portion is a list of events that occur at these same locations. Here are some examples.
Event 1
Start: 1/1/12 8:15 AM
End: 1/1/12 12:15 PM
Event 2
Start: 1/4/12 3:00 PM
End 1/4/12 8:30 PM
What I need to find out is if the person was present at the location during any of the events.
A person may write down more than one visit to the location in a day. The events only happen once per day. I'm sure there's a pretty manual way to do this, however the list of people and events spans an entire year and it will be too time consuming to do it that way.
View 2 Replies
View Related
Apr 29, 2013
I have a graph which is reading from a table. This graph is reading flows from a flow meter, but after reading data for over an hour the graph gets really squished. I'm looking at creating another graph with just the last 50~ values from one column. Is there a way I can create another table that only reads the last 50 values from one column?
View 6 Replies
View Related
Aug 19, 2013
I have multiple columns / rows of data, some of which are duplicates.
Column S is a concat of columns A:R where this data is stored, and is sorted alphabetically.
I'm looking for a way using VBA to find duplicate concat rows by cycling through this list that is already sorted. I'm interested in moving down this list, 1 by 1, and if current cell = cell above, delete the data in columns A:P of that row, then delete the cell data in column R of the cell above the current cell.
So for example, if I have sorted data in S8:S14, and S9 = S8, then I would like to delete A9:P9, then delete the data in R8.
View 3 Replies
View Related
May 9, 2014
I have been trying to set up a macro to find the Minimum and Maximum values from an array of temperatures for painting... so far, partially successful.
The problems I am having are that the values have 1 decimal place and can be anywhere between 22.7 to -1.4. When they come they are put on the spreadsheet, the MinTemp can be 10.0 and MaxTemp 9.9, totally reversed.
Dim Info(2000, 2) As String
Dim MonthValue(31, 9) As Variant
Dim Working1 As Date
Dim I As Integer
Dim J As Integer
Dim Tot As Integer
[code]....
View 2 Replies
View Related
May 22, 2008
Here is a little formula I working on:
=IF(MID(A1, LEN(A1),1)="i", "x", "")
As it is, it would return an X if the last value of a string is "i"
What I want to really do is check if the last value is a " qoute but it seems you can't put """ or you get an error -- example: =IF(MID(A1, LEN(A1),1)=""", "x", "")
View 9 Replies
View Related
Jul 13, 2006
I have a worksheet where column A is a list of numbers and Row 1 is a different list of numbers
Is there a way to activate a cell given the column A value and Row 1 value? (so I can then insert a value)
View 9 Replies
View Related
Mar 27, 2007
I'd like to know if exist a procedure/ function to find a argument in an array and return the index/address of the argument at the array.
For example, I have a array contaning a list of products. i'd to create a function that finds the index of a particular/list of products, feeded as arguments, and returns the indexes locations.
View 9 Replies
View Related
Jun 14, 2014
I'm currently doing a project which requires me to find and replace a large number of values.
ie. Column A Row 1 = 80099, Column A Row 2 = 554441, Column A Row 3 = Z5478, CAR4 = B89877 etc...
I need to replace these numbers with meaningful values and I have a reference table to tell me what these codes mean ie. 80099 = apple juice, 554441 = orange puree, Z5478 = pumpkin soup, B89877 = banana smoothie. The reference table is in a separate excel spreadsheet and is tabulated in two columns ie. Column A = "the code", Column B = "the value".
There are like 1000+ codes I need to find and replace which I know is going to kill me if I did it manually. How do I find and replace all the values?
View 14 Replies
View Related
Jul 3, 2014
I am trying to create a cell that show the value of all specified rows with specific name.
For example:
Apples $2 $5 $10
Oranges $5 $10 $50
Apples $5 $ 6 $ 60
(each of these will be in their own cells, 3 rows and 4 columns)
How do I create a cell that will sum the total value of all the Apples by using Apples as the variable?
View 1 Replies
View Related
Jun 16, 2009
pick a column to test in, this column should be one that will have #N/A error displayed in it and that 'goes as far down the sheet as you need to examine for the #N/A conditions although not all entries have to be #N/A just something in them to the end Using column E for this example as E was where I put a VLOOKUP() formula to test/generate #N/A errors. Const testColumn = "E" change as required
'no other changes to make
Sub DeleteNARows()
Dim naRowList As String
Dim anyRange As Range
Dim anyCellEntry As Range
Set anyRange = ActiveSheet.Range(testColumn & "1:" & _
ActiveSheet.Range(testColumn & _
Rows.Count).End(xlUp).Address)
For Each anyCellEntry In anyRange
If anyCellEntry.Text = "#N/A" Then
naRowList = naRowList & anyCellEntry.Row & _....................
View 2 Replies
View Related
Feb 2, 2006
I have a row of data with the cells I want to find a MAX for are separated from each other:
a1=300 d1=500 g1=800 j1=10,000
I want to find the MAX value for a1,d1,g1,j1, but exclude any value over 1000. So instead of =MAX(a1,d1,g1,j1) returning 10,000, =???(???) will return the next highest max of 800.
View 14 Replies
View Related
Oct 26, 2008
I have a sheet that returns 4 numbers where 2 will match. Is there a command that will return that matching value?
For example:
cell 1 = 5
cell 2 = 9
cell 3 = 4
cell 4 = 9
something to return the value 9
View 2 Replies
View Related
Feb 13, 2009
I have a little table:
Columns A:B (separated by commas)
Row 1- Year One, 20
Row 2- Year One, 20
Row 3- Year One, 10
Row 4- Year One, 30
Row 5- Year One, 20
Row 6- Year One, 20
Row 7- Year Two, 10
Row 8- Year Two, 20
Row 9- Year Two, 20
Row 10- Year Two, 10
Row 11- Year Two, 20
Row 12- Year Two, 40
In the adjacent cells in columns C (C1:C12) I need a formula that would turn the value into a percentage of the total of the values for that year (e.g. in C1: 20 is 16.7% of 120, so the value displayed would be 16.7% (or 0.167)).
View 3 Replies
View Related
Oct 11, 2011
I have in Sheet 1 a list of product codes (that should be only numbers, i.e: 1010) on column A, product description on B and a sales column on C.
On Sheet 2 I have on column A I have in the same cell a letter "C" the product code and the product description(i.e.: C1010 Onions). On column B I got the sales total. ( I dont care about the letter "C" it can be deleted)
I would like to use a VBA code to find the same product codes between sheet 1 & 2 and copy the sales total from sheet 2 to sheet 1 when the product code is the same.
View 1 Replies
View Related
Mar 7, 2012
I need to be able to find a value in another sheet based on 2 criteria
The criteria is a number from a column on my first sheet where the results will reside, the second criteria is based on a fiscal year (calculated based on the year of the current year).
The formula I use is as follow:
=LOOKUP(BA2,Budgets!B:B,Budgets!I:I)
and this is what I use to get the column:
=+INDEX(Budgets!B11:B200,MATCH(Budgets!B11:B200,Budgets!B11:AB200,0),3) getting #N/A here
and I use this too:
=MATCH(IF(MONTH(TODAY()) < 7,YEAR(TODAY()),YEAR(TODAY())+1),Budgets!E:Q) getting #N/A here too
If i keep it like that no problems but I will need to change the criteria 2 every new fiscal year.....
What I need is to replace the criteria 2 with something that will find the proper column wher the data is the fiscal year is listed on E11:Q11..
I have tried index and match and quite frankly, I never made it work, always getting an error.
View 9 Replies
View Related
