• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

to create pivot table on a particular data base

V

vinod_siwan123

New Member
Hi Friends

i have some data in this particular Form and i wish to arrange data in a particular way using pivot table

data in current form
SCRIP NO VALUE UTILISED AMT BAL LOT NO UTILISED AMT LOT NO UTILISED AMT
1213141516 100,000 65532 34,468 100 25,532 101 40,000
2345678901 20000 41000 -21,000 150 20000 151 21000
desired data in below form

SCRIP NO LOT NO UTILISED AMT
1213141516 100 25,532
1213141516 101 40,000
2345678901 150 20000
2345678901 151 21000
 

Attachments

  • PIVOT TABLE.xlsx
    9.5 KB · Views: 13
The data has some caos elements. Why is 1213141516 listet twice? Where is the logic? Same with 2345678901.
This don`t add up? Could u explain it better, and share more data?
 
Hi Vinod,

Welcome to the forum.

Just for your information, and based on my knowledge, Pivot tables take individual columns to be a different set of fields. So Different Lot No & Their Amount will go like Lot, Lot 1 and so on and the same will go for Amount also. And you have planned to extend your data till column O for future data entry, which will not be considered now as Pivot tables just ignores blank cells surrounding your data.

So I would like to suggest you that you change the layout of your data to do an entry of different LOT no and their amount as row wise. Then it is easy to club data to desired Pivot table.

Second option can be going for a Formula solution to get the data in the desired format.

These are the suggestions from my side, you may also wait for the experts of this forum to repond to you on your query. May be they know how to come around this through Pivot table.

Regards,
 
@Kyrre

The logic is to repeat the Scrip No to the total LOT Nos listed for the same Scrip no. So, if there are 2 Lot nos list Scrip nos twice and so on.

Regards,
 
Hi Kyrre and Somendra

First thanks for you quick reply .

The problem is that i got this data from other person and he is storing data as per his convenient and it is real time data
And i am storing the data in the form as i mentioned in my problem desired data so i would make comparision on both data and find out which are entried done by the other person and not reflecting in my data.

till date i din't find any way to compare both data so i raised this problem in this forum.

And for lot no Column header of data posted by me you may imagine as task1 , task 2 and so on and amount as money spent in task 1 and task 2. and scrip no as project i have to find out what are task and amount is done but not updated at my end.

Thanks and Regrds.
vinod kumar
 
Hi Vinod,

Are the number of columns static or can there be additional LOT NO and UTILISED AMT?

cheers
kanti
 
Hi Kanti,

Thanks for analysing my problem , The number of columns are not statics. it may be n numbers but practically maximum 30 or 40.
 
Hi Vinod,

have a look at the attached, it makes provision for a row of data to column ZZ, but that can be changed.

it is important that the structure remains the same, so you will continue with LOT NO followed by Utilised Amount.

It is also important that LOT NO is exactly like "LOT NO"

By the way I doubled the number of rows and increased the LOTS to test the Macro, but that does not matter

cheers
kanti
 

Attachments

  • MakeTable.xlsm
    21.1 KB · Views: 7
Hi kanti

Thanks a lot for providing the way to compare data . As i am simple user of excel, to do work with excel function and tools like pivot table etc.

And very simple understanding with programming structure. So it is not possible to me, to write a VBprogramming code to create data table to execute pivot table for comparison.

Would you like to help me to understand what are the variable, object and classes and there properties in excle( if any reference document or book. so i would enhance my excel skill with help of vbprogramming.

and most of this i have used first time in my life any forum or community group to share my view / questions etc.

So this is a good and nice experience to learn from other and to enhance your knowledge.
 
Hi Vinod,

I am not sure where to start, but you can use the file I sent you as it is without any modification, provided your source data is on the sheet Data as it is in the file, you then run the macro and you should get the result as per the result sheet.

Once you have the results you can use the pivot table on the result. If you need help using the macro, please let me know.

Please look at the Lounge Forum, where people have discussed resources for learning VBA

best of luck, I hope other members will add to your request

regards

kanti
 
Hi Kanti,

Thanks for your support. I have no problem with your file.

i wrote my last mail to thanks to you and to find out any quick reference document or link so i would got a good command on vb programming, helping me to solve some other problems and understand properly your code for macro.

Once again to big thanks for providing soloution to me.

Regards
vinod
 
Thank you for your response.

Basically the macro does the following:

Establish the range of the data using CurrentRegion, the number of rows and columns in the range
Copy the Range to an Array
Loop through the rows of the array, and in each row loop through the columns of the array and copy the relevant data to the result sheet.
 
You must log in or register to reply here.
Back
Top