Auto add last entered data in same row

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
315
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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows
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.
 

Detectiveclem

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

Detectiveclem

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

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Detectiveclem

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

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

B4 I start.
tell does ur data have to be in this form? Or is it possible to transform it into more Proper dataset?
 

Detectiveclem

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

Detectiveclem

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

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows
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)
 

Forum statistics

Threads
1,141,299
Messages
5,705,571
Members
421,399
Latest member
hjweiss00

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
Top