Auto add last entered data in same row

[Detectiveclem]

Hi can anyone help with a formula which will allow the last date entered in a row to be captured in the last date entered cell (E.G. Cell A2) And the last Item entered to appear in the last item entered cell (EG Cell B2).

Please see the attached sample with the data shown in green for illustration purposes to show the data I want captured.

So for example at present A4 shows 10/01/2021 and B4 shows Application but if further dates or items are entered in G4 / H4 then A4 and B4 change to show the latest information.

I hope I have explained this sufficiently.

Your genius would be greatly appreciated.

 

[Detectiveclem]

ok, try this.

oAAQqn69R4ikZ1sZ6xst_work.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Date of last transaction	Last Item	Date	Item	Method	Complete	Date	Item	Method	Complete	Date	Item	Method	Complete	Date	Item	Method	Complete
2	03.02.2021	Email	01.01.2021	Letter	Post	NO	03.02.2021	Email	External email	YES
3	10.01.2021	Application	10.01.2021	Application	Courier	YES
4	25.03.2021	Application	12.02.2021	Email	External email	NO	14.02.2021	Letter	Post	NO	25.03.2021	Application	Courier	YES
5	25.06.2021	Letter	12.02.2021	Letter	Post	NO	05.03.2021	Application	Courier	NO	30.03.2021	Letter	Post	NO	25.06.2021	Letter	Post	Yes
Sheet5

