MAX-COLLECT for text

HI


I am using this formula "=MAX(COLLECT({WTG1}, {T#}, @cell = [Turbine No.]@row)) "


but its work just if cell contain a number , i need to return the text what cell has , how ?

Best Answer

Answers

  • Christian G.
    Christian G. ✭✭✭✭✭✭

    @mohammad saaideh

    Hi !

    MAX only return the maximum number or the latest date.

    What is the information you are trying to max ?

    What is the output expected from that formula?

  • thank u

    i need to return the last text updated in cell ,


    the data in cell will change many time , i need to take the last text was filled .

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try an INDEX/COLLECT instead if you are wanting to capture whatever data is currently in the cell.

  • mohammad saaideh
    edited 11/15/22

    Hi Paul ,


    The idea is :

    i make sheet 1 and i will change cell data on future , i need to make another copy of sheet 1 name sheet 2 that will update data in cell depend on data change in sheet 1 , same data i need ,

    but if i delete row from sheet 1 i didn't need to delete from sheet 2 or give me #ref. error ,

    what i do :

    1- make 3 sheet ( * sheet 1 *sheet 2 *sheet 3 )

    2-when change rows in sheet 1 ---> send copy of row to sheet 2 ((may will have more than copy of row bcz many change ))

    3- sheet 3 same rows & columns in sheet 1 , it will take the last updated cell from sheet 2 and save it


    i use "=MAX(COLLECT({WTG1}, {T#}, @cell = [Turbine No.]@row)) " -----> its work just for number and its perfect , but i need to use for text ( same text in cell )


    is it clear for u ?


  • Christian G.
    Christian G. ✭✭✭✭✭✭

    To get the last updated row from Sheet2 (Coped rows) on Sheet 3 (Copy of Items Tracker), You need an Unique Identifier(UI) in sheet1 (Items tracker).

    In sheet3, use a INDEX (MATCH) on a MAX Modify date, using the UI as a key to collect the right lines

    (that last sentence reads like giberish)

    =index(collect({Turbine No.},UI:UI,UI@row),match(max(collect({Modify date},UI:UI,UI@row)),collect({Modify date},UI:UI,UI@row)))

    This "collect({Modify date},UI:UI,UI@row)" reduce the lookup table to the line with the same Unique Identifier.

    The match(max()) return the position of the latest modified line with that UI

    The index return the item a the position given by the match from the list culled down by the collect.


    You will need one for every column.


    I guess that is one way to build it.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You could insert an auto-number column with no special formatting into the sheet you are pulling from then use something like this on the final sheet:

    =INDEX({Column To Pull From}, MATCH(MAX(COLLECT({Auto Number Column}, {Turbine ID}, @cell = [Turbine No.]@row)), {Auto Number Column}, 0))

  • THANK u Paul,


    its working good @Paul Newcome