Reference lookup formula

[AK262007]

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

[NBVC]

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

[AK262007]

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

[NBVC]

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.

[AK262007]

Thanks Very Much again for you're time - I will have another look

[AK262007]

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

[NBVC]

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

[AK262007]

Thanks Very Much !!!

Works a treat !