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 · Accepted solution]

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 : )

[TheBIguy]

Thanks Mate I have added Join myself but yeah very helpful of yours! 🙂

[atcodedog05]

Hence I provided both approaches😅. I pefer find and replace over join because it doesn't not cause one to many or many to many joins 😅

Happy to help : )

Cheers and have a nice day!