• 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.

a Unique list from 3 Database

N

nadjalil

Member
Dear All
i have a file where i have 3 databases Actual sales for 2014, 2015 and BU 2016, they are not similar but in all three customer name and brand names are same and based on these two elements of my database i have to make a unique list. i need a formula which can bring customer on a new sheet which are present in all the three databases and they build a data based by Month by brands

Customer name - Actual Jan 14 - Actual Jan 15 - BU Jan 16
till december for all three years which can show how much a customer bought in 2014 and 2015 by brands and what he is going to buy in 2016 by brands.

i hope i have cleared my requirement and for reference i have attached a file for your review.

look forward to your help on this.
Kind Regards
 

Attachments

  • UNIQUE LIST BASED ON 3 DATA BASE.xlsx
    19.6 KB · Views: 10
Hi:

In what format you are expecting data in , how large is going to be your data set. If your data sets are going to be large then you may have to consider macros instead of formulas.

Thanks
 
Dear Nebu
it's going to be 30000 rows for actual 2014 and 15 and around 500 row for Bu 2016...
thanks for your help.
 
Hi:

You did not answer all my questions, if you are looking for help clarify the details I asked for.

Thanks
 
Dear Nebu
thanks for looking into my request,
my format of the report will remain the same as i have on BU 16 Sheet Customer Code in "A", Customer Name "B" Brand Names in "D" and then Jan - Dec for Budget, Jan - Dec for Year 2014 and then Jan - Dec for Year 2015 but i need to display all those customer which has even if it has sale only in one period even, but if that customer doesn't has any sales for a particular brand in all three periods i don't want that to be appear and enlarge my report. as per the appearance of Brands a customer can appear multiple times in this report with Code number...
please let me know if you need any further clarification to look into my file.

thanks for your help.

Kind Regards
 
Hi:

This is what I have done.

I had to change your tab names a bit, also for the year 2015 and 2014 I brought down , your month headings to row 8 from row 7. I have coded to consolidate all the 3 tabs, I have then summarized your consolidated data into a pivot. The pivot will automatically refresh on the button click, you may have to change the source of the pivot as the DB grows to capture all the data in your data table. The pivot will give you year wise info, you can even use slicers to further slice and dice the data. Hope this helps.

Note: Clear the consolidated tab, but the headings and hit on the Summarize button on the Pivot summary tab to test the macro.

Thanks
 

Attachments

  • UNIQUE LIST BASED ON 3 DATA BASE.xlsm
    44.3 KB · Views: 6
Dear Nebu
thanks for the file i hope i can start working with Pivot as i didn't work before could you please give me an insight of who you did the table on consolidated sheet. i hope you may double click on something in pivot and it must have extracted it on new file, can you tell where you did that...

thanks anyways for you great help, it seems that i can resolve this mystery in my company.
could you please tell me where i can get video classes on pivot or if you have anything please share with me, now i need to work on pivot as well.

thanks Bro
 
Hi:

The consolidated tab is created by a macro . I have done a bit of VBA programming to get the data on yearly tabs into the consolidated tab. The source data for the pivot is consolidated tab. If you want to learn more about pivot tables , give a search in Google there are lots of resources available which detail about pivot tables.

Thanks
 
You must log in or register to reply here.
Back
Top