+ Reply to Thread
Results 1 to 11 of 11

Lookups between disparate data tables

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,400

    Lookups between disparate data tables

    Not sure if a formula approach can do this, or if I need to go with a VBA approach.

    On the attached, I have 2 tabs. "Source" comes from another entity, as shown. "Formulas" is the work of my predecessor, and is the format my audience is used to and expects to see. As you can see, my predecessor's formulas are all linked into the proper cells in "Source". His method works as long as the other entity doesn't move anything, but I know from experience that stuff moves, so would rather go ahead and develop a more robust and sure lookup method. Knowing I really can't do much in terms of changing the "Formulas" format, how could I go from "Source" to "Formulas" effectively without the dedicated links?
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by jomili; 12-15-2016 at 06:45 PM. Reason: To be more user-friendly

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,224

    Re: Lookups between disparate data tables

    Is it worth the effort?
    If something breaks fix it then.

    To make it "robust" you would need to re-write all the formulas to do all the calculations again based on the looking up the table of raw data sitting at the top of the Source. Quite a time-consuming job

    How about building in a few automatic checks to highlight any unwanted modifications since the last version
    eg - this tells you if year 2012 is no longer on row 183
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I suppose you could make the formula a bit more robust
    eg
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    is the same as
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where Source!A1:Y214 is the Source table , and cell AA1 (in Formulas) holds value 185 ( = row number for Year 2014)
    If an extra row was inserted in Source you would then only have to change one value for each year and all the formulas would be updated
    But of course you would have to alter all your formulas in the first place (fairly quick with VBA)

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,400

    Re: Lookups between disparate data tables

    On the "Is it worth the effort", the report goes to the State Legislature, so has to be accurate. If the rows move, or the columns move, values will still fill in, but they'll be the wrong values. So yes, it's worth the effort.

    I like the check for added/misplaced rows:
    Please Login or Register  to view this content.
    I suppose I could do something similar to check for column movements. That would at least alert me that there's a problem.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Lookups between disparate data tables

    Look at this formula:

    in B6 of "Formulas"

    =INDEX(Source!$A$183:$Y$214,MATCH(A6,Source!$A$183:$A$193,0),9)

    Copy down

    NOTE: the entries in column A would need to be changed to remove the blank so they match with "Source" e.g. fy2014

    For other columns you only need change the column reference (9 above = column I)

    See attached for sample in I16 of "Formulas"
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,400

    Re: Lookups between disparate data tables

    John,

    I don't really, in this situation, see the benefit of the Index/Match solution over a simple VLookup. The problem with both is that you have to know the row range where the data is sitting, and you have to know in which column each discreet piece of information resides.

    I'm beginning to think a VBA solution might work best for me. I think it'll work if I first make copy of "Source" and:
    1) Look from the top of the Source sheet down, and delete everything after the first blank line
    2) Determine my range size
    3) in Row 2, Unmerge all cells
    4) In Row 2, fill all blank cells with the value of the cell to the left, replacing "State Paid" with "Non- IV-E"
    5) In row 1, concatenate Row 2 and Row 3
    6) In column A, replace the date with the Fiscal Year

    Then I could do SumIFs to bring in all my data, and I wouldn't have to be dependent on my rows and columns. Does that sound like it'll work?

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Lookups between disparate data tables

    If you can arrange the data so that any data can be identified by row/column headings then YES this will work well.

    My own response illustrated that with the inconsistency of the "Year" titles. I looked at using column headings but it was reluctant to suggest wholesale changes to the of "source" layout.

    Many problems result from poorly designed "databases" in Excel: I note your proposal to unmerge cells - merged cells should be avoided as they cause many problems.

    INDEX/MATCH is (i believe) more efficient than VLOOKUP but for small data volumes any difference will be marginal.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lookups between disparate data tables

    Quote Originally Posted by JohnTopley View Post
    INDEX/MATCH is (i believe) more efficient than VLOOKUP
    They're pretty much the same but INDEX/MATCH is more versatile.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  8. #8
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,400

    Re: Lookups between disparate data tables

    John Topley,

    I echo your comment about "merged cells should be avoided". However, as I stated at the beginning, "Source" (where the merged cells are) comes from another entity. I have no control over whether they use merged cells or not. My heartache is getting the data from the format they send to the format I need.

  9. #9
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,224

    Re: Lookups between disparate data tables

    Is your problem that
    - the data is in the wrong shape?
    OR
    - fear of someone moving something in the source?

  10. #10
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,400

    Re: Lookups between disparate data tables

    Problem is:
    A) Data from Source group is in the wrong shape for an easy lookup and
    B) Source group may at any time change the layout of the data, making it better or worse. I have no control over what source group does, and they LIKE to use merged cells.

    I inherited the "Formulas" tab from a predecessor; I'm only going to be involved for a couple of months, whereupon I'll pass it to my predecessor's replacement. I'd rather pass on a more robust system.

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Lookups between disparate data tables

    Give example(s) of the changes that occur in "SOURCE".

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Poll on - Lots of simple formulas vs fewer but more complex formulas
    By gassiusmax in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-20-2014, 08:51 PM
  2. Complex lookup (complex for me...)
    By blacryan84 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-28-2014, 04:26 AM
  3. Replies: 4
    Last Post: 02-03-2014, 12:47 AM
  4. [SOLVED] Complex If Then formulas
    By Sneed924 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2005, 03:05 AM
  5. [SOLVED] Complex If Then formulas
    By Sneed924 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-05-2005, 11:05 PM
  6. [SOLVED] Complex If Then formulas
    By Sneed924 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-05-2005, 10:05 PM
  7. [SOLVED] Complex If Then formulas
    By Sneed924 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-02-2005, 12:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1