Cell Formulas
Range	Formula
A2:A5	A2	=AGGREGATE(14,6,ISNUMBER(C2:R2)*(C2:R2),1)
B2:B5	B2	=XLOOKUP(AGGREGATE(14,6,ISNUMBER(C2:R2)*(C2:R2),1),C2:R2,OFFSET(C2:R2,0,1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:R5	Expression	=A$1:R$1="Date"	text	NO

That done, I will try doing transformation in PQ into proper data set.
That will only require to transform ur data into a table with a specific name, or I'll have do a small Mcode editing (nothing hard, just changing the source data)

 

[Detectiveclem]

Thank you, I will try this, but will have to change your formula to suit my worksheet but can't do it until tomorrow morning as our access server has gone down for updates. Thank you and i'll let you know how I get on tomorrow. I also look forward to seeing what you come up with re the proper dataset.

Thank you so much again.

 

[Detectiveclem]

ok, try this.

oAAQqn69R4ikZ1sZ6xst_work.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Date of last transaction	Last Item	Date	Item	Method	Complete	Date	Item	Method	Complete	Date	Item	Method	Complete	Date	Item	Method	Complete
2	03.02.2021	Email	01.01.2021	Letter	Post	NO	03.02.2021	Email	External email	YES
3	10.01.2021	Application	10.01.2021	Application	Courier	YES
4	25.03.2021	Application	12.02.2021	Email	External email	NO	14.02.2021	Letter	Post	NO	25.03.2021	Application	Courier	YES
5	25.06.2021	Letter	12.02.2021	Letter	Post	NO	05.03.2021	Application	Courier	NO	30.03.2021	Letter	Post	NO	25.06.2021	Letter	Post	Yes
Sheet5

Cell Formulas
Range	Formula
A2:A5	A2	=AGGREGATE(14,6,ISNUMBER(C2:R2)*(C2:R2),1)
B2:B5	B2	=XLOOKUP(AGGREGATE(14,6,ISNUMBER(C2:R2)*(C2:R2),1),C2:R2,OFFSET(C2:R2,0,1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:R5	Expression	=A$1:R$1="Date"	text	NO

That done, I will try doing transformation in PQ into proper data set.
That will only require to transform ur data into a table with a specific name, or I'll have do a small Mcode editing (nothing hard, just changing the source data)

Can you tell me what the numbers 14 and 6 represent after the aggregate? Also do I need to do anything with the conditional format information you included? Sorry for being such a novice.

 

[Radoslaw Poprawski]

Hi there.
Here is the PQ solution:

oAAQqn69R4ikZ1sZ6xst_work.csv
	B	C	D	E	F
14	PQ
15	Index	Date	Item	Method	Complete
16	1	10.01.2021	Application	Courier	Yes
17	2	05.03.2021	Application	Courier	No
18	3	25.03.2021	Application	Courier	Yes
19	4	03.02.2021	Email	External Email	Yes
20	5	12.02.2021	Email	External Email	No
21	6	01.01.2021	Letter	Post	No
22	7	12.02.2021	Letter	Post	No
23	8	14.02.2021	Letter	Post	No
24	9	30.03.2021	Letter	Post	No
25	10	25.06.2021	Letter	Post	Yes
26
27	Arrey formula for date per Item & date verification
28
29	Item/Method	Courier	External Email	Post
30	Application	25.03.2021
31	Email		12.02.2021
32	Letter			25.06.2021
33
34	Power query dynamic solution (CTRL+ALT+F5) to refresh all data
35	Last Item	Last Date	Complete?
36	Application	25.03.2021	Yes
37	Email	12.02.2021	No
38	Letter	25.06.2021	Yes
Sheet5

Cell Formulas
Range	Formula
C29:E29	C29	=TRANSPOSE(UNIQUE(Table6_2[Method]))
B30:B32	B30	=UNIQUE(Table6_2[Item])
C30:E32	C30	=LET( MaxIFSDate,MAXIFS(Table6_2[Date],Table6_2[Item],B30#,Table6_2[Method],C29#), ZeroDate,IF(MaxIFSDate=0,"",MaxIFSDate), ZeroDate)
Dynamic array formulas.

and also some spilled arrey report.

1st table:

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"LP"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Proper Headings", each Text.Remove([Attribute],{Character.FromNumber(48)..Character.FromNumber(57)})),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Proper Headings"]), "Proper Headings", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Filled Up" = Table.FillUp(#"Removed Columns1",{"Method", "Complete", "Item"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Date] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"Item", type text}, {"Method", type text}, {"Complete", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Item", Text.Proper, type text}, {"Method", Text.Proper, type text}, {"Complete", Text.Proper, type text}}),
    #"Sorted Rows" = Table.Sort(#"Capitalized Each Word",{{"Item", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Index1",{"LP"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"Index", "Date", "Item", "Method", "Complete"})
in
    #"Reordered Columns"

2nd table (referenced)

let
    Source = Table6,
    #"Grouped Rows" = Table.Group(Source, {"Item"}, {{"Max", each List.Max([Date]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Item", "Max"}, Table6, {"Item", "Date"}, "Table6 (3)", JoinKind.LeftOuter),
    #"Expanded Table6 (3)" = Table.ExpandTableColumn(#"Merged Queries", "Table6 (3)", {"Complete"}, {"Complete"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Table6 (3)",{{"Item", "Last Item"}, {"Max", "Last Date"}, {"Complete", "Complete?"}})
in
    #"Renamed Columns"

 

[Radoslaw Poprawski]

also i figured out this way

oAAQqn69R4ikZ1sZ6xst_work.csv
	G	H	I	J	K
29	Index	Date	Item	Method	Complete
30	3	44280	Application	Courier	Yes
31	5	44239	Email	External Email	No
32	10	44372	Letter	Post	Yes
Sheet5

Cell Formulas
Range	Formula
G29:K29	G29	=Table6_2[#Headers]
G30:K32	G30	=INDEX(Table6_2,MATCH(MAXIFS(Table6_2[Date],Table6_2[Item],UNIQUE(Table6_2[Item])),Table6_2[Date],0),SEQUENCE(,COUNTA(Table6_2[#Headers]),1,1))
Dynamic array formulas.

 

[Detectiveclem]

Thank you, but this all seems to complicated for me. Clearly you are an expert and I remain a novice. But I am keen to learn so thank you.

 

[Radoslaw Poprawski]

I hope I did not scare you :D
and honestly, there are so many ways because I could not figure out the single-cell formula that I found on excelisfun to happen in dynamic arrays.
So in conclusion I may know something, but to be an expert, true one, I still need to learn sh...t tons of stuff

 

[Detectiveclem]

I hope I did not scare you :D
and honestly, there are so many ways because I could not figure out the single-cell formula that I found on excelisfun to happen in dynamic arrays.
So in conclusion I may know something, but to be an expert, true one, I still need to learn sh...t tons of stuff

Hi again Radoslaw,

Sorry for the delay in replying but I have been trying to make your formulas work for me.

Although the cells I am using are different from the example I provided the formula works perfectly in the cell in column marked date of last transaction which in my actual workbook is column M (First cell being M6) =AGGREGATE(14,6,ISNUMBER(O6:AD6)*(O6:AD6),1)........So this works perfectly.

However the second formula as amended by me to suit the same range as above =XLOOKUP(AGGREGATE(14,6,ISNUMBER(O6:AD6)*( O6:AD6),1),O6:AD6,OFFSET(O6:AD6,0,1)) comes up with an error #NAME?

In my actual spreadsheet this is at column N

The relevant data when entered (I.E. In the Item headed columns) would be found in columns Q, V, AA and AF.

Can you assist with this please?

Further I have no idea what to do with this further formula you provided and so far have not used it. Should I use it, if so how do I use it (please note that it still shows the range A1:R5 whereas my actual data starts from M6:AH 5000

Cells with Conditional Formatting
Cell	Condition	Cell Format	Stop If True
A1:R5	Expression	=A$1:R$1="Date"	text	NO

Please help me if you are able to.

Thank you

 

[Detectiveclem]

Hi again, I have provided the exact cell references I am trying to achieve this formula to auto populate the cells between N6:N5000 with the last ‘Item’ entered in the same row at columns Q, V, AA or AF etc. If nothing is entered in these cells on a particular row then the N cell needs to remain blank (For example as seen at Row 10).

Please see the screen shot below.

Your help would be greatly appreciated.

 

[Fluff]

How about

+Fluff 1.xlsm
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
5	Date of last transaction	Last Item	Date	Item	Method	Complete	Date	Item	Method	Complete	Date	Item	Method	Complete	Date	Item	Method	Complete
6	02/03/2021	Email	01/01/2021	Letter	Post	NO	02/03/2021	Email	External email	YES
7	10/01/2021	Application	10/01/2021	Application	Courier	YES
8	25/03/2021	Application	12/02/2021	Email	External email	NO	14/02/2021	Letter	Post	NO	25/03/2021	Application	Courier	YES
9	25/06/2021	Letter	12/02/2021	Letter	Post	NO	05/03/2021	Application	Courier	NO	30/03/2021	Letter	Post	NO	25/06/2021	Letter	Post	Yes
Master

Cell Formulas
Range	Formula
M6:M9	M6	=AGGREGATE(14,6,O6:AD6,1)
N6:N9	N6	=INDEX(P6:AE6,MATCH(M6,O6:AD6,0))