Need Help with Lookup....

[smalik]

I think I need a lookup formula but I can't seem to figure out. Even with nested lookup, I can't get the results I am looking
I will try to explain as best as I can

the source code file has four columns:
Producer Code0,
Producer Code1,
Producer Code2, &
Producer Code3

I am trying to transfer this data into a new system where Producer Code 0 can only have the house Account codes

The House Account code is either "Houeb1" or "Houpe1"

However, in the source system data, these house account codes are found in any of the four Producer Code columns.

Is there a way I can find the house account codes from the source system file and place it in the Producer Code0 field in a different tab? Not only that if I move the house code to the first filed, I need to move the other codes into the following producer code columns. For example,

if the HOUEB1 code from source file is found in Producer Code0, it should stay in Produce Code0 filed in the destination
if the HOUPE1 code is found in Producer Code1, and there is another Code in Producer Code0 filed. The new mapping is HOUEB1 in ProduceCode0 and the other code in ProducerCode1 field
if the HOUEB1 code is found in Producer Code2, and there are other codes in Producer Code0, & Producer Code3 fields, the new mapping should be HOUEB1 in ProducerCode0 field followed by other codes in the ProducerCode1, & 2 fields
and so and so forth.


Not only that there are "Commission Percent" field for each Producer Code field.

Once I move the code, I need to align the Commission Percent field as well. For example, ProduceCode0 gets mapped with Commission Percent0 field

I will be mapping this for 55K+ rows. This will be done three time over the next two months. Preview-1, Preview-2, and final production transfer so any help is greatly appreciated.

Not sure if was able explain my dilemma, so please feel free to ask questions. I would love to get a formula as I don't know anything about macros but if this can only be done via a macro, I would need help adding the code to my file.

I have downloaded xl2bb addin and pasted the Excel.

Thanks for the help in advance.

