Add date according to record appearence

[Frank78]

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

 

[Joe4]

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]

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
	A	B	C	D	E	F	G	H	I	J	K
2	Global ID	L2: Fault Code		Date: SO Created on	Date: SO Closure Date	No. of Imperfect Orders					GID+FC
3	929010792	#	Not assigned	05.08.2018	#	1					929010792 - # - Not assigned
4	929031832	DA9991	NOT ON TIME - Missing Actual Time of Arrival	08.08.2018	#	1					929031832 - DA9991 - NOT ON TIME - Missing Actual Time of Arrival
5	929133800	DA9991	NOT ON TIME - Missing Actual Time of Arrival	27.08.2018	#	1					929133800 - DA9991 - NOT ON TIME - Missing Actual Time of Arrival
6	929421855	COL201	Load Optimization Initiated by Tetra Pak	12.10.2018	#	1					929421855 - COL201 - Load Optimization Initiated by Tetra Pak
7		QAM100	Add Mat delivered out of 5% order quantity	12.10.2018	#	1					- QAM100 - Add Mat delivered out of 5% order quantity
8	929455697	CTC208	Customer requested LT shorter than committed LT	18.10.2018	#	1					929455697 - CTC208 - Customer requested LT shorter than committed LT
9		QAM100	Add Mat delivered out of 5% order quantity	18.10.2018	#	1					- QAM100 - Add Mat delivered out of 5% order quantity
10	929455699	CTC208	Customer requested LT shorter than committed LT	18.10.2018	#	1					929455699 - CTC208 - Customer requested LT shorter than committed LT
11	929457014	COL501	Loading capacity issues	18.10.2018	#	1					929457014 - COL501 - Loading capacity issues
12	929465833	#	Not assigned	19.10.2018	#	1					929465833 - # - Not assigned
13	929482876	QAM100	Add Mat delivered out of 5% order quantity	23.10.2018	#	1					929482876 - QAM100 - Add Mat delivered out of 5% order quantity
14	929513482	CTP402	Capacity reduction due to holidays	29.10.2018	#	1					929513482 - CTP402 - Capacity reduction due to holidays
15	929513496	CTP402	Capacity reduction due to holidays	29.10.2018	#	1					929513496 - CTP402 - Capacity reduction due to holidays
16	929553631	CTO403	Credit Block-NOT Confirmation	05.11.2018	#	1					929553631 - CTO403 - Credit Block-NOT Confirmation
17	929575431	CTC206	Cust. requested date falls on weekend/public holid	08.11.2018	#	1					929575431 - CTC206 - Cust. requested date falls on weekend/public holid
18		QAM100	Add Mat delivered out of 5% order quantity	08.11.2018	#	1					- QAM100 - Add Mat delivered out of 5% order quantity
19		#	Not assigned	08.11.2018	#	1					- # - Not assigned
20	929713304	#	Not assigned	30.11.2018	#	1					929713304 - # - Not assigned
21	929732451	CTO599	Other	04.12.2018	#	1					929732451 - CTO599 - Other
22		#	Not assigned	04.12.2018	#	1					- # - Not assigned
23	929900282	CFO202	Wrong qty entered by customer	04.01.2019	#	1					929900282 - CFO202 - Wrong qty entered by customer
24		CTO301	Incorrect route data	04.01.2019	#	1					- CTO301 - Incorrect route data
25	929908557	QAM100	Add Mat delivered out of 5% order quantity	07.01.2019	#	1					929908557 - QAM100 - Add Mat delivered out of 5% order quantity
26	929909225	COA102	Delay in Production	07.01.2019	#	1					929909225 - COA102 - Delay in Production
27		QAM100	Add Mat delivered out of 5% order quantity	07.01.2019	#	1					- QAM100 - Add Mat delivered out of 5% order quantity
28	930017530	CTO303	Incorrect TP data	23.01.2019	#	1					930017530 - CTO303 - Incorrect TP data
29	930022334	COC307	Credit Block-NOT Arrival	24.01.2019	#	1					930022334 - COC307 - Credit Block-NOT Arrival
30	930103330	QAM100	Add Mat delivered out of 5% order quantity	07.02.2019	#	1					930103330 - QAM100 - Add Mat delivered out of 5% order quantity
31	930249040	CTO401	Down payment order	01.03.2019	#	1					930249040 - CTO401 - Down payment order
32		QAM100	Add Mat delivered out of 5% order quantity	01.03.2019	#	1					- QAM100 - Add Mat delivered out of 5% order quantity
33	930358477	#	Not assigned	19.03.2019	#	1					930358477 - # - Not assigned
34	930430822	QAM100	Add Mat delivered out of 5% order quantity	30.03.2019	#	1					930430822 - QAM100 - Add Mat delivered out of 5% order quantity
35	930435726	COAO01	Other Production reason	01.04.2019	#	1					930435726 - COAO01 - Other Production reason
36		COL201	Load Optimization Initiated by Tetra Pak	01.04.2019	#	1					- COL201 - Load Optimization Initiated by Tetra Pak
37		CTP305	BM replenishment/ordering error	01.04.2019	#	1					- CTP305 - BM replenishment/ordering error
38	930511576	QAM100	Add Mat delivered out of 5% order quantity	11.04.2019	#	1					930511576 - QAM100 - Add Mat delivered out of 5% order quantity
39	930528666	COA102	Delay in Production	15.04.2019	#	1					930528666 - COA102 - Delay in Production
40		QAM100	Add Mat delivered out of 5% order quantity	15.04.2019	#	1					- QAM100 - Add Mat delivered out of 5% order quantity
41	930567310	CFO101	Partially confirmed due to incorrect forecast	20.04.2019	#	1					930567310 - CFO101 - Partially confirmed due to incorrect forecast
42		QAM100	Add Mat delivered out of 5% order quantity	20.04.2019	#	1					- QAM100 - Add Mat delivered out of 5% order quantity
43	930567334	CFC102	Ordered qty exceeded max. order qty	20.04.2019	#	1					930567334 - CFC102 - Ordered qty exceeded max. order qty
44		CTC201	Customer missed LOD	20.04.2019	#	1					- CTC201 - Customer missed LOD
45	930622936	CFO101	Partially confirmed due to incorrect forecast	30.04.2019	#	1					930622936 - CFO101 - Partially confirmed due to incorrect forecast
46	930630210	#	Not assigned	02.05.2019	#	1					930630210 - # - Not assigned
47	930648687	QAM100	Add Mat delivered out of 5% order quantity	06.05.2019	#	1					930648687 - QAM100 - Add Mat delivered out of 5% order quantity
48	930675475	CTO401	Down payment order	09.05.2019	#	1					930675475 - CTO401 - Down payment order
49	930675659	COO201	CSR Error	09.05.2019	#	1					930675659 - COO201 - CSR Error
50	930676370	CTO403	Credit Block-NOT Confirmation	09.05.2019	#	1					930676370 - CTO403 - Credit Block-NOT Confirmation
51	930747156	QAM100	Add Mat delivered out of 5% order quantity	21.05.2019	#	1					930747156 - QAM100 - Add Mat delivered out of 5% order quantity
52	930780795	QOA807	Missing print	27.05.2019	#	1					930780795 - QOA807 - Missing print
53	930799852	CTA201	Lack of capacity	29.05.2019	#	1					930799852 - CTA201 - Lack of capacity
54		QAM100	Add Mat delivered out of 5% order quantity	29.05.2019	#	1					- QAM100 - Add Mat delivered out of 5% order quantity
55	930800007	CTA201	Lack of capacity	29.05.2019	#	1					930800007 - CTA201 - Lack of capacity
56		QAM100	Add Mat delivered out of 5% order quantity	29.05.2019	#	1					- QAM100 - Add Mat delivered out of 5% order quantity
57	930802057	CTC201	Customer missed LOD	29.05.2019	#	1					930802057 - CTC201 - Customer missed LOD
58	930802492	CTC205	Customer misunderstands offered date	29.05.2019	#	1					930802492 - CTC205 - Customer misunderstands offered date
59	930851110	CTA201	Lack of capacity	06.06.2019	#	1					930851110 - CTA201 - Lack of capacity
60		#	Not assigned	06.06.2019	#	1					- # - Not assigned
61	930851181	CTA201	Lack of capacity	06.06.2019	#	1					930851181 - CTA201 - Lack of capacity
62		#	Not assigned	06.06.2019	#	1					- # - Not assigned
63	930851203	CTA201	Lack of capacity	06.06.2019	#	1					930851203 - CTA201 - Lack of capacity
64		#	Not assigned	06.06.2019	#	1					- # - Not assigned
65	930851211	CTA201	Lack of capacity	06.06.2019	#	1					930851211 - CTA201 - Lack of capacity
66	930855244	CTA201	Lack of capacity	07.06.2019	#	1					930855244 - CTA201 - Lack of capacity
67	930855906	CTA201	Lack of capacity	07.06.2019	#	1					930855906 - CTA201 - Lack of capacity
68		QAM100	Add Mat delivered out of 5% order quantity	07.06.2019	#	1					- QAM100 - Add Mat delivered out of 5% order quantity
Sheet1

Cell Formulas
Range	Formula
K3:K68	K3	=CONCATENATE(A3," - ",B3," - ",C3)

 

[Joe4]

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.