Two Columns Data Calculation

TheBIguy

Hello All,

I have a table which I have to combine with my master 

Table 1

a	b	z
P	Q	2000
R	S	3000

Master Table

a	d	e	f	g	h
P	1	2	3	4	5
Q	1	2	3	4	5
R	1	2	3	4	5
S	1	2	3	4	5

Expected output

a	z	d	e	f	g	h
P	2000	1	2	3	4	5
Q	-2000	1	2	3	4	5
R	3000	1	2	3	4	5
S	-3000	1	2	3	4	5

How to summarize this one?

So when P buys Q then P updates value with 2000 but Q loses -2000

Thanks in advance

atcodedog05

Hi @TheBIguy 

Here is how you can do it. By transposing and joining

workflow:

1. Using tranpose tool to convert columns to rows.

2. Using formula z is positive for column name a and z is negative for column name b.

3. Using find and replace to do vlookup of z column to main data.

Edit: I have added a join tool approach too.

Hope this helps : )

abc.yxmd

abc - join tool.yxmd