Hello All,
Hope you all doing fine, I have this issue today where I have some sales values with me and I have to create a pivot based on it, Here is the sample data set.
Table1:
OECODe | Name | Company | Sales |
111 | Sid | 1500 | |
111 | Sid | Ebay | -1500 |
111 | Sid | 5000 | |
222 | Pradeep | Amazon | 2000 |
333 | Nitin | Zara | 3000 |
Result Table:
OECODe | Name | Company | Sales |
111 | Sid | 5000 | |
222 | Pradeep | Amazon | 2000 |
333 | Nitin | Zara | 3000 |
So, From the above I have table 1 data with me it contains some negative values which should Nullify while summing all the values.
Help me with this all.!!!!
Regards,
Sid
Solved! Go to Solution.
Here's my result with a simple summarize. The Values for Sid cancel out leaving a 0
Here's the configuration of the summarize tool
Dan
Hello danilang,
We need company name too with this. Also one row of name sid with sales 5000 is missing here.
Thanks for the help but we need some more accuracy.
reagards,
Sid
Where does the 5000 come from. It's not in your input data, unless you sum the values for 222 and 333.
Dan
Hello Danial,
Sorry for the incorrect data. Just Updated.
Regards,
Sid
I see the new data now. For the question of the company name in the output how do you decide which company to put in? In the following data set, what would the output record be for Pradeep and how did you calculate it?
OECODe | Name | Company | Sales |
111 | Sid | 1500 | |
111 | Sid | Ebay | -1500 |
111 | Sid | 5000 | |
222 | Pradeep | Amazon | 1000 |
222 | Pradeep | 1000 | |
222 | Pradeep | Ebay | -1000 |
333 | Nitin | Zara | 3000 |
Dan
Hello Dan
An OECODe will not have more than 2 sales value same under it. on the combination of OECODe/Name.
Regards,
Sid
So, if I understand correctly, your input data will always have either 1 row per client, like 222 and 333 OR 3 rows for each client with the 1st 2 rows reversing each other with the same amount?
OECODe | Name | Company | Sales |
111 | Sid | 1500 | |
111 | Sid | Ebay | -1500 |
111 | Sid | 5000 | |
222 | Pradeep | Amazon | 2000 |
333 | Nitin | Zara | 3000 |
Are there any other possibilities?
Dan
Yes Exactly!