Completado

Equivalent MYSQL output but using Google Sheets (script) or Microsoft Excel (macro) to do the query command

Equivalent MYSQL output but using Google Sheets (script) or Microsoft Excel (macro) (must work with MacOS) to do the equivalent query command..

Existing MySQL structure:

Two tables (Activity & Allocations)

Activity - Fields:

credit_card

type

trans_date

post_date

description

amount

cost_center

Activity - Current Sample content:

"id","credit_card","type","trans_date","post_date","description","amount","cost_center"

67,"Chase","Sale","9/1/16","9/1/16","Transaction A",100.00,"N"

68,"Chase","Sale","9/2/16","9/2/16","Transaction B",200.00,"B"

85,"Chase","Sale","9/19/16","9/19/16","Transaction S",1900.00,"D"

86,"Chase","Sale","9/20/16","9/20/16","Transaction T",2000.00,"E"

87,"Chase","Sale","9/21/16","9/21/16","Transaction U",2100.00,"M"

88,"Chase","Sale","9/22/16","9/22/16","Transaction V",2200.00,"B"

89,"Chase","Sale","9/19/16","9/19/16","Transaction S",1900.00,"D"

90,"Chase","Sale","9/19/16","9/19/16","Transaction S",1900.00,"D"

91,"Chase","Sale","9/2/16","9/2/16","Transaction C",200.00,"B"

92,"Chase","Sale","9/2/16","9/2/16","Transaction D",200.00,"B"

Allocations - Fields:

cost_center

underlying_cost_company

allocation

Allocations - Current Sample content:

"id","cost_center","underlying_cost_company","allocation"

1,"A","Company A",1.0000

2,"B","Company B",1.0000

3,"C","Company C",1.0000

4,"D","Company D",1.0000

7,"E","Company E",1.0000

9,"F","Company F",1.0000

10,"G","Company G",1.0000

11,"H","Company H",1.0000

12,"I","Company I",1.0000

13,"J","Company J",1.0000

14,"K","Company K",1.0000

15,"L","Company L",1.0000

16,"M","Company M",0.5000

17,"M","Company N",0.5000

18,"N","Company O",0.2711

19,"N","Company Q",0.7289

Concept being that a cost center can have multiple underlying_cost_company so it is repeated until the sum is 1. (done manually)

Query being ran:

SELECT credit_card, `type`, trans_date, description, amount, Activity.cost_center, underlying_cost_company, allocation, round((amount * allocation),2) AS allocated_amount

FROM Activity

JOIN Allocations

ON Activity.cost_center=Allocations.cost_center

ORDER BY underlying_cost_company ASC

Query output:

"credit_card","type","trans_date","description","amount","cost_center","underlying_cost_company","allocation","allocated_amount"

"Chase","Sale","9/2/16","Transaction B",200.00,"B","Company B",1.0000,200.00

"Chase","Sale","9/22/16","Transaction V",2200.00,"B","Company B",1.0000,2200.00

"Chase","Sale","9/2/16","Transaction C",200.00,"B","Company B",1.0000,200.00

"Chase","Sale","9/2/16","Transaction D",200.00,"B","Company B",1.0000,200.00

"Chase","Sale","9/19/16","Transaction S",1900.00,"D","Company D",1.0000,1900.00

"Chase","Sale","9/19/16","Transaction S",1900.00,"D","Company D",1.0000,1900.00

"Chase","Sale","9/19/16","Transaction S",1900.00,"D","Company D",1.0000,1900.00

"Chase","Sale","9/20/16","Transaction T",2000.00,"E","Company E",1.0000,2000.00

"Chase","Sale","9/21/16","Transaction U",2100.00,"M","Company M",0.5000,1050.00

"Chase","Sale","9/21/16","Transaction U",2100.00,"M","Company N",0.5000,1050.00

"Chase","Sale","9/1/16","Transaction A",100.00,"N","Company O",0.2711,27.11

