excel01noob
Board Regular
- Joined
- Aug 5, 2019
- Messages
- 93
- Office Version
- 365
- 2016
Hi
I have two table, table 1 is the registered invoices for a particular month where I usually have:
- old customer accounts (more than 5 digits - column ACCT_ID - table 1);
- 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.
For the old accounts, I need to get a REGIST number that comes from table 2 (this table is saved on another workbook which I don't want to open)
Under column F "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: I need to pick the first 4 digits from the left of the account under ACCT_ID. Then I need to match that 4-digit number with ACCT_ID from table 2 (example: both accounts 7268999 and 7268111 will have the same first 4 digits which as per table 2 gives REGIST F72682001
How can use a macro that used both workbooks and fill in column F on table 1?
Thank you so much!
I have two table, table 1 is the registered invoices for a particular month where I usually have:
- old customer accounts (more than 5 digits - column ACCT_ID - table 1);
- 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.
For the old accounts, I need to get a REGIST number that comes from table 2 (this table is saved on another workbook which I don't want to open)
Under column F "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: I need to pick the first 4 digits from the left of the account under ACCT_ID. Then I need to match that 4-digit number with ACCT_ID from table 2 (example: both accounts 7268999 and 7268111 will have the same first 4 digits which as per table 2 gives REGIST F72682001
RANS_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 | 1009 | 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 |
How can use a macro that used both workbooks and fill in column F on table 1?
Thank you so much!