I am in need of a formula to compare data in two columns and identify what data is present in one column but not the other.
e.g. Column A has data from last week, Column B has data from this week. I need to know if there were any new items this week, not present in the week before.
Can anybody help? Currently we are needing to manually go through the list (sometimes containing 1000+ items) and as you can imagine, this is very time consuming.
Any help would be greatly appreciated.
in col c =countif(a:a,b1)>0
will return true if its in last weeks list and false if it isnt
or
maybe something like
=if(countif(a:a,b1),"","new item")
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Thanks @martindwilson. The first formula works as far as finding a region/area where there is a difference but still requires a little bit of looking to identify the actual new item. Is there such a thing to compare two lists and have a result that equals the cell name that is new?
i.e if i had two columns with different types of fruit for instance and the only new items this week (column B) where apples and pears, the forumla would present a result of apple/pear (cell name).
Is that asking too much?
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
example.xlsx
Please see the attached example
Try this array formula in C1:
Confirmed with Ctrl-shift-enter
Please Login or Register to view this content.
Drag down.
Quang PT
thats what auto filter is for just filter the column on false or true whichever you want to see
bebo021999 - I can't get that forumla to display anything at all...
Pls make sure that using Ctrl-Shift-Enter instead of Enter only...Originally Posted by t.regi
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks