Transition data to assigned columns

[smalik]

I like to transition data to assigned columns.

My apology in advance. I am using a company computer that has disabled installing any applications/add-ins, so I cannot provide an XL2BB file. Hopefully, I can explain the dilemma clearly using a snap shot.

• Source system has four fields (highlighted in Blue)
• Destination System has ten fields (highlighted in Yellow)
• A mapping table is created at the bottom (highlighted in Green)

I like to transfer the data from blue section to the yellow section based on the mapping in Green section.

It would have been an easier task if it was a one time deal. However, Several source systems have different assignments. For example, one source system can have "CSR" mapped to "Role-5" while another system may have that mapped to "Role-10"

Also, sometimes one role (source system) can mapped to two roles (destination system). CSR as an example here (Role-5 & Role-7)

What is the best way to transition this data. I just thought of the mapping table, However, if there is a better way, I am open to that option as well.

Please feel free to ask questions if I did not explain the dilemma clearly.

Any help is greatly appreciated.

 

[Peter_SSs]

What is the desired outcome for that sample data?
Please manually fill that in then copy range B2:R25 and paste directly into the forum. It will not be as good as XL2BB but at least helpers will be able to copy it for testing, unlike the image.

 

[smalik]

Here is a snap shot of the desired results.

 

[Peter_SSs]

Here is a snap shot of the desired results.

As indicated before, we cannot copy from an image to test.

 

[smalik]

Hope this is helpful. It does not get the row and column titles.

Source System

Destination System

Executive

CSR

Acct Manager

Producer

Role-1

Role-2

Role-3

Role-4

Role-5

Role-6

Role-7

Role-8

Role-9

Role-10

Policy-1

Murray

Smith

Bobby

Chris

Policy-1

Bobby

Murray

Smith

Smith

Chris

Policy-2

Smith

John

Smith

Antonio

Policy-2

Smith

Smith

John

John

Antonio

Policy-3

Smith

John

Bobby

Denise

Policy-3

Bobby

Smith

John

John

Denise

Policy-4

Henry

Tony

Smith

Denise

Policy-4

Smith

Henry

Tony

Tony

Denise

Policy-5

Donald

Samantha

Smith

Chris

Policy-5

Smith

Donald

Samantha

Samantha

Chris

Policy-6

Henry

Samantha

Smith

Denise

Policy-6

Smith

Henry

Samantha

Samantha

Denise

Policy-7

Donald

Carmen

Smith

John

Policy-7

Smith

Donald

Carmen

Carmen

John

Policy-8

Donald

Carmen

Smith

Antonio

Policy-8

Smith

Donald

Carmen

Carmen

Antonio

Policy-9

Donald

Stephanie

Bobby

Chris

Policy-9

Bobby

Donald

Stephanie

Stephanie

Chris

Policy-10

Henry

Smith

John

Chris

Policy-10

John

Henry

Smith

Smith

Chris

Define/Map Roles

Role-1

Acct Manager

Role-2

Role-3

Executive

Role-4

Role-5

CSR

Role-6

Role-7

CSR

Role-8

Role-9

Producer

Role-10

 

[Peter_SSs]

Hope this is helpful.

It certainly is since we can copy it. Thanks.

It does not get the row and column titles.

Sure, but we can easily see where to paste from your image or, if there was no image you just need to say, for example, "Source System is in cell C1"

See if this would suit you.

23 11 08.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Source System						Destination System
2		Executive	CSR	Acct Manager	Producer			Role-1	Role-2	Role-3	Role-4	Role-5	Role-6	Role-7	Role-8	Role-9	Role-10
3	Policy-1	Murray	Smith	Bobby	Chris		Policy-1	Bobby		Murray		Smith		Smith		Chris
4	Policy-2	Smith	John	Smith	Antonio		Policy-2	Smith		Smith		John		John		Antonio
5	Policy-3	Smith	John	Bobby	Denise		Policy-3	Bobby		Smith		John		John		Denise
6	Policy-4	Henry	Tony	Smith	Denise		Policy-4	Smith		Henry		Tony		Tony		Denise
7	Policy-5	Donald	Samantha	Smith	Chris		Policy-5	Smith		Donald		Samantha		Samantha		Chris
8	Policy-6	Henry	Samantha	Smith	Denise		Policy-6	Smith		Henry		Samantha		Samantha		Denise
9	Policy-7	Donald	Carmen	Smith	John		Policy-7	Smith		Donald		Carmen		Carmen		John
10	Policy-8	Donald	Carmen	Smith	Antonio		Policy-8	Smith		Donald		Carmen		Carmen		Antonio
11	Policy-9	Donald	Stephanie	Bobby	Chris		Policy-9	Bobby		Donald		Stephanie		Stephanie		Chris
12	Policy-10	Henry	Smith	John	Chris		Policy-10	John		Henry		Smith		Smith		Chris
13
14
15		Define/Map Roles
16		Role-1	Acct Manager
17		Role-2
18		Role-3	Executive
19		Role-4
20		Role-5	CSR
21		Role-6
22		Role-7	CSR
23		Role-8
24		Role-9	Producer
25		Role-10
smalik

Cell Formulas
Range	Formula
I3:R12	I3	=FILTER($C3:$F3,$C$2:$F$2=XLOOKUP(I$2,$C$16:$C$25,$D$16:$D$25),"")

 

[smalik]

It certainly is since we can copy it. Thanks.


Sure, but we can easily see where to paste from your image or, if there was no image you just need to say, for example, "Source System is in cell C1"

See if this would suit you.

23 11 08.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Source System						Destination System
2		Executive	CSR	Acct Manager	Producer			Role-1	Role-2	Role-3	Role-4	Role-5	Role-6	Role-7	Role-8	Role-9	Role-10
3	Policy-1	Murray	Smith	Bobby	Chris		Policy-1	Bobby		Murray		Smith		Smith		Chris
4	Policy-2	Smith	John	Smith	Antonio		Policy-2	Smith		Smith		John		John		Antonio
5	Policy-3	Smith	John	Bobby	Denise		Policy-3	Bobby		Smith		John		John		Denise
6	Policy-4	Henry	Tony	Smith	Denise		Policy-4	Smith		Henry		Tony		Tony		Denise
7	Policy-5	Donald	Samantha	Smith	Chris		Policy-5	Smith		Donald		Samantha		Samantha		Chris
8	Policy-6	Henry	Samantha	Smith	Denise		Policy-6	Smith		Henry		Samantha		Samantha		Denise
9	Policy-7	Donald	Carmen	Smith	John		Policy-7	Smith		Donald		Carmen		Carmen		John
10	Policy-8	Donald	Carmen	Smith	Antonio		Policy-8	Smith		Donald		Carmen		Carmen		Antonio
11	Policy-9	Donald	Stephanie	Bobby	Chris		Policy-9	Bobby		Donald		Stephanie		Stephanie		Chris
12	Policy-10	Henry	Smith	John	Chris		Policy-10	John		Henry		Smith		Smith		Chris
13
14
15		Define/Map Roles
16		Role-1	Acct Manager
17		Role-2
18		Role-3	Executive
19		Role-4
20		Role-5	CSR
21		Role-6
22		Role-7	CSR
23		Role-8
24		Role-9	Producer
25		Role-10
smalik

Cell Formulas
Range	Formula
I3:R12	I3	=FILTER($C3:$F3,$C$2:$F$2=XLOOKUP(I$2,$C$16:$C$25,$D$16:$D$25),"")

This worked..... Thank you.

 

[Peter_SSs]

You're welcome. Glad it helped. Thanks for the follow-up.