vlookup, index &match or other?

[excel01noob]

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..

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

 

[daregu]

Can you put a desired result in the result column. I'm having trouble visualizing this as I don't see a column with the year opened in it. That being said, the easiest way I've found to deal with multiple workbooks is power query.

 

[excel01noob]

the results in this case should be

O72682001
O40431985
O72682001
O44821989
N26725000
O72682001
N27194000
O70041978
N27193000

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	TRANS_TYP	ARTIC	VALUEDT	ACCT_ID	AMMT	RESULT?		MANDT	ACCT_ID	LEG_ACCT	REGIST
2	INVCAS	3225TF	2021-05-03T00:00:00.000000	7268999	22564	O72682001		10	7268	000	F72682001
3	INVCHE	1174WE	2021-05-04T00:00:00.000000	4043111	1005.99	O40431985		10	4043	002	F40431985
4	INVCHE	459DE	2021-05-08T00:00:00.000000	7268111	1985.43	O72682001		10	4482	283	F44821989
5	INVCAS	287SY	2021-05-11T00:00:00.000000	4482999	34.5	O44821989		10	7004	204	F70041978
6	INVCAS	1174WE	2021-05-11T00:00:00.000000	26725	2569.52	N26725000		10	1005	000	F10091993
7	INVACC	133LO	2021-05-13T00:00:00.000000	7268000	875.44	O72682001		10	1005	080	F10091993
8	INVACC	459DE	2021-05-13T00:00:00.000000	27194	224.55	N27194000		10	1005	203	F10091993
9	INVCHE	117RE	2021-05-28T00:00:00.000000	7004111	103298.5	O70041978
10	INVCHE	287SY	2021-05-28T00:00:00.000000	27193	1433.99	N27193000
11
Lists

Cell Formulas
Range	Formula
F2:F10	F2	=IF(LEN(D2)=5,"N"&D2&"000",SUBSTITUTE(VLOOKUP(LEFT(D2,4),$I$2:$K$8,3,0),"F","O"))

 

[excel01noob]

Fluff, it is exactly that!!!!

How do I put this into VBA now?

 

[Fluff]

If you wanted a VBA solution why didn't you say so?

 

[excel01noob]

I did mention it in my first post, was struggling initially with the excel formula

 

[Fluff]

You said it was a first step prior to VBA. That is not the same.

 

[excel01noob]

how to translate the "substitute" part of the formula into VBA if:

-I have the table 2 in a closed workbook which I don't need to open, location N:\My docs\Table 2 closed.xlsx;

-the table 2 data is on the same columns H to K but it is stored as text, it seems I might get an error "When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, the VLookup method may give an incorrect or unexpected value."


this is what I got so far

Option Explicit

Sub accounts_old_and_new()

Dim LastRow As Long

LastRow = Range("D" & Rows.Count).End(xlUp).Row

Dim i As Long

For i = 2 To LastRow

If Len(Range("D" & i)) = 5 Then
Range("F" & i).Value = "C" & Range("D" & i)

'Else

Range("F" & i).Value = ???

Else

End If
Next i

End Sub

 

[Fluff]

Using Xl formulae in a macro is normally best avoided as it tends to be slow. I would suggest that you start a new thread, explaining that you want a macro & giving all relevant info.