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
Solved! Go to 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 : )
Thanks Mate I have added Join myself but yeah very helpful of yours! 🙂
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!