Create Sub-list From Larger List And Find MIN Value In Another Column?
Feb 6, 2014
I am looking for a formula to search two columns.
1) The first column needs to be search to match a text string.
2) If the text string matchs, I need excel to evaluate the number in the second column and find the MIN value of all numbers associated with the text string.
So:
Joe 50
Jim 12
Joe 10
Rob 25
Jim 8
Rob 99
When searching for "Joe", the function would return 10. When searching for "Jim" the function would return 8. For "Rob", it would return "25".
It is a product list of office stationary with codes, descriptions and quantities. Using this spreadsheet we input into the quantity how many we want of the item, all well and simple you could call the list a stationary order form.
What I would like to be able to do with this Giant list is to have a function that would extract items off the list into a cleaner consolidated form. As in, if I wanted to order some of item A, C and F, I would like for the second table to just Show ACF without every letter in between or blank cells.
I know a simple method would be to have an IF formula to return the information to a new sheet that I could simply remove blank cell rows (unordered items) and ready for print each time. But I would love to know if there is a better way this could be achieved.
I have basic/intermediate knowledge of excel and can setup the data if need be in order for the function to work, but I can't seem to find the correct function for this project.
I have a large amount of data, 40,000+ rows about how students do in remedial classes including from whom they took the classes. I have pulled from it a subset of student success as relates to the separate instructors. Now I would like to extract a consolidated list of those instructors for whom the student gain on criterion based pre and post course assessments is above a specific level. I have no trouble writing if statements that will look at two types of data, the course taken and the average student increase to determine which instructors meet or surpass the criterion, but I can only do that within a list that includes all the instructors. Because there are more than an hundred such instructors, it would be easier for the dean if I could provide a short list of those that meet specific criteria. I believe that it would be an array that I should use, but I have been fooling around with this for 2 days now, and am finding myself at a series of deadends!
I'm having some problems sorting a list of numbers that some are larger than others, but it would sort it by how large the number is in regards to 123 or 45... 45 would go first instead of 123... I tried to change the column to numbers but it still won't work. as well as in the custom sort section. I'll try to upload a sample...
I have a spreadsheet of part #'s, descriptions, manufacturer names, and manufacturer part #'s. (It's a list of the inventory in my warehouse). Each row contains information for just the item in that row. Row 2 references another part in my warehouse, row 3 yet another, and so on.
Many of the parts have more than one potential manufacturer and part #, (meaning that any of those manufacturer's part #'s are basically the same tool; just different brands. At one time we may get a shipment of one, at other times we may get a shipment of another). For example, a screwdriver may be listed like this:
Part # 1234 screwdriver, mfg Snap-On, part # 456, mfg Stanley, part # 789, mfg Mac Tool, part # 439.
Then further down the list, there may be another part listed like this:
Part # 9980 wrench, mfg Stanley, part #741, mfg Snap-On, part # 852, mfg Proto, part # 369.
If you can imagine that data across the cells of a spreadsheet row, notice how the mfg name 'Snap-On' was the first mfg name on the screwdriver, but it was listed as the 2nd mfg name on the wrench.
So, here's my question: I want to be able to group all of the items made by any one manufacturer together in a new list. If all of the manufacturer names were in the same column, I could simply sort the list by that column, but since I've got thousands of rows with the mfg name I'm looking for in different columns on different rows, I thought maybe a macro could search each row for the word I'm looking for, then if found, take the whole row and copy it to a new worksheet. So the end result would be, If I wanted to see all items of which Snap-On is an acceptable supplier, I could get a list of all potential Snap-On items grouped together.
I'm sorry this is so long. I may have over-worded this and it may not be too clear. I could email an example of the spreadsheet if anyone needed more info to figure out what I'm looking for and was willing to take a look at it.
i have many excel workbooks in a folder i want a macro that will get the names of all the files and make the file names so extracted as a hyperlink to open the files.
I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
I've searched the forum and believe this thread to be the closest to what im looking for, but its doesnt completely apply: matrix to list conversion. I have an attachment to support my questions (see attached). I have a matrix of words (strings) that contain repeating and non-repeating contents. The matrix also has spaces which are of no value. Essentially, the VBA needs to ' analyze' this matrix and create a consolidated frequency list (as shown in the file).
The matrices are HUGE and therefore some of the clumsy VBA i am using is turning out to be a bit inefficient. The file also contains formulas and such that I cannot use AutoFilter nor can I use Insert or Delete rows...so the VBA shouldnt use those either.
Excel 2007 I have a spreadsheet of file boxes the keeps a log of boxes, contents, locations and shred dates. Currently figuring out when and which boxes can be shredded is a manual hunt, find, and then deal with it. I can use CF and Sorting but I have been looking at some search type functions to return a list meeting the requirements and trying to step my way towards that.
1st attempt. Was find out how many boxes meet the shred (before today) that have not already been detroyed. Column K is the Shred Date, D1 is Todays date, Column G is Status. Got this working good.
My failed attempt came at trying to find the earliest shred date excluding those that have been destroyed. This is not really neeed but I was hoping it would get me closer to creating the list of boxes that meet shred that have not already been destroyed.
Note: Column A is the Box No. and the spreadsheet contains blanks as we continue to add file boxes to storage.
Below array formula find the highest box number used by each department.
I would like to create a list of boxes including Column A "Box no.", Column H "Location", Column K "Shred Date" for any box whose shred date is before today that has not aleady been labeled "Destroyed" in Column G.
The attached file (a copy of my main one) has a list of our engineers, and what stock they carry. The stock parts are the 64, 65, 66... numbers.
I need to create a list from this (as underneath the main table), for all instances where there is a 'Y' in the columns next to each engineer. So if an engineer has 3x pieces of stock, they need to appear in the list 3x times. If they have 1x piece of stock, they appear in the list once.
I've attached a spreadsheet showing the effect I'm trying to achieve. A list (which changes weekly) contains employee details and length of service. Based on the length of service I need to build a new list grouping the employees based on the service length.
In Column A I have a list of Products and in Column B is the list of corresponding prices. I'd like to create a drop down list with the items in Column A that would automatically fill-in the pricing from Column B next to it. I've tried using an IF statement, but my product list is too long.
I have three columns of data (A,B,C) and I want to add a formula or script to create the data in column D. The 'XXX's are all numerical values and will vary.
Column D should be a sequential list, which restarts at 1 any time that either: - the value in column A changes, OR - column A stays the same, but the value in column B goes from something to nothing, together with the value in column C going from nothing to something (or vice versa).
I have the below code, I want to end up with values in column 'A', and a hard coded value in column 'B' for each row in column 'A'. But, I'm ending up with the hard coded value just in the first row of column 'B'. In other words, I want to repeat a value and copy it down column 'B' the same number of times that there are values in column 'A'. Here is what I am working with so far that is producing just the value in the first row of column 'B' -
Private Sub dispnames() Dim b As Integer Dim r As Integer Dim sh As Worksheet
In D2:D11, I want a non-duplicate list of all the "1" names, i.e., Bob and Josh. In E2:E11, a list of all the "2" names (Sally, Bill & Jacob), and so on. Sorted would be nice, but not necessary. When new names and their numbers are added to A1:B200, they need to automatically show up in D2:G:201 as appropriate. I've looked over VLOOKUP, INDEX, MATCH, etc., but all seem to simply return the first and not every match. Maybe I'm just missing it.
Is this possible without VBA? Not that I have any objection to it, but delving into and learning it is a little outside the available time I have now. If it takes VBA I'll address it when I can free up some time.
I want to end up with a list of unique items (nothing repeated) from a variable length list starting in cell B5. That unique list is then placed in cell D3.
I am using the code below (found on Ozgrid) which almost works. But it seems to be dependent on what’s in column A (which I wish to ignore) for it to work.
How can I modify the code so that the results are based on column B only?
Dim Rng As Range Dim fRng As Range Dim lRow As Long Dim Dest As Range
lRow = Range("B" & Rows.Count).End(xlUp).Row Set Rng = Range("A5:B" & lRow) Set Dest = [D3]
With Rng . AutoFilter Field:=1, Criteria1:="1*" Set fRng = .Offset(1, 1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) With fRng .AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Dest, Unique:=True
I need to create a mailing list for people located in a specific area from a master list.
The address data is not in great shape so I need to check address field 2 and address field 3 in the list to see if the suburb in the location tab appears in either of the two fields.
Desired result appears in column F of the List tab of the attached spreadsheet.
Name --------- Jones, Bob Jones, Bob Jones, Bob Smith, Mike Smith, Mike Smith, Mike Calai, Dave Calai, Dave Calai, Dave Etc...
What I want to do is take the entire list and create a distinct list that I can use in a named range then use a validation list to have a drop down within a different tab.
I have a spreadsheet that lists dates in row 6, columns V through BE. We use these dates to log attendance for kids, so under each date there may be one of many symbols, such as "x" if the student was there. I would like to create a list of the dates that meet one of two criteria: either blank or containing "WR". So, let's say column Z is blank and Y has "WR" in it. In column FB I would like a list of all of the dates that were blank or WR, so I would want the dates contained in row 6 for Y and Z. Is there a function that will 1) look for a blank or WR across a row, 2) find the associated date for that blank in row 6, and 3) write the in another column in the form of a list, each date separated by commas? Here is an example of my information. I would like for a function to automatically make the list in column FB for any dates that are blank or contain "WR".
I am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple.
I have a list of 200,000 e-mail addresses in a excel document in column A. What i am trying todo is find all the duplicate e-mail addresses within the list that appears more than four times.
I have tried this forumla in colunm B so it lists how many times the record appears: =countif($A$2:$A$244270,A2) which works fine and then a number appears next to each record. Then i pressed the sort filter to arrange all the records in number order so all the 5's are at the top of the worksheet but that does'nt always list them in the correct number order.
Basically all i want todo is find all the records that appear in my list more than five times then make a list of just one of those 5 records so i can remove them from my e-mail system.
I need to create a 'sub' list from a drop down list.
I have 8 categories in a drop down list in column A, but i need column B to also have a drop down list of sub categories depending on what category is selected in column A.
And once column B is populated, i need column C to auto populate with a value. All data is stored on the spreadsheet.
I'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?
1- I have a list full with numbers in a column. And I want to count how many digits are there in every cells. If digit of the number is less than 7, macro will complete it to 7 digits by adding 0 (zero) at the beginning of the number.
Example: A1 = 85468... A1 value is 5 < 7 in this case our number will be 0085468 (total 7 digits)
PS: sometime, when numbers start with 0 (zero), excel delete all 0 value at the beginning of the numbers. Macro should prevent this error.
2- I have another list with 2 columns which arranged in order. I mean: A1=xx1, B1=xx2, A2=xx3, B2=xx4, A3=xx5, B3=xx6... etc. One is at A column, next number in B column.
My request is I want to make this list 1 column as arranged. A1=xx1, A2= xx2, A3=xx3, A4=xx4... etc.
3- Macro will add a new line to the end of the above list with this format: Z00001xxxx
Z00001 is static xxxx is number of the full cells of A column plus 1. It will be 4 digits. If count is less than 4, it will complete it to 4 digits. Example: if there are 15 cells in the list, last line will Z00010016 (15 + 1)
