dummybhatia
New Member
- Joined
- May 24, 2023
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
Hi,
Requirement for column "D":
Requirement for column "D":
- Check for the same "Area code" in column "A"
- If only single area code in the "A" Column then result in column "D" should come "No records for comparison"
- If there are 2 same area codes then in column "D" against 1st area code result should come as "1st Visit" then for 2nd area code result should come as difference of population for 2nd survey compared to 1st survey date.
- If more than 2 surveys for same area code then for 1st area code result should come as "1st visit", against 2nd no. of area code difference of population as compared to last survey date, against 3rd no. of area code difference of population as compared to 2nd survey date.
- Same above process should get replicated for multiple entries.
- Check for the same "Area code" in column "A"
- If only single area code in the "A" Column then result in column "E" should come "No records for comparison"
- If there are 2 same area codes then in column "E" against 1st area code result should come as "1st Visit" then for 2nd area code result should come as difference of days in dates of surveys subtracting no. of holidays during that period referring holiday list.
- If more than 2 surveys for same area code then for 1st area code result should come as "1st visit", against 2nd no. of area code difference of days in dates of surveys subtracting no. of holidays during that period referring holiday list, against 3rd no. of area code difference of days in dates of surveys subtracting no. of holidays during that period referring holiday list as compared to last survey date.
- Same above process should get replicated for multiple entries.
Book1 | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Area code | Survey date | Population | Result expected(Manual) | Formula requried | Result expected(Manual) | Formula requried | Holiday list | |||||
2 | 12992 | 11-04-2023 | 78,84,56,751 | No records for comparison | No records for comparison | S.No | Date | Status | |||||
3 | 88802331000932 | 12-04-2023 | 1,19,90,51,462 | 1st visit | 1st visit | 1 | 02-04-2023 | Holiday | |||||
4 | 31744 | 12-04-2023 | 24,67,88,90,321 | No records for comparison | No records for comparison | 2 | 08-04-2023 | Holiday | |||||
5 | 31753 | 13-04-2023 | 15,40,15,32,301 | No records for comparison | No records for comparison | 3 | 09-04-2023 | Holiday | |||||
6 | 89802331002741 | 12-04-2023 | 67,44,46,445 | 1st visit | 1st visit | 4 | 16-04-2023 | Holiday | |||||
7 | 31613 | 16-04-2023 | 75,53,62,976 | No records for comparison | No records for comparison | 5 | 22-04-2023 | Holiday | |||||
8 | 539668 | 19-04-2023 | 1,23,06,70,213 | No records for comparison | No records for comparison | 6 | 23-04-2023 | Holiday | |||||
9 | 12993 | 22-04-2023 | 89,51,44,100 | No records for comparison | No records for comparison | 7 | 30-04-2023 | Holiday | |||||
10 | 12869 | 22-04-2023 | 89,31,01,090 | No records for comparison | No records for comparison | 8 | 07-05-2023 | Holiday | |||||
11 | 89802331003054 | 30-04-2023 | 3,46,54,56,789 | No records for comparison | No records for comparison | 9 | 13-05-2023 | Holiday | |||||
12 | 88802331000274 | 30-04-2023 | 3,36,65,57,880 | No records for comparison | No records for comparison | 10 | 14-05-2023 | Holiday | |||||
13 | 89802331003061 | 05-05-2023 | 3,34,66,56,780 | 1st visit | 1st visit | 11 | 21-05-2023 | Holiday | |||||
14 | 31746 | 07-05-2023 | 16,54,03,29,410 | No records for comparison | No records for comparison | 12 | 27-05-2023 | Holiday | |||||
15 | 111784 | 09-05-2023 | 67,65,67,575 | No records for comparison | No records for comparison | 13 | 28-05-2023 | Holiday | |||||
16 | 89802331003560 | 13-05-2023 | 86,54,45,505 | No records for comparison | No records for comparison | 14 | 04-06-2023 | Holiday | |||||
17 | 13124 | 16-05-2023 | 1,33,32,47,251 | No records for comparison | No records for comparison | 15 | 10-06-2023 | Holiday | |||||
18 | 31710 | 27-05-2023 | 97,74,33,455 | No records for comparison | No records for comparison | 16 | 11-06-2023 | Holiday | |||||
19 | 31756 | 27-05-2023 | 1,75,68,34,512 | No records for comparison | No records for comparison | 17 | 18-06-2023 | Holiday | |||||
20 | 88802331000932 | 05-06-2023 | 17,56,79,08,345 | 16,36,88,56,883 | 43("Difference in survey days-holidays during that period") | 18 | 24-06-2023 | Holiday | |||||
21 | 31764 | 08-06-2023 | 18,67,84,67,120 | No records for comparison | No records for comparison | 19 | 25-06-2023 | Holiday | |||||
22 | 89802331003061 | 13-06-2023 | 12,56,03,90,090 | 9,21,37,33,310 | 30("Difference in survey days-holidays during that period") | 20 | 02-07-2023 | Holiday | |||||
23 | 31761 | 13-06-2023 | 24,79,03,48,190 | No records for comparison | No records for comparison | 21 | 08-07-2023 | Holiday | |||||
24 | 89802331002741 | 14-06-2023 | 17,65,05,32,900 | 16,97,60,86,455 | 50("Difference in survey days-holidays during that period") | 22 | 09-07-2023 | Holiday | |||||
25 | 100107 | 15-06-2023 | 64,47,66,769 | No records for comparison | No records for comparison | 23 | 16-07-2023 | Holiday | |||||
26 | 100418 | 17-06-2023 | 17,53,49,01,100 | No records for comparison | No records for comparison | 24 | 22-07-2023 | Holiday | |||||
27 | 117538 | 28-06-2023 | 17,54,42,10,580 | No records for comparison | No records for comparison | 25 | 23-07-2023 | Holiday | |||||
28 | 537705 | 05-07-2023 | 12,45,63,40,231 | No records for comparison | No records for comparison | 26 | 30-07-2023 | Holiday | |||||
29 | 88802331000932 | 28-07-2023 | 18,45,33,31,191 | 88,54,22,846 | 42("Difference in survey days-holidays during that period") | 27 | 06-08-2023 | Holiday | |||||
30 | 89802331003055 | 05-10-2023 | 18,54,23,61,191 | No records for comparison | No records for comparison | 28 | 12-08-2023 | Holiday | |||||
31 | 29 | 13-08-2023 | Holiday | ||||||||||
32 | 30 | 20-08-2023 | Holiday | ||||||||||
33 | 31 | 26-08-2023 | Holiday | ||||||||||
34 | 32 | 27-08-2023 | Holiday | ||||||||||
35 | 33 | 03-09-2023 | Holiday | ||||||||||
36 | 34 | 09-09-2023 | Holiday | ||||||||||
37 | 35 | 10-09-2023 | Holiday | ||||||||||
38 | 36 | 17-09-2023 | Holiday | ||||||||||
39 | 37 | 23-09-2023 | Holiday | ||||||||||
40 | 38 | 24-09-2023 | Holiday | ||||||||||
41 | 39 | 01-10-2023 | Holiday | ||||||||||
42 | 40 | 08-10-2023 | Holiday | ||||||||||
43 | 41 | 14-10-2023 | Holiday | ||||||||||
44 | 42 | 15-10-2023 | Holiday | ||||||||||
45 | 43 | 22-10-2023 | Holiday | ||||||||||
46 | 44 | 28-10-2023 | Holiday | ||||||||||
47 | 45 | 29-10-2023 | Holiday | ||||||||||
48 | 46 | 05-11-2023 | Holiday | ||||||||||
49 | 47 | 11-11-2023 | Holiday | ||||||||||
50 | 48 | 12-11-2023 | Holiday | ||||||||||
51 | 49 | 19-11-2023 | Holiday | ||||||||||
52 | 50 | 25-11-2023 | Holiday | ||||||||||
53 | 51 | 26-11-2023 | Holiday | ||||||||||
54 | 52 | 03-12-2023 | Holiday | ||||||||||
55 | 53 | 09-12-2023 | Holiday | ||||||||||
56 | 54 | 10-12-2023 | Holiday | ||||||||||
57 | 55 | 17-12-2023 | Holiday | ||||||||||
58 | 56 | 23-12-2023 | Holiday | ||||||||||
59 | 57 | 24-12-2023 | Holiday | ||||||||||
60 | 58 | 31-12-2023 | Holiday | ||||||||||
61 | 59 | 07-01-2024 | Holiday | ||||||||||
62 | 60 | 13-01-2024 | Holiday | ||||||||||
63 | 61 | 14-01-2024 | Holiday | ||||||||||
64 | 62 | 21-01-2024 | Holiday | ||||||||||
65 | 63 | 27-01-2024 | Holiday | ||||||||||
66 | 64 | 28-01-2024 | Holiday | ||||||||||
67 | 65 | 04-02-2024 | Holiday | ||||||||||
68 | 66 | 10-02-2024 | Holiday | ||||||||||
69 | 67 | 11-02-2024 | Holiday | ||||||||||
70 | 68 | 18-02-2024 | Holiday | ||||||||||
71 | 69 | 24-02-2024 | Holiday | ||||||||||
72 | 70 | 25-02-2024 | Holiday | ||||||||||
73 | 71 | 03-03-2024 | Holiday | ||||||||||
74 | 72 | 09-03-2024 | Holiday | ||||||||||
75 | 73 | 10-03-2024 | Holiday | ||||||||||
76 | 74 | 17-03-2024 | Holiday | ||||||||||
77 | 75 | 23-03-2024 | Holiday | ||||||||||
78 | 76 | 24-03-2024 | Holiday | ||||||||||
79 | 77 | 31-03-2024 | Holiday | ||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D20 | D20 | =C20-C3 |
D22,D29 | D22 | =C22-C13 |
D24 | D24 | =C24-C6 |