Figuring out user status

[skittlz]

Hi,

I have a tab that has user and their status in 1 tab of raw data, and a final status in another tab. The raw data has multiple entries for the user AND status. How do I match all the entries, and figure out the (final) user status? The user status can be Active, Inactive, or Decertified. Decertified would supersede Inactive, Inactive would supersede Active. See Screenshot below!

re

 

[etaf]

maybe a count in an IF
whats the order
Decertified , inactive , active

=IF ( countifs($H$2:$H$100, A7, $I$2:$I$100, "decertified") , "decertified" , IF(countifs($H$2:$H$100, A7, $I$2:$I$100, "inactive"), "inactive", IF(countifs($H$2:$H$100, A7, $I$2:$I$100, "active"), "active", "" )))

but not sure , as thats not what shown in the diagram

so change the IF order to match the priority

 

[skittlz]

In my screenshot above - my current formula returns the 1st value only for status - not the "actual" value.

 

[etaf]

what is the actual value required

I have put the order in the IF for a count formula to look at
=IF ( countifs($H$2:$H$100, A7, $I$2:$I$100, "decertified") , "decertified" , IF(countifs($H$2:$H$100, A7, $I$2:$I$100, "inactive"), "inactive", IF(countifs($H$2:$H$100, A7, $I$2:$I$100, "active"), "active", "" )))

so if decertified exists in RAW data for that user - then decertified is added to the status , and thats it , the IFs stops
however if decertified is not in the raw data for that user
then we do the next IF and see if "inactive" is in the raw data list - if not then the 3rd IF

so the order we look for is important as to what comes first,
so which order takes precedent

JIM would be decertified
BOB active
JOE decertified
HARRY Active
SAM InActive

so what should the order be for the status

whats the expected result ?

 

[skittlz]

Hi Etaf -that partially works - it sounds like the order of the "If's" will determine the higher-archy - is that correct? This is really helpful.

The Formula however doesn't take the "Doc#" in account. Here's the complexity:

1) If the person is "Inactive" - they are Inactive for ALL doc#s (that is - person is out of office, so cant be trained - so flagged as "inactive").
2) If a person is "decertified" to a given Doc# - they are ONLY decertified for that one document - not others.


