Add date according to record appearence

Frank78

New Member
Joined
Jan 4, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello and Happy New Year everybody!
My name is Franco and this is my first thread, i already apologize myself as i am not a technician so my language may not be very specific, but i hope you will help me :).
Every day an Excel analysis file run automatically. It reports a certain number of rows. Each rows represent an issue and is characterized by a unique code. Day by day the number of rows increases as new issues are generated. What is missing and i would like to add, is the date when this issue appears, in other words, if a certain code that is unique, was not present in previous days, and today it appears, i would like to have the today date applied to a specific field. Of course this date should not change, it should be kept as the date when that row with the unique code appeared in the file. The file overwrites every single day.
I have no clue how to do that.
Could you help me?
Thanks in advance
Franco
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,703
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

To do something that that will require VBA.
However, this one line concerns me:
The file overwrites every single day.
If that is truly the case, doesn't any date we then enter simply get lost/overwritten by the next day's file?
If so, then that could be problematic to what you are trying to do.

I think it would be helpful for us to see the structure of your data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

Frank78

New Member
Joined
Jan 4, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

To do something that that will require VBA.
However, this one line concerns me:

If that is truly the case, doesn't any date we then enter simply get lost/overwritten by the next day's file?
If so, then that could be problematic to what you are trying to do.

I think it would be helpful for us to see the structure of your data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Hi Joe, thanks.
I was thinking to add 2 columns, one with the concatenation to obtain the unique record name, the other for the date. Every day i enter and refresh analysis, so it recalculate everything.
Otherwise i could generate a different file everyday or a different tab. Although more time consuming it could be easier maybe.
Ok, i attach a small part of data as you described, thanks!

