Identifying Data In One Column Based On Highest Figure Data In Another Column?

Jun 19, 2014

I am trying to write a formula that shows the best grade for each each student and the subject in which
that grade was achieved.

In a previous post i was shown how to identify the highest grade

MAX(IF(A$3:A$34=A7,C$3:C$34))

I hit CTRL, SHIFT & ENTER to activate the formula - Result in column E

However, I also want to identify the subject in which they scored their highest grade in column F

I have two problems

1. I don't know how to write a formula that brings in the subject based on the grade for each student

2. I don't know how to write a formula in case there is a tie (see DAVID L)

View 11 Replies


ADVERTISEMENT

Find Highest No. In A Column But Inclusion Is Based On Data In Other Cells

Oct 26, 2009

Iím keeping tabs of some clay pigeon shooting scores. I go to alternate locations each week and normally shoot 100 clays, however sometimes itís only 50. Iíve used MAX to find the highest score in Column D of a spreadsheet and it did what I required. However I now wish to find the highest number in Column D - but only include rows if Column C = P and column E =100.


Col B Col C Col D Col E Col F
date Location score out of % hit

25/10/2008A6210062
08/11/2008P3510035
22/11/2008A4210042
06/12/2008P6610066
20/12/2008A6010060
03/01/2009P295058
17/01/2009A5110051
31/01/2009P4010040

In other words I want to find the highest score for location P but only if that week it was out of 100 shots. It would return 66. Then I can do the same formula for location A and it would return 62. I canít see how to do this and have searched the forum to no avail. It doesnít look like I can just use the MAX anymore and Iíve tried incorporating that into a (nested) IF but unsuccessfully.

View 2 Replies View Related

Merge Two Workbooks. Copy Column Data Based On Numerical ID Match Of Another Column.

Mar 13, 2009

I am trying to get excel to search a workbook/(or worksheet if easier) for a matching unique value and fill in its associated data. My first workbook has the SKU (A) filled in but not the UPC (B). My second workbook has both the SKU (A) and the matching UPC (C) filled in.

I need to take both workbooks/(worksheets), compare the SKUs, and if a matching SKU is found, extract the UPC from Workbook 2 and fill in the UPC field in Workbook 1, and if no UPC is present in Workbook 2, then it leaves the cell in Workbook 1 blank.

View 2 Replies View Related

Creating Validation List From Table Column Based On Data In Another Column

Dec 11, 2013

If I have a table as noted below with the following assumptions:

- this table will likely grow
- the 'Include' column data will change based on external criteria/formulas, so the 'Include' column will not be sorted.
- Macros aren't an option as this sheet needs to be macro free.

A
B
C
1
Item
Calories
Include

[Code]...

How do I build a formula that I can place in a data validation drop down to only include 'Item's that have Yes indicated in the 'Include' column?

I've been researching this and found answers if the 'Include' column was sorted via offset, but I haven't found any to sift through when unsorted. I feel like there is a simple answer to this that I am missing. Here is the sheet --> ExampleSheet.xlsx

View 1 Replies View Related

Move Data From One Column To Another Based On Column Headers And Row Text

Feb 7, 2014

I am in need of restructuring a spreadsheet of addresses for mailing purposes. (I tried to find something similar answered previously, but nothing seemed to work for me.)

I have attached a small example spreadsheet below, but our spreadsheets can be hundreds or thousands of rows in length.

I need a macro that will look for the "PO Box" addresses under the column headers named "ADDRESS2" and "ADDRESS3".

The PO Box addresses will need to be moved under the column header "ADDRESS1" within the same row.

It will need to overwrite the text that is already under "ADDRESS1" and delete the text from the "ADDRESS2" and "ADDRESS3" columns - UNLESS the text in 'ADDRESS2" is a PO Box AND "ADDRESS1" begins with "c/o".

If the data in "ADDRESS2" or "ADDRESS3" is anything other than a PO Box it will remain the same. As will "ADDRESS 1".

Basically if there is a PO Box it needs to be in the column named "ADDRESS1" and overwrite anything else that was there. The exception will be for PO Boxes that are in c/o someone else, the PO Box will then need to be listed in the column directly after the column that has c/o.

If the c/o exception will be too difficult the code could just highlight those scenarios and we could fix them manually. We usually do not have a lot of them, but enough that we need to be mindful of them.

The different scenarios are listed in my sample spreadsheet.Also, the code will have to use the column header names in row 1 because those headers are not always in the same column.

View 14 Replies View Related

Populate Column Based On Change In Data Of Another Column

May 30, 2014

I am trying to find a way to populate a column based on the changes in the data of another column...

For example, I want to fill in the code column....so when the customers cost changes, I want a numeric code to populate & change. So for the first 5 lines, the code could be 21, and then when the cost changes to $37.51, want the code to change to 22.

CodeCustomer's Cost
$34.10
$34.10
$34.10
$34.10
$34.10
$37.51
$37.51
$32.50
$32.50
$33.48
$33.48
$34.10
$37.51
$37.51

View 7 Replies View Related

Count Data In Column X Based On Conditions In Column Y

May 6, 2009

I am wanting to count the number of records (excluding cells with no value) based on criteria in a corrosponding column. In column "AS" I have a number of records that are not sorted showing values "7", "13" and "2".

In column "AL" there are values attached to some of these records based on certain IF statement conditions. I am wanting to count and sum the number of records in column "AL" that meet the conditions of "7" in column "AS" and so forth.

View 3 Replies View Related

Clear Contents Of Column Based On Data In Another Column?

Apr 10, 2014

I need to clear the contents of a columns G and H starting 11th row based on what is there in F column. The macro should check for last non-empty cell in column F starting F11 (assume it finds F30 to be last non-empty cell), then it should clear the contents of G11 to G30, H11 to H30.

View 1 Replies View Related

Pull The Data From The First Column Based On The Ranking Of The Second Column

Sep 15, 2005

Item Quantity
A 2
S 7
D 3
F 6

I am looking for a ranking formula that will pull the data from the
first column based on the ranking of the second column
so that the end result will look like this
1 S
2 F
3 D
As S has the largest quantity, F 2nd largest etc

The list I will pull this from is variable in length but in the
hundreds.

View 9 Replies View Related

Get Column Alphabet Based On Data It Contains Or Column Heading Using VBA?

Oct 28, 2011

How to get the Column Alphabet based on the Data it contains or the Column Heading using VBA?

Is it possible to get the Column Alphabet using any Macro or any function that within a Module it can always take the New Column Name during Execution..

Example: I have certain Columns where I have Yes and No Tick using the Wingdings P and Y..
Now these Columns are alternately Placed and there are six columns in all and they are spread over 12 Columns as the Alternate COlumns is reserved for Manual Entries.

Now I intend to increase the Manual Entry Columns but the problem is that every time I do that I need to make changes in my VBA Code.

Is it possible that even when the Columns are Inserted or Deleted in between before or after these columns I dont need to change the Explicit references by changing some approach.

I am not fuly conversant in VBA but use it whenever things are not completely feasible with Formulas AFTER GOOGLING.

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("X3:X13")) Is Nothing Then ' You can Change the range here
Cancel = True

[Code]....

As you can see the Ranges are Alternately Placed and there are about 6 such Columns with the above TICKS..

Now, how do I keep it FLoating so that even after changing the COlumn Location it does not need to get Updated i.e. X and Z Column Alphabets..

View 9 Replies View Related

Summing Cells In Column Based On Data In Another Column?

Feb 27, 2012

I have a statement from an account (which happens to be the government) in which they list every invoice they are paying and each item on that invoice. But they don't have an invoice total. I'd like a way to add up the item totals for each invoice and put the total in column D. Each invoice could have 1 to 10 different items on it.

