Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts

    [SOLVED] - Automatically adding variables to entries based on former entries.

    Hi Loungers,

    I have a problem, and I'm not quite sure, if it belongs here or in the Access. Nevertheless.

    Every month I get a table with numerous entries in Excel. All these entries have a measure ID.

    I manually add a column in this table, where I type in a new variable/note.


    This was very very exhausting for the first report.


    When the next report is coming in around a hundred new entries will have been added - and scattered randomly in different rows.


    I want to have some mechanism/method that adds the variables I added in the first report to the new report, based on the measure ID, so I easy can identify the new entries, and don't need to retype the varibles for the entries that I did last month.


    Visual example:
    EXAMPLE.png

    Hope ya' can help - thanks.
    Last edited by V.W.Birgisson; 2015-04-13 at 07:02.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi

    Assuming you are using Excel2007 or later, you can use a formula to 'retrieve' values from previous month.
    see attached example file.

    So, if your four columns are in [A] to [D], the formula for col [B] to retrieve data from previous month's sheet would be:
    Code:
    =IFERROR(INDEX(Month1!B:B,MATCH(A2,Month1!A:A,FALSE)),"")
    After you copy the formulas down in column [B], you can then convert to values using copy-paste>pastespecial etc etc

    zeddy
    Attached Files Attached Files

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi

    ..and then you could sort on column [B] to put all the blank entries together, for easier updating.

    zeddy

  4. The Following User Says Thank You to zeddy For This Useful Post:

    V.W.Birgisson (2015-04-13)

  5. #4
    New Lounger
    Join Date
    Mar 2015
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    Thank you very much - this was exactly what I was looking for.

    Cheers,
    V.W.Birgisson

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Alternatively to Zeddy's formula, you can use Vlookup. Assuming a header A1 to D1:

    In cell B2 enter IFERROR(VLOOKUP($A2,'Month 1'!$A$1:$D$7,2,FALSE),"") then copy down.

    If you wish to automatically pull the other values over then
    In cell C2 enter IFERROR(VLOOKUP($A2,'Month 1'!$A$1:$D$7,3,FALSE),"") then copy down.
    In cell D2 enter IFERROR(VLOOKUP($A2,'Month 1'!$A$1:$D$7,4,FALSE),"") then copy down.

    In your application, either Index/Match or Vlookup will work equally as well.

    HTH,
    Maud

    vw.png
    Attached Files Attached Files

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Zeddy,

    Both your formula and mine are experiencing the same issue:

    On sheet 1, Cell A8 = 12, which is a second instance in column A, is pulling the wrong value into B11 on sheet 2. Instead of BOL it should be JF. The same issue will occur if the formula is carried to the other columns as well

    Maud

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2015-04-13)

  9. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Maud

    Well spotted.
    I ignored the ToolX and ToolY columns in my posted sample file.

    If the ID Measure is NOT unique, then we should of course 'merge' the 3 columns into a 'helper' column, and then use that for both the match and lookup, and then remove the 'helper' columns.
    It is possible to use a complicated non-helper-column array solution, but I would choose the simple 'helper-column' method.

    zeddy

  10. #8
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    May I suggest a simpler method.
    Copy Month1 after month1>rename month2>fill in the new items>sort if desired.

    Also, if col b items are recurring then have a macro using FINDNEXT or index/match WHICH IS BETTER THAN vlookup, fill in the col b blanks from a table.
    Last edited by Supershoe; 2015-04-13 at 15:29.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  11. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Don

    ..but what if a particular ID Measure doesn't appear in the following month???

    zeddy

  12. #10
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    No mention in OP post, so I guess OP will have to tell us what is desired.....
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  13. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    You are correct. So far, all we were told was..
    I want to have some mechanism/method that adds the variables I added in the first report to the new report, based on the measure ID
    zeddy

Posting Permissions

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