Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Search for the last entry

Hello, I have a list in column B which are A,B,C, and so on, I keep adding to this list everyday so that list keeps growing constantly.

How do I search for "A" (whatever I enter in F1) in the B column and have a cell tell me what is in column C like G1 ?

Input : A (F1)
Output : 14.37 (G1) - the last entry with A in column B

I have tried index and match but this only matches the first instance of A not the last one. I would like to get the last one.


Post your answer or comment

comments powered by Disqus
A while ago I modified a code I found on the web to search for the last row of data then enter in new data based on entry within a form onto the next row. I now need to modify this code to be based upon a specific Row (B to be exact). Can someone please verify this code:

Dim iRow as Long
Dim ws as Worksheet

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

I then modified the last line to the following in an attempt to specify the search for the last row in Column B

Range("B65535").End(xlUp).Offset(1, 0).Select

But I then get a compile error....

any Ideas what I did wrong...
BV

Hello,

I am trying to intergrate a stock level calculator to determine the level of stock with our customers into an existing woorkbook that logs when certain items were sent to certain customers. Each customer is on a different sheet and then the products are listed down the far left colum with the date of dispach along the top. The numper of each product dispached are entered in the cells in between.

Not all products are sent out to the customer at the same time so I am looking to find the number of each product from the last entry (the most recent delivery) then get the date it was sent out (From the row with all the dates in it).

I have managed to find this nice little piece of code to find the last entry of each product row from http://j-walk.com/ss/excel/tips/tip30.htm


	VB:
	
 
    Dim WorkRange As Range 
    Dim i As Integer, CellCount As Integer 
    Application.Volatile 
    Set WorkRange = rngInput.Rows(1).EntireRow 
    Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange) 
    CellCount = WorkRange.Count 
    For i = CellCount To 1 Step -1 
        If Not IsEmpty(WorkRange(i)) Then 
            LASTINROW = WorkRange(i).Value 
            Exit Function 
        End If 
    Next i 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and then adapted it to return a cell address instead of the value and put it into

	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But this isn't working...

Any ideas?

What function(s) do I use when I want the function to return the coordinates (or, at least, row numbers) for the first and the last entry in a given range of cells within a column?

Example (column A):

1
2 256
3 Aa
4 312
5
6 ZzZz
7 481

Now I need the function which returns coordinates (A2, A7) or row numbers (2 and 7) for "256" and "481".

What's the easiest way to do this?

Thanks.

I am having trouble thinking of how I can select the last entry from a row.
Example: I have numbers for Jan - Feb and I need a cell updated with the "last month's entry". I don't want to have to hunt for the cells that require updating. How can I tell a cell to choose the cell that has the last number, in today's case it would be the APRIL column.

THANK You!!!

Cell BM6 Sheet1, has a formula that gives me a numeric total.

The total belongs in Column D1 Sheet2 after the last entry, and there will be cells that will remain blank in that column before the last entry.

Sheet1 is a work sheet, which will be copied and saved with a new file name at the end of the month after the totals have been sent to Sheet2. Sheet2 is a permanent, ongoing record.

The numbers on original Sheet1 will then be erased and the worksheet reused for the new month.

How do I get the values into the appropriate last row of Sheet2 and have them remain there when I reuse the worksheet?

Many Thanks, JagBbeach.

I am using the below code to log access to a shared workbook:


	VB:
	
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) 
AccessLog = "C:" & WorkbookName & "_Access Log.log" 
 
If Dir(AccessLog)  "" Then 
    SetAttr AccessLog, vbNormal 
End If 
Open AccessLog For Append As #1 
Print #1, Environ("username"), Now 
Close #1 
SetAttr AccessLog, vbReadOnly 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How do I read the last entry in this file and populate a msgbox? I'd also like to put the information (user name & time accessed) on 2 separate lines in the msgbox.

I've attached a sample log.

So here's what I need:

