ExcelNewbie2020
Active Member
- Joined
- Dec 3, 2020
- Messages
- 322
- Office Version
- 365
- Platform
- Windows
I would like to textjoin the date every 1st and 4th times the name of the fruit appear then sum its qty and amount.
For example the apple. it appear on 1/7/24 (1st) then on 5/7/24 (4th) then sum the qty (2+3+3+9) and amount(16+45+44+43)
it appear again on 6/7/24 (1st) then on 10/7/24 (4th) then sum qty (8+2+9+5) and amount (12+41+19+32)
For example the apple. it appear on 1/7/24 (1st) then on 5/7/24 (4th) then sum the qty (2+3+3+9) and amount(16+45+44+43)
it appear again on 6/7/24 (1st) then on 10/7/24 (4th) then sum qty (8+2+9+5) and amount (12+41+19+32)
Date | Name | Qty | Amount |
01-07-24 | Apple | 2 | 16 |
02-07-24 | Apple | 3 | 45 |
04-07-24 | Apple | 3 | 44 |
05-07-24 | Apple | 9 | 43 |
06-07-24 | Apple | 8 | 12 |
07-07-24 | Apple | 2 | 41 |
09-07-24 | Apple | 9 | 19 |
10-07-24 | Apple | 5 | 32 |
Book3 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Date | Name | Qty | Amount | Expected Result | |||||||
2 | 01-07-24 | Apple | 2 | 16 | Name | Date Covered | Qty | Amount | ||||
3 | 01-07-24 | Banana | 2 | 35 | Apple | 1/7/24 - 5/7/24 | 17 | 148 | ||||
4 | 01-07-24 | Cherry | 5 | 23 | Apple | 6/7/24 - 10/7/24 | 24 | 104 | ||||
5 | 01-07-24 | Berry | 2 | 44 | Banana | 1/7/24 - 4/7/24 | 15 | 102 | ||||
6 | 01-07-24 | Orange | 4 | 19 | Banana | 5/7/24 - 10/7/24 | 26 | 139 | ||||
7 | 01-07-24 | Mangoe | 7 | 22 | Cherry | 1/7/24 - 4/7/24 | 14 | 138 | ||||
8 | 02-07-24 | Apple | 3 | 45 | Cherry | 5/7/24 - 10/7/24 | 14 | 75 | ||||
9 | 02-07-24 | Banana | 7 | 19 | Berry | 1/7/24 - 4/7/24 | 19 | 121 | ||||
10 | 02-07-24 | Cherry | 3 | 50 | Berry | 5/7/24 - 9/7/24 | 20 | 115 | ||||
11 | 02-07-24 | Berry | 4 | 37 | Orange | 1/7/24 - 4/7/24 | 18 | 115 | ||||
12 | 02-07-24 | Orange | 3 | 46 | Mangoe | 1/7/24 - 5/7/24 | 16 | 115 | ||||
13 | 03-07-24 | Banana | 4 | 38 | ||||||||
14 | 03-07-24 | Cherry | 1 | 38 | ||||||||
15 | 03-07-24 | Berry | 7 | 29 | ||||||||
16 | 03-07-24 | Orange | 8 | 14 | ||||||||
17 | 03-07-24 | Mangoe | 1 | 27 | ||||||||
18 | 04-07-24 | Apple | 3 | 44 | ||||||||
19 | 04-07-24 | Banana | 2 | 10 | ||||||||
20 | 04-07-24 | Cherry | 5 | 27 | ||||||||
21 | 04-07-24 | Berry | 6 | 11 | ||||||||
22 | 04-07-24 | Orange | 3 | 36 | ||||||||
23 | 04-07-24 | Mangoe | 3 | 21 | ||||||||
24 | 05-07-24 | Apple | 9 | 43 | ||||||||
25 | 05-07-24 | Banana | 7 | 31 | ||||||||
26 | 05-07-24 | Cherry | 6 | 32 | ||||||||
27 | 05-07-24 | Berry | 6 | 42 | ||||||||
28 | 05-07-24 | Orange | 2 | 26 | ||||||||
29 | 05-07-24 | Mangoe | 5 | 45 | ||||||||
30 | 06-07-24 | Berry | 7 | 28 | ||||||||
31 | 06-07-24 | Orange | 4 | 31 | ||||||||
32 | 06-07-24 | Mangoe | 3 | 40 | ||||||||
33 | 06-07-24 | Apple | 8 | 12 | ||||||||
34 | 07-07-24 | Apple | 2 | 41 | ||||||||
35 | 07-07-24 | Banana | 6 | 23 | ||||||||
36 | 07-07-24 | Cherry | 3 | 10 | ||||||||
37 | 07-07-24 | Berry | 1 | 10 | ||||||||
38 | 07-07-24 | Orange | 9 | 41 | ||||||||
39 | 07-07-24 | Mangoe | 2 | 28 | ||||||||
40 | 08-07-24 | Mangoe | 10 | 26 | ||||||||
41 | 09-07-24 | Apple | 9 | 19 | ||||||||
42 | 09-07-24 | Banana | 7 | 37 | ||||||||
43 | 09-07-24 | Cherry | 2 | 11 | ||||||||
44 | 09-07-24 | Berry | 6 | 35 | ||||||||
45 | 10-07-24 | Apple | 5 | 32 | ||||||||
46 | 10-07-24 | Banana | 6 | 48 | ||||||||
47 | 10-07-24 | Cherry | 3 | 22 | ||||||||
48 | 10-07-24 | Berry | 10 | 14 | ||||||||
Sheet7 |
Last edited by a moderator: