+ Reply to Thread
Results 1 to 8 of 8

Reference lookup formula

  1. #1
    Registered User
    Join Date
    03-17-2008
    Posts
    85

    Reference lookup formula

    Hello all - hoping you may be able to help me again here although I think this may be a bit of a tricky one..

    Basically each week I get a report on e-mail and what I have to do is add all new data to an existing report but avoid duplicates etc. What I need is a formula that looks through the existing report and picks out from the new report anything thats missing then starts a new row and inserts all the relevant fields (if any of that makes sense) The only field that is individual to each record is the ARN number.

    I have attached a mock example of what I mean to help to explain this.

    Tab "Main data" is the existing report and I would need to formula to start in A6 and copy accross and down

    Tab "New Data" is where I will paste the data I get from the weekly report.

    So any field that shows in the "new data tab" and is not already in the "Main data" tab needs to be automatically picked out and automatically added as a new row to "Main data".

    While the formula is dorment - I am hoping it will not show N/A but maybe a 0 or blank cell

    If this makes any sense at all I would appreciate any help

    Thanks Very Much !!

    HELP.zip

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    See attached...

    I based it on the ARN code being the unique identifier...

    adjust ranges to suit in A6 and confirm with CTRL+SHIFT+ENTER then copy down and across
    Attached Files Attached Files
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    03-17-2008
    Posts
    85
    Thanks very much for looking at this for me - its much appreciated!

    This is kind of what I was after although I need (if possible) the formula to look through the New Data tab when new data has been pasted into it and pick out only the new rows from the data (there may only be one new row in with a load of duplicates) and add them to the bottom of the main data list.

    If for instance 12 rows of duplicate get pasted into the new data tab then for nothing extra to show in the main data tab

    I know this is confusing and not sure if that makes any sense but if you could spare me some more time on this one it would be a huge help

    Thanks again

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Try this one... see attached.

    Formula in A6, confirmed with CTRL+SHIFT+ENTER, then copied down as far as you want and across as far as needed.

    Please Login or Register  to view this content.
    Note: This covers for upto 2000 records - 3. You can update the 2000's in the formula to cover more....but the more you cover the more likely the sheet will slow down.

    After adjusting fomrula you must reconfirm with CSE keys
    .

    Also...if you want to suppress all the zeroes... go to Tools|Options and check off Zero Values in the View Tab.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-17-2008
    Posts
    85
    Thanks Very Much again for you're time - I will have another look

  6. #6
    Registered User
    Join Date
    03-17-2008
    Posts
    85
    I'm struggling to get this one working - Even if I use the one you have done in the attached sheet.

    If for example I add one row to the "new data" tab then copy all the rows and paste them at the bottom

    I would expect to see only the new row appear at the bottom of the data in the "main data" tab

    Thanks for you're help on this one but I think this might have to be put to sleep

    AK

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Sorry that was my fault... I didn't copy down my new formulas over all my previous formulas...

    Just select all the cells in row 6 and copy them down to the bottom... it should work now.... but you will notice that it is very slow since those are array formulas and they are looking at large arrays...

    One way to speed it up is to apply the array formula only to column E (which is the unique identifier column...you may have to change for your original data). Then all the other column just use simple Index/Match formulas to get the other data.

    See attached....column E has the array formula...all other have non-array formulas.

    Hopefully this time, you are satisfied...

    remember to get rid of zero's....Tools|Options >> View tab... check off the Zero Values
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-17-2008
    Posts
    85
    Thanks Very Much !!!

    Works a treat !

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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