I am stuck to find a good function to solve my problem.
| ptf | ISIN | CUSTOM CODE |
|---|---|---|
| FA_ABC | AAPL 06/20/25 P155 EQUITY | 583417_FA_ABC_U03_EUR |
| FA_ABC | CA21037X1006 | 583417_FA_ABC_U03_EUR |
| FA_ABC | CA21037X1345 | 583417_FA_ABC_U03_EUR |
| LV_XYZ | AAPL 06/20/25 P155 EQUITY | 7259157_LV_XYZ_013_USD |
| LV_XYZ | CA21037X1006 | 190167_LV_XYZ_S01_CAD |
| LV_XYZ | CA21037X1345 | 572720_LV_XYZ_W02_CAD |
I want to assign new codes for securities in this table, for duplicated items duplicated exactly twice, by assigning new codes to items with ptf code FA_ABC with custom code from items with ptf code LV_XYZ for the same ISIN.
Here’s the desired outcome – from and to codes are show in in bold italics:
| ptf | ISIN | CUSTOM CODE | DESIRED CUSTOM CODE |
|---|---|---|---|
| FA_ABC | AAPL 06/20/25 P155 EQUITY | 583417_FA_ABC_U03_EUR | 7259157_LV_XYZ_013_USD |
| FA_ABC | CA21037X1006 | 583417_FA_ABC_U03_EUR | 190167_LV_XYZ_S01_CAD |
| FA_ABC | CA21037X1345 | 583417_FA_ABC_U03_EUR | 572720_LV_XYZ_W02_CAD |
| LV_XYZ | AAPL 06/20/25 P155 EQUITY | 7259157_LV_XYZ_013_USD | 7259157_LV_XYZ_013_USD |
| LV_XYZ | CA21037X1006 | 190167_LV_XYZ_S01_CAD | 190167_LV_XYZ_S01_CAD |
| LV_XYZ | CA21037X1345 | 572720_LV_XYZ_W02_CAD | 572720_LV_XYZ_W02_CAD |
I have tried a lot with if, countif, match and vlookup but still don’t get what I want
[what I see now vs. what I would like to see][1]
It seems I am not able to embed images, only links to it
Any help or hint is really appreciated
thank you.
5
Is this what you’re trying to get – enter the following in D2 (clear cells below):
Requires Microsoft 365
=LET(
data, TRIM(A2:XLOOKUP(TRUE, C:C <> "", C:C, , , -1)),
to, "LV_XYZ",
from, "FA_ABC",
ptf, INDEX(data, , 1),
ISIN, INDEX(data, , 2),
custom, INDEX(data, , 3),
new_custom, FILTER(HSTACK(ISIN, custom), ptf = to),
IF(
ptf = from,
XLOOKUP(
ISIN,
INDEX(new_custom, , 1),
INDEX(new_custom, , 2)
),
custom
)
)
Data used:
| ptf | ISIN | CUSTOM CODE |
|---|---|---|
| FA_ABC | AAPL 06/20/25 P155 EQUITY | 583417_FA_ABC_U03_EUR |
| FA_ABC | CA21037X1006 | 583417_FA_ABC_U03_EUR |
| FA_ABC | CA21037X1345 | 583417_FA_ABC_U03_EUR |
| LV_XYZ | AAPL 06/20/25 P155 EQUITY | 7259157_LV_XYZ_013_USD |
| LV_XYZ | CA21037X1006 | 190167_LV_XYZ_S01_CAD |
| LV_XYZ | CA21037X1345 | 572720_LV_XYZ_W02_CAD |
3