If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Last value in an unsorted table.
1. I have a table of around 3000 rows, incresing daily.
How can I find the last entered value 'X' in column A. Table is not sorted by column A and the value 'X' can (and will) appear several times in this table on several position in column A only. Please I need urgent help. 2. How can I find all values 'X' on this table. Thanks, Catalin |
#2
|
|||
|
|||
Last value in an unsorted table.
With "X" in D1:
=MAX(IF(A1:A3500=D1,ROW(A1:A3500))) Array-entered (press ctrl/shift/enter). HTH Jason Atlanta, GA -----Original Message----- 1. I have a table of around 3000 rows, incresing daily. How can I find the last entered value 'X' in column A. Table is not sorted by column A and the value 'X' can (and will) appear several times in this table on several position in column A only. Please I need urgent help. 2. How can I find all values 'X' on this table. Thanks, Catalin . |
#3
|
|||
|
|||
Last value in an unsorted table.
Hi Catalin:
1. This is not very elegant. It also requires that you know the top row of the table (the header row if there is a header) and that there are no blanks in column A between the top or header row and the last row, and also that there are no entries in column A below the last row of the table. If row 3 is the top (or header) row: =OFFSET(A3,65536-COUNTBLANK(A:A)-1,0) will give you the last entry in column A. 2. Use conditional formatting on column A. Highlight the column, with cell A1 active. Then use Format | Conditional Formatting | Formula Is | =A1=OFFSET(A$3,65536-COUNTBLANK(A:A)-1,0) and choose a color. This will color all the cells that match the last entry in the column. Regards, Vasant. "Catalin" wrote in message ... 1. I have a table of around 3000 rows, incresing daily. How can I find the last entered value 'X' in column A. Table is not sorted by column A and the value 'X' can (and will) appear several times in this table on several position in column A only. Please I need urgent help. 2. How can I find all values 'X' on this table. Thanks, Catalin |
#4
|
|||
|
|||
Last value in an unsorted table.
1.
=MAX((A1:A500="x")*(ROW(A1:A500))) entered with ctrl + shift & enter (adapt to fit you range) will return the row number, if you need the cell address =CELL("address",INDEX(A1:A500,MAX((A1:A500="x")*(R OW(A1:A500))))) entered the same way 2. Use dataautofilter and filter on "x" -- Regards, Peo Sjoblom "Catalin" wrote in message ... 1. I have a table of around 3000 rows, incresing daily. How can I find the last entered value 'X' in column A. Table is not sorted by column A and the value 'X' can (and will) appear several times in this table on several position in column A only. Please I need urgent help. 2. How can I find all values 'X' on this table. Thanks, Catalin |
Thread Tools | |
Display Modes | |
|
|