Find Cell In Range Based On A Value

Mar 30, 2012

I have a sheet that has six sets of three columns on it. The columns are labels Ball #1, Number Times Drawn, Last Date Came Up. The first five "sets" are the same except the ball # changes from 1 to 2 to 3 to 4 to 5. The last set has columns labeled Mega Ball, Number Times Drawn, Date Last Came Up. Yes I am doing stats analysis on lottery numbers for a project.

What I want to find is the last date drawn for each of the numbers, 1-56, and have it go into the third column of the 3 column sets.

I will use the first column set for example. I have numbers down the first column A numbered from 1 to 56 (In series). In the next column B, I have the number of times drawn for each number, which I have working. In the last column of the set I want to have the Last Date Drawn.

I tried the following function (Formula):
=INDEX(Table1[Date],SUMPRODUCT(MAX((Item=A2)*ROW(Item)))-ROW(Table1[Date])+1)

This kind of worked. It returned a date but it was not correct. It actually said the same date for each number, which I know is wrong. I can't figure out where it is actually pulling that date from.

I should clarify that the drawing numbers are on a second sheet labeled "Mega_Millions_Numbers" in a table named Table1 and the Date column is A with a column label of Date. The table has column headings of Date, Ball#1, Ball#2, etc. through to Mega Ball column.

What I need is a function or formula to pull the last date drawn. I am NOT good in any way shape or form with VBA so if I can do this without VBA it would be best for me. I can follow along with formulas and functions well enough, just not VBA.

I will need to have this formula copied down 56 times per set and there are 5 sets so i will have to modify it for the other columns. (If your keeping track that totals 336 formulas).

View 1 Replies


ADVERTISEMENT

Find Last Cell In Range, And Changing Another Cell Based On Offset Text

May 4, 2009

If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"

and also t = time()

View 7 Replies View Related

Cut Range Based On Find

May 10, 2007

What I'm attempting to do is find the rownumber from 2 Cell.Find operations, and then basically cut (or copy) the range between the start row and end row. The cose I have worked out is:

Sheets("$$37073").Select
Range("A1").Select
Cells.Find(What:="#bale#", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
EndRow = ActiveCell.Row
Range("A1").Select
Cells.Find(What:="#bals#", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
StartRow = ActiveCell.Row

Now, I had hoped that to select the range I could just use: Rows("StartRow:EndRow").Select

However, I get a Mismatch (13) error with the macro, assuming at the run-time is trying to literally use "startrow:Endrow" as my range instead of pulling the values in the variables.

View 5 Replies View Related

Find Discrepant IDs Based On Range Of Values In Each ID Row

Jun 12, 2014

I have a workbook with three sheets containing patient information from a clinic. Each sheet pertains to a different year. The sheets are formatted such that every row of each sheet contains a patient ID number in column A; in the same row, following the patient ID, is data pertaining to that patient, part of which stays the same (intake data)[=values in columns B:O] and part of which changes (encounter data)[=columns P:AA].

My challenge in cleaning and merging this dataset is that the ID numbers may not be trustworthy: I need a way to validate that there are no patient records [=rows] such that a given row has the same set of identifying information (OpenRecord Date, Sex, Age, History, etc.) located in columns B through O but a different ID number (column A) in a different sheet. In other words, I need to make sure the same patient isn't masquerading by a different ID number across different years [=worksheets]. What I am trying to do is develop a macro or formula that compares each row's range of values for B:O to every other row across each of the three worksheets; then find and list only matches for B:O that do not have the same value in Col A.

Complicating this operation are two considerations: 1) Each sheet in the workbook (excerpt attached) contains about 40,000 rows. 2) DOB is missing; instead, we have "Age." For those patients which appear in all three years captured in the three sheets, the "Age" value (column F) increases by +1 year.

A link to an example file on Google Drive : [URL] ....

View 2 Replies View Related

Find Date Range Based On Value In Cells?

May 14, 2012

I have one excel sheet. In the first column there are various Project Codes and in the first row there are various date. Cells in the matrix contains 0 or 1 value. If value is equals to 1 that means the project was on hold during this months and if the cell value is null of 0 (Zero) it means the project was active. For a project in a particular row there are several times a project went on hold and now I want a date range showing the project's hold duration. If project went on hold more than one time the result should also give different range for each time when project went on hold.

follow the link to view the excel file.

http://www.excelforum.com/attachment...3&d=1336994800
[IMG]http://www.mrexcel.com/forum/data:image/png;base64,
iVBORw0KGgoAAAANSUhEUgAABAAAAAMACAIAAAA12IJaAAAgAElEQVR4nOy9e3gT153/P99fd7vd77fxV+3udtO06fr3PL

[code].....

View 8 Replies View Related

Find Matched Value In A Range For Named Cell Then Copy Range Cells Below

Aug 6, 2013

I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.

The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.

View 9 Replies View Related

Correct Syntax For Function To Find Average Based On Background Color Of Cells In Range

May 20, 2014

I'm working on a workbook that will track staffing patterns.

The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".

On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:

The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"

[Code] ........

The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"

