I'm totally at using Excel, so here's my problem:
I have a huge document with 12735 rows and 21 columns, but I only want to focus on everything in columns C-I. In those columns, each cell contains a value from 0-31. I want to write a formula that will tell me which rows have a 31 in each cell. Can any one help me?
Also, how can I write another formula that will tell me which rows have cells with each value greater than or equal to 27?
garbo7441
Favorite Answer
You cannot use a formula to list the rows containing the specified value. You could use a formula in a 'helper' column to display 'Yes' if C:I contained a specified value in that row. You still would have to scroll down to identify the rows.
Here is a different approach using a VBA macro. The following macro, when called, will request user input to enter a value to search for. After the user enters a number, a message box will display advising how many rows contain the value and list the row numbers in column X. If you are using column X, change the "X" references in lines 4, 9, 13, and 22 to a column letter you wish to display the result in.
Copy the macro to the clipboard:
Sub List_Rows()
Dim i, LastRow, rowList, ctr
LastRow = Range("C" & Rows.Count).End(xlUp).Row
Columns("X:X").ClearContents
setCrit = InputBox("Please enter the number to audit.", "> Or =")
If setCrit = "" Then
Exit Sub
End If
Range("X1").Value = "Rows >= " & setCrit
For i = 1 To LastRow
For j = 3 To 9
If Cells(i, j).Value >= Val(setCrit) Then
Range("X" & Rows.Count).End(xlUp).Offset(1, 0).Value = Cells(i, "C").Row
ctr = ctr + 1
GoTo here
End If
Next j
here:
Next i
If ctr > 0 Then
MsgBox ctr & " rows contain a number greater than or equal to " & setCrit _
& ". " & Chr(10) & Chr(10) & "The row numbers are listed in Column X.", _
vbOKOnly, "Results for " & setCrit
Else
MsgBox "No rows found containing a value greater than or equal to " & _
setCrit, vbOKOnly, "Not Found"
End If
End Sub
Press ALT + F11
In the menus at the top of the VBE, select INSERT > MODULE
Paste the macro into the module editing area to the right.
Close the VBE and return to Excel.
Press ALT + F8
When the Macros window opens, highlight the macro and click 'Options..'.
Enter a letter to be used as a keyboard shortcut and click 'OK'.
Close the Macros window.
Press CTRL + your shortcut letter whenever you wish to call the macro.
Anonymous
Use IF() function, and as the logical test employ the AND() function:
=IF(AND(C1=31; D1 = 31; E1 = 31; F1 = 31; G1 = 31; H1 = 31; I1=31); "yes"; "no")
You would write this function in your 22nd column, and fill the rest of the rows.
The second formula is pretty much the same:
=IF(AND(C1>=27; D1>=27; E1>=27; F1>=27; G1>=27; H1>=27; I1>=27); "yes"; "no")
Edit: I forgot the alphabet :p
IXL@XL
In J1 enter =IF(SUM(C1:I1)=317,ROW(),"")
In K1 enter =IF(OR(C1>=27,D1>=27,E1>=27, F1>=27,G1>=27,H1>=27,I1>=27), ROW(),""))
In L1 enter =SMALL(J:J,ROW()) gives rows with all cells 31
In M1 enter =SMALL(K:K,ROW()) gives rows with any cell >=27
Copy the 4 cells down list, L and M until NUM appears. If these formula are placed in row 2 due to headers amend Row() to read Row()-1
?
Are the numbers to the right of the / character always one digit in length? If so, paste this formula in D16 =SUMPRODUCT((N(RIGHT(A1:A1000, 1)=RIGHT(D15,1)))*(B1:B1000)) If your data goes past row 1000, change the 1000's to some number larger than what you have. The above formula assumes all your data in column A starts with g/z.