SplitMerge.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Producer Code 0	Producer Name 0	Producer Type 0	Producer Code 1	Producer Name 1	Producer Type 1	Producer Code 2	Producer Name 2	Producer Type 2	Producer Code 3	Producer Name 3	Producer Type 3	Commission Percent 0	Commission Percent 1	Commission Percent 2	Commission Percent 3
2	HOUEB1	House Employee Benefits	PPAY										0.0000@
3	HOUPE1	House Personal	PPAY										0.0000@
4	HOUEB1	House Employee Benefits	PPAY										0.0000@
5	HOUEB1	House Employee Benefits	PPAY										0.0000@
6	HOUEB1	House Employee Benefits	PPAY										0.0000@
7	HOUEB1	House Employee Benefits	PPAY										0.0000@
8	HOUEB1	House Employee Benefits	PPAY										0.0000@
9	HOUEB1	House Employee Benefits	PPAY	PAYKR1	Kristi Payne	PPAY							0.0000@	10.0000@
10	HOUEB1	House Employee Benefits	PPAY	PAYKR1	Kristi Payne	PPAY							0.0000@	0.0000@
11	HOUEB1	House Employee Benefits	PPAY										0.0000@
12	HOUEB1	House Employee Benefits	PPAY										0.0000@
13	BRETI1	Tim Bresnahan	PPAY										18.0000@
14	BRETI1	Tim Bresnahan	PPAY										18.0000@
15				BRETI1	Tim Bresnahan	PPAY								18.0000@
16	CUNMI1	Michael Cunningham	PPAY										20.0000@
17	CUNMI1	Michael Cunningham	PPAY										20.0000@
18	CUNMI1	Michael Cunningham	PPAY										20.0000@
19				KANCH1	Chris Kane	PPAY								30.0000@
20				KANCH1	Chris Kane	PPAY								0.0000@
21	KANCH1	Chris Kane	PPAY										40.0000@
22				KANCH1	Chris Kane	PPAY								15.0000@
23				KANCH1	Chris Kane	PPAY								0.0000@
24				KANCH1	Chris Kane	PPAY								0.0000@
25				HOUEB1	House Employee Benefits	PPAY								0.0000@
26				HOUEB1	House Employee Benefits	PPAY								0.0000@
27				HOUEB1	House Employee Benefits	PPAY								0.0000@
28				HOUEB1	House Employee Benefits	PPAY								0.0000@
29				HOUEB1	House Employee Benefits	PPAY								0.0000@
30				HOUEB1	House Employee Benefits	PPAY								0.0000@
31				DONGR1	Gregory C. Donnelly	PPAY								30.0000@
32				DONGR1	Gregory C. Donnelly	PPAY								30.0000@
33				DONGR1	Gregory C. Donnelly	PPAY								30.0000@
34				HOUPE1	House Personal	PPAY								30.0000@
35	FEDEGRO-01	The Fedeli Group	BPAY	APBENEF-01	AP Benefit Advisors, LLC	BPAY	HOUEB1	House Employee Benefits	PPAY				83.0000@	15.0000!	0.0000!
36	FEDEGRO-01	The Fedeli Group	BPAY	APBENEF-01	AP Benefit Advisors, LLC	BPAY	HOUEB1	House Employee Benefits	PPAY				83.0000@	15.0000!	0.0000!
37	FEDEGRO-01	The Fedeli Group	BPAY	APBENEF-01	AP Benefit Advisors, LLC	BPAY	HOUEB1	House Employee Benefits	PPAY				83.0000@	15.0000!	0.0000!
38	FEDEGRO-01	The Fedeli Group	BPAY	APBENEF-01	AP Benefit Advisors, LLC	BPAY	HOUEB1	House Employee Benefits	PPAY				83.0000@	15.0000!	0.0000!
39	FEDEGRO-01	The Fedeli Group	BPAY	APBENEF-01	AP Benefit Advisors, LLC	BPAY	HOUEB1	House Employee Benefits	PPAY				83.0000@	15.0000!	0.0000!
40							HOUEB1	House Employee Benefits	PPAY						0.0000@
41							HOUEB1	House Employee Benefits	PPAY						0.0000@
42				KANCH1	Chris Kane	PPAY	HOUEB1	House Employee Benefits	PPAY					40.0000@	30.0000@
43				KANCH1	Chris Kane	PPAY	HOUEB1	House Employee Benefits	PPAY					30.0000@	30.0000@
44				KANCH1	Chris Kane	PPAY	HOUEB1	House Employee Benefits	PPAY					30.0000@	30.0000@
45				KANCH1	Chris Kane	PPAY	HOUEB1	House Employee Benefits	PPAY					30.0000@	30.0000@
46				KANCH1	Chris Kane	PPAY	HOUEB1	House Employee Benefits	PPAY					30.0000@	30.0000@
47	KANCH1	Chris Kane	PPAY				HOUEB1	House Employee Benefits	PPAY				30.0000@		30.0000@
48				SMIJO2	Jon Smith	PPAY	DEGKI1	Kim DeGiralomo	PPAY					18.0000@	12.0000@
49				SMIJO2	Jon Smith	PPAY	DEGKI1	Kim DeGiralomo	PPAY					18.0000@	12.0000@
50	DONGR1	Gregory C. Donnelly	PPAY	ZIMAN1	Anthony Zimbardi	PPAY	BLACH1	Chris Black	PPAY				10.0000@	10.0000@	20.0000@
51				ZIMAN1	Anthony Zimbardi	PPAY	BLACH1	Chris Black	PPAY					20.0000@	20.0000@
52				ZIMAN1	Anthony Zimbardi	PPAY	BLACH1	Chris Black	PPAY					20.0000@	20.0000@
53				ZIMAN1	Anthony Zimbardi	PPAY	BLACH1	Chris Black	PPAY					15.0000@	15.0000@
54				ZIMAN1	Anthony Zimbardi	PPAY	BLACH1	Chris Black	PPAY					15.0000@	15.0000@
55				SMIJO2	Jon Smith	PPAY	ASSUOFO-01	AssuredPartners of Ohio, LLC - Richfield	BPAY					20.0000@	20.0000@
56				SMIJO2	Jon Smith	PPAY	ASSUOFO-01	AssuredPartners of Ohio, LLC - Richfield	BPAY					15.0000@	15.0000@
57				SMIJO2	Jon Smith	PPAY	ASSUOFO-01	AssuredPartners of Ohio, LLC - Richfield	BPAY					15.0000@	15.0000@
58				SMIJO2	Jon Smith	PPAY	ASSUOFO-01	AssuredPartners of Ohio, LLC - Richfield	BPAY					15.0000@	15.0000@
59				SMIJO2	Jon Smith	PPAY	ASSUOFO-01	AssuredPartners of Ohio, LLC - Richfield	BPAY					15.0000@	15.0000@
60	KEVILIN-01	Kevin Lindsley	BPAY	KANCH1	Chris Kane	PPAY				RICBE1	House- Richfield Benefits	PPAY	50.0000@	30.0000@		30.0000@
61				KANCH1	Chris Kane	PPAY	KEVILIN-01	Kevin Lindsley	BPAY	RICBE1	House- Richfield Benefits	PPAY		30.0000@	50.0000@	30.0000@
62				KANCH1	Chris Kane	PPAY	KEVILIN-01	Kevin Lindsley	BPAY	RICBE1	House- Richfield Benefits	PPAY		30.0000@	50.0000@	30.0000@
63				KANCH1	Chris Kane	PPAY				RICBE1	House- Richfield Benefits	PPAY		30.0000@		30.0000@
64				MITJE1	J.E. Mitchell	PPAY	RICBE1	House- Richfield Benefits	PPAY	JEDAG1	Aggie Jedlicka	PPAY		25.0000@	25.0000@	5.0000@
65				MITJE1	J.E. Mitchell	PPAY	RICBE1	House- Richfield Benefits	PPAY	JEDAG1	Aggie Jedlicka	PPAY		25.0000@	25.0000@	5.0000@
66				MITJE1	J.E. Mitchell	PPAY	RICBE1	House- Richfield Benefits	PPAY	JEDAG1	Aggie Jedlicka	PPAY		25.0000@	25.0000@	5.0000@
67				MITJE1	J.E. Mitchell	PPAY	RICBE1	House- Richfield Benefits	PPAY	JEDAG1	Aggie Jedlicka	PPAY		25.0000@	25.0000@	5.0000@
68				MITJE1	J.E. Mitchell	PPAY	RICBE1	House- Richfield Benefits	PPAY	JEDAG1	Aggie Jedlicka	PPAY		25.0000@	25.0000@	5.0000@
69				MITJE1	J.E. Mitchell	PPAY	RICBE1	House- Richfield Benefits	PPAY	JEDAG1	Aggie Jedlicka	PPAY		25.0000@	25.0000@	5.0000@
70				MITJE1	J.E. Mitchell	PPAY	RICBE1	House- Richfield Benefits	PPAY	JEDAG1	Aggie Jedlicka	PPAY		25.0000@	25.0000@	5.0000@
71				MITJE1	J.E. Mitchell	PPAY	RICBE1	House- Richfield Benefits	PPAY	JEDAG1	Aggie Jedlicka	PPAY		25.0000@	25.0000@	5.0000@
72				KANCH1	Chris Kane	PPAY	KEVILIN-01	Kevin Lindsley	BPAY	HOUEB1	House Employee Benefits	PPAY		30.0000@	50.0000@	30.0000@
Source

 