[Code] .......

I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.

[Code] ........

How might I use the following functions to find the average of the source fields instead of the SUM?

View 7 Replies View Related

Multiple Find And Replace Cell Content Based On Another Cell?

Mar 25, 2014

multiple search match and replace content in a different column so for example

new workbook (look up table)
sku
search1
search2

[Code]....

so something like where you compare two tables and find and replace based on another cell that matches in my sku.. more details would be if the table column aren't exactly matching but the column header and the row header would match and fill or replace in the correct/corresponding cell is there a macro or vba to do this job in excel?

View 6 Replies View Related

Excel 2010 :: Find A Cell Within Range Then Copy That Cell To Another Location

Sep 26, 2013

(I am using Excel version 14.0.7106 and MS Office Professional 2010.)

I have a macro in which I have named a range of numbers in a spreadsheet, used the "find" function to find a particular number in that range, and now would like to copy some information into a cell in the same row as the found number. However, when I try to move over to the cell that I'm copying to, it only goes to the 1st row in the range that I have defined.

Here's the portion of code I think you need to see. Everything works...it finds a match...but then I don't know what to do from there to get the information to the correct row:

'Grab the 1st project number
Range("C8").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value "" Then
prjnum = ActiveCell.Value
Selection.Offset(0, 8).Select

[Code] ..............

View 7 Replies View Related

Find Empty Cell In Range Then Copy Info In Cell Above It?

Dec 15, 2011

I've got a range of data in Column D approx 50,000 rows long and I need to go down this range and when theres a blank cell copy the info from the cell above. I've got some code which loops through this but I need to make sure I put "EOF and the bottom of the info to stop the loop. Is there a slicker way of writing this code?

Code:
Sub TestBlankCell()
Range("D5").Select
Do

[Code].....

View 6 Replies View Related

Find Value In Range Using Cell Value?

Sep 29, 2011

How do I put a cell value from worksheet into a statement in VB to find a given value in a range?

Example:

Instead of this > Set Foundcell = Sheets("Event Records").Range("a2:a2298").Find("1002") This works, but I want to put in a cell value from a worksheet

I need something like this >

Set Foundcell = Sheets("Event Records").Range("a2:a2298").Find = { (Sheets("BlankForm").Range("R8").Value) }

Or thinking something like this >

Sheets("BlankForm").Select
RecordNbr = Range("R8").Value
Sheets("Event Records").Select
Set Foundcell = Sheets("Event Records").Range("a2:a2298").Find(SetValue=RecordNbr)

View 2 Replies View Related

Find Last Cell In The Range And Sum It Via Vba

Nov 19, 2009

complete the following partial code.

I am trying to determine the last cell for Non-ILEC piece and then want to insert one row below the last Non-ILEC cell and insert "Total BS Non-ILEC" and sum "H" Range just like the way its done in the partial code. The partial code I have posted is working perfectly fine with regard to ILEC piece. I need to further add the Non-ILEC piece in the partial code to accomplish my task ....

View 15 Replies View Related

Find The First Used Cell In A Range

May 11, 2006

I am trying to find the code for finding the first cell with a value in it for a range. I have seen code for finding the last used cell in a range such as....

i = Range("B65536").End(xlUp).Row

Is there a code that is the opposite of the one shown above?

View 2 Replies View Related

Find Cell Based On Parameters?

Dec 19, 2013

see attachment.

A2 is the given length and B2 is the given weight. I want B14 to net the answer of "Type G" because the length (A2) is equal to C10:C12 but the weight (B2) is within D11 and E11.

How do I formulate B14 to net the answer of "Type G"? This has to be flexible as the data in A2 and B2 will be entered in differently each time and B14 needs to find the appropriate "Type".

View 3 Replies View Related

Find Cell Based On Return Of MAX Value

May 10, 2009

This time I'm trying to use the max value and row value at the same time, but it won't let me. Here's what I'm trying to do...

A B C D
1 a b c d
2 7 9 4 2
3 e f g h
4 8 2 7 8
5 i j k l
6 4 3 3 9

I want Cell A7 to return the letter before the highest value in column A...
A7=e
B7=b
C7=g
D7=l

I tried to do something using two cells, but even then I couldn't figure it out.
I thought a ROW(MAX(A1:A6)) would give me the row and I could go from there, but that didn't work.

View 4 Replies View Related

Find Cell Based On Column And Row

Dec 2, 2009

I have a spreadsheet which has about 100 different SKUs in column A and the inventory for each SKU by period in columns B:Z
So for example B would be P1W1 and C would be P2W2, etc

what kind of lookup function would I use in VBA to find per say, the value of SKU #: "27017" in P1W2 ?

Apart from pivot table.

View 6 Replies View Related

Find Name In Range And Return Cell To The Right?

Jan 25, 2012

Have a range of data with names dotted in different rows and cells.

I want to find a name, the return the number in the cell to the right.

Can use VLOOKUP as names arent always in same column of the range.

View 9 Replies View Related

Find Text Within Cell Range Then Sum It Up

Jul 21, 2014

Type/Unit Sold

