Conditional formatting, Vlookup or IF statement

[Hiren101]

Hi Guys, I've been stuck on something for a while, hoping somebody can help. I seem to have lost it but would really appreciate some help. I'll try to explain as best as I can.

I have 2 sets of data on 2 separate tabs. For instance.

Tab1: Name | InHere | Exempt | NotHere
Tab2: Name | InHere| Exempt | NotHere

InHere/Exempt/NotHere can either be 1 or 0, signifying the attendance.

I filtered Tab1 with everybody who is NotHere (1=NotHere) back in 2017. In an attempt to move everybody from NotHere 1 to Inhere 1 or Exempt 1. Unfortunately only approx. 5% transpired.

I now have data 2018 data on tab 2 which includes a load of other names + all the names in Tab1.
I would simply like to check all those NotHere in tab 1 (NotHere=1 / Exempt=0 / InHere=0), which of them are now InHere or Exempt in Tab2 (NotHere=0 / Exempt=1 OR InHere=1) . And Highlight the ones that are still NotHere.

I apologise if I am not good at explaining this, but I have tried as best as I could. Really would appreciate if somebody could help how to best run this logic.
Happy to elaborate if need be.

THanks so much in advance, Im using the latest version of excel

[Richard Buttrey]

Hi,

Have you tried a CF along the lines of this.

Please Login or Register  to view this content.

It will return a TRUE if the vlookup can't find anything and a 1 or 0 depending on whether the value is he same or not.

I couldn't follow where everything is so you may have to play around with it a bit.

[Hiren101]

Hi,

Have you tried a CF along the lines of this.

Please Login or Register  to view this content.

It will return a TRUE if the vlookup can't find anything and a 1 or 0 depending on whether the value is he same or not.

I couldn't follow where everything is so you may have to play around with it a bit.

First of all thank you for the prompt reply Richard. I've given that a crack several times, without much joy
Let me elaborate and show you an example.
So sheet 1 in 2017 and sheet 2 is 2018. Sheet 1 contains names 'Clients' who were either InHere / Exempt / NotHere.
Sheet 2 contains the same purpose but includes names in 2017 as well as new names in 2018.

I would like to check if those NotHere in 2017 (NotHere = 1) are either InHere or Exempt in 2018 (need to check if those NotHere = 1 in 2017 are either Exempt = 1 or InHere = 1 in 2018) and flag those that are in NotHere = 1 in 2018.

Hope that makes some sense. I tried apply to CF you suggested, but as you can see it's giving all 'TRUE'.
THanks again!



image001.png
image002.png
image003.png

[Richard Buttrey]

Hi,

Would you upload the workbook, anonymised as necessary. It's always easier if we can see the request in context.

[Hiren101]

Hi,

Would you upload the workbook, anonymised as necessary. It's always easier if we can see the request in context.

Sure, Im trying to attach it but its not allowing me at the moment. Is there another way? Email or private message maybe?

[Hiren101]

Hi,

Would you upload the workbook, anonymised as necessary. It's always easier if we can see the request in context.

I think it is attached now Richard. Please could you kindly take a look.
I've filtered sheet 2017 with NotHere = 1. All those ID's which were NotHere in 2017 I would like to check which of those are either InHere or Exempt in 2018.
The ID's which have remained the same NotHere in 2017 and again NotHere in 2018 I would like to highlight too but not essential. (Client can often vary and own a different ID) So ID would be the unique identifier I guess.

Thanks very much again Richard.

Data.xlsx

[JohnTopley]

On unfiltered data in 2017

in F2

=IF(SUMPRODUCT(('2018'!$C$2:$D$1000)*('2018'!$B$2:$B$1000=$B2)*($E$2:$E$1000=1)),"Yes","")

copy down

then filter on YES in F

[Hiren101]

On unfiltered data in 2017

in F2

=IF(SUMPRODUCT(('2018'!$C$2:$D$1000)*('2018'!$B$2:$B$1000=$B2)*($E$2:$E$1000=1)),"Yes","")

copy down

then filter on YES in F

Thanks very very much John!! That worked a treat!! Super grateful.
Thanks for your efforts too Richard. You guys have nailed it for me.

Is there any links/tutortials that could direct me towards to help me understand this stuff?