excel01noob
Board Regular
- Joined
- Aug 5, 2019
- Messages
- 93
- Office Version
- 365
- 2016
Hi
I'm having a bit of hard time trying to figure out how to solve this.
I have two table, table 1 is the registered invoices for a particular month where I could have old customer accounts (more than 5 digits - column D), newer accounts have only 5 digits as account number (column ACCT_ID - table 1)
I need to prepare report where I combine both account numbers. where for the old ones, I need to get a REGIST number that comes from table 2 (this is actually a table saved in a separate file).
Under column "result" I want to have the accounts with this format:
-new accounts: starting with N and then the 5-digits account and three zeros (example: acc 27193 is N27193000);
-old accounts: they need to start by O then the first 4 digits and year when opened. This I take from table 2 (example: both accounts 7268999 and 7268111 will have the same first 4 digits which as per table 2 gives REGIST F72682001
I have tried to combine If, left, len, vlookup but end up not getting it right.
What do you advise me to do?
this is just the first step prior to implement a VBA later..
I'm having a bit of hard time trying to figure out how to solve this.
I have two table, table 1 is the registered invoices for a particular month where I could have old customer accounts (more than 5 digits - column D), newer accounts have only 5 digits as account number (column ACCT_ID - table 1)
I need to prepare report where I combine both account numbers. where for the old ones, I need to get a REGIST number that comes from table 2 (this is actually a table saved in a separate file).
Under column "result" I want to have the accounts with this format:
-new accounts: starting with N and then the 5-digits account and three zeros (example: acc 27193 is N27193000);
-old accounts: they need to start by O then the first 4 digits and year when opened. This I take from table 2 (example: both accounts 7268999 and 7268111 will have the same first 4 digits which as per table 2 gives REGIST F72682001
I have tried to combine If, left, len, vlookup but end up not getting it right.
What do you advise me to do?
this is just the first step prior to implement a VBA later..
TRANS_TYP | ARTIC | VALUEDT | ACCT_ID | AMMT | RESULT? | MANDT | ACCT_ID | LEG_ACCT | REGIST | |
INVCAS | 3225TF | 2021-05-03T00:00:00.000000 | 7268999 | 22564 | 010 | 7268 | 000 | F72682001 | ||
INVCHE | 1174WE | 2021-05-04T00:00:00.000000 | 4043111 | 1005.99 | 010 | 4043 | 002 | F40431985 | ||
INVCHE | 459DE | 2021-05-08T00:00:00.000000 | 7268111 | 1985.43 | 010 | 4482 | 283 | F44821989 | ||
INVCAS | 287SY | 2021-05-11T00:00:00.000000 | 4482999 | 34.5 | 010 | 7004 | 204 | F70041978 | ||
INVCAS | 1174WE | 2021-05-11T00:00:00.000000 | 26725 | 2569.52 | 010 | 1005 | 000 | F10091993 | ||
INVACC | 133LO | 2021-05-13T00:00:00.000000 | 7268000 | 875.44 | 010 | 1005 | 080 | F10091993 | ||
INVACC | 459DE | 2021-05-13T00:00:00.000000 | 27194 | 224.55 | 010 | 1005 | 203 | F10091993 | ||
INVCHE | 117RE | 2021-05-28T00:00:00.000000 | 7004111 | 103298.5 | ||||||
INVCHE | 287SY | 2021-05-28T00:00:00.000000 | 27193 | 1433.99 | ||||||
TABLE 1 | TABLE 2 |