1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

match reference table

Discussion in 'Business Applications' started by etaf, Apr 15, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. etaf

    etaf Moderator Thread Starter

    Joined:
    Oct 2, 2003
    Messages:
    65,413
    First Name:
    Wayne
    i hope you can help with this problem

    I have a excel report which shows for simplicity the following fields

    Name
    JobNumber
    DateSent
    DateCompleted
    Value

    I then have another reference table
    Name
    Effective Date

    I want to match the files where
    The Name matches and the datecompleted is =to or > the dateEffective
    and extract those rows

    if there is no effective date entered then I do not want to include that name

    I have attached an example

    the real reference will have 500 names
    and the real data will have 3000 records+ each month
     

    Attached Files:

  2. dannyn

    dannyn

    Joined:
    Nov 8, 2007
    Messages:
    1,689
    i think Microsoft project can do that?
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    A VB macro can easily do this, I assume that you want to include all rows that meet your requirements? Bomb, Rollin or Firefytr could do it easily. I will have a go at it if you want, but not quickly as I am Access Programming at the moment.
    Will you be controlling the Output, headings, paging etc? ie, have the macro just dump everything to one worksheet.
    This is also a classic for an Access database, it could probably be done with one simple Query. :D
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Hi etaf.

    OBP's point is the key, namely "Will you ... have the macro just dump everything to one worksheet?"

    Also relevant is "if there is no effective date entered then I do not want to include that name"; so you're actually saying you want it to run for any Name listed on Ref. (?)

    See the added Extract sheet, Name1s currently listed. Switch to Ref, select a different name (the coloured cells), run the test macro.

    Then post back with more info. :)
     

    Attached Files:

  5. etaf

    etaf Moderator Thread Starter

    Joined:
    Oct 2, 2003
    Messages:
    65,413
    First Name:
    Wayne
    Bomb #21thanks for that,
    yes happy to move to a new sheet as a dump with the titles
    If there is no effective date in the reference then ignore that name
    otherwise all names should be extracted and where datesent is greater/equal to the effective date in reference

    On reflection it mahy also be required to update the original data sheet, with 2 extra columns 1) Matched 2) Effective Date

    OBPThanks
    I was thinking about doing it in access, but this is not availble to the admin who will run the process, although I may be able to get a copy onto her machine as the company does have MSpro but only deploys excel.
    I'm investigating if MS Access exists on her PC and if IT will supply, then I can provide a Database linked to the control spreadsheet (reference) and linked to the Data (Monthly report) and output a spreadsheet, I may look at developing the access over the weekend and will post back if i have problems - although from the org. cultutre excel will be preferred
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Bomb's good and quick, isn't he (y):)
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "otherwise all names should be extracted and where datesent is greater/equal to the effective date in reference"; you meant datecompleted right?

    Either way, here's an update with a different angle. Adds one sheet per Name, which I kind of prefer (although it might throw a wobbly @ 500 Names).

    You need to chew over your output format, IMO. OTOH, if IT holds out over Access, OBP could no doubt knock up a run-time thingy. :D

    HTH

    (with Data selected, run test2)
     

    Attached Files:

  8. etaf

    etaf Moderator Thread Starter

    Joined:
    Oct 2, 2003
    Messages:
    65,413
    First Name:
    Wayne
    No date sent

    I have potentially 1000 names so I would not want one / sheet

    I'm still playing with the live data and think I need to update the actual data sheet by adding two extra colums
    1) with Match
    2) with the effective date

    thanks for the work
     
  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Work is what you get paid for. :D

    If not one/sheet, upload a sample "desired end state". :)
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/704101

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice