Picking Different Items Between Two Lists and Creating a New List

[CWatsonJr]

I have been reading posts about comparing lists for a couple of hours now but I have not found a post that will deal with my issue (or if I did read it, it was not obvious to me).

Each Monday I run a query in our master Access database to get a subset of items specifically for my needs. Each week this list of items will grow (by the end of the year it could be anywhere between 4K - 10K items). During the week, I will run the same query but it will contain items that have been entered late. I need to be able to pick out these late items and create a new list of them.

In the attached sample workbook (I have removed all of the sensitive cells and have just left the columns I need to work with). I have the first tab which will give me a summary (just a count) of the late items and this is also where I would like to have a list generated of the different items that are on tab 2 but not on tab 3. On the second tab, I have the query results from today (Wednesday). On the third tab, I have the query results from Monday. On the fourth tab, I paste a report which has several numbers I use but I have only left one of them in this sample workbook which is relevant to this task.

Thank you in advance for any assistance on this.

[alansidman]

In column C of the Current Tab, type =IFERROR(VLOOKUP(B2,Original!$B$2:$B$1342,1,0),"Late") and copy down.
Now, filter the records in the Current Tab, Column C with the term "Late"
Copy the filtered records shown and paste to the appropriate location

[CWatsonJr]

That won't work because I have data out to QZ (this is just a sample workbook, ideally, the formula would go on the first tab). I dump the data from the Access queries right onto the Current and Original tabs replacing the old data (which would replace all instances of this formula on those tabs - even if I put the formula on RA).

I guess I could use one of my other spreadsheets as a template and use a helper tab where I list all the record numbers from the Original tab and then use this formula to filter out the late reports but I'm hoping there is a cleaner way and I really don't want to copy/paste 10,000 instances of a formula by the end of the year.

I usually have between 10-40 late reports a week, so, it is like looking for a needle in proverbial haystack.

Any other ideas?

Thank you!


Sent from my iPad using Tapatalk

[alansidman]

I'm not sure what you are referring to by QZ and RA. In any event, here is an alternative solution that may work for you. In Access, take your mid-week query that you would normally export and create a Make-Table Query. Create a copy of the Monday table. Then using the tutorial attached as your basis, delete the values found in the copied Monday table. Then export or report as necessary.

http://www.datapigtechnologies.com/f...teproblem.html

[CWatsonJr]

Thank you for thinking outside the box. Without going into mundane details, I need to stick with the parameters that have been given to me. Tomorrow I am going to create a helper tab and see if that works the way I need it to. Ideally, I should be able to just drop the data into the workbook and have the formula(s) do all the work without any extra steps for me.

Thanks again

[FDibbins]

Just so I undrerstand what you are working with here...

In your sample WB, you have 1 column of data in both Curr and Orig. Are those unique ID numbers that will have additional data attached to them?

You say your data goes out 468 columns - what is in all those columns?

perhaps try this approach. In Late Report

A8=Current!B2
copied down, to bring Current info in.

Them B8=IF(ISERROR(MATCH(A8,Original!$B$2:$B$1342,0)),"Not Found","")
copied down

Next, Apply filters, and filter on Not Found (unbcheck blanks)
You will be left with all the mis-matches

on Late Report,

[CWatsonJr]

Yes, those are unique ID numbers. The rest of the columns have various parameters of confidential information attached to each report. Basically, a report has about 200 fields that can hold information. Some fields are broken up into multiple fields when entered into the system. When I dump that data into excel, those fields translate to columns and there are a lot of them.

This is an option I can try although I need to keep my cell definitions variable because there are additional records added over the course of the week. This means I will either need to define a Name that counts how many rows of data there is (and use that for the cell definition) or use B:B to select the whole column.

I can tell I am going to have dreams about formulas tonight... This dialogue is helping me sort this out too... Thank you very much!


Sent from my iPad using Tapatalk

[FDibbins]

Well My suggestion was only for the ID, but would work just as easily to pull in the rest of the columns. Overwriting the data on the other 2 sheets should not affect the formulas on the 1st sheet, and you can copy them down as far as you need

[CWatsonJr]

Thank you for your ideas! As I dreamt about this workbook last night it became much more detailed and I am going to be adding more information to the report than originally planned.

To solve the problem of picking the late reports, I created a helper tab which copies the ID numbers

Please Login or Register  to view this content.

and then I used alansidman's formula with a slight twist

Please Login or Register  to view this content.

I had to add the additional IF statement because I copied the formulas down to row 5500 (which produces 0s where this no data on the Current tab) and when the time comes when I need more, I will just copy them down another 1,000 rows as needed.

I defined a Name variable which lays out how many rows of data I have (in Name Manager - ListLates)

Please Login or Register  to view this content.

(Helper!H2 is just a count of the rows of data on the Current tab)

On the Late Reports tab I used an array formula to grab the IDs that were labeled as late reports

Please Login or Register  to view this content.

I then just use VLOOKUP to match the report ID and fill in the rest of the information I want to display.

So, in the end, I dump my data into the Current and Original tabs and let the formulas do the rest.

Thank you again!!! I love this bulletin board!!!!

[FDibbins]

Awesome, glad you got it resolved