A(invoice#) B(Item) C(total) D(invoice total)
111 widget 1 $5
111 widget 2 $10
111 widget 3 $8 XXXXX
222 widget 1 $5
222 widget 5 $15 XXXXX
333 widget 2 $10 XXXXX
444 widget 5 $15 XXXXX

I had thought an IF formula would be the way to go.

View 6 Replies View Related

Move Info In One Column Based On Data In Another Column

Aug 28, 2012

I am trying to move a column of numbers based on the information in another column. I've been looking for about a week and find macros that are close but not quite.

In one column it reads Mobile, Home, or is blank. If the number is a Mobile (column R), the area code (column P) needs to move to column S and the phone (column Q) needs to move to column T and the primary phone (column R) needs to move to column U. Home and blank cells remain as they are.

area (P)
phone (Q)
primary phone (R)
col S
col T
col U

[code]....

View 7 Replies View Related

How To Determine Highest Value Based On Another Column Criteria

Jun 14, 2013

I am trying to determine for each account number in column A, the most popular method for accessing our services based on visits in column B. And then having those numbers summarized as a total for each method.

See below for an example. As you can see Mobile was the most popular for 2 clients Java was for 1 and Desktop was not for anyone. Not all accounts will use all methods.

Account
Visits
Method

1
10
Mobile

[Code] .......

View 5 Replies View Related

Sum Data In One Column Based On Same Date Column?

Sep 27, 2012

I am trying to write a formula in a cell that sum the Total/Day and the Total/month and graph it based on Date

example:

-----Date-------- Sale------Total/day-----Total/month

1---10/10/2012 ---$5
2---10/10/2012 ---$7 ------$12
3---10/13/2012 ---$2 -------$2
4---10/17/2012 ---$8 -------$8 -----------$22

1---11/3/2012 ---3
2---11/3/2012 ---5 ----------8
3---11/7/2012 ---6 ----------6 ------------14

1---12/15/2012 ---2 ---------2
2---12/25/2012 ---4
3---12/25/2012 ---2 ---------6 ------------ 8

View 1 Replies View Related

Find Max Or Highest Time In Column G Based On Criteria?

Mar 20, 2014

Im looking to find the max or highest time in column G based on the criteria (date selected and name) So if the date is the 10/02/2014 in G10 it would look up that date and the name and bring back the time in column H. I have put an example answer in H12.

View 9 Replies View Related

Copy / Filter Data Based On Data In Column Occuring X Times

Mar 7, 2008

I have a excel worksheet with the following columns: First name, Last Name, Email address, domain of email, product type, date registered. The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as webmessenger.com appears 40 times, so I want to get that data).

For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times). To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.

So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. company.com 200, yahoo 120, etc... I need it to show me the 200 rows of company.com emails and extract them to a new sheet so that I can then follow up with company.com and see if they are interested in the corporate version.)

View 2 Replies View Related

Lookup Or Data Change Based Upon The Data Reported In Column

Mar 7, 2008

I need to pull info to additional tabs. The problem I am having is that the data is sorted daily based upon performance numbers, so the REP names in column A can change based upon the data reported in column C.

What I want to accomplish:

Pull daily data for each unique name in column A to a team tab/worksheet that will be used to report that team's daily data.

Below is a representation of the data I need to break down by Rep Name and Team.


Rep DATA DATA DATA DATA DATA DATA DATA
1 2 3 4 5 6 7 REP 2 27.5% 14


51 86 REP 1 33.3% 14


42 74 REP 3 19.3% 11


57 86 REP 5 19.6% 9


46 66 REP 4 33.3% 9 ...........................

View 9 Replies View Related

Locate Data Based On Found Data In Another Column

Nov 30, 2006

I have this nice formula (listed below) that I'm trying to use to get the employee names that belong to each manager. How ever using the first formula I only get the first name in each cell and using the second formula (associates is a define name for the range A1:A70) gives me name that do not belong the that manager....

I would like this formula to only pull names from the manager that is listed in the C column....

View 9 Replies View Related

Filtering Out Data From One Column Based On Data From Another

Aug 7, 2014

I have two Excel tables.

Table 1 has Column A, with 500 unique names/codes listed.
Table 2 has Column A with the 500 names, plus 500 more mixed in (all unique). Table 2 also has columns B-G, with values corresponding to each of names in Column A.

Basically, what I'd like to do is filter out from Table 2 the 500 names (and their corresponding info in columns B-G) not listed in Table 1.

I've tried to look around, but most answers seem to be in reference to situations in which Column A has numerical values rather names.

View 2 Replies View Related

Find Lowest 5 Numbers In Column A With Highest Values In Column B

Oct 5, 2013

I am looking for a formula that will find the lowest 5 number group in column A with a total value from column B under $100 in the below table.

