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

Need help in data consolidation

@Gopigk

@Gopigk

Member
Hi all
Herewith I attached sample excel file contains 3 sheets. 1) summary, 2) received 3) to be receive. 2nd & 3rd sheets data from Pivot table based on my actual data. (here I removed pivot form). Now I need to consolidate those 2nd & 3rd sheets in to Summary sheet to present. when trying to consolidate manually it's getting confuse some times because, every months pivots won't be the same, like, some month may not have all weeks, and some months may not have both types, some months my not have both customer and vendor. if I consolidate manually in one month, next month any column or row missed, all references are changing and giving wrong summary. Hence I need this summary should be in Dynamic way with excel formulas.

Please help to automate this summary sheet based on 2nd and 3rd sheets

Thanks in advance,
Gopichand
 

Attachments

  • Sample file.xlsx
    17.5 KB · Views: 6
@Gopigk
Is Your sample files Summary-sheet result of those two sheets?
eg cell D5 (980) ... how do You get that value?
... of course, there is formula, but ...
 
Sorry sir, while copying from my main file, links were disturbed. Now here I attached revised files for your ready reference. yes sir, summary is result of data from those both sheets, but need company wise, which I forgot to mention in earlier. now in this excel i separated it.
 

Attachments

  • Sample file- Revised.xlsx
    17.2 KB · Views: 4
@Gopigk
Your original files links were ... okay - but hard to use.
You've missed some formulas ... okay.
You seems to remember something ...
Here You could find the first steps - how to get Your results.
Ps. Your summary-sheet has few typos and
Your Pivot-tables have to have needed layout.
 

Attachments

  • Sample file- Revised.xlsx
    18.2 KB · Views: 6
vletm said:
@Gopigk
Your original files links were ... okay - but hard to use.
You've missed some formulas ... okay.
You seems to remember something ...
Here You could find the first steps - how to get Your results.
Ps. Your summary-sheet has few typos and
Your Pivot-tables have to have needed layout.
Click to expand...
Excellent sir, thank you very much for your kind help!. now my requirement has been fulfilled. I tried on my original data after rectify all typo errors, trim errors and adjust all text strings exactly in all 3 sheets. It' works fine. And I have one doubt is, should I add another same SUMIFS formula to consolidate "To be receive" sheet data also in summary file? Pls confirm. {Ex: SUMIFS(Received sheet)+SUMIFS(To be receive sheet)}, and also can we make weeks also dynamic ? because if any of the week missed in a month, with this formula can we get correct result?
 
Last edited:
@Gopigk
now my requirement has been fulfilled.
... and after that You asked something more ... hmm?
I wondered one second about Your trimming and so?
... did You test - do it work with Your Pivot-tables?
And I have one doubt is, ... which I cannot figure ... Your Ex. looks wild.
... make weeks also dynamic ... write to summary sheet's cell D2 and M2 =Received!E1 and copy right three cells.
 
You must log in or register to reply here.
Back
Top