Finding new items in lists

Resolved · Medium Priority · Version 2010

Thu 27 Feb 2014, 22:45 Delegate Genta said...

Genta has attended:
Excel Advanced course

Finding new items in lists

Hi

I have 2 lists with a large no. of postcodes in each.

I need to cross check the second list against the first one to see if there are any new additions on to the second list.

How do I do this?

Thanks

Fri 28 Feb 2014, 10:52 Trainer Cindy said...

RE: finding new items in lists

Hello Genta,

Thank you for your question regarding cross checking.

My suggestion is to...

1. combine both sets of data onto one sheet.
2. highlight the column containing the data you want to cross reference (postcodes?).
3. on the Home ribbon, in the Styles group, select Conditional Formatting, Highlight Cells Rules then Duplicate Values.

This will result in all duplicate values being highlighted. Anything that only appears once will not be highlighted.

Hope this helps.

Kind regards,

Cindy
Microsoft Office & Soft Skills Trainer

Fri 28 Feb 2014, 19:02 Delegate Genta said...

RE: finding new items in lists

Hi Cindy

Thank you for your help.

However, since the majority of postcodes will be the same i.e. only a few new postcodes would have been added to the second list, I will end up with a large number of highlighted values and thus can miss the non highlighted ones.

Is there a way to use subtotaling to find the newly added postcodes in the second list, as for each postcode I have the corresponding ward as a column adjacent to the postcode column in each spreadsheet?

Genta

Fri 28 Feb 2014, 19:02 Delegate Genta said...

RE: finding new items in lists

Wed 5 Mar 2014, 11:43 Trainer Cindy said...

RE: finding new items in lists

Hello Genta,

Suggestion 2 is to use V-Lookup’s (pg. 21 of the course manual).

If you do vlookup’s in the worksheet with the newly added postcodes, it will return N/A if the item is not on the other list. You can then sort the data so all N/A’s are together.

This method will allow you to easily identify all the new additions.

Let me know if you have any further questions.

Kind regards

Cindy
Microsoft Office Trainer

Wed 12 Mar 2014: Automatically marked as resolved.

Training courses