Compile List Based On Identifier
Feb 20, 2008
I am trying to achieve the following. In D2: AQ2 there a values indicating the subject identifier for that column, eg in D2 it is SEN and E2 is Art. Each row represents a pupil name into which grades are inputted. I want to compile a list of column identifiers that corresponds to a D grade (it could be D, D- or D+) eg
A2 Name ----D2 SEN --- E2 Art --- F2 Business Studies
A3 Bob ------D3 D+ ----- E3 A -----F3 C
A4 Tim ------D4 C -----E4 D------F4 D
A5 Pete------D5 D------E5 D+ -----F5 D-
Expected output for Bob is SEN. Expected output for Tim is Art, Business Studies. Expected output for Pete is SEN, Art, Business Studies.
I've enclosed a small sample of the actual worksheet. Expected results for row 3 start in cell CP3.
View 5 Replies
ADVERTISEMENT
Oct 15, 2007
I have been attempting to solve a problem for quite some time and would love any help anyone can provide. I'm on Win XP, Excel 2003.
I have a series of assets each with a unique number to identify it in it's own row. In a separate worksheet I have a column with just these numbers next to a column with a word assigned to each number. Some are only listed once but many are listed multiple times. I was looking for a function that will retrieve the information in the second worksheet and place it in the row that it corresponds to the number in the first sheet. I've listed an idea of what it looks like below. I'm looking for it to search for every instance of that number and provide what's next to it. The first set of information is what I have, the last is what I'd like it to look like.
1 hamster..............1 hamster
2 cat......................2 cat, feline, kitten
2 feline..................3 dog, puppy
2 kitten..................4. etc.....
3 dog
3 puppy
View 9 Replies
View Related
Mar 23, 2014
I have a set of 4 multiple choice answers B,C,D,E column "F" lists a # 1-4 which lets you know which column letter (b,c,d,e) the correct answer is in. I want to set up a formula that will copy the contents of the correct answer (based on that # identifier) and copy it into column "A" which is currently blank.
View 7 Replies
View Related
Mar 23, 2014
Here's an example of what i want to do
A_________ B Frank Sinatra C Sammy Davis D Dean Martin E Joey Bishop F 3
I want to put "D"'s value 'Dean Martin' in column A - i know it belongs in A because the "3" in column F indicates its the 3rd answer listed i.e Dean Martin. This format would be the same where there are 4 possible answers on the column to the right dictates which answer is correct.
View 2 Replies
View Related
May 15, 2008
I have information on 7 seperate sheets in a workbook and need to bring the information from each individual sheet and compile on one sheet. I know this is simple and I'm making it a lot more complicated then it needs to be...
View 10 Replies
View Related
Dec 21, 2009
I need to compile a list of all worksheets except Data, Attempted Calls, Completed Calls, Completed MOU, Incomplete Calls, Percent Complete, Busy Calls, and Percent Busy.
I need to put this list in B4
Then I need cells in C, D, etc to reference the value in B as the target worksheet.
All worksheets are formatted exactly the same, except the ones above.
Let me know if you need the data file or not?
View 9 Replies
View Related
Jul 7, 2009
I am starting up a project where I will be managing a certain amount of resources, for this example colored pencils (to simplify the scenario). I have just received lists from the other members of my team requesting particular colored pencils for their portions of the projects, but I do not have enough to fill all the requisitions at the same time. I have created a simplified workbook with two spreadsheets: the first of which has the list of the colored pencils I have and quantities, the second has a compilation of all the requested colors by my teammates.
My Question: Is there either a function or a known macro that would allow me to take the data in the second sheet to create a list of who is waiting for which pencils in the first sheet? And could this waitlist be organized by the order of how many pencils each person had requested with people with fewest requests first?
View 3 Replies
View Related
Jan 17, 2014
I want to essentially create a meal plan and then have my shopping list generated from the ingredients columns/areas into a separate sheet for printing. I need shopping list generation. I have attached a sample of what I am currently working with but still needs a lot of tweaking. I am not the most advanced excel user as you can see but I continually try to improve upon all of my work.
Test_Meal_Plan.xlsx
View 2 Replies
View Related
Jul 3, 2007
I'm trying to count the number of unique values from a list of over 8,000 records -- and it's very likely that nearly all of the records are unique. At the moment, I am keeping track of an array where I store every unique value I find, expanding the array and adding values as I go, and at the end return the size of the array to tell me how many unique values there are in the range. My method works, but it is very slow, even when I turn on manual calculation.
Here is what I have so far:
Function CountUniqueCases(inputRange As String)
Dim i, j As Integer
Dim cCaseID As Range
Set cCaseID = Worksheets("Results").Range(inputRange)
Dim uniqueCases() As String
Redim uniqueCases(1)
uniqueCases(1) = cCaseID.Cells(1).Value
Is there a faster way to do this? I was hoping there was an array search function built in to VBA, but apparently not. My first attempt at running this function returns about 7904 out of ~8000 as being unique.
View 5 Replies
View Related
Aug 30, 2006
I've been trying to create a formula that will take data with a specific status and put those in a list on a summary worksheet.
On my data worksheet I have two columns a category/ name column and a risk/status column, on my summary page I want to generate a list solely made up of names that have a specific status.
I've attached a sample.
View 4 Replies
View Related
Nov 6, 2012
I am a Microsoft Excel 2010 user and am trying to create a usage report for a website that I work on. I have been compiling the usage reports into one spreadsheet. The columns I use are Month, Device, Language, Title 1, and Title 2. I can filter each of these rows and it shows me the number of records found. Each row on this spread sheet refers to a time that someone selected something on the site. For example the row may say;
Month (filtered)
Device (Filtered)
Lan (filtered)
[Code].....
How do I automatically populate a table on a separate sheet with the number of records found for a certain combination of filters applied. So if I'm looking at monthly trending I want to know how many times the stress test was started in May, June, or July, but automatically using info from the multiple filters.
What is a formula I can use to compile the filtered data from above.
Jan
Feb
Mar
Aprl
May
Start
=formula?
View 4 Replies
View Related
Aug 18, 2011
I have a few people all entering in data into excel, since they cannot be all in one sheet they each have their own copy. I would like to be able to extract all data where column P reads "Red" from each users spreadsheet and then delete the corresponding lines from their spreadsheet (end of day activity)
The Sheets will be named
FDEntryU1AM.xlsm
FDEntryU2AM.xlsm
FDEntryU3AM.xlsm
FDEntryU4AM.xlsm
[Code] .......
I will then need to do the same in another workbook for all items Labeled Blue, but that of course will be same code with just an item replaced.
The idea is after they close up the person overseeing the data can import all at once and leave the users with fresh empty workbooks.
View 9 Replies
View Related
Jan 24, 2010
does anyone have any thoughts on any way to create a formula that will create a unique identifier for a single row? I thought about using concatenate to combine a few columns in a row which will probably work 99% of the time, but I cant risk it being wrong 1% of the time. Any ideas on how to incorporate checking if the concatenation (aka the unique identifier) already exists, and if so, add a number or something to it to make it unique?
The data has rows added and removed on a normal basis in random spots on the sheet so I cant simply just have a number incrementing for each row I add as the unique identifier. In other words, the unique identifier should never change even if I add or remove rows anywhere in the sheet.
View 9 Replies
View Related
May 13, 2008
when I code VBA .. I am constantly referring to cells like:
Worksheets("").Range("")
or Workbooks("").worksheets("").Range("")
is it possible to do something like
Dim var_1 as Range
var_1 = workbooks("blah").Worksheets("blahagain")
then to use:
msgbox var_1.Range("A1").value
View 9 Replies
View Related
Jan 5, 2006
I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I now
need to be able to identify each row via a unique identifier, but am having
trouble working out what to do when new rows are added or when existing rows
are copied and paste.
New rows and copied rows should get new unique identifiers, but I can see no
way of doing this. Does anyone have any suggestions? There is nothing unique
about the data that I can hang on to and the "SheetChanged" event does not
fire for inserted rows in Excel 2000.
This spreadsheet is owned by one of our clients, so I can't add new
worksheets or columns to the existing workbook - I can just add cell
comment.
View 14 Replies
View Related
Aug 10, 2007
Here at work we have a set of 12 workbooks, that form an extensive activity plan. I have created an add-in that creates a worksheet menu bar item and adds different functionalities to these 12 files. Because any user might have more than one of these documents open at one time, I am forced to refer to the documents by ActiveWorkbook. A bonus added feature: the names of these files change periodically, but not at the same time.
Before running any procedure I must validate that the active book is one of these files. Currently I am testing the existence of a veryhidden sheet codenamed "shIsValid" in the workbook. Now I'm thinking, there has to be a better way to do this. Simplest way would be if ThisWorkbook object had a Tag property similar to UserForms, but this doesn't seem to be the case. I wonder, is there actually a way to give a workbook any identifier (boolean, string or number) that I can access directly, so I can get rid of these dummy worksheets.
View 3 Replies
View Related
Jul 29, 2012
I'm looking for a code where all those rows which contain some identifier be deleted.
Eg, If any row contains identifiers like "----" , "PROG:" , etc shall be deleted.
NOTE: The identifiers are found on the A Column of any row.
All rows which do not contain the above identifiers shall remain untouched.
View 9 Replies
View Related
Jun 7, 2014
I thought this would be very easy to achieve in Excel but is proving difficult. Basically I have a number of excel worksheets with the headers as below. What I would like to do is to use the staff number from each worksheet to populate and sum a master worksheet. Some staff numbers will be in each of the workbooks while some are only in one. While the employee name should also be the same in each of the workbooks there are some differences in spelling etc.
Mon - Fri Mon - Sat Saturday Sunday/Public Hols OVERTIME
Staff NoEmployee Name6 am - 11 am5pm - 12 Midnight12 Midnight - 8am8am - 12 Midnight8am Sun - 8am Mon
View 6 Replies
View Related
Sep 3, 2008
I have undertaken a project and have hit a brick wall.
I'll try and translate my request in the easiest way I can.
I have a piece of software that stores what software is installed on a machine when it connects to my domain.
I therefore have a list of software that is installed on machines that connect to my domain.
Each machine that connects to the domain has a unique identifier code.
At present I have about 500 different pieces of software on the various machines.
The unique identifier for the software installed on each machine is a combination of the application (column B), the program (column C) and the version (column D)
I wanted to give each seperate piece of software a unique ID number.
I was wanting to run a formula similar to the pseudocode below:
If $b$1="adobe" and $c$1="8.0.0.1" and $d$1="adobe reader", then "1",
elseif $b$1="adobe" and $c$1="7.0.0.1 and $d$1="adobe pro" then "2", else "no software ID"
I am thinking that if I can get every piece of software that is installed on the machines to have a software ID then I can create a software ID allowed list then I can create a formula to identify what laptops have software on the "allowed" and "banned" lists.
The problem at present is getting the sotfware to lookup what software ID they have as they need to check 3 columns with a lookup list.
View 9 Replies
View Related
Dec 14, 2011
How would I set up a formula that can search for a unique identifier if the unique identifier is not know.
I would like to set up this formula in Tab 1 where it looks to column A in Tab 2, which is a column of about 600 unique identifiers, to find any cells containing the string "VA". There will be multiple cells that include "VA" in the string
I would like this formula to collect all the cells in the column A in Tab 2 into a column of cells in Tab 1 beginning in Cell A5. I would copy the formula in Cell A5 of Tab 1 down multiple lines. I can add a segment to the formula that will return a blank if there are no more cells with the indicated string included.
Tab 1 formula begins in Cell A5Tab 2 of Unique IdentifiersFormula that will copy first cell in Tab 2 that includes VA in the string from Tab 2VANewsprint23005-2119Formula that will copy next cell in Tab 2 that includes VA in the string from Tab 2TNPackaging37309etcALPackaging35044etcVAPackaging24526-0040etcONPackagingP0K 1E0NHPackaging03743VAPackaging24521NYPackaging13667MIPackaging49099VA
Packaging24426ONPackagingM1P2Y9ONPackagingM1P 2Y9WIPackaging53512-0386MIPackaging49042VAPackaging24504
View 1 Replies
View Related
Oct 6, 2012
I have a table with an identifier in column A and values in columns B to Z.
The structure is like this:
IdentifierA
value1
value2
value3
value4
IdentifierB
value5
value6
[Code] ......
There is 3.000 rows of data. As you can see some of the identifiers have no values.
On another sheet I need the following table to be generated in this format (two columns):
IdentifierA Value1
IdentifierA Value2
IdentifierA Value3
IdentifierA Value4
IdentifierB Value5
[Code] .....
Note that as IdentifierD has no values it shouldn't be in the new table.
View 3 Replies
View Related
Dec 8, 2009
I think a macro could probably do this, but I am worthless with VBA. What I need to do is consolidate lines of text if they have the same numeric identifier. See below.
ID Text
345 This is a sentence that
345 has line breaks in it, but
345 needs to be consolidated
345 into one cell, if you would
345 be so kind as to help me.
345 KTHX!
346 Oh no, here's another one
346 that has a different number
346 of rows. Eep!
Not sure how to consolidate text or concatenate it, since it's not always going to be the same number of rows each time this happens. I need something with an if statement and...some other crap.
View 9 Replies
View Related
Feb 10, 2010
this formula is on Sheet1 and works ok but would be a LOT better if I could use some variables in it.
=SUMIF('Sheet2'!$D$56:$D$96,Sheet1!G3,'Sheet2'!$E$56:$E$96)
G3 is a date
$d$56 to $d$96 are dates
$e$56 to $e$96 are Dollar amounts
I would like to be able to replace the $d$56 to something like $D
and $d$96 with $D
same with $e...
View 9 Replies
View Related
Jun 15, 2009
I am trying to use a formula that will populate A3 with the value of A2, all the way down the column. (as per the area highlighted in yellow) As you can see on the example sheet, the account code changes periodically, this has been causing me many problems!
As the list i am working on has around 100 differfent codes, it is too time consuming to copy paste manually
Reason for this is so that each invoice number in column B, will have a unique identifer in column A (account code)
View 12 Replies
View Related
Oct 6, 2008
I have a Sheet in a workbook that has about 250 Unique supervisors in it (column A) It has about 2300 rows of employees. Is there a macro that can take the employees supervisor column (A) and break the rows associated to that supervisor into a new sheet & and name the sheet with the supervisor’s name? Can excel have 250 sheets (2003 version)?
View 3 Replies
View Related
May 5, 2014
I have an extract that includes unique attempts to complete a transaction. Sometimes these transactions fail 1 or multiple times. Sometimes they are successful in one try. other times they first fail and then are retried and succeed. I am trying to flag failures in a new column that later succeed in the same session. Here is my data:
Date
Order #
Session ID
Mac Address
Status
5/2/14
O123
A100
11111111F
Failed
[Code] ............
I would what these flagged as such:
Date
Order #
Session ID
Mac Address
Status
Successful Retry?
5/2/14
O123
A100
11111111F
Failed
Yes
[Code] ........
View 2 Replies
View Related
Apr 1, 2014
I have tried a slew of different formulas but I can't seem to get the outcome I am looking for. I need to make a station comparison for an individual project build based off a master list. Sheet 1 has the individual build with column A as a part number, column b as a description and column c as a quantity required and column D is blank. Sheet 2 has a master list of every part we carry formatted as: column a as a part number, column b as a description, column c as a quantity required and column d as a unique station identifier for that part (which is always a number).
What I am trying to do is match the part number on Sheet 1 Column A to Sheet 2 Column A, and when a match is found, take the unique identifer under column D associated with that part number and have it displayed on Sheet 1 Column D.
Sheet 1 will change with each build, but it will always be the same format in columns a,b,c,d. Basically I have a format on sheet 1 for what is needed to build each custom kit (787 kits) and sheet 2 carries every part we offer along with the identifer in column d (always a number value). I want to create a formula I can easily copy to match the value in sheet 1 column a to sheet 2 column a and add the identifier from sheet 2 column D to sheet 1 column D. The average kit is roughly 120+ parts so doing that for each part 787 times will be a nightmare. Also in case this matters, sheet 1 column A will have the parts arranged in a random order based on what is desired for that kit. Sheet 2 column A has the part numbers in order from our lowest part number to the highest number (basically an entire inventory list sorted from lowest to highest).
Example
(For sheet 1, since it doesnt carry formating 11111111 is in column a, nut, flange is in column b, 1.00 is in column c and the output data i want would go into column D)
Sheet 1
A B C D
11111111Nut, Flange 1.00 *Starts blank* -looking for - Output from Sheet 2 Column D (1)
33333333Bolt, Squared 4.00 *Starts blank* -looking for - Output from Sheet 2 Column D (4)
55555555Bolt, Coated 3.00 *Starts blank* -looking for - Output from Sheet 2 Column D (2)
22222222Bolt, Hex 4.00 *Starts blank* -looking for - Output from Sheet 2 Column D (11)
44444444Bolt, Screw Type 2.00 *Starts blank* -looking for - Output from Sheet 2 Column D (3)
Sheet 2
A B C D
11111111Nut, Flange 1.00 1
22222222Bolt, Hex 4.00 11
33333333Bolt, Squared 4.00 4
44444444Bolt, Screw Type 2.00 3
55555555Bolt, Coated 3.00 2
View 1 Replies
View Related
Jul 8, 2014
I would like to copy values corresponding to a unique identifier from one sheet to another, using the vlookup function. The problem is that after a certain cell the values are given as N/A even though they are present in the sheet I want to copy them from.
View 3 Replies
View Related
Sep 22, 2008
I would like to combine values from multiple records into a single record using a unique identifier. In the example below 'ID' is the unique identifier.
For example:
View 3 Replies
View Related
Mar 8, 2012
I'm using Excel 2010 on a Windows 7 machine.
I have one sheet with a large list of dates and total prices.
I have a second sheet with a list of dates and unit prices.
I want to divide the total price in my first list by the unit price on my second list.
I want to somehow point Excel to the date in the first list, and then lookup the corresponding date in the second list to use the correct unit price.
My thoughts thus far have been along using VLOOKUP with WEEKNUM and YEAR but I've been unsuccessful.
View 2 Replies
View Related
