Auto add last entered data in same row

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
320
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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.


7D4C8C41-C0E6-47BA-A71E-5D90C100CF86_4_5005_c.jpeg
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
can you provide that table in XL2B addon or post a link to it?
This should be doable using xlookup last to first with some criteria.

also this would be very easy to do if you dataset would in proper form (no columns with the same title, but 1 Date column, item column etc.
 
Upvote 0
can you provide that table in XL2B addon or post a link to it?
This should be doable using xlookup last to first with some criteria.

also this would be very easy to do if you dataset would in proper form (no columns with the same title, but 1 Date column, item column etc.
 
Upvote 0
can you provide that table in XL2B addon or post a link to it?
This should be doable using xlookup last to first with some criteria.

also this would be very easy to do if you dataset would in proper form (no columns with the same title, but 1 Date column, item column etc.
I have tried to upload using XL2BB, it is shown in my excel sheet but I don't know how to add it here.
 
Upvote 0
use the mini sheet option.
u'll get a msg box that it was copied to the clipboard.
now simply paste the code as if a normal message and sent the post :)
 
Upvote 0
use the mini sheet option.
u'll get a msg box that it was copied to the clipboard.
now simply paste the code as if a normal message and sent the post :)
Last item and date capture example.xlsx
ABCDEFGHIJKLMNOPQR
1Date of last transactionLast ItemDateItemMethodCompleteDateItemMethodCompleteDateItemMethodCompleteDateItemMethodComplete
23/2/21Email1/1/21LetterPostNO3/2/21EmailExternal emailYES
31/10/21Application1/10/21ApplicationCourierYES
43/25/21Application2/12/21EmailExternal emailNO2/14/21LetterPostNO3/25/21ApplicationCourierYES
56/25/21Letter2/12/21LetterPostNO3/5/21ApplicationCourierNO3/30/21LetterPostNO6/25/21LetterPostYes
6
7
8
9
Sheet1
 
Upvote 0
B4 I start.
tell does ur data have to be in this form? Or is it possible to transform it into more Proper dataset?
 
Upvote 0
B4 I start.
tell does ur data have to be in this form? Or is it possible to transform it into more Proper dataset?
Thanks I am certainly open to suggestions, however this is the way it currently and my data set contains 1000s of rows so perhaps leaving it the way it is. But for future reference would love to see what you suggest.

I really appreciate your help
 
Upvote 0
I should add that in my real data set the rows where this is placed starts at 6 and the column is P so difficult to alter, although I can easily change the column headings which sit on row 6
 
Upvote 0
ok, try this.

oAAQqn69R4ikZ1sZ6xst_work.csv
ABCDEFGHIJKLMNOPQR
1Date of last transactionLast ItemDateItemMethodCompleteDateItemMethodCompleteDateItemMethodCompleteDateItemMethodComplete
203.02.2021Email01.01.2021LetterPostNO03.02.2021EmailExternal emailYES
310.01.2021Application10.01.2021ApplicationCourierYES
425.03.2021Application12.02.2021EmailExternal emailNO14.02.2021LetterPostNO25.03.2021ApplicationCourierYES
525.06.2021Letter12.02.2021LetterPostNO05.03.2021ApplicationCourierNO30.03.2021LetterPostNO25.06.2021LetterPostYes
Sheet5
Cell Formulas
RangeFormula
A2:A5A2=AGGREGATE(14,6,ISNUMBER(C2:R2)*(C2:R2),1)
B2:B5B2=XLOOKUP(AGGREGATE(14,6,ISNUMBER(C2:R2)*(C2:R2),1),C2:R2,OFFSET(C2:R2,0,1))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:R5Expression=A$1:R$1="Date"textNO


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

Forum statistics

Threads
1,212,927
Messages
6,110,710
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top