Jim is Inactive for all Docs (1 and 2 - since he's Inactive for Doc 1)
Bob is Active for Doc 1 and 2
Joe is Decertified for 1, but Active for 2
Harry is Active for 1 and 2
Sam is Inactive for 1 and 2

 

[etaf]

ok, i didnt realise doc number was important from the post - so its a lot more complicated then

you also have revison highlighted , is that also an issue - your example has the same revision , but would that need to be considered

you have doc number in B2 - so thats that number should be considered only for the status

it sounds like the order of the "If's" will determine the higher-archy - is that correct?

Yes

breaking it down a bit

Inactive - so doc does not matter so no need to check that
therefor a
(countifs($H$2:$H$100, A7, $I$2:$I$100, "inactive")
if 1 or more will be inactive status

decertified - depends on doc number
( countifs($H$2:$H$100, A7, $I$2:$I$100, "decertified" , $J$2:$J$100, $B$2)
Now we count to see if decertified and also includes the doc number

Active - you have not mentioned active - but looks like from the list - also needs doc number
countifs($H$2:$H$100, A7, $I$2:$I$100, "active", $J$2:$J$100, $B$2)

so again , i dont know the order of preference

1) If the person is "Inactive" - they are Inactive for ALL doc#s (that is - person is out of office, so cant be trained - so flagged as "inactive").

BUT thats not what is shown JIM is Active , are you saying that the table is NOT the expected results

JIM would be decertified
BOB active
JOE decertified
HARRY Active
SAM InActive

So
JIM has all possible status and Decertified is first - Because its also the same Book Number , so that comes first in the IF and overrides the other possible status , if the doc number is the same as B2
BOB is active - but hes active for all - so know idea what order from him
JOE - has all possible status Decertified or active - and as JIM decertified is first - Because its also the same Book Number , so that comes first in the IF and overrides the Active, if the doc number is the same as B2
HARRY - active but thats the only choice
SAM - could be inactive or active as same doc number - BUT inactive chosen - so inactive comes NEXT and Active last in the order

EDIT - opps i appear to have missed the doc number on the active statement - But i will let you come back to some of the answers before i correct all this - as only have 10mins and will need a new post
and most assumptions maybe wrong anyway

I'll let you answer the below as well



= IF( ( countifs($H$2:$H$100, A7, $I$2:$I$100, "decertified" , $J$2:$J$100, $B$2) , "decertified",
IF( countifs($H$2:$H$100, A7, $I$2:$I$100, "inactive") , "inactive",
IF( countifs($H$2:$H$100, A7, $I$2:$I$100, "active") , "active" , "" )))

=IF(COUNTIFS($H$2:$H$100, A7, $I$2:$I$100, "decertified", $J$2:$J$100, $B$2),"decertified",IF(COUNTIFS($H$2:$H$100, A7, $I$2:$I$100, "inactive"),"inactive",IF(COUNTIFS($H$2:$H$100, A7, $I$2:$I$100, "active"),"active","" )))

i get a different result to you

JIM

2) If a person is "decertified" to a given Doc# - they are ONLY decertified for that one document - not others.

user
Jim I have decertified you have inactive - WHY, has decertified for 1
Bob I have active correct
sam I have inactive correct
joe I have active you have decrtified - WHY has doc 2 , so not doc1
harry I have active correct

i have mocked up just the main bits

Book12
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	doc	1						name	status	doc		Rev
3								Jim	active	1		E
4	rev	E						jim	inactive	1		E
5								jim	decertified	1		E
6	user		you say					jim	active	2		E
7	Jim	decertified	inactive - WHY, has decertified for 1					bob	active	1		E
8	Bob	active	correct					bob	active	2		E
9	sam	inactive	correct					bob	active	1		E
10	joe	active	decrtified - WHY has doc 2 , so not doc1					joe	decertified	2		E
11	harry	active	correct					joe	active	1		E
12								harry	active	1		E
13								harry	active	1		E
14								harry	active	2		E
15								sam	active	2		E
16								sam	inactive	1		E
17								sam	active	1		E
18								sam	active	3		E
19								sam	active	2		E
Sheet1

Cell Formulas
Range	Formula
B7:B11	B7	=IF(COUNTIFS($H$2:$H$100, A7, $I$2:$I$100, "decertified", $J$2:$J$100, $B$2),"decertified",IF(COUNTIFS($H$2:$H$100, A7, $I$2:$I$100, "inactive"),"inactive",IF(COUNTIFS($H$2:$H$100, A7, $I$2:$I$100, "active"),"active","" )))

i have put on a share - BUT only for a few days and will be deleted

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[skittlz]

Hi Etaf - the formulas above were very complex and I was struggling to get them to work (and they were slowing down the spreadsheet - ALOT) - I have thousands of entries to filter through. I ended up using a helper tab to minimize my search fields. Its simplified down to the user name, and a mix of status.

Is there any way to populate the "final" status in column C for each name?

 

[etaf]

the formulas above were very complex

OK, but its just 3 count formulas
within an IF
and so executed one after the other if NOT true



how many thousands of rows .. i have used a count on very large excel sheets , BUT thats all relative

. I ended up using a helper tab to minimize my search fields. Its simplified down to the user name, and a mix of status.

therss only 3 columns in your example - is the real data a lot more complex than thats
otherwise HOW did you simplify ???

cant see much from a image , as mentioned

 

[skittlz]

Hi - I was trying to filter out "Inactive" (and "terminated", which are treated the same as inactive), and then copy the day over to a new tab but you're right - best to combine.

The order should be Terminated (everything), Inactive (everything), Decertified (that document ONLY), and Active (that document only - leftover).

=IF(COUNTIFS('Training Log'!A:A,Formulas!A2,'Training Log'!B:B,"terminated"),"Terminated",IF(COUNTIFS('Training Log'!A:A,Formulas!A2,'Training Log'!B:B,"Inactive"),"Inactive",IF(COUNTIFS('Training Log'!A:A,Formulas!A2,'Training Log'!B:B,"Decertified",'Training Log'!E:E,'Training Matrix'!$B$1),"Active","")))

I got the "terminated" and "inactive" - if they're terminated/inactive, it updates ALL the documents. I cant get the decertificate to work. What am I missing?

Regards,

- Ritul

 

[etaf]

,IF(COUNTIFS('Training Log'!A:A,Formulas!A2,'Training Log'!B:B,"Decertified",'Training Log'!E:E,'Training Matrix'!$B$1),"Active","")))

you are not outputting "decertified" any where - just active, and if not then a blank
maybe thats why - Active is in place of decertified in your equation
change that bit to
,IF(COUNTIFS('Training Log'!A:A,Formulas!A2,'Training Log'!B:B,"Decertified",'Training Log'!E:E,'Training Matrix'!$B$1),"Decertified", "Active")))
BUT that assumes it must be active if none of the other are true
,IF(COUNTIFS('Training Log'!A:A,Formulas!A2,'Training Log'!B:B,"Decertified",'Training Log'!E:E,'Training Matrix'!$B$1),"Decertified", IF( TEST, "Active", ""))))

BUT you probably want and active condition IF as well
if you want "" at the end