Cross check new data with existing data to identify new data... HELP!!!

[LeahStreet]

Hi All,

firstly let me say my excel knowledge is quite basic but i want to learn... but im self teaching so need help!!

I'm working with some pretty huge data files (they take ages to open!!)

and what I need to set up is a spreadsheet that I can input new daily data - approx 1000 lines per day (this will be a mix of existing repeated membership numbers and new ones) and those lines check against the existing previous data (293000 lines and growing, gathered over the last 2 years) and then highlight any new membership numbers that have been added....

then with that I need to report weekly on how many new numbers have been added split by person who added them.... maybe in a growth chart style... but doesn't have to be, could just be a table or something.....

I haven't actually created this yet and so can't attach anything but I hope that makes sense!!!!!

This is my first post so please let me know if this isn't posed correctly or is in the wrong section.... I feel like I haven't explained it very well but don't know what else to put!!

Thanking you all for all and any help!

Leah

[PFDave]

Hi Leah,

Welcome to the forum.

This is all do able, however it's going to depend on how you handle the data as to how you can get the desired output.

Removing the duplicate membership numbers isn't difficult to only leave you with unique values. This can be achieved in a number of ways.

Are you able to upload a sample workbook with your before and after views and I'll look to sort this out for you. Please ensure you remove any sensitive data and use dummy details where appropriate.

Regards

Dave

[FDibbins]

Hi, welcome to the forum

Without seeing what you are working with, I can think of a few ways to ID new data, but they depend on you adding a "helper" column and using a formula in those cells. Below are a few options you could use. Assume your data starts in A2, down
=countif(A:A,A2)
This will give a count of how many times the data in A1 is repeated
OR
=MATCH(A1,A:A,0)
This will either return all matches, or an error for non-matching items

With both of those, you could modify them to start a count of new items...
=if(countif(A:A,A2)=1,B1+1,B1)
OR
=if(iserror(MATCH(A1,A:A,0),B1+1,B1)

Once we have that, it is a (relatively) simple matter to extract the new entries

[LeahStreet]

Hi Both,

Thanks for helping me out!

Ok.. both your solutions just about make sense in my head... I've attached an example book... both of what you've said just about makes sense... I think that probably the helper column would probably work better as (and I didn't mention this originally) but I might have to use the data for other reports and so removing duplicates I think would then mean I couldn't use the data for other things?

It's the 'IDENTITY' column that I'll have to check for the new membership numbers that have been added.... I've highlighted in green.... (not sure how that's helping!) The two yellow columns already have formulas in (=VLOOKUP(MONTH(I265241),$T$1:$U$12,2,FALSE)&" 2016") and =VLOOKUP(L265242,LIST!$W$2:$X$296,2,0) ... I didn't do the 1st one but did do the 2nd one....which pulls some data from another sheet in the book....

Thanks again and hope that makes it make more sense...

I'll try out your suggestions later!!

Thanking you

Leah

oh!! after all that it won't let me attach anything.... here's a picture instead... !Excel capture.JPG

[FDibbins]

What message did you get when you tried to upload the file?
How big is the file? (we only need a small sample, not the whole file)

[LeahStreet]

It just didn't come up with anything... the drop down didn't drop down properly, I tried in IE and Firefox

Capture.JPG

[JohnTopley]

To Attach a File:

1. Click "Go Advanced"
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

The Paper clip icon doesn't work!

[FDibbins]

Yes, apologies, we are having problems with the paperclip icon, use the suggestion from John

[LeahStreet]

ah... okay, Thanks - I think that's attached now

[LeahStreet]

So the Countif seems to be working (although the match was throuwing out weird numbers!!)
... the only problem I can see with it at the moment would be if the new number is then used again on that same day it would show as '2' in the helper column and so I woulnd't spot it as a 1 identifying it as a new number...
I thought maybe this was the 2nd bit you put F Dibbins but that bit went very much over my head... what do the if(countif(A:A,A2)=1,B1+1,B1) bits mean? and would I use B.. i guess not in my actual sheet...

...then if I wanted to make a chart/league table of who (RM) has added the most new numbers by week??

Thanks

[Tony Valko]

Yes, apologies, we are having problems with the paperclip icon, use the suggestion from John

Then why not remove the paperclip icon to avoid the confusion?

[Jacc]

It's like people have gotten used to it being difficult to explain how to post workbooks, it's just the way it is. I hate that.
The feature is implemented like it's something secondary that we really don't use much.
The userinterface should be reworked, posting workbooks is a primary feature! That is what we do all the time!


Not sure who I'm yelling at here but it seemed like a good opportunity to post a little rant about this.

[Tony Valko]

It's like people have gotten used to it being difficult to explain how to post workbooks, it's just the way it is. I hate that.

We see this many times EVERY DAY.

You would think that the forum owner:

https://www.excelforum.com/showthread.php?p=4497793

Would take some pride in having a professionally supported website.

Unfortunately, that's not the case.

This is the most poorly administered website I've ever seen and I've been on the internet since the beginning!

Yelling at people for using "poor thread titles" is more important! GGTPS

Yeah, I know. Soon, very soon.