South Europe Daily 4th Jan TEST.xlsx
ABCDEFGHIJK
2Global IDL2: Fault CodeDate: SO Created onDate: SO Closure DateNo. of Imperfect OrdersGID+FC
3929010792#Not assigned05.08.2018#1929010792 - # - Not assigned
4929031832DA9991NOT ON TIME - Missing Actual Time of Arrival08.08.2018#1929031832 - DA9991 - NOT ON TIME - Missing Actual Time of Arrival
5929133800DA9991NOT ON TIME - Missing Actual Time of Arrival27.08.2018#1929133800 - DA9991 - NOT ON TIME - Missing Actual Time of Arrival
6929421855COL201Load Optimization Initiated by Tetra Pak12.10.2018#1929421855 - COL201 - Load Optimization Initiated by Tetra Pak
7QAM100Add Mat delivered out of 5% order quantity12.10.2018#1 - QAM100 - Add Mat delivered out of 5% order quantity
8929455697CTC208Customer requested LT shorter than committed LT18.10.2018#1929455697 - CTC208 - Customer requested LT shorter than committed LT
9QAM100Add Mat delivered out of 5% order quantity18.10.2018#1 - QAM100 - Add Mat delivered out of 5% order quantity
10929455699CTC208Customer requested LT shorter than committed LT18.10.2018#1929455699 - CTC208 - Customer requested LT shorter than committed LT
11929457014COL501Loading capacity issues18.10.2018#1929457014 - COL501 - Loading capacity issues
12929465833#Not assigned19.10.2018#1929465833 - # - Not assigned
13929482876QAM100Add Mat delivered out of 5% order quantity23.10.2018#1929482876 - QAM100 - Add Mat delivered out of 5% order quantity
14929513482CTP402Capacity reduction due to holidays29.10.2018#1929513482 - CTP402 - Capacity reduction due to holidays
15929513496CTP402Capacity reduction due to holidays29.10.2018#1929513496 - CTP402 - Capacity reduction due to holidays
16929553631CTO403Credit Block-NOT Confirmation05.11.2018#1929553631 - CTO403 - Credit Block-NOT Confirmation
17929575431CTC206Cust. requested date falls on weekend/public holid08.11.2018#1929575431 - CTC206 - Cust. requested date falls on weekend/public holid
18QAM100Add Mat delivered out of 5% order quantity08.11.2018#1 - QAM100 - Add Mat delivered out of 5% order quantity
19#Not assigned08.11.2018#1 - # - Not assigned
20929713304#Not assigned30.11.2018#1929713304 - # - Not assigned
21929732451CTO599Other04.12.2018#1929732451 - CTO599 - Other
22#Not assigned04.12.2018#1 - # - Not assigned
23929900282CFO202Wrong qty entered by customer04.01.2019#1929900282 - CFO202 - Wrong qty entered by customer
24CTO301Incorrect route data04.01.2019#1 - CTO301 - Incorrect route data
25929908557QAM100Add Mat delivered out of 5% order quantity07.01.2019#1929908557 - QAM100 - Add Mat delivered out of 5% order quantity
26929909225COA102Delay in Production07.01.2019#1929909225 - COA102 - Delay in Production
27QAM100Add Mat delivered out of 5% order quantity07.01.2019#1 - QAM100 - Add Mat delivered out of 5% order quantity
28930017530CTO303Incorrect TP data23.01.2019#1930017530 - CTO303 - Incorrect TP data
29930022334COC307Credit Block-NOT Arrival24.01.2019#1930022334 - COC307 - Credit Block-NOT Arrival
30930103330QAM100Add Mat delivered out of 5% order quantity07.02.2019#1930103330 - QAM100 - Add Mat delivered out of 5% order quantity
31930249040CTO401Down payment order01.03.2019#1930249040 - CTO401 - Down payment order
32QAM100Add Mat delivered out of 5% order quantity01.03.2019#1 - QAM100 - Add Mat delivered out of 5% order quantity
33930358477#Not assigned19.03.2019#1930358477 - # - Not assigned
34930430822QAM100Add Mat delivered out of 5% order quantity30.03.2019#1930430822 - QAM100 - Add Mat delivered out of 5% order quantity
35930435726COAO01Other Production reason01.04.2019#1930435726 - COAO01 - Other Production reason
36COL201Load Optimization Initiated by Tetra Pak01.04.2019#1 - COL201 - Load Optimization Initiated by Tetra Pak
37CTP305BM replenishment/ordering error01.04.2019#1 - CTP305 - BM replenishment/ordering error
38930511576QAM100Add Mat delivered out of 5% order quantity11.04.2019#1930511576 - QAM100 - Add Mat delivered out of 5% order quantity
39930528666COA102Delay in Production15.04.2019#1930528666 - COA102 - Delay in Production
40QAM100Add Mat delivered out of 5% order quantity15.04.2019#1 - QAM100 - Add Mat delivered out of 5% order quantity
41930567310CFO101Partially confirmed due to incorrect forecast20.04.2019#1930567310 - CFO101 - Partially confirmed due to incorrect forecast
42QAM100Add Mat delivered out of 5% order quantity20.04.2019#1 - QAM100 - Add Mat delivered out of 5% order quantity
43930567334CFC102Ordered qty exceeded max. order qty20.04.2019#1930567334 - CFC102 - Ordered qty exceeded max. order qty
44CTC201Customer missed LOD20.04.2019#1 - CTC201 - Customer missed LOD
45930622936CFO101Partially confirmed due to incorrect forecast30.04.2019#1930622936 - CFO101 - Partially confirmed due to incorrect forecast
46930630210#Not assigned02.05.2019#1930630210 - # - Not assigned
47930648687QAM100Add Mat delivered out of 5% order quantity06.05.2019#1930648687 - QAM100 - Add Mat delivered out of 5% order quantity
48930675475CTO401Down payment order09.05.2019#1930675475 - CTO401 - Down payment order
49930675659COO201CSR Error09.05.2019#1930675659 - COO201 - CSR Error
50930676370CTO403Credit Block-NOT Confirmation09.05.2019#1930676370 - CTO403 - Credit Block-NOT Confirmation
51930747156QAM100Add Mat delivered out of 5% order quantity21.05.2019#1930747156 - QAM100 - Add Mat delivered out of 5% order quantity
52930780795QOA807Missing print27.05.2019#1930780795 - QOA807 - Missing print
53930799852CTA201Lack of capacity29.05.2019#1930799852 - CTA201 - Lack of capacity
54QAM100Add Mat delivered out of 5% order quantity29.05.2019#1 - QAM100 - Add Mat delivered out of 5% order quantity
55930800007CTA201Lack of capacity29.05.2019#1930800007 - CTA201 - Lack of capacity
56QAM100Add Mat delivered out of 5% order quantity29.05.2019#1 - QAM100 - Add Mat delivered out of 5% order quantity
57930802057CTC201Customer missed LOD29.05.2019#1930802057 - CTC201 - Customer missed LOD
58930802492CTC205Customer misunderstands offered date29.05.2019#1930802492 - CTC205 - Customer misunderstands offered date
59930851110CTA201Lack of capacity06.06.2019#1930851110 - CTA201 - Lack of capacity
60#Not assigned06.06.2019#1 - # - Not assigned
61930851181CTA201Lack of capacity06.06.2019#1930851181 - CTA201 - Lack of capacity
62#Not assigned06.06.2019#1 - # - Not assigned
63930851203CTA201Lack of capacity06.06.2019#1930851203 - CTA201 - Lack of capacity
64#Not assigned06.06.2019#1 - # - Not assigned
65930851211CTA201Lack of capacity06.06.2019#1930851211 - CTA201 - Lack of capacity
66930855244CTA201Lack of capacity07.06.2019#1930855244 - CTA201 - Lack of capacity
67930855906CTA201Lack of capacity07.06.2019#1930855906 - CTA201 - Lack of capacity
68QAM100Add Mat delivered out of 5% order quantity07.06.2019#1 - QAM100 - Add Mat delivered out of 5% order quantity
Sheet1
Cell Formulas
RangeFormula
K3:K68K3=CONCATENATE(A3," - ",B3," - ",C3)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,703
Office Version
  1. 365
Platform
  1. Windows
I think we need to know a little bit more about this data file, exactly how it is being maintained and updated.
For example, if you hard-code something in today, when you open the file tomorrow, will that hard-coded information still be in there, or will it be overwritten?
How exactly is new data being entered? Imported? Manually? etc?

Maybe the best thing to do is walk us through an example of a typical day of working this file, being specific to tell us what you are entering and where.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,315
Members
416,239
Latest member
Counselor85027

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