Vlookup - Finding Additional Values From Duplicates Using Additional Column
Jan 29, 2014
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.
View 1 Replies
ADVERTISEMENT
Mar 17, 2014
Sheet 2 in the attached sample workbook (without all the pre-existing formula's from the original) contains data that I paste in (Cols A to D).
Sheet 1 then searches that data (minus Col D) from Sheet 2 and returns "Yes" to Col I if matching data is found.
What I would like to do now is if Sheet 1 returns "Yes" then the corresponding value from Sheet 2 would be returned to Sheet 1 Col J.
So in this example the value "5" would be returned to Sheet 1 Cell J1
I already have all the formulas in place to find the matches and return "Yes", this is just to return the additional information from Col D Sheet 2 to Sheet 1 Col J..
View 3 Replies
View Related
Aug 13, 2012
I need to count the amount of unique values in a range, but only count unique values that also have a seperate condition. To illustrate with the 2 column dataset below....
Colour Letter
Yellow A
Yellow A
Green B
Yellow B
Blue A
Blue A
Yellow C
For this set of data i want 3 seperate cells for each 'Letter' telling me that, for 'A', the answer is 2 unique values (i.e. Yellow and Blue). For 'B' there is also 2 unique values (i.e. Green and Yellow) and finally for 'C' there is just 1 unique value (i.e. yellow).
View 2 Replies
View Related
Mar 25, 2013
I'm having where basically I've got the below scenario on a spreadsheet:
Start Date - 14/03/2013 (Cell A2)
I want to look up this date against a 52 week structure to see where it is in our "working calendar year". I've got a table which has the following fields:
Week Start (Cell E2) - E.G 11/03/2013
Week End - (Cell F2) - E.G 18/03/2013
Week Title - (Cell G2) - Week 1
This table follows the same format for all 52 weeks.
Now in this example "Start Date" falls between the "Week Start" and "Week End" date so I want to output Week 1 in cell B2. However I could have a "Start Date" of 29/03/2013 that falls in week 3 of the working calendar.
View 1 Replies
View Related
Jun 9, 2014
I have 400 source files containing (among others) 8 sheets with daily results: "Fri 23", "Mon 26", "Tue 27", "Wed 28", "Thu 29", "Fri 30", "Sat 31 (if applicable)", "Mon 2".
Each sheet contains also:
State - D1
Role - D2
Staff ID - D3
Date - D4
Activity group name in column A (starting from row 8)
Activity type in column B (merged with C and D) (starting from row 8)
Activity time in columns E:GV (starting from row 8). Usually, there is none or only one value in whole range (e.g. E8:GV8). But sometimes there are two values.
Customer ID in row 6 (value appears only if time was reported in E:GV range)
CC Number in row 7 (value appears only if time was reported in E:GV range)
It's all about transferring values from all daily sheets in all files (.xls) sitting in folder C:WADFinal to one simple table (WAD_Consolidation_file.xls, sheet "Consolidated") consisted of 9 columns: Staff ID, Role, State, Date, Activity Group, Activity Type, Minutes, Customer ID, CC Number.
Additional note if two values exist in the same row they should be copied as two separate entries to consolidation file.
View 9 Replies
View Related
Apr 10, 2013
i have data stored like:
col1
XXXX1244 50
XXXX1519 60
XXXX1244 50
xxxxx1111 10
xxxxx1519 65
the last 4 caracters are numbers. I need to test these numbers and sum the corresponding values them in a single cell without adding new column(SUMIF like).
so in the above example I need to sum all ending at 1244 or 1519, therefore the sum showed in the single cell equals 225
to extract from a single cell: =VALUE(RIGHT(D8;4))
I tried to use an array formula but it seems to crash if a blank cell is in the array
View 5 Replies
View Related
Jan 16, 2014
I am working on a project that requires Other or Additional info to be available if I click on a specific item. For example, if I click on a person's name a message or pop up show appear with the person's address or any other information I might want to add. Is this possible in Excel or should I be using a different program?
View 1 Replies
View Related
Aug 2, 2012
I have a macro attached to a command button to clear entries in my workbook for me however, I left out one tab that needs to be cleared.
I need to add the following tab and cells.
Add Tab "Time Off Glance", Cells (B1:P35) to the same command button.
The code I have now is:
Private Sub CommandButton2_Click()
Dim rng As Range
Dim x As Integer
Dim y As String
For x = 1 To 27
y = x
Union(Sheets(y).Range("B9:O15"), Sheets(y).Range("Q9:T15")).ClearContents
Next x
End Sub
View 5 Replies
View Related
Mar 16, 2007
I am trying to find some better looking command buttons for userforms in excel VB.
Maybe like the ones you can use in Access from the command button wizard. inoculars for find, door for exit etc. Does anyone know how to get these in excel VB editor?
View 2 Replies
View Related
Mar 28, 2014
I have a code that searches in a column for a certain value, if it finds it, it copies this value to a range adjacent to it.
[Code] ..........
However, if the cell where the code is supposed to find a new value is blank it makes the range blank. But in that case, I wan't it to leave the range cells with their original value.
View 11 Replies
View Related
Jun 21, 2009
I am building a database of music files and using Excel(2007) to catalog them. As an extra bonus I'm setting up a sheet of statistics about the database with suchs things like the # of tracks and longest song, etc.
Most of it has been easy to figure out but there's one formula I'm not quite sure how to do, or the way I think it is to be done is incorrect or contains some error on my part. I already have a cell that gives me the length of the longest song in the database. What I want it to do is give me the name of the longest song. This is what I tried to do, without success, giving me an popup error telling me that the formula has an error.
View 3 Replies
View Related
May 5, 2007
I have one sheet that receive data automaticly.
I have colorfunction in the end to count the red,yellow and green points.
My problem is sometimes there are points that have more than one
coordinate (X,Y,Z) as you can see on line 6,7,8 and they should count only as one.
The criteria for that is if any of them is yellow it should count as yellow,or if any is red it should count as red, always the worst result.
View 9 Replies
View Related
Jan 6, 2009
So we created a workbook that has lots of worksheets inside it. The problem is due to the high number of worksheets there are alot of tabs along the bottom. Many of the tabs are hidden behind the scroll bar. Is it possible to have 2 rows of tabs at the bottom of the screen?
View 2 Replies
View Related
Apr 1, 2013
I'm working on a spreadsheet that needs a lot of check-boxes and every time I add one I get the text "Check box" right next to it. I can't seem to get rid of it.
Format control - Alt Text didn't work.
View 2 Replies
View Related
Mar 10, 2014
I have problem with additional "hidden"(duplicated?) sheets (not by atribute)
This is my excel, created from new fresh file : excel tables.bmp
And this is what i see when i get all sheetnames (with C#, word and some other programs) word-tables.JPG
where A1$ have same value as A1$_4349_inkjet_WZ
I have this problem with some other files but in this one i have 100% confidence that none of sheets is hidden nor very hidden.
I found that 3 proper tables are "System table" and 2 additional are "tables".
[URL] .....
View 1 Replies
View Related
Aug 18, 2014
I have an existing macro that convert active sheet to pdf then email it as an attachment thru outlook.
now, what i need is to attach another pdf file in it, so it will now be 2 attachment. here is my codes:
var all_spans = document.getElementsByTagName( 'SPAN' );
for( var i =0,skip =0 ; i [code]......
View 6 Replies
View Related
Feb 3, 2009
I have seen this on many spreadsheet but never figured out how to do this - click on a cell in a spreadsheet to open up a new spreadsheet?
View 4 Replies
View Related
Dec 29, 2011
I need to add a condition to a statement that adds a "If TRUE" reference to another cell.
The other cell can be either a TEXT value "RM1" OR "TE2". If neither condition is true than the value is blank or zero.
So right now I have in the destination cell =IF(AB7="CONT",V7,"") That works lovely, but really isn't countable unless cell "Z7" ALSO has Either "RM1" OR "TE2".
If cell "Z7" has the text "DEA" or "SP" then the value of "V7" will be placed in a different column. I will use a different destination column (for this modified "DEA" or "SP" condition)
View 7 Replies
View Related
Aug 3, 2012
I have a spreadsheet that records data on various sites with data entered on a userform.
Every so often a site may need to be added or deleted. Is there anyway to use VBA to add an additional field to a Userform, and then also delete the field if necessary?
View 2 Replies
View Related
Aug 13, 2013
Trying to perform a match of 2 sets of data.
Data set 1 is 2 columns: A = ID number, B = Text entry
Data set 2 is 1 column: C = Text entry
All of the cells in column C can be found in column B so I'm trying to match that data but I also want to create a new column that lists the ID number for the matched cell.
For Example:
Column A
Column B
Column C
Desired Result
1
Sock
Underwear
9
[code].....
I've tried to do this MATCH and VLOOKUP. Kept getting error messages. I threw a stapler.
View 5 Replies
View Related
Jan 17, 2008
The codes below allows me to change the color of the cells in Col F & G dependence on the value of Col G. I like to add another condition and that is if Col K the value is cancelled or closed the whole row will be CellColor = 48: FontColor = 2
Sub Risk_Color()
Dim c As Range, myFontCol As Integer, myCol As Integer
For Each c In ActiveSheet.Range("f7:g20000")
myFontCol = xlAutomatic
myCol = xlNone
Select Case c.Value
Case Is = 1, 2, 3
myCol = 34....................................
View 9 Replies
View Related
Sep 18, 2008
when the user enters the letter Y in a cell within a set column a macro kicks in and enters a VLOOKUP formula in a cell 1 row above and 4 columns to the left by using ActiveCell.Offset (-1,-4).
This seems to work fine however when I try and get the coding to copy and paste special as values it just seems to ignore it?
View 9 Replies
View Related
Sep 20, 2008
i have a macro that copies info from cell in row A and uses that info to name the whole row, now if the name has a space between the words it uses _ (underscore) substition so i don't get an error (when row name is defined - you can't use any special characters or spaces) i was wondering if anyone can help me to add "-" besides the space
here is the macro
Sub Macro1()
Dim a As Long
For a = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(a, 100) = Application.WorksheetFunction.Substitute(Cells(a, 1), " ", "_")
ActiveWorkbook.Names.Add Name:=Cells(a, 100), RefersToR1C1:="=Sheet1!R" & a
Next a
End Sub
View 9 Replies
View Related
Oct 8, 2008
I've managed to create a drop-down list with the following possibilities:
Yes
No
Others
When the option Others is selected, I would like the user to be able to input additional information in a new line with wrap text formt (this new line should be hidden when either Yes or No is selected).
View 9 Replies
View Related
Apr 11, 2008
As a follow-up to Cannot Access Additional Controls From Toolbox.
I have been having the same problems adding additional controls to the toolbox in Excel VBA on Windows Vista running Office 2007 Pro.
I ensured I had a full install of Office and I was logged in with admin rights.
Two work-arounds:Create a form in Excel on a machine that has the control you need to use, then export the form. Copy it to the PC that doesn't have the additional controls functioning properlyOn my machine, creating a new profile and running VBA enabled the additional controls *only for this profile*, not the existing profile.
While these work-arounds don't solve the problem, they point to it being a profile issue, not an Office installation issue.
Hope this saves you the hours of Googling that I have spent trying to find a solution.
View 3 Replies
View Related
Jun 18, 2008
The code is as follows:
Sub addtaskpc()
ActiveSheet.unprotect
Application.DisplayAlerts = False
myrow = Cells. Find(" Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
.EntireRow.Copy
.EntireRow.Insert Shift:=xlDown
End With
Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True
ActiveSheet.protect
End Sub
View 9 Replies
View Related
Feb 18, 2014
I want to use excel to create quotations for customers. (currently doing it in word)
Tab 1 is the quote, has 4 columns, part number, description, cost, yearly maintenance
Tab 2 has all the things we sell, part number, description, cost, yearly maintenance. 150 lines.
So, Tab 1, description column is a drop down box from Tab 2. Working fine.
Question. When an item is selected from the drop down, how do I get it to then bring from tab 2 the part number, cost and yearly maintenance.
View 8 Replies
View Related
Sep 29, 2009
I have two worksheets, 'monthly budget' and 'summary sheet'.
Summary sheet has been set up so that any additional records added to 'Monthly Budget' will be copied using the formula below (kindly provided by Cheeky Charlie yesterday):-
=IF(ISBLANK(INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A))),"",INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A)))
I have set up a unique entry count on 'summary sheet' which is currently showing 5. If I add a new name 'Jim' to the 'Monthly budget' sheet, this is copied on the 'summary sheet' however the unique entry count remains at 5.
View 5 Replies
View Related
Mar 14, 2014
I'm attempting to create a worksheet to consolidate his financial data.
How to add the last loop or where to place it.
I can copy the values from the P&L sheet to the DATA sheet but I cannot discover a way to also pull the values from the BalSht sheet to the Data sheet.
I think the BalSht loop will be almost identical to the "P&L sheet loop, and be placed just below it - but I'm not successful in multiple attempts and days of trying.
How can I accomplish this? Here's the VBA code:
[Code] .....
View 3 Replies
View Related
Jan 25, 2012
I have a rather expansive spreadsheet with inventory statistics and want to insert some cells for an additional calculation and it will not let me add or delete cells. when i select the range to insert the cell my only choices are insert/delete rows.
View 2 Replies
View Related