[earthworm]

Kindly give the answer as to how do you want the data to appear from above mentioned sheet.

 

[smalik]

Kindly give the answer as to how do you want the data to appear from above mentioned sheet.

First, Thanks for your help.

Second, I was able to download and launch the XL2BB plugin earlier. However, since we are not allowed to add anything to our corporate laptops, the plugin has been delted from my computer since then. Therefore, I have to work with snapshots.

Second, here is a screen shot of the desired results from the above data set. I will be happy to email the Excel file if need be.

Hope this is helpful.

 

[earthworm]

Column 2 code-2 has more then one code . still didn't get it Why more product code are present in column 2 if there is single product code appearing in code 1 in desired result.

 

[Peter_SSs]

I will be happy to email the Excel file if need be.

You can upload a sample file to DropBox or OneDrive, Google Drive etc and provide a publicly shared link here in the forum but emailing the file to a member would be a breach of #4 of the Forum Rules

 

[smalik]

Column 2 code-2 has more then one code . still didn't get it Why more product code are present in column 2 if there is single product code appearing in code 1 in desired result.

These codes are producer codes (or sales person). The source system had option for up to four producers assigned to an account including the house account. The source system did not care where the house account was, i.e., Code-1 or Code-2 or Code-3, etc. The new system also has the option for four codes. However, Code-1 is limited to the house account. Hence I have to find the house account form the source system data and place it in the first spot. The remaining three spots will then be adjusted accordingly.

Row 36 in the above example shows the following:
House Account (HOUEB1) is in the code-3 spot
FEDEGRO-01 is in the Code-1 Spot
APBENEF-01 is in the Code-2 Spot.

When I transfer this data for the new system I will have to place them as follows:
Code-1 HOUEB1 (House account code)
Code-2 FEDGRO-01
Code-3 APBNEF-01

Hence, for a list of 55K+ records, I will have multiple codes in the second spots or third spot. However, first spot will always be the two House Account codes. (HOUEB1 or HOUPE1)

Hope this is helpful.