Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Mixing two databases - Would you be so kind to help me out ?

  1. #1

    Lightbulb Mixing two databases - Would you be so kind to help me out ?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello friends, I would like to request your assistance on the following matter; I do have a database with 5 years of information, the data has countries, Quantity Sold, the dates are in format mmyy ieAug-11), also contains products codes, and other attributes. What I need is to bring that information to another report. As you can see the months are in column, and I am going to write the report placing the months in one row, then having in the new report a column with the products code and the country where the products were sold an since the data contains hundred of transactions with one code at the same time throughout the whole database and years; I need to bring to each month in the new report the sum of all accumulated quantity sold in each country per code and per month of each year. This is not related to PIVOT TABLES or a simple VLOOKUP. This is a case to build a formula to index the months in my report to match it to the database, to find the country and code and then to sum all the records for that product code for each month and bring it to each cell on my new report. I WILL APPRECIATE ALL COMMENTS FOR GOOD. THANKS A LOT.
    Apr-11 May-11 Jun-11 Jul-11 Aug-11 Sep-11 Oct-11 Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12
    Code Country Formulas must be here to bring the numbers from the database.
    The database I put only one month per year. The reality is that this is a database with 500K rows the smallest one.
    Month Country Code Qty_Shipped
    Apr-11 France 2525 15
    May-11 Switzerland 3121 48
    Jun-11 Poland 1852 987
    Jul-11 Italy 3315 159
    Aug-11 Russia 4597 654
    Sep-11 Holland 1548 289
    Oct-11 England 6594 264
    Nov-11 Norwegian 4362 596
    Dec-11 Spain 1825 123
    Jan-12 France 4358 684

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    I think a SUMIFS formula should do it...

    e.g

    =SUMIFS('Table Sheet'!$D:$D,'Table Sheet'!$A:$A,C$1,'Table Sheet'!$B:$B,$B2,'Table Sheet'!$C:$C,$A2)

    Where Table Sheet is the name of the sheet containing the reference table. Assuming data as presented in your sample occupies columns A in that sheet.
    So here you are summing column D where column A matches date in C1 of summary sheet, column B matches B2 of summary sheet and column C matches A2 of summary sheet.

    You can copy formula down and across the table.


  3. #3

    The report must be in HORIZONTAL rows and the DB is from the VERTICAL. Any idea ?

    Quote Originally Posted by NBVC View Post
    I think a SUMIFS formula should do it...

    e.g

    =SUMIFS('Table Sheet'!$D:$D,'Table Sheet'!$A:$A,C$1,'Table Sheet'!$B:$B,$B2,'Table Sheet'!$C:$C,$A2)

    Where Table Sheet is the name of the sheet containing the reference table. Assuming data as presented in your sample occupies columns A in that sheet.
    So here you are summing column D where column A matches date in C1 of summary sheet, column B matches B2 of summary sheet and column C matches A2 of summary sheet.

    You can copy formula down and across the table.
    Apr-11 May-11 Jun-11 Jul-11 Aug-11 Sep-11 Oct-11 Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12
    Country 0 0 0 0 0 0 0 0 0 0 0 0 0 0

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Thanks, but what are trying to tell me? If you are trying to say you only got 0's returned, then please double check your references.

    If you can't get it, then instead of posting crude pictures which say nothing to me, post an actual sample workbook.


  5. #5
    Quote Originally Posted by NBVC View Post
    Thanks, but what are trying to tell me? If you are trying to say you only got 0's returned, then please double check your references.

    If you can't get it, then instead of posting crude pictures which say nothing to me, post an actual sample workbook.
    Hello friens , I have the below database with more than 500K rows of information. I need to build a report with months in a row not columns, and from the DB get all sales per country by month and by code
    ColumnA ColumnB ColumnC ColumnD
    Month Country Code Qty_Shipped
    Apr-11 France 2525 15
    May-11 Switzerland 3121
    Jun-11 Poland 1852 987
    Jul-11 Italy 3315 159
    Aug-11 Russia 4597 654
    Sep-11 Holland 1548 289
    Oct-11 England 6594 264
    Nov-11 Norwegian 43626
    Dec-11 Spain 1825 123
    This is the model I need. Can you help me to build a formula?
    Country Code Apr-11 May-11 Jun-11 Jul-11 Aug-11 Sep-11 Oct-11 Nov-11
    The database I put only one month per year. The reality is that this is a database with 500K rows the smallest one.
    Can you help me with ideas and
    Thanks

  6. #6
    Good tactic. Get an answer that you don't like, so just pretend nothing happened and post anew.

  7. #7
    Good to hear from you Bob Philips. Do you have any idea, to help me out ?

  8. #8
    Before I would even began to do such a thing, I would like to see you acknowledge NBVC and pay him the respect of a serious response.

  9. #9
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    It is comforting to know that in the world of math, rules are set in place - one and one will always equal two, where there is no room for error. It would be nice if we could adhere to simple logic such as this to the human race...

  10. #10

    Mr. Bob Phillips,(Super Moderator) Please show some respect and humbleness, Can you?

    Hello Bob ("Super Moderator").
    I never answer to any kind of bad response or attitude. This is the first time for me being related to a situation like this, it might be because I am new to this forum. The response I have received from your colleague as a solution is not viable; on top of that he has sent a very RUDE message like this:



    “If you can't get it, then instead of posting crude pictures which say nothing to me, post an actual sample workbook”. As you can realize is a disrespectful comment towards my person and the forum.
    However you are claiming to "Pay him respect". Based on what? On the job he could not respond properly, but instead with an aggressive and disrespectful wording? Or maybe you can provide me with some light or guidance to reward him, in order to fulfil your or his recognition expectations.

    And let me tell you, that while some people were playing with toys; I was building my way in life with nothing but my dreams and courage. I am a self-made professional and I have learned with the years everything I am as a professional, by competing with so called gurus. No Grants, No mom no pop and nowhere to run while misfortune was chasing me. At the end I have prevailed mister, because there is someone up above with more power than those little so called kings, and He is always protecting me because He is still in the throne and his name is Yahweh.


    I only know that a professional must deliver results not excuses of any kind. Failure never was an option in my life, and the reason, (despite receiving an uneducated answers), for me looking for other people’s knowledge on this site; is because I needed to deliver a result not excuses. I did it by using my own skills.

    If somebody wants to receive a Bravo Zulu, for doing “Regular Stuff” or by showing lack of respect; then I am not the person to be asked to “Pay respect”. I only believe in talent not in titles and I pay respect to some old values and to people that have earned what they show; not to some arrogant, proud and selfish people that need recognition without deserving it or need to hide behind the internet to hurt other people.



    Now Mr. Bob Phillips, what can you tell to a person that has seen the greatness of human kind and also its misery. You have to do better than offering me a poor “Before I would even began to do such a thing, I would like to see you acknowledge NBVC and pay him the respect of a serious response”.

    You own an apology to everybody Mr. Bob Philips, regarding your colleague, as you can see he did not claim respect because deep inside; he knows he did deliver only disrespect.



    Show respect and guide other people, that are in need of help, then look yourself in a mirror and you will see peace and reward.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •