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
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.
I have two columns. One column has UPCs - some of which are duplicates. The second column just has number values. I'm trying to add the sum of all of the numbers in column two which are attached to their respective UPC. For example,
COL A///// Col B
11111111111///// 10
00000000000///// 15
11111111111///// 10
11111111111///// 4
00000000000///// 2
So, I need a third and fourth column to give me the total value for a single SKU(col A) of all the values in col B. In this example the Third column would contain the SKU, and the fourth column would contain the sum of all values in column B that are associated with the single SKU in column three. The third and fourth column would look like this:
COL C///// COL D
11111111111///// 24
00000000000///// 17
I have an excel sheet with about 1000 reference numbers in. I would like to know how many of these are unique...they look like this:
UCR104
UCR1042
UCR1043
UCR1048
[Code] ........
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 RelatedI have a spreadsheet and on sheet 1 it currently has 45791 rows filled with data and it increases each day.
Each row looks like this
A RACECOURSE
B DATE
C RACE DETAILS
D HORSES NAME
E RATING OF THE HORSE
On Sheet 2 I have 3 columns. These are the list of horses that are running on a particular day.
A DATE
B RACECOURSE
C NAME OF RACEHORSE
What I want to do is to be able to list in columns D,E,and F on sheet 2 the last three ratings the horses achieved from sheet 1.
how to acquire the latest rating of the horse by using the following formula.
=IF(ISNUMBER(v(LOOKUP(9.9999999999999E+307,1/SEARCH(C2,Sheet1!$D$3:$D$45790),Sheet1!$E$3:$E$45790))),v(),"")
This works a treat but for the life of me I can't fathom out how to get the formula to pick the latest three ratings and place them on sheet 2.
I am using excel 2003
I need some code that will find the column with the header of "Gender" and change the values from "M" and "F" to "Male" and "Female" but I keep running into issues.
I want to use a Select Case statement so I can set all other found values as blanks.
I am using a spreadsheet as a score sheet for a competition. One of the columns is the student's GPA. After entering all the scores there are duplicate final scores. I need a way to have it look at the final score and then use the GPA so that it will not put a duplicate value in the final column.
al Column N is the Total Column, Column O has the Names that correspond to the Total Column. Currently I am taking this total and putting it into Column Q (High Scores) in high to low order. Column R should have the names that match the scores. But with duplicate scores, it is only putting the first name associated with the score. I would like to use the GPA as a final determining factor for the duplicate scores. The higher GPA would come before a lower GPA. I have tried to put an additional column to bring the GPA over to correspond with the High Scores Column, but could not get it to work.There are actually more names for the competition and the top 10 will be moved to a different sheet and further judged. I have attached a sample with the exact formulas that I am using.
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.
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 RelatedActually 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).
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.
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.
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.
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]....
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", "")
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)
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.
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?
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?
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 & _....................
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.
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
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)).
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.
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.
Code:
Sub DeleteCells()
Dim ws As Worksheet
Dim cell As Range, search_cell As Range
Dim i As Long
[Code]...
A few issues with the above code: I have manually checked that there is a sheet with a value in M12 that is found in the H column range, but it doesn't change the value to zero. (this was confirmed with an =IF(M12 = H101,1,0) returning a 1 value.
Secondly, it takes a long time to process not a lot of data. (about 2 minutes to get through 3 sheets of 180 actual rows of data, (rest blank). Is there any way I can speed it up? I will eventually be cranking it up to 30 sheets of data and up to 360 rows. (The 500 was thrown in earlier to make sure it covered everything).
finally, it also lines every cell value in I(end of data):I501with a zero, when there are blanks in other cells, can i get it to not put a zero in,
I have two pairs of columns, A B and E F.
Column A contains one numeric value in each row. Column B lists one person's name in each row.
Column E contains a distinct list of the names found in column B (no repeats), with one name in each row. Each row of column F contains one alpha ID that belongs to the adjacent name in column E.
I need to replace each name in column B with its respective alpha ID from column F, effectively pairing each numeric value in column A with an alpha ID from column F.
Have I stated my goal clearly enough? I could Find/Replace a few hundred times, but I thought there might be a better way using an Excel formula. I'm a beginner-intermediate user, and I clearly don't know enough about Excel to even ask about specific functions for this task.
I have a list of 120 values in column A. Is there a quick way to search the entire workbook and return what tab the value appears in column B ?
View 2 Replies View RelatedA
B
C
D
E
[Code]...
In the example above I am trying to look up a value from columns C-E. I need to be able to search/index using 2 criteria to figure out which row to match with the given column. for example: If I want to know the invoice qty. for R&D for Jan-2012, so the returned value would be 13. I have tried several different combinations of match and index to get this to work but have had no success. Ultimately what I want to do is have a drop down for the month and year that our VP can select and it will give him the given values for that month.
