In column A, I have a list of names.
In column B, I have a list of values.
I want column C to show me which name has the smallest value from column B.
In other words, if there are 5 names, I want ONE name to give me a value of 10 (for the smallest value), and the rest of the names I want to show zero.
I also have in column D another list of values.
In column E, I want to show first, second and third place amongst the list of values from column D. The rest of the names I want to show zero.
I currently am working with a worksheet that has over 250,000 data points. It is a spreadsheet for a company in which different work order are organized. A given work order can have several parts (ranging anywhere from one component to 40ish) and thus the height of the cells in which each specific work order is placed differs from work order to work order. For each component, the spreadsheet tells you how many can currently be made based on the materials available. What I would like to do, for each specific work order, is go through the data and simply tell whether or not the work order can be completed (the quantity of each specific component needed is also given in the spreadsheet) and then report out exactly how many components can be made.
Ultimately, this means that a "limiting factor" analysis must be done. For instance, if the work order is asking to create plastic bottles that consist of the bottle, the cap, and the label and it asks for 3 bottles but only 2 caps are able to be made, the limiting factor would be the caps and thus only two bottles could be made and the work order could not be fully completed.
The spreadsheet is set up based on another worker here, I am just trying to come up with a way to analyze it. Cells A1:Axxxxx are the work order numbers and the size of these cells differ because they are merged to fit the number of components they have. The componenets available are then ranged C1:Cxxxxx etc and the quantity needed are also listed in a similar fashion. What sort of structure could I use.
Find smallest numbers in the range ignoring zeros. I want it returned as value "1", all others - as value "0". Example: range A1:J1 contains - 5,0,7,3,6,5,8,0,9,3 desirable result in the range A2:J2 - 0,0,0,1,0,0,0,0,0,1
I have been using the SMALL function to find 10 smallest values in a list, however, some of the values are the same, is there a way of only having the 10 smallest unique values within a list?
Lets pretend that I have a boat load of automatically generated values from a different program.
There's a specific equillibrium point to a certain measurement, but this measurement involves a lot of fluctuation. To get a near accurate result, I must find the maximum value in an entire graph on the Y Axis value(Column B). Ofcourse, this is easily done using the =MAX value
Exactly 450 seconds (X Axis value(Column A)) positively and negatively from this point, something like this happens.
Problems:
A.) I must find the highest points 450 seconds negatively ( X ) to the max of the entire graph.
B.) I must find the lowest point closest to the two points we uncovered, both on the positive ( X -->) side.
C.) The fluctuation of every spike can last to about ±30 measured points in the graph. (±20 seconds) Thus, the maximum point surrounding the +/- 450 seconds (±693 x values) needs to be accurately predicted as being around 450 seconds, not 10 seconds more or less.
D.) The measurement is known to something glitch out and suddenly get a spike somewhere along the road, which will then quickly fall back down, which is a false maximum level. How can I prevent excel from taking this as the maximal point?
Remember, this needs to be fully automated into a macro later on, ergo, I cannot manually add the formulas to all the (650 different) sheets.
I have 25 days before the project gets cancelled if this doesn't make it.
I am using the following array equation to return the value in column 1 for the smallest 5 numbers. It works for small 1 and 3 but i get a #NUM! for 2 , 4 , and 5. The smallest 5 numbers are:
Currently we are using the formula below to get an average of column E in a spreadsheet. I was wondering how to modify the formula to get the smallest number in that column rather than the average.
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 copied column A from a PDF file. It contains entries like this, all in the same column:
5 0.002HP Direct- 26845 7/1959 Airesearch
This column contains an ID # (5), a description (0.002HP Direct), a part # (26845), a date (7/1959), and a manufacturer (Airesearch). All this info is in one column. This column is 11,000+ entries long.
I have column B which is 45,000+ entries long, and contains ONLY part numbers, like this:
325111
I would like to compare column B to find the matching results from within the text of column A, and put these matches into column C.
augmenting current code to select the column after a column. Current code is:
Code: If Sheets(data_sheet1).Cells(2, iCol).Value = "Stations" Then TargetCol = 1 If Sheets(data_sheet1).Cells(2, iCol).Value = "Agency" Then TargetCol = 2 If Sheets(data_sheet1).Cells(2, iCol).Value = "Advertisers" Then TargetCol = 3 If Sheets(data_sheet1).Cells(2, iCol).Value = "Product Code" Then TargetCol = 4 The next line should find the column after "Product Code".
I have a working formula that gives me the last value in a column using Index formula below. How can i slightly adapt the formula to find the maximum value in that column instead?
Using and array to go through a series of sheets and do stuff (Thanks Gerald and Von Pookie BTW). I have used code which finds the last row (varies from sheet to sheet), but not the last column (which also varies sheet to sheet).
Finding Last row LastRow = Range("A" & Rows.Count).End(xlUp).Row
However this code doesn't seem to work for last column...sample... LastCol = Range("A" & Columns.Count).End(xlLeft).Column
Is there a trick I'm not seeing? Does this only work using the 'Cell' function in VBA? If so, how would the line of code look? I'd really prefer finding the column letter as opposed to using the 'Cell' method if possible.
Can anyone give me the version of this that would find the last cell in a row that contains data. The code would be used in a loop so I would need the row reference to be a variable.
I need to append this macro to find the next empty column to place the data in. The orignal VBA works fine, but I need to go into the editor, and repalce the offset number every time I add a new row (weekly).
I have a large list of numbers in two columns. I need to know what numbers are in column A but don't appear in column B. For example if the numbers are 1 2 and 3 in column A but column B only has 1 and 2 I need to know that 3 is missing.
the only thing I can think of is doing =IF(A1=B1,"TRUE","FALSE") however typing this a thousand times would not be practical.
Is there a macro that can check column A against column B and tell me which ones are missing?
I have a 70x70 table where I teased out the largest 20 values. I'd like to utilize a formula to deliver the column headings (as well as the row) headings for each of these 20 values. VLookup and HLookup don't seem to work for me, and Index/Match seems to only work for single columns.
It might be worth mentioning that is highly unlikely that any of the values in the table are repeated.
I have one column that has over 120,000 rows. In the column it is split into sections. I can manually find the min max from each section but would like to know how I could do this automatically throughout the whole column.
Why is lastRow always returning 1? Even after adding values to worksheet and running macro - its always returning 1 for lastRow.... Am i missing something here? Do I need to refresh worksheet or something? I just dont get it :/
.Range("B:B").End(xlUp).Row
Code: Option Explicit Private lastRow As Long Private curRow As Long[code].....
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?
I have serial numbers say 1 to 100 in column A. In column B i have values which are text and numbers combined. In column C I have similar values as in B.
Now, if any value in Column B appears in Column C, then in Column D It should tell me Yes or No or 0 or 1.
I basically want to know whether any value from Column B exists or not in column C. I tried Countif and Vlookup but didn't work.
I have three columns of names. I need to loop through the first two checking to see if any name is in the third column and if so, place a 'found' in a fourth column.
how I can get the userform to close when another worksheet is selected. what I really need is for the userform to just show on one worksheet (not close) Is that possible? If not I want to be able to re-size the userform when another worksheet is selected (like getting it to minimise)
