Find Highest Value In Column And Then Copy Contents Of Another Cell In That Row?
Aug 18, 2014
I have a worksheet where columns C, F, I, L, O record scores within a league. Each row records a persons score in that league and there are two rows per person recording their score and their handicap score. So Person A would be on rows 3, 4, Person B on 5, 6 etc. The persons name is recorded in Column B.
What I would like to do is to have a cell(s) elsewhere in the worksheet which show the highest score in that league and display that score and the name of the person who achieved it. This ideally would need to be done for the highest score and the highest handicap score.
For the life of me I can't even begin to work out how to do that or even know if it is possible in Excel.
So to clarify, lets say the highest score is in cell L7 and the highest handicap score was in M3. The cell(s) containing this formula should then show the name in B7 and the score in L7 and below it the name in B3 and the score in M3.
View 7 Replies
ADVERTISEMENT
Dec 17, 2013
I have a script that copies data to files based on many cells contents but where I am having a problem is creating series numbers for each file.
File-01.txt
File-02.txt
..
File-100.txt
In my current code I copy files to folders by date and each folder I need series of files (Lab Testing series)
In column A1:A100 I have a series of numbers 01, 02, 03 ...100
Column B contains the Files to be saved
C:LAB2012Jan1file-01.txt
C:LAB2012Jan2file-01.txt
C:LAB2012Jan3file-01.txt
C:LAB2012Jan4file-01.txt
...
..
C:LAB2012Dec31file-01.txt
So this works fine
I now need the Script to do is to loop to Column A and select Cell 2 and do the File Copy again on the Next series
C:LAB2012Jan1file-02.txt
C:LAB2012Jan2file-02.txt
C:LAB2012Jan3file-02.txt
C:LAB2012Jan4file-02.txt
...
..
C:LAB2012Dec31file-02.txt
When Complete repeat until it reaches the end of column A
Since my Cells are populated by all the data in the workbook I thought at the end of my copy script I would take the next Cells data in A and put it in Cell H8 where all the constants are for the file names.
Column B is built using
=IF($C1="","",$I$1&$G$4&$D1&""&$I$8&$H$8)
=IF($C2="","",$I$1&$G$4&$D2&""&$I$8&$H$8)
View 1 Replies
View Related
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
Jul 30, 2013
I'm using excel 2010 and windows XP with a moderate amount of experience tinkering with macro programming. I know what I need is very doable but I can't get my head around what the code would look like. I must not be wording my searches correctly because most of what I'm getting for results are iterative programs based on a cells value which isn't what I need.
I'm trying to build a macro that will check a cell (C3) and based on the contents of that cell copy a column (I) to one of 12 different columns (K:V). So if the value in C3 is 1 it should copy I to K, if the value is 2 it should copy I to L, and so on.
View 2 Replies
View Related
Mar 8, 2012
I have two columns each having 8500 rows.
A= Zip code
B= Sales
Duplication of zips appear in column A - and I only need to return the highest sales in B. So based on the example below..I only need to return $500 in column C.
A
A2=50201
A3=50201
B
b2=$345
b3 $500
View 4 Replies
View Related
Sep 12, 2012
make and loop that loops true all values in column A and B. For example, if the cell A3 is higher the cell B3 then I want it to write the higher value in new sheet.
View 2 Replies
View Related
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
Oct 26, 2009
Im keeping tabs of some clay pigeon shooting scores. I go to alternate locations each week and normally shoot 100 clays, however sometimes its only 50. Ive 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 cant see how to do this and have searched the forum to no avail. It doesnt look like I can just use the MAX anymore and Ive tried incorporating that into a (nested) IF but unsuccessfully.
View 2 Replies
View Related
Feb 24, 2007
Starting at C6 and running down the C column there is a numerical list of values which have been generated from a PivotTable. I'd like some code that will scan down the C column and copy the highest 10 values in that column and the contents of the cell in the corresponding A column starting at F5 and finishing at F15 (a top ten list).
A sample workbook with desired output is included.
View 3 Replies
View Related
Nov 19, 2007
I have a list of about 2 million phrases. The list was created by combining two lists in all possible combinations:
a list of about 800 street names
a list of about 1000 property names
example
church street flat
church street apartment
house church street
house king's road
etc
what I would like excel to do is the following:
look at each cell and determine which of the 800 street names it contains. write that streetname into adjacent cell.
this would then leave me with the original column, where each of the 2 million cells has been assigned one of 800 streetnames.
for example:
church street flat | church street
church street apartment | church street
house church street | church street
house king's road | king's road
that's all. I think that can't be too difficult, and probably it has already been discussed here in the forum. unfortunately I did not know for which keywords to search, that is why I couldn't find the posts.
I hope somebody can help me as I need to get this sorted for work as soon as possible. All semi-automatic ways I could think of to accomplish this would take me days that I don't have.
View 9 Replies
View Related
Mar 11, 2009
I have the file here i work with, basically the first column is a legend and the column to the right of it is a pointer column to help me find out where a legend is located in another file. So i was wondering if a macro could be made to basically find where the "legend column" A, C, E ect ends ( every other column is a legend column , one next to it is a pointer column ). and then combine the ends all of the columns contents and put them into 1 column.
In the file with this question i have showed you what i start off with, i highlighed in yellow where each column legend ends, ( normally these are not highlighted and i find them manually ). In the 2nd tab i show what the end result should be. All the columns are now consolidated into 1 column. 1 after the other.
View 4 Replies
View Related
May 30, 2008
I have imported and filtered a .csv. to specified sheet names. I have rows that have been sorted by a specific column's cell contents. i.e.
A B C C E F G H I
xxx xxxx xxx xxx xxx 1 xxxx xxx xxx
xxx xxxx xxx xxx xxx 1 xxxx xxx xxx
xxx xxxx xxx xxx xxx 2 xxxx xxx xxx
xxx xxxx xxx xxx xxx 2 xxxx xxx xxx
xxx xxxx xxx xxx xxx 2 xxxx xxx xxx
I need to be able to select all the rows or ranges that contain a common value 1's and then loop back and select the next group 2's of rows until the row or column contains "".
View 4 Replies
View Related
Mar 2, 2012
pictorial representation find highest in the cell
data & output
View 1 Replies
View Related
Jun 5, 2008
Basically this is what I want to do:
1. Search a specific column (Column 21/U) for non-blank values in Worksheet 1
2. Copy the entire row containing the non-blank values
3. Paste these rows into Worksheet 2.
Repeat steps 1-3 an additional 2 times, where Worksheet 1 is always searched but one more column to the right (ex. Column 22/V) is the target column for the search, then the rows are pasted into the next Worksheet (for ex. Worksheet 3)
View 7 Replies
View Related
Feb 23, 2008
I went through all the forums but confused a little. In the work sheet I want Excel to find an exact word such as letter "a" in all over the work sheet (not a column name) and paste it to the destination work sheet. I am attaching an example file, can some help me to solve the problem.
View 9 Replies
View Related
Aug 9, 2007
What is the proper VBA code to copy the contents of column T and column W (beginning at row 2 and ending at the last nonempty cell) in sheet 1 to column D and column E in sheet 3? Column T and column W always contain the same number of nonempty cells.
View 9 Replies
View Related
Aug 14, 2013
I am working with arrays that extend far beyond their actual content, and so i am looking for a way, through macros, to find the first blank cell in a column and then copy all preceding cells in that column.
View 8 Replies
View Related
Jun 1, 2012
I am trying to work out some code that will allow me to search worksheet2 to find the last filled cell in column A.
I would then like to copy the contents of the cell.
Then past this data into the first empty cell in a column in worksheet2
View 1 Replies
View Related
Feb 9, 2009
Was looking into this, but basically i have a repetitive task of copying contents from various columns, and pasting them into 1 column.
1 right after the other. I figured there could be a way for a macro to copy contents from multiple columns and paste them into 1 column.
I have including 2 files, 1 from the start of the file, and 1 how it should be so it will have clarify what i am trying to do.
So basically if you open book1 , i have factory install tabs,
I need the columns contents from the first tab the (5k-20k) starting with Column B, then E, then H, and so on( Every 3rd column starting with B ) to be copied and pasted in Column A of the (Factory Install Tab) ( last tab ).
If you open the 2nd file this will be clearly understood. In the second file in the factory install tab you will notice i did the first 2. I highlighted each starting part in yellow just so its easier to see. SO in column A you should have all the contents in the 5k-20k tab ( starting with b every 3rd ), pasted in column A 1 after the other.
The next tab is the 30k tab, i need all of the columns contents copied starting with B ( every 3rd again ), copied in Column C of the Factory Install tab. Basically every tab's contents needs to have its own column in the Factory Install tab.
If anyone could help that would be great, this is a very time consuming process manually, and it would seem like a macro could be made to easily copy contents.
View 6 Replies
View Related
Apr 16, 2009
I've got a code that does what I want it to do, but I have to select the start point manually becuase it's position varies on different documents. It's usually around row 650 out of 1200, but the cell I want to start the macro at always contains the word ELECTRONIC. What I would like to do is modify my code so that it finds that cell itself and then runs the rest of the code. I'm not very good with VBA, but I've tried a few things like Dim LR-2 and using that but I get error messages about duplicates.
Here's my code
HTML Sub Macro1()
'
' Macro1 Macro
' SALES ORDER FORMAT
'
'
View 9 Replies
View Related
Dec 9, 2009
Id like to check each row in Column F and Column K of Sheet Check. If Column F has the contents Out and Column K has any contents inside its cell, Id like to copy that row and insert it into Sheet Alert. As a result the same row will exist in Sheet Check and Sheet Alert. This code will cut the row out of Sheet Check and paste the row into Sheet Alert if the contents Out is found in Column F.
View 2 Replies
View Related
Sep 3, 2006
On Sheet2 I have a table where I want M2 to do the following:
1.) Look at cell A2 where the name "John" is and B2 where the name "smith" is.
2.) Look at sheet1 and look in B2 and C2 for "John" and "smith"
3.) Once it matches the name, place the contents of sheet1, cell I2 into sheet2, cell M2.
So in short, match the name on sheet2 to the name on sheet1 and return the number in I2 to M2 on sheet2.
Maybe combining the persons name in to one column would make it easier?
View 9 Replies
View Related
Apr 3, 2007
I want to have a macro that will look at a few cells say A1 & A2 and then use the contents of the cells say 1Q07 & 2Q07 respectively to do a find and replace find 1Q07 and replace it with 2Q07. I want it to use the contents of the cell and not just the text because the contents of the cell will change each time used. I tride to do it here but I dont have it yet. It doesnt seem to pass the value to the variable and the variable doesnt seem to do the find and replace.
Private Sub Update_Click()
Application. ScreenUpdating = False
Dim quarter As String
quarter = CStr(txtquarter.Text)
txtquarter.Text = CStr(quarter)
Sheets("Income Statement").Select
Dim oneq As Single
Dim twoq As Single
Dim threeq As Single
Dim fourq As Single
Dim fiveq As Single
oneq = ActiveSheet.Range("A2").Value
twoq = oneq - 1
threeq = twoq - 1
fourq = oneq - 4
fiveq = oneq - 5
View 9 Replies
View Related
Aug 12, 2014
I have data arranged in cells A1 to Z1 as Name1, Amount1, Name2, Amount2, Name3, Amount3, etc. Is there a formula that can determine the name that corresponds to the maximum amount in the row?
View 7 Replies
View Related
May 11, 2007
I have a workbook with 2 worksheets, A and B. Sheet A contains source data and sheet B a pivot table of this data. I want my user to click on a cell within the pivot table,click a button that runs a macro to find the instance of this value within Sheet A. I did record a macro but it did not work.
View 3 Replies
View Related
Feb 20, 2008
I would like to find a text string in a document, move the information in the cell to the right of this to another cell (say A1).
View 8 Replies
View Related
Jun 21, 2012
How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G$3) is equal to 9 I want it to use 8 instead.
Code:
IF(ISERROR(INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0))),"",
INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0)))
I tried using G$3-1 but this doesn't appear to work.
View 1 Replies
View Related
Jun 24, 2014
I have a spreadsheet with rows of data. I need a formula that will return the column number of the last column in a row where there is a value >0.
Let's say that cells A1:F1 contain values. Some have values of 0 while others have values >0. I need a formula in cell G1 that will tell me the column number of the last value >0.
A B C D E F
0 2 5 0 6 0
So the formula in G1 would return a value of 5, which corresponds to column E.
View 3 Replies
View Related
Nov 23, 2008
I am trying to copy a column of cells from one sheet to another, but also want to keep all the formatting. The origin sheet has times, but when I copy these to the destination sheet they are displayed as decimal numbers (using the code snippet below). I can change these back to times by formatting the cells using the format painter after the macro completes but I would like the VBA to do this for me. (using 2002 SP3).
View 4 Replies
View Related
Dec 2, 2011
I need a way to copy part of contents from a cell, the cell contains product information like size, name and weight of a product, and I need a copy the weight to be put in it’s own cell, here’s a copy of one cell “E65 MAPP SKDV 5 DIGIT 90G ST.K” the information I need from this is “90” the number is always followed by a capital G, but it’s between two and three digits where the lowest is 55G and the highest is 300G. Is this possible?
View 6 Replies
View Related
