0

I have a lot of spreadsheets that pull transactional information from our ERP software into Excel using the Microsoft Query that we then perform other calculations on automatically. Recently we upgraded our ERP system, but management made the decision to leave the transactional history in the old databases to have a clean one going forward in the new system. I still need to have some "rolling 12 months" graphs, but if I use only the old database, I'm missing new data and if I use only the new, I'm missing the last 11 months data.

Is there a way that I can write a query in Excel to pull data from the old database PartTran table and merge it with the new database PartTran table without user intervention each time? For instance, I don't want my users (if possible) to have to have two queries that they copy and paste into one Excel table. The schema of the tables (at least the columns I need) are identically named and defined.

Error
  • 25
  • 1
  • 6
  • What have you tried so far? There's a couple ways off the top of my head this could be addressed ( with varying degrees of stability ); and it'll help inform answers to see your current approach. – Peter Vandivier Jan 09 '16 at 15:08
  • I think Microsoft PowerQuery is for just such a situation. Behind the scenes, it loads MS SQL Server Express locally and manages the loading and joining of data. I haven't worked with it (I have worked with PowerPivot, which does sort of the same thing), but it shows promise. Check out the link. https://support.office.com/en-us/article/Introduction-to-Microsoft-Power-Query-for-Excel-6E92E2F4-2079-4E1F-BAD5-89F6269CD605. Bear in mind, worse case your source can be Excel itself, meaning once you get the data into Excel, it's a "source" – Hambone Jan 09 '16 at 16:44
  • Microsoft PowerQuery will do this. – Chicago Excel User Jan 09 '16 at 19:21
  • @PeterVandivier I haven't tried anything yet. I was having trouble wrapping my head around the two sources; kind of a writer's block if you will. – Error Jan 10 '16 at 14:11
  • @Hambone Power Query sounds like it's the way to go. Will I have to install the add-in on all the users' Excel's or just mine since I'm the one creating the query? – Error Jan 10 '16 at 14:13
  • You only need to install it on the machine that actually pulls the data. From there, you can distribute the output like a "normal" spreadsheet. Good luck! – Hambone Jan 10 '16 at 18:46

1 Answers1

0

If you want to take a bit of a fun, hacky Excel approach, you could do the "copy-paste" bit FOR your users behind the scenes. Given two similar tables OLD and NEW with structures

+-----+------+-------+------------+
| id  | foo  |  bar  |    date    |
+-----+------+-------+------------+
|  95 | blah | $25   | 2015-06-01 |
|  96 | bork | $12   | 2015-07-01 |
|  97 | bump | $200  | 2015-08-01 |
|  98 | fizz |       | 2015-09-01 |
|  99 | buzz | $50   | 2015-10-01 |
| 100 | char | ($1)  | 2015-11-01 |
| 101 | mope |       | 2015-12-01 |
+-----+------+-------+------------+

and

+----+-----+-------+------------+------+---------+
| id | foo |  bar  |    date    | fizz |  buzz   |
+----+-----+-------+------------+------+---------+
|  1 | cat | ($10) | 2016-01-01 | 285B | 1110111 |
|  2 | dog | $25   | 2016-02-01 | 27F5 | 1110100 |
|  3 | ant | $100  | 2016-03-01 | 1F91 | 1001111 |
+----+-----+-------+------------+------+---------+

... you can union together the data for these two datasets with some prudent excel wizardry as below:

Your UNION table ( named using alt+j+t+a ) should have the following items:

  1. New natural ID
  2. DataSet pointer ( name of old or new table )
  3. Derived ID from original dataset
  4. Columns of data you want from Old & New DataSets

example:

+---------+------------+------------+----+------+-----+------------+------+------+
| UnionId | SourceName | SourceRank | id | foo  | bar |    date    | fizz | buzz |
+---------+------------+------------+----+------+-----+------------+------+------+
|       1 | OLD        |            |    |      |     |            |      |      |
|       2 | NEW        |            |    |      |     |            |      |      |
+---------+------------+------------+----+------+-----+------------+------+------+

You will then make judicious use of Indirect() and VlookUp() to derive the lookup id and column targets. Sample code below

SourceRank - helper column

=COUNTIFS([SourceName],[@SourceName],[UnionId],"<="&[@UnionId])

id - the id from the original DataSet

=SMALL(INDIRECT([@SourceName]&"[id]"),[@SourceRank])

Everything else is just VlookUp madness!! Although I've taken the liberty of copying the sample code below for reference

foo =VLOOKUP([@id],INDIRECT([@SourceName]),MATCH(UNION[[#Headers],[foo]],INDIRECT([@SourceName]&"[#Headers]"),0),0)

bar =VLOOKUP([@id],INDIRECT([@SourceName]),MATCH(UNION[[#Headers],[bar]],INDIRECT([@SourceName]&"[#Headers]"),0),0)

date =VLOOKUP([@id],INDIRECT([@SourceName]),MATCH(UNION[[#Headers],[date]],INDIRECT([@SourceName]&"[#Headers]"),0),0)

fizz =VLOOKUP([@id],INDIRECT([@SourceName]),MATCH(UNION[[#Headers],[fizz]],INDIRECT([@SourceName]&"[#Headers]"),0),0)

buzz =VLOOKUP([@id],INDIRECT([@SourceName]),MATCH(UNION[[#Headers],[fizz]],INDIRECT([@SourceName]&"[#Headers]"),0),0)

Output

You'll likely want to make prudent use of If() and/or IfError() to help your users ignore the new column references to the old table and those rows that do not yet have data. Without that, however, you'll end up with something like the below.

UnionTable

This is both ready to accept & read new inputs to both OLD and NEW DataSets and is sortable to get rid of those pesky placeholder rows...

UnionTable_Sorted

Hope this helps! Happy coding!

Peter Vandivier
  • 606
  • 1
  • 9
  • 31
  • This is, of course, all just a workaround for just getting your data into a [proper view in the database](http://dev.mysql.com/doc/refman/5.7/en/create-view.html) prior to importing to excel. But this way is fun too :-) – Peter Vandivier Jan 09 '16 at 16:16