RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- Office Version
- 2019
- Platform
- Windows
I have to delete the rows which have one Invoice number more than once marked in green. There are multiple Invoice numbers in column E which are common. With the help of a formula in column D, I want to display only the invoice number once and leave the rows blank where the invoice number is the same. The yellow marked cells in column D have still common numbers. I need help in editing the formula to get each invoice number only once in column D. Then I can sort the column D and delete the blank cells.
Book1.xlsx | ||||||
---|---|---|---|---|---|---|
D | E | F | G | |||
1 | Get 1 Invoice Number | Invoice number | Invoice Date | Invoice Value (₹) | ||
2 | 10 | 06-06-2020 | 48486.00 | |||
3 | 11 | 22-02-2021 | 7080.00 | |||
4 | 67 | 67 | 10-07-2020 | 3127.82 | ||
5 | 67 | 10-07-2020 | 3127.82 | |||
6 | 116 | 27-10-2020 | 24800.00 | |||
7 | 115 | 115 | 27-10-2020 | 24800.00 | ||
8 | 115 | 115 | 27-10-2020 | 24800.00 | ||
9 | 115 | 27-10-2020 | 24800.00 | |||
10 | 239 | 239 | 16-01-2021 | 14780.00 | ||
11 | 239 | 16-01-2021 | 14780.00 | |||
12 | 309 | 309 | 31-03-2021 | 21249.48 | ||
13 | 309 | 31-03-2021 | 21249.48 | |||
14 | 829 | 829 | 21-01-2021 | 1014.00 | ||
15 | 829 | 829 | 21-01-2021 | 1014.00 | ||
16 | 829 | 21-01-2021 | 1014.00 | |||
17 | 3846 | 03-12-2020 | 24135.00 | |||
18 | 3862 | 3862 | 07-12-2020 | 7880.00 | ||
19 | 3862 | 07-12-2020 | 7880.00 | |||
20 | 3935 | 3935 | 31-12-2020 | 1530.00 | ||
21 | 3935 | 31-12-2020 | 1530.00 | |||
22 | 0011/20-21 | 0011/20-21 | 04-06-2020 | 14967.00 | ||
23 | 0011/20-21 | 04-06-2020 | 14967.00 | |||
24 | 0116/20-21 | 0116/20-21 | 21-05-2020 | 29280.00 | ||
25 | 0116/20-21 | 0116/20-21 | 21-05-2020 | 29280.00 | ||
26 | 0116/20-21 | 21-05-2020 | 29280.00 | |||
27 | 015/BR/20000708 | 10-07-2020 | 12071.00 | |||
28 | 0411/20-21 | 0411/20-21 | 09-09-2020 | 42579.00 | ||
29 | 0411/20-21 | 09-09-2020 | 42579.00 | |||
30 | 0478/20-21 | 0478/20-21 | 18-09-2020 | 104953.00 | ||
31 | 0478/20-21 | 0478/20-21 | 18-09-2020 | 104953.00 | ||
32 | 0478/20-21 | 18-09-2020 | 104953.00 | |||
33 | 0479/20-21 | 01-09-2021 | 104953.00 | |||
34 | 0688/20-21 | 0688/20-21 | 10-10-2020 | 93114.00 | ||
35 | 0688/20-21 | 10-10-2020 | 93114.00 | |||
36 | 1494/20-21 | 1494/20-21 | 27-01-2021 | 257332.00 | ||
37 | 1494/20-21 | 27-01-2021 | 257332.00 | |||
38 | 1585/20-21 | 1585/20-21 | 08-02-2021 | 186029.00 | ||
39 | 1585/20-21 | 08-02-2021 | 186029.00 | |||
40 | 1632/20-21 | 1632/20-21 | 15-02-2021 | 176632.00 | ||
41 | 1632/20-21 | 15-02-2021 | 176632.00 | |||
42 | AIM-20-048 | AIM-20-048 | 23-09-2020 | 106305.16 | ||
43 | AIM-20-048 | 23-09-2020 | 106305.16 | |||
44 | B/S/696/2021 | B/S/696/2021 | 31-08-2020 | 158468.98 | ||
45 | B/S/696/2021 | 31-08-2020 | 158468.98 | |||
46 | BIS-BLR/2703 | BIS-BLR/2703 | 02-03-2021 | 20268.00 | ||
47 | BIS-BLR/2703 | BIS-BLR/2703 | 02-03-2021 | 20268.00 | ||
48 | BIS-BLR/2703 | 02-03-2021 | 20268.00 | |||
49 | D200002 | D200002 | 05-05-2020 | 14066.00 | ||
50 | D200002 | 05-05-2020 | 14066.00 | |||
51 | INC/2622 | INC/2622 | 15-01-2021 | 1416.00 | ||
52 | INC/2622 | INC/2622 | 15-01-2021 | 1416.00 | ||
53 | INC/2622 | 15-01-2021 | 1416.00 | |||
54 | INC/2784 | INC/2784 | 10-03-2021 | 44241.00 | ||
55 | INC/2784 | 10-03-2021 | 44241.00 | |||
56 | INC/2825 | INC/2825 | 23-03-2021 | 36519.00 | ||
57 | INC/2825 | 23-03-2021 | 36519.00 | |||
58 | INC/2846 | INC/2846 | 31-03-2021 | 16468.00 | ||
59 | INC/2846 | 31-03-2021 | 16468.00 | |||
60 | INMDTA2021/00360 | INMDTA2021/00360 | 08-05-2020 | 103456.00 | ||
61 | INMDTA2021/00360 | 08-05-2020 | 103456.00 | |||
62 | INMDTA2021/00440 | INMDTA2021/00440 | 15-05-2020 | 618867.55 | ||
63 | INMDTA2021/00440 | 15-05-2020 | 618867.55 | |||
64 | INMDTA2021/00751 | INMDTA2021/00751 | 26-05-2020 | 490541.60 | ||
65 | INMDTA2021/00751 | 26-05-2020 | 490541.60 | |||
66 | INMDTA2021/04675 | INMDTA2021/04675 | 10-10-2020 | 450045.60 | ||
67 | INMDTA2021/04675 | 10-10-2020 | 450045.60 | |||
68 | OIM-20-008 | OIM-20-008 | 28-07-2020 | 56132.06 | ||
69 | OIM-20-008 | 28-07-2020 | 56132.06 | |||
70 | PH-1498 | PH-1498 | 20-01-2021 | 10780.00 | ||
71 | PH-1498 | 20-01-2021 | 10780.00 | |||
72 | PIA2001782 | PIA2001782 | 30-07-2020 | 134437.02 | ||
73 | PIA2001782 | PIA2001782 | 30-07-2020 | 134437.02 | ||
74 | PIA2001782 | 30-07-2020 | 134437.02 | |||
75 | PIA2005028 | PIA2005028 | 08-12-2020 | 93949.00 | ||
76 | PIA2005028 | 08-12-2020 | 93949.00 | |||
77 | SERVBAJV21000449 | SERVBAJV21000449 | 24-06-2020 | 53692.00 | ||
78 | SERVBAJV21000449 | 24-06-2020 | 53692.00 | |||
79 | SERVBAJV21001330 | SERVBAJV21001330 | 06-10-2020 | 17833.00 | ||
80 | SERVBAJV21001330 | 06-10-2020 | 17833.00 | |||
81 | SERVBAJV21002524 | SERVBAJV21002524 | 20-01-2021 | 27951.00 | ||
82 | SERVBAJV21002524 | 20-01-2021 | 27951.00 | |||
83 | SERVBAJV21003276 | SERVBAJV21003276 | 30-03-2021 | 59058.00 | ||
84 | SERVBAJV21003276 | SERVBAJV21003276 | 30-03-2021 | 59058.00 | ||
85 | SERVBAJV21003276 | 30-03-2021 | 59058.00 | |||
86 | SIKRMGL4387 | SIKRMGL4387 | 01-01-2021 | 23800.00 | ||
87 | SIKRMGL4387 | 01-01-2021 | 23800.00 | |||
Query. |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D87 | D2 | =IF(E2=E3,E2,"") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
E37:E49,E52:E87,E5:E26,E29:E34 | Expression | =OR(AND(E5=E6,G5=G6),AND(E5=E4,G5=G4)) | text | NO |
E4,E36,E51,E28 | Expression | =OR(AND(E4=E5,G4=G5),AND(E4=#REF!,G4=#REF!)) | text | NO |
E35,E50,E27 | Expression | =OR(AND(E27=#REF!,G27=#REF!),AND(E27=E26,G27=G26)) | text | NO |
E2 | Expression | =OR(AND(E2=#REF!,G2=#REF!),AND(E2=#REF!,G2=#REF!)) | text | NO |
E3 | Expression | =OR(AND(E3=#REF!,G3=#REF!),AND(E3=#REF!,G3=#REF!)) | text | NO |