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

garibaldimarsala

New member
Joined
Aug 19, 2014
Messages
5
Reaction score
0
Points
0
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 ie:(Aug-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-11May-11Jun-11Jul-11Aug-11Sep-11Oct-11Nov-11Dec-11Jan-12Feb-12Mar-12Apr-12May-12
CodeCountryFormulas 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.
MonthCountry CodeQty_Shipped
Apr-11France252515
May-11Switzerland312148
Jun-11Poland1852987
Jul-11Italy3315159
Aug-11Russia4597654
Sep-11Holland1548289
Oct-11England6594264
Nov-11Norwegian4362596
Dec-11Spain1825123
Jan-12France4358684
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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:D 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.
 

garibaldimarsala

New member
Joined
Aug 19, 2014
Messages
5
Reaction score
0
Points
0
The report must be in HORIZONTAL rows and the DB is from the VERTICAL. Any idea ?

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:D 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-11May-11Jun-11Jul-11Aug-11Sep-11Oct-11Nov-11Dec-11Jan-12Feb-12Mar-12Apr-12May-12
Country00000000000000
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

garibaldimarsala

New member
Joined
Aug 19, 2014
Messages
5
Reaction score
0
Points
0
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Good tactic. Get an answer that you don't like, so just pretend nothing happened and post anew.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 

Mike_Alex

New member
Joined
Jun 10, 2014
Messages
57
Reaction score
0
Points
0
Location
Middle Wisconsin
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...
 

garibaldimarsala

New member
Joined
Aug 19, 2014
Messages
5
Reaction score
0
Points
0
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.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
I don't entertain nonsense with my time or effort.... good bye and good riddance!

We are volunteers (working for FREE!) trying to help you. If you can't help yourself then what do you expect?!
 
Top