Attached is a sample file that has a Data entry sheet in which the user can paste in data. They can test & paste anywhere from 1 to 50 compounds at a time and run the list through 1 to 55 Assays. I want to generate a sheet (Plate & Assay Info) that would first display the list of compounds that were tested as well as the list of Assays that they were run through.
View 13 RepliesI 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?
View 2 Replies View RelatedI have 15 different words that I want to order in as many ways as possible, but only 3 at a time.
So let's say the words are:
cat
dog
elf
clown
monkey
rock
bananas
(etc)
then I want a list that has all of these possible combinations:
cat dog elf
cat dog clown
cat dog monkey
cat dog rock
cat dog bananas
cat elf clown
cat elf monkey
(etc)
until all are listed. I understand there is a huge number, I don't mind having a couple of thousand as long as they are all genuinely unique.
I CAN have elf dog cat, elf cat dog, dog elf cat later on ... it's just the order that needs to be unique not the words in the phrase.
I am trying to create a drop down list that when selected will display data in the 10 rows below and 4 columns, so a total of 40 cells. I would like the data to be based on the selection in the drop down list.
The data looks like this,
RequirementsQtyResourcesQty
Level 1 Sentinel10Level 1 Sentinel105
Level 2 Sentinel10Level 2 Sentinel167
Level 3 Sentinel10Level 3 Sentinel156
Level 1 Banga20Level 1 Banga401
Level 2 Banga20Level 2 Banga307
Level 4 Banga20Level 4 Banga0
i have 27 different combinations of the above list with up to 10 different rows. I have a label for each one, but i cannot get the rows below my drop down list to display the proper values base don the selection in the drop down list.
Columns A & B contain several thousand Project Numbers (e.g., P1052, PA844, etc.). Many of the Project Numbers in column B are the same as in column A, but column B also has additional (i.e., newer) Project Numbers scattered throughout. Ideally, I would like to use a built-in function (versus a custom function if possible) that compares all the Project Numbers in both columns and then separately lists those that are unique to column B.
View 9 Replies View RelatedI have a workbook with 7 sheets, the drop down list operates correctly on all but one sheet, it used to work on all sheets. The arrow on the right of the selected cell dose not appear onthis particulr sheet. I have rebooted the PC and double checked the in-cell option. I have search the forum for similar problem and found the same problem but no remedy.
View 2 Replies View RelatedI'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?
View 5 Replies View RelatedI am trying to search for a string of numbers (column 2) in an array, and have "YES" be written on the same line in column 3 if the string is found in the names ANYWHERE in column 1. Please see the desired results on the picture in column 3.
I have tried many things, including SEARCH function which can only work with 1 cell not many, COUNTIF and more advanced functions, but I think have not succeeded because of my lack of knowledge in arrays.
I have a spreadsheet where I want to do some fancy stuff:
1) find the lowest value in a range of cells (S9:S17)
2) output its corresponding value in A9:A17 into another cell, say C87
3) increment its corresponding value in C9:C17 by 1
4) repeat 9 times, but inputting it to C88, then C89, and so on.
Everything in the spreadsheet depends on the values in C9:C17, including the range S9:S17, so when the values in column C change, so do the ones in column S.
But I don't want to actually change anything, just simulate it. But because the values in S9:S17 are formulas from yet another column in the spreadsheet, the best thing to do would be to remember the column C values beforehand, do the 4 steps above, and write them back afterwards, otherwise the macro starts to get complicated and brings in more values etc.
At my work we use excel to keep a large number of labels done in one day, those labels are then added up to give our weeks total, running total and average by day.
What i am looking to do though on a seperate sheet though is find out which day we had the most, an example of what it looks like
want a way to find out any special character in text file (.txt).
The .txt file I use is very large about 100 mb to 1GB. I need to find a way to write vba code that asks for input text file and the validates it and gives the message that following special characters are present in the file. Also, it gives their column number and row no's, where they are located.
The characters which needs not treated as special characters are numbers (0-9), alpha (A-Z) and special characters (@,-,%,$,+,=).
I have a large keyword phrase list.
In a sheet called "AllKWs".
What I would like to be able to do is find all Country and town names in that list and delete the names only in the phrase, not the whole row.
Several points though.
Can the macro (As I will assign a text button to it).
Only find and delete these words if they are either at the start or end of a phrase?
4 example phrases; ....
I have a generated list that pulls data from a different sheet. I need a formula to pull only unique values from that generated list since some values are list multiple times.
[URL] ......
I have some raw data of employment records on one workbook and from this i need to autopopulate there information into another workbook.
I need to split the records up by the different departments they work . I.e so what i am after if possible is -
If The department name in the raw data matches column A1 for example then it auto populates down in B2 all the names of people who work in that department .
I have a generated list that pulls data from a different sheet. I need a formula to pull only unique values from that generated list since some values are list multiple times.
View 3 Replies View RelatedI have list of various city names in column B. From that i need unique city names in validation cell.
The reason is,
we have emp id, name, designation, area, marital status for our employees. From that if i select emp id from validation cell1 i have to get all employee id's available in the sheet1 is need to come under the validation2 cell automatically. and if i select "area" from validation cell1, all unique areas need to be list out in the validation cell2. Its look likes a filter. but without filter i need this for creating application.
I will copy a report into sheet 1, starting in the same place(A1), on a weekly basis. The headers are: Item, Quantity & Price
Week 1's report shows the following:
Windows 5 $1.00
Doors 6 $1.50
Chairs 7 $2.00
Week 2's report shows the following:
Windows 3 $1.00 (change in quantity)
Tables 7 $5.00 (new item)
Chairs 7 $2.50 (change in price)
I need to have a rolling, unique list on Sheet 2 that will do the following:
- add new line items
- keep old line items while checking to see if the quantity or price has changed.
Therefore, after week 2's report is pasted on sheet 1, Sheet 2 should look like this:
Windows 3 $1.00
Doors 6 $1.50
Chairs 7 $2.50
Tables 7 $5.00
Here's the most important part: There will be formulas in Column D in Sheet 2 that I need to stay with each item. generating the desired result on Sheet 2!
I need a Macro to create a unique, rolling data list. On Sheet 1, a report is pasted(from outside source) in cell A3(Rows 1 & 2 are headers) on a weekly basis. It is always the same amount of columns wide(138). The number of rows will always vary but will never be more than 500.
Here’s what I need for Sheet 2:
-Column A should be a copy from Column A on Sheet 1 (data is ITEMS)
-Column B should be a copy from Column BH on Sheet 1 (data is QUANTITY of items)
-Column C should be a copy from Column BI on Sheet 1 (data is PRICE of items)
Here’s the challenge (when the new report is pasted on Sheet 1):
-Sheet 1. Find new ITEMS not on Sheet 2 and paste them at the end of the report on Sheet 2 along with their QUANTITY and PRICE
-Sheet 1. Find any ITEMS already on Sheet 2, then update new QUANTITY and PRICE from Sheet 1 onto Sheet 2
-Sheet 2. Leave ITEMS alone that are now missing from the new report on Sheet 1
I have a spreadsheet of 12 columns (A to L), with a list of student names under each down to row 31. Each column represents an after-school club that the child can attend.
However, a child can sign up to multiple clubs, so their name can appear in several places across the spreadsheet.
What I would like to do is have a 13th column which is an overall list of students who have signed up to at least 1 club, i.e. their name appears at least once in columns A to L.
How do I achieve this so that only unique entries are listed and duplicates are removed in this 13th column?
In cell H5 there is list of dates and in H7 there is codex, based on these two conditions in H11 a data validation list should be populated from the code_sheet using column B. The populated list should be unique entries, as the column B in code_sheet has duplicates.
View 9 Replies View RelatedI have 6 worksheets in my file. In sheets 1-5, column A2:A26 list people's names. Some people's names appears on more than one sheet. Not all cells are populated with a value.
ex.
SHEET1
COLUMNA
Bill
[Code]....
My attempt was... =INDEX('Week1:Week5-!$A$2:$A$26,MATCH(0,COUNTIF($A$1:A1,"Week1:Week5"!$A$2:$A$26),0))
where the sheets were Week1-Week5 and the values on each sheet was A2:A26. But I think there's an issue with Excel being able to 3D reference for these types of functions.
I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
I'm after a formula to find the smallest unique value in a range
For example..
1
1
2
3
Small(Range,1) = 1
Small(Range,2) = 1
But i need the second row to show '2' as the result.
Each product is represented by a serial number (column A).
The can be sorted on column A from smallest to largest prior to calculating results if that helps.
The repair list contains 1 entry per spare part used, so the same serial number may occur several times.
Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).
The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.
Sample list:
Serial........Repair date
207742052008-09-04
207755082008-12-17
207755212008-12-31
207755212009-01-22
207755212009-01-22
207755212009-01-22
207755212009-02-13
207755212009-07-24
207755362009-05-20................................
I have a data table that repeats as follows:
CITI
SCB
SCB
SCB
SCB
SCB
SCB
SCB
RBC
RBC
RBC
RBC
From the data above I need to make a new unqie list that would grab all unique entries.
CITI
SCB
RBC
I am trying to use the following guide:
http://blogs.techrepublic.com.com/howdoi/?p=111
Get stuck on "Listing A:
mockup.xlsxStarting out with a 9 by 11 grid.
Column A will have times that need to be referenced in a lookup. (Ex Sat 8:00am)
Row 1 will have basball field codes reference with a lookup. (Example Field 4
Cells B2:I11 have distinct codes or possibly nothing. (An example would be: Tier 1 A1-A3)
These codes will move around from time to time. They will always remain distinct within the grid, but could appear in any of the cells from B2:I11.
I have 2 columns I want to populate with values from the grid.
First is Fields. I want the first cell to populate with the baseball field found in row 1 of which the code Tier1 A1-A3 falls under.
For example if this season Tier1 A1-A3 is found in the column under Field 4, then I need to return Field 4 as a result.
Second is the time. I want the time to be populated based on what row the Tier1 A1-A3 code falls in.
For example if Tier1 A1-A3 is in the Sat 8:00am row, then I need that value returned.
In short, I need to search the grid and find a given value. I then need to use a lookup (or other tool) to return the first value in that row. And secondly via lookup return the first value in that column.
edit: added spreadsheet example. First sheet is grid in question, 2nd sheet is a typical schedule.
The first Team (Scared Hitless) is A1, the team they play first is Sands Duel Fuel (A3) They play at 11:00am on Field TR3.
You will see on the grid under TR3 and Sat 11:00 the code Tier 4 A1v3. This code is what I want to search and correlate into times on the schedule rather than doing the data entry by hand.
I have a spreadsheet with two columns:
The first one will always have a 'W' or a 'E'.
The second one will have a three digit code. Like this:
W 507
W 507
W 507
E 504
E 504
W 505
W 505
W 504
W 504
Is it possible to have output that show how many of each combination there is? Like this(for the example above):
W 504 2
W 505 2
W 507 3
E 504 2
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] ........
I have a file with immunophenotypic markers and their relationship to certain blood cells.
What I need to do is search the list and pull out the groups of defining markers for each blood cell. Some of the cells have many markers, some only have one but I need to find which combinations of markers identify each cell if possible.
I have attached the file (I think!)
I have two different lists of names. One is about 3000 names long and the other has about 3500 names. I want to identify the 500 people that are not in the first list that are in the second. If there anyway to do this? I was going to put them in A-Z order and then go through and using an IF(A=B,"yes","no") and go through and insert new cell when ever it says no till it says yes BUT the functions start messing up when I insert a new row and it's taking way too long.
View 2 Replies View Related