• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help to count multiple criteria to generate ID

T

Trish

New Member
I'm looking to generate an ID for each Employee in Column B to be used as a reference in the rest of my spread sheet. I was able to use COUNTIF for those that just worked across the one Area using the following formula:
=IF(COUNTIF($B$2:B3,B3)=1,A2+1,A2)
But I'm a bit baffled for those that work across more than one Area. I think I should be able to use COUNTIFS, but can't quite get what I'm after.
I've uploaded a shortened version of my file. The shaded data in the ID column (A) is the result I am looking for.
It's sorted on Date and I'd really like to keep it that way as I need to calculate an allowance based on the chronological order in which they worked up to a weekly cap, but I could probably work around this if I have to.

Sorry if I haven't explained that well enough.
Thanks in advance.
 

Attachments

Hi Trish,

Does an employee have a different ID based on the area, in your table employee 5 has and ID of 9 for area AB and 10 for area SLC?

cheers

kanti
 
Hi Trish,

Try this formula placed in E2 and copy down:
Code: 
=--(COUNTIF($B$2:$B2,$B2)=1)+MAX($E1:$E$1)

Or if you want it in column A, then place this one in A2 and copy down:
Code: 
=--(COUNTIF($B$2:$B2,$B2)=1)+MAX($A1:$A$1)
 
Or this in A2 and copy down
Code: 
=--(COUNTIFS($B$2:$B2,$B2,$D$2:$D2,$D2)=1)+MAX($A1:$A$1)
 
HI Trish,

If you could sort your data first (by employee AND area), then my solution would work. Is this an option for you?
Code: 
=--(COUNTIFS($B$2:$B2,$B2,$D$2:$D2,$D2)=1)+MAX($A1:$A$1)
 
Thank you all so much for your quick responses!
Xiq, I'd like to keep my data sorted in it's present form, but I'll do some experimenting with your solution. I may have to change my plan.
Narayan, I will also try your solution....to actually see if I can work out what it's doing!
Kchiba, I really like your helper column....it may be the simplest solution for me as I'm still building on my excel skills.
I L-O-V-E this place.....you guys are awesome!
Thanks again.
 
Hi Trish ,

Sorry , but my earlier posted solution is wrong , which is why I have deleted the post.

Do the following :

In A2 enter 1.

In A3 , enter the following array formula , using CTRL SHIFT ENTER :

Code: 
=IF(OR(ISNUMBER(SEARCH(B3&"|"&D3,$B$1:B2&"|"&$D$1:D2))),MAX(IF(($B$1:B2=B3)*($D$1:D2=D3),($A$1:A2),)),MAX($A$1:A2)+1)
Copy down.

Narayan
 
Hi Trish,
Here is another try, place in A2 and enter as array (ctrl + shift + enter), then copy down:
Code: 
=IFERROR(INDEX($A$1:A1,MATCH(B2&D2,$B$1:B1&$D$1:D1,)),MAX($A$1:A1)+1)
 
NARAYANK991 said:
Hi Trish ,

Sorry , but my earlier posted solution is wrong , which is why I have deleted the post.

Do the following :

In A2 enter 1.

In A3 , enter the following array formula , using CTRL SHIFT ENTER :

Code: 
=IF(OR(ISNUMBER(SEARCH(B3&"|"&D3,$B$1:B2&"|"&$D$1:D2))),MAX(IF(($B$1:B2=B3)*($D$1:D2=D3),($A$1:A2),)),MAX($A$1:A2)+1)
Copy down.

Narayan
Click to expand...

OMG! That worked!
Now, to study it to understand why....lol!
 
Xiq said:
Hi Trish,
Here is another try, place in A2 and enter as array (ctrl + shift + enter), then copy down:
Code: 
=IFERROR(INDEX($A$1:A1,MATCH(B2&D2,$B$1:B1&$D$1:D1,)),MAX($A$1:A1)+1)
Click to expand...

I tried this, but can't get it to work properly. Not sure what I'm doing wrong, but tried a few times using ctrl+shift+enter :(
 
Hi Trish,

sorry i uploaded the file without all the formulas, here is a file with all the formulas. I trust that it will work.

I am not sure why the file is not uploading the final, but in cell G3 enter the following and copy down.

=IF(COUNTIF($F$2:F3,F3)>1,INDEX($A$2:A3,MATCH(F3,$F$2:F3,0)),MAX($A$2:A2)+1)
 

Attachments

kchiba said:
Hi Trish,

sorry i uploaded the file without all the formulas, here is a file with all the formulas. I trust that it will work.

I am not sure why the file is not uploading the final, but in cell G3 enter the following and copy down.

=IF(COUNTIF($F$2:F3,F3)>1,INDEX($A$2:A3,MATCH(F3,$F$2:F3,0)),MAX($A$2:A2)+1)
Click to expand...

Thank you....the second file contained the formula. :)
 
Hi again, I just used the formula (from Narayan) in my spreadsheet at work today and, not only does it work a treat, it makes me look AWESOME and has cut down a whole heap of time in this weekly task! So I just wanted to reiterate my THANKS to you all for helping me. I've still got a way to go before it is fully automated but over time I hope to get it this way and I hope you don't mind helping next time I get stuck. I sooooo appreciate it!

Narayan, I wonder if you wouldn't mind putting the formula into plain English so that I can know exactly what it is doing in each element?
 
Hi Trish ,

I am embarrassed ! The formula I posted is a round-about way of doing what Xiq’s formula does more simply. I suggest you use that.

Regarding how the formula works , I can explain ; both formulae do exactly the same thing.

Actually , if you think about it ( I know that time is something you cannot spare ! ) , manually the steps we would take to fill up column A would be as follows :

1. The first entry in A2 would always be 1 , which is why I suggested that you just enter 1 manually in that cell
2. For the second entry onwards the logic is the same ; take the combination of Employee ID and Area.
3. Check whether this combination has occurred earlier in columns B and D ; if it has , then take the value in column A which was assigned to this combination. If it hasn’t , then take the maximum value which we have so far in column A , and increment it by 1.

For implementing step 2 , we have the construct :​
​
B3 & “|” & D3​
​
The reason for the addition of the vertical bar between B3 and D3 is not applicable in this case , but this is an idea which you can use in situations where it is a must. Suppose both column B and column D contain numbers , and all values are possible ; thus we may have the number 117 in cell B3 and the number 23 in D3 ; without the vertical bar in between B3 and D3 , the simple concatenation of the two cells would result in the value 11723. When we try to match this concatenated value , we will get an unintended match if a cell in column B has the number 11 , and the corresponding cell in column D has the number 723 ! Of course , this can also occur if the data in columns B and D is text , but it is probably more commonly encountered in numbers.​
​
Checking whether the concatenated combination has already occurred is a simple matter using the MATCH function , which is what Xiq has used.​
​
If there is a match , we take the corresponding value from column A , otherwise we take the maximum number which has already been used , and take the next higher number by incrementing this maximum value by 1.​
​
Narayan​
​
 
Thank you both very much. Narayan, your explanation is wonderful. Thanks for taking the time to do this. Very much appreciated! :)
 
You must log in or register to reply here.
Back
Top