Report The Value Of An Intersecting Row And Column, Without Numbering The Row And Column
# STUDENTS THAT GOT 100% IN EACH SUBJECT IN EACH SCHOOLKKVRockfortCampionENGLISH232013MATHS382518PHYSICS422515CHEMISTRY483020BIOLOGY503523
Consider the above table. Ca someone help me understand how to report the value of the # of students that got 100% (output) in a given school (input 1), and given subject (input 2).
In other words, if the inputs are the following:
Input Cell 1: Rockfort
Input Cell 2: Pysics
Then, Output Cell should report: 25
I know one option is to use the INDEX function, but with index function, I have to provide Row_Num & Col_Num as inputs. That doesn't fit my example above, as my inputs are specific values from the leading row and the leading column of the array in question.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Highlighting Active Cell's Row, Along With Any Row That Shares Same Value In That Column
Is it possible to click on a cell in column C, and have the wishlist below happen: That active cell's row is hightlighted. Any cell in that column that has the same value as active cell is also highlighted. Plus, any cell in another sheet that has that value it's row is highlighted too. Example: I click on C5 in Sheet 2 its value is 45000789 it row is highlighted, this value also appears in C3 in the same sheet, so it's row is highlighted as well. Plus, in sheet 1 in C10 this value appears and it's row is highlighted as well. When any of the values are clicked again the highlight is removed from all parties.
View Replies!
View Related
Lookup Row In Column & Heading In Row
I'm trying to enter info in a cell based on what is entered in two other cells. So I have a dropdown box for the species on another sheet as well as a dropdown box for rail sizes. Then I have to crossreference them to come up with an upcharge number. Sorry for my basic lingo. I hope it's understandable.
View Replies!
View Related
Pass Row Number Of Last Used Column Row To Variable
This line in my code is causing an invalid qualifer error message: lngNew = wsNew. Range("B65536").End(xlUp).Row.Offset(1, 0) Option Explicit Sub Disney_DumpData() Dim wbBook As Workbook Dim wbNew As Workbook Dim ws As Worksheet Dim wsNew As Worksheet Dim rng As Range Dim rngNew As Range Dim rngUnit As Range Dim rngUnitPaste As Range Dim lngNew As Long Dim Cnt As Integer Application. ScreenUpdating = False Application.Calculation = xlCalculationManual Application.DisplayAlerts = False Set wbBook = ThisWorkbook Cnt = 0 'Dump To New File '/Define new workbook Set wbNew = Application.Workbooks.Add.....................
View Replies!
View Related
Match Text In Column Of Another Workbook & Return Nth Column Same Row
I have two excel files. File #1 is a varying length and has 4 columns with ID #s in Column A. File #2 is a two column file that contains a list of ID #s in column A AND modified ID#s in column B. I need to match the ID #s in column A from file# 1 to the list in column A of file#2 then replace ALL instances of the matching ID #s in column A of file#1, with the values found in column B of file #2. I've tried using the substitute function but I could only replace one found ID at a time.
View Replies!
View Related
Macro - If Cell In Column Is Blank Then Input Text Into Same Row In Another Column
I am looking for a Macro that will search a column for blank cells, and when one is found will add text to the same row in another column. For example: The below is a spreadsheet. I am trying to find something that will search through column "C" in this case and add text (of my choice) to Column "A" if the cell is Blank. So Since cell C1 is Blank then Type "ERROR" is cell A1. A B C D E 1 X X X X 2 X X X X 3 X X X X X 4 X X X X 5 X X X X
View Replies!
View Related
Find Value In Column & Return Cell On Same Row- Different Column
I have an excel sheet I am working on and in columns F1:F2000 I have an IF statment, I need to be able to add more "IF"s to it but I will exceed the 1024 char limit. Is there a way I can put this formula into VB as a function called DocumentType() and then in excel F1:F2000 =DocumentType()? Is it as simple as: Function DocumentType() If(...........) End Function
View Replies!
View Related
Formula To Go Through Each Row And If The 2nd Column Is Yes Then Return The First Column
I have a collection of data like the following: Week1 | yes Week2 | no Week3 | no Week4 | yes Week5 | no ... Week28 | yes I need a formula to go through each row and if the 2nd column is yes then return the first column. I could then copy and paste this formula into a the next cell, add an argument saying the returned value cannot be the same as the previous ones, and so on. Essentially this is a series of nested IF statements. However, Excel prevents more than 7 statements from being nested, when I need all 28 to be searched. I cannot think of a way to get VLOOKUP, INDEX & MATCH, or defined names to make this work, because it needs to step through each row.
View Replies!
View Related
Copy Non Blank Cells In Column Up 1 Row To Another Column
I need a macro to copy and paste data from Col A to Col B. But I need it pasted 1 row up. In other words if A6 has data I need it pasted in B5 and Bolded. I got a start but don't know how to finish. Dim ii As Long For ii = lastrow To 6 Step -1 If Not IsEmpty(. Range("A" & ii).Value) Then ****.Range("A" & ii).Copy***** Next ii
View Replies!
View Related
If Column F Has “Out” And Column K Has Any Contents Copy The Row
I’d 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, I’d 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 Replies!
View Related
Find Column A Of Same Row Anywhere On Row
I typically record macros that need to copy information like a company name from say sheet 2 and paste in a specific cell in sheet 1. As the cursor can be anywhere on a specific row I usually create a column that holds something like zz.. and then in the macro go Shift + Spacebar to select the line I need to work from then Ctrl + F to find zz.. and that is how I know where the cursor is as a starting point. How is it possible to have the cursor know how to find a specific point i.e. column A from any specific column on that same particular row?
View Replies!
View Related
Copy From Row In Column To Last Used Row
i hav a file with over 2000+ rows of data..i'd only like to copy out F7:F77 only..However the length of information in the reticle data (refer sample) may varies..so from cell F7 i found the last used cell using --> range("F7").End(xldown).Select how do i select the whole column F7:F77 from there so i can copy it out?
View Replies!
View Related
Duplicating A Row And Numbering?
I've been given an excel file with 75 addresses (1 address entry per row) and I have to make 150 copies of each address while also numbering column D for each row 1-150. So in the end it would go from: (sorry for the periods.. extra spacing didn't work!) A........B................C.......D AAA...123 Street...City...<blank> BBB...456 Street...City...<blank> CCC...789 Street...City...<blank> To: A........B................C.......D AAA...123 Street...City...1 AAA...123 Street...City...2 AAA...123 Street...City...3~ AAA...123 Street...City...150 BBB...456 Street...City...1 BBB...456 Street...City...2 BBB...456 Street...City...3~ BBB...456 Street...City...150 CCC...789 Street...City...1 CCC...789 Street...City...2 CCC...789 Street...City...3~ CCC...789 Street...City...150 I don't mean to be lazy and just ask for a macro code, but I'm a complete excel novice and just looking for a quick and easy fix rather than copy/pasting these entries manually.. edit: this file has a deadline for it, which is the reason for the quick fix not to just get out of learning how to do it I've tried to make a macro consisting of inserting a row, copying a row then pasting it, but that only worked for the first row that I'm duplicating.
View Replies!
View Related
In Sheet Row Numbering And TOC
is possible to add a hyperlinked TOC (Table of contents) within a spead sheet (Excel 2003) like it is possible to do in Word. Also how do I go about sequential numbering of rows with sub numbering as shown below? Where if i add a row between 1.2.2 and 2 it would be 1.3. 1 1.1 1.1.1 1.1.2 1.2 1.2.1 1.2.2 2
View Replies!
View Related
Automatic & Dynamic Row Numbering For Outline Numbers Like #.#.#
I would like to ask for some assistance to my Spreadsheet problem. To illustrate my problem please see below: 1------------> Level 1 1.1----------> Level 1 Sublevel 1 1.1.1--------> Level 1 Sublevel 1 Item 1 1.1.2--------> Level 1 Sublevel 1 Item 2 1.2----------> Level 1 Sublevel 2 1.2.1--------> Level 1 Sublevel 2 Item 1 1.2.2 2 2.1 2.1.1 2.1.1 2.2 and so on I need to have a formula to automate the adding of outline numbers from "Level 1, Sublevel 1, Item 1..." So that if I deleted or added a row, the rest will automatically update themselves. I will be the one manually inputing the Top Level and Sublevels I just need that the Items will be automatically added and computed basing from the previous level. What I am planning to do is: 1. Check previous cell if the format has two decimal points or periods --> kinda stuck here 2. If there's only one then it will be the Sublevel 1 Item 1 (or depending to its location) 3. If there's already two periods "." then it will compute automatically for what item it is already. ie: 1 1.1 1.1.1 1.1.2----->Checks the previous cell if it has 2 decimal places then if it does then it will add 1
View Replies!
View Related
Last Row, Last Column
When I hit CNTRL-END in a worksheet Excel places the cursor at the intersection of the Last Row and the Last Column in the workbook. Is there a way to get these values programatically? I have over 100 columns to process and they each have a different number of entries. It changes every day so I can't just say "Column AA has the most entries".
View Replies!
View Related
Last Used Column In Row
The columns will always be changing. So I just need a simple code to show me the last column but I need to paste the info into the cell over. I have listed the code I've gotten so far but don't know how to tell it to move over one column/cell over so that I can paste the information. Any help would be nice. I've searched and haven't found anything close. Sub Range("IV2").Select Selection.End(xlToLeft).Select End Sub
View Replies!
View Related
Delete Row If Zero In Column
I have a master list of employees, which is pulled from another workbook using a formula like this: ='S:PII ProductionP2 crew listscrew pII[1st shift PII staffing.xls]4310'!B9 If a position is currently empty, then the column (J) that lists the employee number will show a zero. I would like this spreadsheet to show only the positions which are currently filled. Is there an easy way to do this?
View Replies!
View Related
Column - Row Loop
I am trying to loop through columns and rows to get the sum of each and delete that column that equals zero. I have- Dim lastrow Dim r, c As Integer Dim RowSum RowSum = 0 ActiveSheet.UsedRange.Select lastrow = Selection.Rows.Count For r = 2 To 80 For c = 2 To lastrow RowSum = RowSum + ActiveSheet.Cells(r, c).Value ' here I get error Application-defined or object-defined error Next c If RowSum = 0 Then ActiveSheet.Cells(r, c).EntireColumn.Delete End If Next r What am I doing wrong?
View Replies!
View Related
Returning ROW Or COLUMN
since my previous post got a little complex I will make it easier so I can continue puzzling myself. I have a value "10:00" and a table: 00:00 08:00 08:01 12:00 12:01 14:00 etc Now I sue LOOKUP("10:00",A1:B3). This will return "12:00" But I would not want it to return this VALUE but want it to return ROW or COLUMN. I tried ROW(LOOKUP.....) but this doesn't work. How do I look it up and then return either B, or 2 (in this example) Once I have this I combine my 3 way lookup by combining my new found knowledge
View Replies!
View Related
Lookup Value In Row And Column
I am trying to create a formula that will look up the data in Sheet 2, A3 and if found in Sheet 1 to then look up Sheet 2, C1 in Sheet 1 and then enter the data if found into C3. Is there a way to do this? I have attached a sample workbook.
View Replies!
View Related
Value Picked In The First Column Regardless Of The Row
I have a drop down list in rows of a column to fill out a ledger. The items have multiple account number that I want to put in a second column. I can do the lookups, but I want to have one cell that equals the value picked in the first column regardless of the row. For example if I pick a value for A1 I want it to equal O1, and if I pick a value for A2 I want it to equal O1 and so on, in order to activate a lookup for the second list.
View Replies!
View Related
Concatenate Column With Row Value
I have created vbscript that references a particular column and row. This cell is referred to many times in the scripting but the row may change from time to time. I would like to know how I can define the row value at the top and reference this row within my vbscript. Dim row As Integer row = 1 'vb script coding referring to cell ws.Range("B&row").Copy Destination:= Sheets("Summary").Range("B1") ws.Range("E&row").Copy Destination:=Sheets("Summary").Range("B16") ws.Range("F&row").Copy Destination:=Sheets("Summary").Range("B17") I tried to concatenate the row to the column but it doesn't seem to work. When I would like to change the row value I would like to only make the change where row is defined rather than within each line of coding.
View Replies!
View Related
Change From Column To Row
I need this code to "Paste" in a row instead of a column, I think I have the copy fixed though not sure how to modify the paste part. Public Sub CopyData() Dim dc As String 'destination column Dim src As Worksheet Dim dst As Worksheet Set src = Worksheets("ChartsandGraphs") Set dst = Worksheets("TrendingData") 'find column on destination sheet, note 19 = Row 19 dc = col(dst.Name, 19, Date) 'if error encountered searching for date, display message and stop If dc = "0" Then MsgBox "Error finding column" Exit Sub End If 'copy and set values dst.Range(dc & "19").Value = Date src.Range("B19:G19").Copy 'This sets the range to paste the values dc = the column the 2 numbers the row ranges dst.Range(dc & "20:" & dc & "26").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub
View Replies!
View Related
Select Row And Column
I want the entire row and column of the active cell to be selected - now only column "A:A" gets selected. Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveCell.EntireRow.Select ActiveCell.EntireColumn.Select End Sub
View Replies!
View Related
Macro - Use Row Instead Of Column
Does anyone know how to modify the following macro so it picks up the row number instead of the column number? Right now, it is picking up column E. Say for example, I want to pick up row "20" instead of column "E" under the replacement part ...
View Replies!
View Related
Filter Using Row And Column
I have set of data(attached example file) in which i have to categorize the data depending on the LOC (column C) which is below 1000,between 1000-3000 and greater than 3000. Once i have filtered data i need to get the counts for each impacts. I have put the sample output in next tab(sheet 2)
View Replies!
View Related
Row And Column Color
Change color of the row and column by click on one cell. I saw it somewhere,so when I click on e.g. cell M15,the whole row 15 and column M highlights,in this case,in yellow color. These row and column intersects in the cell I clicked on. Is there any formula or I must use VBA,or something else?
View Replies!
View Related
AutoFill To Last Row In Column A
the code as shown below so that in column A of worksheet: Data to Text I need an AutoFill from cell A1 to the last row. The value in cell A1 needs to be: 1 If there are more than 1 records then I need an AutoFill from cell A1 to the last row. However, the value in cell A1 needs to be 1, value in cell A2 needs to be 2, value in cell A3 needs to be 3 etc...
View Replies!
View Related
|
TRACKER
