Hi Guys
I came across this formula here and i was wondering how to go about it which am not sure, so i decided have a play around the formula to extract the unique voyage no based on 2 criteria the name and month.
My Aim is to extract total voyage made for each name so i consider that at most each name can make 10 trips with different voyage number
in Column A i have the month format, then column B I have the voyage listed with duplicate, in column F2 i have predicted that for each name at least 10 trips in a month and in column F1 will be creating a drop down to select month so my idea is that in column G2 i need to extract the unique Voyage for each name without duplicate base on criteria for every month.
i will be very happy is this formula have answer to that and also if there is better solution without the use of array formula to extract the unique voyage
I came across this formula here and i was wondering how to go about it which am not sure, so i decided have a play around the formula to extract the unique voyage no based on 2 criteria the name and month.
My Aim is to extract total voyage made for each name so i consider that at most each name can make 10 trips with different voyage number
in Column A i have the month format, then column B I have the voyage listed with duplicate, in column F2 i have predicted that for each name at least 10 trips in a month and in column F1 will be creating a drop down to select month so my idea is that in column G2 i need to extract the unique Voyage for each name without duplicate base on criteria for every month.
i will be very happy is this formula have answer to that and also if there is better solution without the use of array formula to extract the unique voyage
Book2 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Month | 01-Jan-21 | Extract Unique Voyage Base 2 Criteria Name and Month | |||||||
2 | Voyage No | Name | maximum Trip in a Month 10 to 11 for each | Name | Voyage No | |||||
3 | 01-Jan-21 | 3705 | ADNOC-810 | 1 | ADNOC-810 | #N/A | ||||
4 | 01-Jan-21 | 3705 | ADNOC-810 | 2 | ADNOC-810 | |||||
5 | 01-Jan-21 | 3705 | ADNOC-810 | 3 | ADNOC-810 | |||||
6 | 01-Jan-21 | 3705 | ADNOC-810 | 4 | ADNOC-810 | |||||
7 | 01-Jan-21 | 3706 | ADNOC-224 | 5 | ADNOC-810 | |||||
8 | 01-Jan-21 | 3706 | ADNOC-224 | 6 | ADNOC-810 | |||||
9 | 01-Jan-21 | 3706 | ADNOC-224 | 7 | ADNOC-810 | |||||
10 | 01-Jan-21 | 3706 | ADNOC-224 | 8 | ADNOC-810 | |||||
11 | 01-Jan-21 | 3706 | ADNOC-224 | 9 | ADNOC-810 | |||||
12 | 01-Jan-21 | 3666-O | ASL SWIFT | 10 | ADNOC-810 | |||||
13 | 01-Jan-21 | 3666-O | ASL SWIFT | 1 | ADNOC-224 | |||||
14 | 01-Jan-21 | 3666-O | ASL SWIFT | 2 | ADNOC-224 | |||||
15 | 01-Jan-21 | 3707 | Z-POWER | 3 | ADNOC-224 | |||||
16 | 01-Jan-21 | 3707 | Z-POWER | 4 | ADNOC-224 | |||||
17 | 01-Jan-21 | 3707 | Z-POWER | 5 | ADNOC-224 | |||||
18 | 01-Jan-21 | 3707 | Z-POWER | 6 | ADNOC-224 | |||||
19 | 01-Jan-21 | 3707 | Z-POWER | 7 | ADNOC-224 | |||||
20 | 01-Jan-21 | 3708 | SMIT LUZON | 8 | ADNOC-224 | |||||
21 | 01-Jan-21 | 3708 | SMIT LUZON | 9 | ADNOC-224 | |||||
22 | 01-Jan-21 | 3708 | SMIT LUZON | 10 | ADNOC-224 | |||||
23 | 01-Jan-21 | 3709 | SMIT LUMUT | |||||||
24 | 01-Jan-21 | 3709 | SMIT LUMUT | |||||||
25 | 01-Jan-21 | 3709 | SMIT LUMUT | |||||||
26 | 01-Jan-21 | 3709 | SMIT LUMUT | |||||||
27 | 01-Jan-21 | 3710 | B-LIBERTY-313 | |||||||
28 | 01-Jan-21 | 3710 | B-LIBERTY-313 | |||||||
29 | 01-Jan-21 | 3710 | B-LIBERTY-313 | |||||||
30 | 01-Jan-21 | 3710 | B-LIBERTY-313 | |||||||
31 | 01-Jan-21 | 3710 | B-LIBERTY-313 | |||||||
32 | 01-Jan-21 | 3711 | MAC PHOENIX | |||||||
33 | 01-Jan-21 | 3711 | MAC PHOENIX | |||||||
34 | 01-Jan-21 | 3712 | ADNOC-812 | |||||||
35 | 01-Jan-21 | 3713 | A-HERCULES | |||||||
36 | 01-Jan-21 | 3713 | A-HERCULES | |||||||
37 | 01-Jan-21 | 3713 | A-HERCULES | |||||||
38 | 01-Jan-21 | 3713 | A-HERCULES | |||||||
39 | 01-Jan-21 | 3713 | A-HERCULES | |||||||
40 | 01-Jan-21 | 3713 | A-HERCULES | |||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G3 | G3 | =IF(C3="","",INDEX(B3:B40,MATCH(ROWS(G$3:G3),C3:C40,0))) |