Kimgfung20
01-22-2020, 05:10 AM
I am new here and I have encountered a huge challenge for days.
I dont know how to start with this type of data filling.
It seems not just filter, copy & paste that easy.
Here is the backgroud, I have got 40000+ records awaiting to be filled.
In thise case, is it better to separate file into monthly?
Three difficulties -
1) There are some overlapping promotion dates in file A. (See the ID = ABC)
2) Take the highest discount rate if the ID are found overlapped period and paste to file B. (See the ID=CCC)
3) Ignore those IDs not within the period (See ID = QWE)
Copy from File A
Start Date | End Date | ID | Price | Discount %
1 Dec 2019 | 10 Dec 2019 | ABC | $50 | 20%
1 Nov 2019 | 31 Dec 2019 | ABC | $50 | 20%
1 Dec 2019 | 10 Dec 2019 | XYZ | $550 | 20%
1 Dec 2019 | 10 Dec 2019 | BBB | $80 | 40%
1 Jan 2018 | 31 Dec 2020 | CCC | $200 | 50%
10 Dec 2019 | 11 Dec 2019 | CCC | $200 | 80%
1 Jan 2020 | 31 Jan 2020 | QWE | $500 | 20%
Paste to File B
2019 Dec
ID | Price | 1 Dec | 2 Dec | .... | 10 Dec | 11 Dec |...... | 31 Dec
ABC | $50 | 0.2 | 0.2 | .... | 0.2 | 0.2 | .... | 0.2
XYZ | $550 | 0.2 | 0.2 | .... | 0.2 | | .... |
BBB | $80 | 0.4 | 0.4 | .... | 0.4 | | .... |
CCC | $200 | 0.5 | 0.5 | .... | 0.8 | 0.8 | .....|
Thank you!
I dont know how to start with this type of data filling.
It seems not just filter, copy & paste that easy.
Here is the backgroud, I have got 40000+ records awaiting to be filled.
In thise case, is it better to separate file into monthly?
Three difficulties -
1) There are some overlapping promotion dates in file A. (See the ID = ABC)
2) Take the highest discount rate if the ID are found overlapped period and paste to file B. (See the ID=CCC)
3) Ignore those IDs not within the period (See ID = QWE)
Copy from File A
Start Date | End Date | ID | Price | Discount %
1 Dec 2019 | 10 Dec 2019 | ABC | $50 | 20%
1 Nov 2019 | 31 Dec 2019 | ABC | $50 | 20%
1 Dec 2019 | 10 Dec 2019 | XYZ | $550 | 20%
1 Dec 2019 | 10 Dec 2019 | BBB | $80 | 40%
1 Jan 2018 | 31 Dec 2020 | CCC | $200 | 50%
10 Dec 2019 | 11 Dec 2019 | CCC | $200 | 80%
1 Jan 2020 | 31 Jan 2020 | QWE | $500 | 20%
Paste to File B
2019 Dec
ID | Price | 1 Dec | 2 Dec | .... | 10 Dec | 11 Dec |...... | 31 Dec
ABC | $50 | 0.2 | 0.2 | .... | 0.2 | 0.2 | .... | 0.2
XYZ | $550 | 0.2 | 0.2 | .... | 0.2 | | .... |
BBB | $80 | 0.4 | 0.4 | .... | 0.4 | | .... |
CCC | $200 | 0.5 | 0.5 | .... | 0.8 | 0.8 | .....|
Thank you!