Car-A/20
Bike-A/5
Bike-B/20
Car-B/50
Car-C/100
Bike-C/100

I need a formula if it's Car then sum it up to 170 and for Bike is 125.

View 9 Replies View Related

Find The First Non Zero Length Cell In A Range

May 5, 2009

I have date in a range from a1 to cw.... this could range to the whole worksheet as it varries.

Now i started to write the macro by inserting three rows at the top of the sheet, no problem, now how do i make cell a5 become the new active cell so that i can freeze pane .

so i really need to do the "find the first non zero length cell in a range" code as I know it will always be a5. this part will not change.

I cant seem to find how to change the active cell from a1 to a5....

View 9 Replies View Related

Find Next Blank Cell In Range

Jul 22, 2009

I had a macro to do this but forgot to save before close now i can't find it.

I need to find the next blank cell in range F15:F240 and select it so i can paste data there.

View 9 Replies View Related

Find Range With Value X And Copy To A Cell

Feb 13, 2007

I have small problem to solve and I can't find out how: I have to find cells in a range (L4:L20) with value PM and copy range with two next cells to cell F4 (not a cell with "PM") If in range("L4:L20") cells = PM then copy range with next 2 cells to ("F4"). See attached worksheet.

View 3 Replies View Related

Find Worksheet Based On Data In A Cell

Jan 13, 2010

In Sheet1 I've got a list of names in column A, then I've got a worksheet for each name in the list. I'd like to fill in column B with information from the worksheet of the same name as in column A.

Quick example...name in column A is Davis, want to fill in column B with a value from cell C3 on worksheet named Davis and do it for the entire list. Data being looked for is in same cell on each worksheet. Tried using a formula and dragging it down the list, problem with that is it doesn't change the sheetname just the cell.

View 3 Replies View Related

Macro To Find Field Based On A Cell Value?

Aug 18, 2014

I have 2 sheets in same excel.

Sheet 1:
A B C D E F
HR 2015 Q1 Database Planned 176
CRM 2014 Q4 Major Product Planned 125
HCM 2015 Q1 Database Planned 113

Sheet 2:
A B C D E
Doc 2015 Y 176 -----

simple requirement is to search column A in sheet 1 based on Column D in sheet 2. and write it in Column E.

The answer should be
A B C D E
Doc 2015 Y 176 HR

I need to perform this search on various fields in sheet A. this is a small snapshot of sheet. Vlookup wont work in this condition as there are several searches I need to do, I cant change the columns.

View 11 Replies View Related

Find Column Based On Active Cell

May 16, 2013

How to find the column based on my active cell...if my active cell is in A1 then it pops up saying your in column A.

View 3 Replies View Related

Formula To Find Date Based On A Cell Value

Sep 6, 2007

First one needs to look through a single row and find where the first cell with a value (text) in it, then finds the date that that cell is under.

Second one needs to look through the same row to find the last cell that has a value (text), then finds the date again that that cell is under.

Example workbook is attached

View 2 Replies View Related

Find Value In Range Then Return The Cell Address?

Mar 16, 2014

construct a formula that finds a value in a range , then returns the cell address of that value.

Say, i wanted to find the amount 12385 from another sheet , range C2:AA12 (contains only numbers , no duplicates). result should give me the address of that value.

I have tried the address & match function but gives me an N/A error.

View 3 Replies View Related

Find Next Cell In Range With Interior Color

Mar 8, 2014

I have a B2:M13 range. I would like to find a way to find the next cell with Interior.ColorIndex = 1.

For example, if position is currently B2 (so myrange(1, 1)) and the next black colored cell is on B6 (so myrange(5, 1)), I would like store 5 and 1 into variables.

So if no black background is found after current cell on the same row, look for next black background on next row.

If current cell is in row-M (the last one ** the range), for example, and there's no black background following on this row, find first black background in row-B.

View 5 Replies View Related

How To Turn Range.find Into A Cell Reference

Dec 9, 2009

I am writing a function that opens up an external excel document, identifies a string in a certain range and then i also want it to return a cell reference for that string including the number of rows beneath the cell that the string was found in.

View 6 Replies View Related

.range.find Need To Return Cell Address

Nov 14, 2007

when I use the range.find function to find a certain value in a column.. i want to return the address of the cell.. and save it in a range variable. how would I do that?

so this is what i have now.. but Rng does not return as a range.. it returns 69... when i use ctrl + G and type ?rng

set rng = .range(A:A).find(what:=69, After:=.Cells(1, 1), Searchorder:=xlByRows, searchdirection:=xlPrevious)
ok so say it picks up..... cell A69... how do i get it to save range A69

View 9 Replies View Related

Find The Bottom Left Cell Of A Range

Oct 27, 2008

I am trying to find a code that will simply find the last cell in Column A of a named Range (titled: ConditionlFormatArea)?

Also IF I could get a line(s) of code that would just find the last cell in Column A of the Used Area.

I ask for both because I am not yet certain which line of code will work best for me.

When I have that cell located I need to Remove the Conditional Formatting. I think I can get the code for that part.

View 9 Replies View Related







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