Hi,
I have a data table that I get it from the server Sales GL entries)
I need to create the Profit and Loss table from this data where Income should be filtering by Profit center and Expense should be filtering by Vehicle nr.
Income and Expense do not have the same profit center.
how can i create the pivot table so?
Kind regards ,
Zipvenus
@zipvenus
Welcome to the excel forum.
Create in a new column an unique link for Income and Expences.
Probably it can be done with a formula.
But without seeing the data it is hard to tell.
HOW TO ATTACH YOUR SAMPLE WORKBOOK:
Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Hi
I have attached excel file for the example.
donīt know which formula to use and how to manage this on the simple one pivot og should this be done by data modelling
if you could guide me step by step or show me by return how you do it would be greatly appreciated as I will be using this kind of method monthly.
see attached excel file.
I made a VLookup table in the sheet Oeldere data
Sheet Data
Then I added column O & P
02 =LEFT($A2;2)*1
P2 =VLOOKUP($O2,'Oeldere data'!$A$2:$B$6,2,1)
After that I made a pivot table on sheet PT Oeldere.
See the attached file.
If you have any questions, feel free to ask.
but direct costs need to filter by those vehicle no. that listed on the filter sheet
how can we do that ?as not all the vehicle no. should be included.
Then select the vehiclenummer in the pivot table.
I see there is no vehiclenummer in the data for the income-group!!
See the attached file.
Hi,
so sorry I know you may think it is so simple why I canīt see it .....
My problem is , if you look at my filter sheet you would see how thing should be filtering (income and expenses are not registered on the same profit center, from this big data , I need only 3 account no. 2239, 2240, 2241 as mentioned for Income they are registered under profit center MAK-300 ( and there are no vehicle nr. for the income)
Salary only amount that registered on Profit center RAK-300 - no vehicle nr.
Direct costs are filtered by Vehicle no.and only those mentioned on the list (most of Vehicle no. are registed on profit center RAK-300)
I have tried to filter by profit center RAK-300 so I get all costs both direct cost and salary and I know that I can filtered by vehicle no. but then I can not get the correct amount for the income as itīs registered on MAK-300
I have even tried to make seperate privot table to get the correct fitler and amount but that is not the point , the point is it should be in the same table.
i need the formula that look up in account no. / vehicle no and profit center at the same time
The point is you have to make sure, the data is in the table. After that you can use a pivot table to analyse the data.
If the data is not in the table, you can use formula's to get the expected results.
But that will not be that flexible.
Instead use all same sort of information in the same collumn.
So make sure you get the vehiclenr also in Column J (for the income accountnummers).
You know which information is in your file and where you can find it.
I have to look in A1:N2214 and try to understand what you want to achieve.
Please let me know if it is possible to add the data in the corresponding collumn?
There is no vehicle for income so there will no data to add.
moreover , Income & expense are not using the same profit centers, that is why itīs so difficult for me to create such a table and i donīt know any formula that can make it work. that is why i was hoping that some expert in here can help
I can certainly help you with that problem.
Let start again from the begin.
What needs to be connected?
Where do I find those data in your file?
Make a small excel file, with this criteria, which makes it a lot easier to understand for the forummember.
Yes, Let us start over....
you can find everything on my excel sheet.
what we need to see on the table.
Income - We use only 3 mentioned acccount no # 2239, 2240, 2241 need to be on the table.. there is no vehicle nr. for Income (no additional data need to be add) Income booked only profit center MAK300
Direct costs : We only want to amount that connected with these vehicles nr. I have mentioned on my filter sheet.
Salary : donīt use vehicle nr , only amount book on Profit center RAK300
thatīs it.
With a table of accounts that meet the criteria.
After that VLookup.
After that a pivot table.
Please reply on this improved one.
See the attached file.
Last edited by oeldere; 07-06-2020 at 03:11 PM.
Hi
This is almost what I want , make criteria is a great idea indeed.
Next question how can i make this privot to show direct cost only for those 15 vehicle no. while income and salary stay unchange
Make an (small) excel file and add the expected values manualy in the file. After that I will take a look at it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks