MAX-COLLECT for text

mohammad saaideh

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 ?

Paul Newcome

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))

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?

mohammad saaideh

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

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

mohammad saaideh

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.

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

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))

mohammad saaideh

THANK u Paul,

its working good @Paul Newcome

Paul Newcome

Happy to help. 👍️