1 $28.75
2 $28.00
3 $27.75
4 $24.75
5 $25.50
6 $25.25
7 $25.25
8 $16.50
9 $24.75
10 $26.50
11 $24.50
12 $27.00
13 $26.50
14 $23.50
15 $23.00
16 $19.25
17 $20.75
18 $25.00
19 $20.50
20 $20.50
21 $17.75
22 $22.50
23 $20.50
24 $19.75
25 $20.00
26 $10.75
27 $11.25
28 $5.25
29 $13.00
30 $9.00

View 3 Replies View Related

Convert Column B As Multiple Column Titles And Move Data In Column C Into New Columns?

Jun 30, 2014

I have a excelsheet that looks like this:

Column A | Column B | Column C
Los Angeles | Fire Dept | 3
Los Angeles | Health Services | 12
New York | Fire Dept | 8
New York | Health Services | 22
New York | Internal Services | 100
New York | Public Works | 7
Chicago | Health Services | 15
Chicago | Public Works | 56
Chicago | Social Services | 4

And I am trying to make it look like this:

Fire Dept
Health Services
Internal Services
Public Works
Social Services

Los Angeles
3
12

New York
8
22
100
7

Chicago

15

56
4

View 8 Replies View Related

Formula To Transfer Column Data To Another Column But Omit Cells With No Data?

Dec 12, 2013

I need a formula to automatically transfer data in a column into another column, omitting cells in the 1st column that do not have data in them.

So, for example, transfer the data in column "A" below to column "C" below omitting any blanks when the formula automatically copies data over:

Example Spreadsheet.xlsx

View 3 Replies View Related

Formula That Will Rank Based On Highest Data

Mar 12, 2014

I am building a football stat database which I am looking to put in an individual sheet which will search the other 20 sheets, collate the information and then display a top 5 ranking in each area. For example, this is our setup for the data:

Screenshot 2014-03-12 21.56.18.jpg

So what I want to to is have a separate sheet which will have a section for say over 1.5 goals %. The formula would go and retrieve all of the % results for each individual team. Then it would rank the 5 best teams and display the team name in the first cell and the % in the other cell.

View 3 Replies View Related

If Column Contains Data Then Insert A Blank Column And Shift Specific Column To The Right

Apr 22, 2009

What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.

View 4 Replies View Related

Identifying The Highest From Worker List

Dec 22, 2013

I have a number of workers in one column and how many dvds they have sold for the week in another column. how would I automatically identify the worker from the worker list who has sold the most dvds in that week? is there a formula for this?

View 11 Replies View Related

Identifying The Highest Value Displayed From A Cell

Jul 15, 2008

I want to be able to record the highest value displayed in a cell (b2) in another cell (c2). Cell b2 is a cell that has continuously updating numbers from a live feed, which are updated approximately every second.

--I'm not sure if I need to have a log created based on the changing numbers. If so, I wouldn't anticipate the length of time to determine the highest value would be more than 7 hours.

--Is there a function/filter that would be able to report the highest number recorded?

View 10 Replies View Related

Locate Highest Number In Column B Then In Column C?

Mar 10, 2013

I need to locate the highest number in column b then in column c minus the users score from the highest so they can see how much they are behind the highest score. eg.

A B C D
fred 150 highest
dawn 125 25 points needed
mark 100 50 points needed
kevin 80 70 points needed

View 4 Replies View Related

How To Sort Data Based On Numbers From Lowest To Highest

Feb 17, 2014

I can't find a way to sort my data. It looks similar to below. And I need to sort them based on the numbers from lowest to highest.

Tom Simmon (23.2)
Jason Paige (19.1)
Jeff Johns (14.1)

View 8 Replies View Related

Show Highest And Lowest Figure In A Range

Oct 13, 2008

how can I get excel to show the highest and lowest figures in a range to display in another 2 cells. Can this be done without having to sort the data and remove all of the other rows except highest and lowest. I am using Excel 2003.

View 5 Replies View Related

From A Value Select A Range Then Find The Specify The Highest Figure

May 19, 2009

I am trying to work out how to select a range from a formula.

One formula works out when a specific number in a list of rows is reached, and returns the number of values it counted before it reached the number.

MATCH(TRUE,INDEX($A$2:$A$50>=B1,0),0)

With the figure returned from that can I then select the range from the first row, to the number of rows counted. And with that selection find the highest number within that range, specifying the highest number as the result?

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved