Hi Guys,
I have difficulty of returning the values according to the conditions. Appreciate it if you could look into it and share your knowledges and expertise on this issue.
some data in column A ( FY 22 and FY23 client code) has associated billing number(s) in Column D (Billing Numbers). Using Column B (FY23 Client code), it is to identify the same data in Column A and to return all associated data in Column D (Biling Number) and in Column F(document billed gross Amt) in respective column with comma (if there are multiple values return). Upon comparison of column B and A and if only one billing number is found, first column returns to corresponding billing number and subsequent columns returns to "No Further bills" If data in column B is not matched with the one in column A, it is to return "not found".
I have difficulty of returning the values according to the conditions. Appreciate it if you could look into it and share your knowledges and expertise on this issue.
some data in column A ( FY 22 and FY23 client code) has associated billing number(s) in Column D (Billing Numbers). Using Column B (FY23 Client code), it is to identify the same data in Column A and to return all associated data in Column D (Biling Number) and in Column F(document billed gross Amt) in respective column with comma (if there are multiple values return). Upon comparison of column B and A and if only one billing number is found, first column returns to corresponding billing number and subsequent columns returns to "No Further bills" If data in column B is not matched with the one in column A, it is to return "not found".
Raw Data | End result | ||||||||
| FY22 and FY23 Client code | FY23 Client code | AR Code | Billing Number | Date Billed | Document Billed Gross Amt | FY23 client code | Billing Number | Document Billed Gross Amt | |
| A-66063406 | A-66335647 | 12151918 | AS01A000042675 | 25 Jul 2022 | 17,676.40 | A-66335647 | AS01A000055978, AS01A000055982 | 17,841.60,1,080.00 | |
| A-66335647 | A-67401729 | 12151918 | AS01A000055978 | 20 Jun 2023 | 17,841.60 | Not found | Not found | ||
| A-66335647 | A-67401729 | 12151918 | AS01A000055982 | 20 Jun 2023 | 1,080.00 | Not found | Not found | ||
| A-66063406 | A-66335647 | 12174743 | AS01A000038057 | 25 Apr 2022 | 32,742.00 | A-66335647 | AS01A000055979 | 50,667.12 | |
| A-66063406 | A-66335647 | 12174743 | AS01A000042672 | 25 Jul 2022 | 42,778.60 | No Further Bills | |||
| A-66335647 | A-67401729 | 12174743 | AS01A000055979 | 20 Jun 2023 | 50,667.12 | Not found | |||
| A-66458454 | A-67255819 | 12760057 | AS01A000046835 | 14 Nov 2022 | 10,914.00 | Not found | Not found | ||
| A-66458454 | A-67255819 | 12760057 | AS01A000050261 | 03 Mar 2023 | 11,016.00 | Not found | Not found |