I have a workbook, that may have 1 worksheet or may have 15 worksheets. These worksheets may have 5 rows/columns of data or 400 rows/columns of data. After figuring out what the last row of data is, I need to insert two or three rows of the same text into each sheet. (I have another workbook that I'm working where I'll need to find the last column in order to insert a few columns of static data, too - thought I'd cover both bases at once).

I've tried a few of the suggestions on the site such as using SpecialCells (the xlLastRow item) and haven't had any luck.

Thasnk for any help in advance!
Kevin
Newburgh, New York, USA

Hope someone can help me with this.

I have a sheet with over 30000 rows and need to complete it with a couple of formulas.

Problem: In one of these formulas I want to specify a value by instructing excel to look for it this way:

Finding the last entry of a certain value (X), they're ID numbers, in column A, then giving me the value in column G on that very same row. That's the value I want to use in my formula.

Hope I was clear enough...

I have a column of data that could be 1 to 10 rows long. I need to be able to find the last entry in the column whether it is 3 rows long or 10 rows long

Thanks for the help

I am using the function "lookup(10000, a1:a5)" which gives me the last entry in the range. However if the last entry is text it doesnt change the result from the last numeric entry. I would like the result to show that the last is text 55 66 77 88 H Result 88 It doesnt really matter what the result shows providing its identifable, for example 55 66 77 88 H Result Na Anybody have any ideas

I've got this code when i recorded making my pivot table, but because
the number of rows can vary, how can i make it automaticly search for
the last (bottom) one?

this is my code:

Sheets("Datasheet").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Datasheet!R1C1:R8622C31", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Graphsheet!R1C1", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion12


Hi, I would like to enter a formula in Cell C4 to show the value of the last entry in that column, ie if the last cell with a data entry is C5000, I want this figure to show in C4.
Any idea's
Thanks for your help.
Paul

Is it possible to use a macro to look for the last entry in a column and move it to another cell. Ie if the last entry in column A is in cell 199, the content should be moved to column A, cell 204? If so, can anyone tell me what the code would look like? I have contents in column A and B that would need to be moved.

How can you correctly search for the symbol * ? Whenever I try, it just goes to the next item (since * is the catch-all symbol). I need to find any instance of * and remove it in the spreadsheet, but I can't correctly find it.

I can't find anything on MrExcel or on Google for the same reason that I can't use * in the search box.

Thanks for any help anyone can give!

--Brendan--

Sir,

How to put a formula wherein there is a coloumn which has got a number of entries say til Z23, in this row there are several other entries on the right hand side say in coloumn AD and AE, The last cells in the columns AB and AC should indicate the value which is in the row that has got the last entry in coloumn z.

Please help.

Regards,
Vithal

Hi Again,
EveryTime I print a particular range I add A comment, or add to a comment, the date of printing. I wish to be able to display the Last entry only.How to do this Please?
The Code Below displays all the entries.
RePrint_Certs.TextBox1.Value = ActiveCell.Offset(0, 25).Comment.Text

Thanks Regards Ron

Hello, I am trying to find a way to look for the last entry in a column from A6 to A266,even if the user skips an entry or ten and then enters in another entry. Below is some code which will work if no entries are skipped but I really need some help with how to still find the last entry if they skip some. Thanks alot!

Sheets("PS").Select
'Columns("A").Find("", after:=[N5]).Select
ActiveCell.Offset(-1, 0).Select
actual_balance = ActiveCell
ActiveCell.Offset(1, 0).Select
ActiveWindow.ScrollColumn = 14
Sheets("PS History").Select
ActiveSheet.Cells(10, 14).Select
Selection.Value = actual_balance
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1

Precede it with a tilde:
~?

Same would pertain to other wildcard:
~*
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Ian" > wrote in message
...
Is there a way of searching the cells in a worksheet for the actual
character "?" since I don't want to use it as a wildcard but actually
search for the character itself within cells

Thanks in advance.

I am running a column of decending and assending numerical values and I am
not sure which cell will contain the last entry. However, when the last
numerical figure posts, I need to move this value to another cell location,
automatically. What function can I use to obtain this result? There are blank
cells between the last entry in the column and the desired cell, of
destination. Rather then Copy & Paste. The location and value of the last
entry in a column may vary from month to month, but it is important to
relocate this amount to a new destination cell. I am unaware of any function
that will give me the desired results.

Hello I am entering data on a sheet each day and analyzing it on a separate
sheet. Many of the formulas i use need to reference the newest entries. Can
anyone tell me the function you use so that the formula automatically uses
the last entry in a column (so that all i need to do is key in the data each
day.

Is there a way of searching the cells in a worksheet for the actual
character "?" since I don't want to use it as a wildcard but actually
search for the character itself within cells

Thanks in advance.

I have a database with approx 2000 entries. Im trying to get to the last
entry I put in quickly. Ive tried using ctrl + end and it takes me
straight to the end of the workbook and not the last entry.

Anyone got any other ideas how I can overcome this and go to the last
entry and not the end of the workbook.

--
tweacle

Hi!

Assuming that the entries are text values:

For the next to the last entry:

=INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2)

For the last entry:

=LOOKUP(REPT("Z",255),A:A)

Biff

"Teri" <Teri@discussions.microsoft.com> wrote in message
news:C80625B5-6EBB-4443-9B45-4B3604AE7B89@microsoft.com...
>I must set up a spreadsheet and extract the last and next-to-last entries
>in
> certain columns. Those values will be linked to another sheet in the
> workbook entitled "Summary". The columns are set up as shown below. I
> have
> a second set of columns which are from 14:00 Friday July 29 through 8:00
> Saturday July 30. I know this is a lot of info, but I'm such a novice at
> this. Any help would be greatly appreciated!
> City Mile Marker Odometer
> 19:00 Thursday July 28
> 20:00 Thursday July 28
> 21:00 Thursday July 28
> 22:00 Thursday July 28
> 23:00 Thursday July 28
> 24:00 Thursday July 28
> 01:00 Friday July 29
> 02:00 Friday July 29
> 03:00 Friday July 29
> 04:00 Friday July 29
> 05:00 Friday July 29
> 06:00 Friday July 29
> 07:00 Friday July 29
> 08:00 Friday July 29
> 09:00 Friday July 29
> 10:00 Friday July 29
> 11:00 Friday July 29
> 12:00 Friday July 29
> 13:00 Friday July 29
>

Hello,

I have a list of entry's that grows every day.
I want to know the substraction of the last entry in the list and the first
entry.
The position of the first entry is known (b3).
The position of the last enty is increased every day.

How do I select th last entry?
How do I enter this in a formula?


No luck finding an answer? You could always try Google.