Vba help

[Isabella]

Hi,

Is there a macro that will return the abbrev in Col B under the heading "Rec" by looking at the mapping in Col J & K?

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
1	Rec	Account							Account	Abbrev
2	SMP	NCSX.GESBEQ							GOLD	SMP
3	SMP	NCSX.BENWEB							NCSX	SMP
4	SMP	NCSX.GESBE							STAT	SMP
5	SMP	NCSX.GESS							UBSX	SMP
6	SMP	UBSX.SMPSPE							MIRX	MIR ASIA
7	SMP	ABNA.HOOGOV							JPMC	SMP
8	SMP	ABNA.HOOGOV							DIAT	SMP
9	BNPPINTL	BNPP.ABAAOF							BNPP	BNPPINTL
10	NZACLEAR	AIAU40							AMPW	NZACLEAR
11									NTCO	NZEXTERNAL
12									AMP.FUTURES	FUTURES
13									AIAU40	NZACLEAR
14									AIEO40	NZACLEAR
15									AIFB40	NZACLEAR
16									AIFG40	NZACLEAR
17									AIFN40	NZACLEAR
18
Sheet2

 

[Isabella]

The code fails on Exigo

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
1	Rec	Account							Account	Abbrev
2	SMP	NCSX.GESBEQ							GOLD	SMP
3	SMP	NCSX.BENWEB							NCSX	SMP
4	SMP	NCSX.GESBE							STAT	SMP
5	SMP	NCSX.GESS							UBSX	SMP
6	SMP	UBSX.SMPSPE							MIRX	MIR ASIA
7	SMP	ABNA.HOOGOV							JPMC	SMP
8	SMP	ABNA.HOOGOV							DIAT	SMP
9	BNPPINTL	BNPP.ABAAOF							BNPP	BNPPINTL
10	NZACLEAR	AIAU40							AMPW	NZACLEAR
11	N/A	EXIG.COGN20							NTCO	NZEXTERNAL
12	N/A	EXIG.COGNIN							AMP.FUTURES	FUTURES
13	N/A	EXIG.AMPS20							AIAU40	NZACLEAR
14	N/A								AIEO40	NZACLEAR
15	N/A								AIFB40	NZACLEAR
16	N/A								AIFG40	NZACLEAR
17	N/A								AIFN40	NZACLEAR
18	N/A								ABNA	SMP
19									EXIG.COGN20	NOM EXIGO
20									EXIG.COGNIN	NOM EXIGO
21									EXIG.AMPS20	LIFE EXIGO
22
Sheet2

 

[hiker95]

Isabella,

The macro I posted on reply #9 will yield:

Excel Workbook
	B	C	D	E	F	G	H	I	J	K
1	Rec	Account							Account	Abbrev
2	SMP	NCSX.GESBEQ							GOLD	SMP
3	SMP	NCSX.BENWEB							NCSX	SMP
4	SMP	NCSX.GESBE							STAT	SMP
5	SMP	NCSX.GESS							UBSX	SMP
6	SMP	UBSX.SMPSPE							MIRX	MIR ASIA
7	SMP	ABNA.HOOGOV							JPMC	SMP
8	SMP	ABNA.HOOGOV							DIAT	SMP
9	BNPPINTL	BNPP.ABAAOF							BNPP	BNPPINTL
10	NZACLEAR	AIAU40							AMPW	NZACLEAR
11		Isabella							NTCO	NZEXTERNAL
12	NOM EXIGO	EXIG.COGN20							AMP.FUTURES	FUTURES
13	NOM EXIGO	EXIG.COGNIN							AIAU40	NZACLEAR
14	LIFE EXIGO	EXIG.AMPS20							AIEO40	NZACLEAR
15									AIFB40	NZACLEAR
16									AIFG40	NZACLEAR
17									AIFN40	NZACLEAR
18									ABNA	SMP
19									EXIG.COGN20	NOM EXIGO
20									EXIG.COGNIN	NOM EXIGO
21									EXIG.AMPS20	LIFE EXIGO
22
Sheet2

 

[Peter_SSs]

Hi peter that works but how do i get rid of the N/A in col b?

From your results it would indicate that there is something in column C in those rows but I can't see what it is. However, surely you can see the part of my code that returns "N/A" and try modifying that yourself.

The code fails on Exigo

This comes back to the questions asked by rudfaden in posts #2 and #4 which were never clearly answered. Until you posted this new data that my code fails on, I guessed that the "mapping" was that you looked up in column J what came before the "." (if there was one) in column C and returned the corresponding value from column K. I must admit I hadn't fully considered the "AMP.FUTURES" entry in column J but no examples of that were given.

So what is the logic behind what is to be returned?
How do we know whether just to check the first part of column C or the whole of it?

Hiker's code may be just what you are after but note that it may return incorrect results if and entry like "NCSX.ABNA" was possible in column C. Would that be possible?

Do you have a comprehensive list of all possible values and their abbreviations for columns J:K? If it isn't too long, could we see it?

Similarly, is there comprehensive list of possible values for column C?

Note also that the post #8 code seems to work for any of the data you have posted so far (provided the colummn J & K range in the formula is extended to be big enough for the data)