"Chase","Sale","9/1/16","Transaction A",100.00,"N","Company Q",0.7289,72.89

The above query, grabs all of the requeted data via a select command. Pairs it with its corresponding cost_center, then multiplies the amount by the allocation..

Overall objective to move the Excel or Google Sheets is to have each Table above be its own sheet and have the output of the query be its own sheet allowing the amount to have a $ included.. the allocation is just a decimal value.

It is in effect a pivot table of a pivot table but one that creates a new line per query the way it works in MySQL

Habilidades: Excel, MySQL

Ver más: excel 2003 macro query data, excel macro query xml web service, simple mysql query view script, excel macro query doesnt javascript table, script run mysql query write results csv file, query macro query excel, excel 2003 macro script, mysql query bash script, mysql query linux script, script create excel 2007 macro, best excel export script php mysql, mysql query shell script csv, macro place data multiple sheets entry excel, performing mysql query bash script, script excel spreadsheet macro read cells, calculation sheets different excel files macro

Información del empleador:
( 3 comentarios ) beaverton, Spain

Nº del proyecto: #11890250

Adjudicado a:

MimiFL

Hi, i m serious freelancer with 100% completion rate and i believe i can help you. I've done hundreds of excel projects (with both formulas and macros) and i've never had a single problem. Also i've successfully comple Más

$45 USD en 0 días
(169 comentarios)
5.8

13 freelancers están ofertando el promedio de $105 para este trabajo

dpune

Hi, I have more than 14 years of Excel macro/VBA exp and I am expert in this kind of work. I have completed more than 290 projects. Please look at the feedback left by my employers to know more about my wor Más

$100 USD en 3 días
(116 comentarios)
6.1
MoohHHooM

Hi, i can create a macro for you to get this output as your SQL query. even for the cost center i can create that fun Más

$60 USD en 1 día
(7 comentarios)
4.3
appsoulutionphp

Dear Client, Thanks for this opportunity. We have very strong experience in web development with PHP, especially in Wordpress,Joomla,Magento,OsCommerce,framework such as Codeigniter,Zend,Laravel,Cakephp and Yii ,websi Más

$61 USD en 3 días
(5 comentarios)
2.1
sandroshubladze

Hello, I am VBA programmer Excel expert Have got bachelor degree in finance I can do whatever you like very fast and for a cheaper price So, feel free to contact with me and ask any questions you have. I made av Más

$155 USD en 3 días
(2 comentarios)
1.6
$55 USD en 3 días
(1 comentario)
1.0
dipikaparekh19

A proposal has not yet been provided

$166 USD en 3 días
(0 comentarios)
0.0
nadeem121P

We are into software development from the last 3 years, we have completed more than 50 projects successfully...we may new to freelancer but we have vast experience outside

$105 USD en 2 días
(0 comentarios)
0.0
IgorBezborodov

DEAR EMPLOYER! We are an IT team with 3 years’ old experience in Website and Application Development. What we can do are Wordpress CMS, html5, PHP, CSS3, JavaScript, Joomla CMS. Our major base of clients is commercia Más

$166 USD en 3 días
(1 comentario)
0.0
nikhil231095

because this is what I will be working on

$100 USD en 10 días
(0 comentarios)
0.0
$30 USD en 2 días
(0 comentarios)
0.0
allWebDesignPro

A proposal has not yet been provided

$77 USD en 3 días
(0 comentarios)
0.0
kolyfedoriv

Greetings, I am ready to start doing it right [login to view URL] can check out my works in my portfolio. Contact me! with regards, Nick. You can see my portfolio here:[login to view URL] Más

$200 USD en 14 días
(0 comentarios)
0.0
Sunboss3000

I have a bachelor degree from the National University of Rwanda, and many trainings in Project Management, Business administration, ICT,…with 13 years of experience in Data Entry services.I am a Translator consultant a Más

$155 USD en 3 días
(0 comentarios)
0.0