Count unique last values with formula

[martinpaiva]

Hi All,


First at all; thanks for help me with this and sorry its my english is not the best ....


I build a spreadsheet to manage a stock. So, when you assign a product (tshirt, jean,ect) to a person, I want to see in a column like +1.. But then if you unassigned this same product to the same person i want to see 0 in the two records.


I could do that using the COUNTIF, but the problem that i have if you assing again the product ... I just want to see +1 in the last row and not in the 3 rows.


Sample data:

[pre]

Date	      Employee	Product	Size	Action	   Last Record
4/3/2013	Test1	Prodct1	L	Assign	        0
4/19/2013	Test1	Prodct1	L	Unassigned	0
4/23/2013	Test1	Prodct1	L	Assign	        1

[/pre]
The date is not importan, I just added to show that this "transaction" was in differents times.


So, its possible create a formula to COUNT ONLY THE ASSIGNED PRODUCTS PER EMPLOYEE, IF WAS ASSIGNED AND THEN UNASSIGNED SHOW 0, IF THEN ASSIGN AGAIN SHOW 1 ONLY FOR THAT ROW.

 

[SirJB7]

Hi, martinpaiva!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


Regarding your question, consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!


PS: Don't worry about your English, there're a lot of people here for whom it isn't their native language, including myself.

 

[NARAYANK991]

Hi Martin ,


In the example you have given , at the moment , the last entry , dated 4/23/2013 ( I am also using the date only to differentiate one entry from the other ) is ASSIGNED , which is why the LAST RECORD column entry against this is showing 1.


Now suppose you change the ASSIGNED status to UNASSIGNED ; first , please confirm whether this can happen ; if I assume it can happen , then you want the LAST RECORD entry should change to 0. Should the status of the first entry , dated 4/3/2013 , change from 0 to 1 ?


Now suppose you change the UNASSIGNED status for the second entry , dated 4/19/2013 , to ASSIGNED , should the corresponding cell in the LAST RECORD column change to 1 ?


Can we take it as a rule that which ever is the last entry of a given employee + product combination which has ASSIGNED as its status will have its LAST RECORD status as 1 ?


Narayan

 

[martinpaiva]

Hi Narayank991 / SirJB7,


I wrote this post at night and with the brain borned. Below you can find a sample data with the bussines cases for that .


https://dl.dropboxusercontent.com/u/85562478/SampleData.xlsx


The table that i building is to track the IN/OUT on a stock; Then this data will be used by a pivot table to check what "product"(tshir, jacket, pants) are assigned for each employee.


Hope now is more clear ... Thanks for the help

 

[SirJB7]

Hi, martinpaiva!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Count%20unique%20last%20values%20with%20formula%20-%20SampleData%20%28for%20martinpaiva%20at%20chandoo.org%29.xlsx


In column M I created a list of employees, that you could either enter it manually or automatically as described below. In column N you'll find the formula I think you were looking for:

N3: =SI(M3="";"";SUMAR.SI(EmployeeList;M3;AssignmentList)) -----> in english: =IF(M3="","",SUMIF(EmployeeList,M3,AssignmentList))


If you don't want to use named ranges, you can replace EmployeeList and AssignmentList for their actual range addresses (see below too).


Below.


How to create an automatic list of unique values, and indeed in frequency order? Well, here you have a detailed explanation of how it works:

http://chandoo.org/wp/2012/10/04/formula-forensics-no-030/


It uses 3 named ranges:

- EmployeeList: D3:D11

- AssignmentList: H3:H11

- Criteria: =(EmployeeList<>"") ... this is a named formula


Just advise if any issue.


Regards!

 

[martinpaiva]

Hi SirJB7,


What you did is very closed that I need... I attached the spreadhsheet with the data that i have and what i want to report.


In the example is like my Primary Keys are "Employee", "Stock Product", "Size", "Actions"... I think I need to group by the keys and calculate +1 when is assigned and -1 when is unasigned.


In the sheet1 are what you suggest me and in th sheet1 what i have and what i want to report.


https://dl.dropboxusercontent.com/u/85562478/sampledata.xlsx


Really thanks for this! I'm new with the excel formulas and i just know the basic combinations.

 

[SirJB7]

Hi, martinpaiva!


I'm bad news. What you want in worksheet Sheet2 isn't easily (if possible, still don't know) achievable with direct formulas, so your choices are:

a) wait till a solution pops up in my mind

b) wait till someone else find it out

c) accept a formula solution with helper cells (you may hide them if needed)

d) change the formula solution to a macro solution (VBA code involved)


BTW, don't get worried regarding your comprehension of complex formulas, it took me a few days to understand that monster created by the ideas union of the three monsters Chandoo, Oscar & Haseeb.


Regards!

 

[koi]

hi,


maybe like this ? http://speedy.sh/YaZcb/Copy-of-sampledata.xlsx


using some helper column + pivot table

 

[Faseeh]

Hi martinpaiva,


Refering to sheet2!L3:L6, of your sample data file, please try this:


=COUNTIFS($C$3:$C$11,I3,$D$3:$D$11,J3,$E$3:$E$11,$K3,$F$3:$F$11,"Assign")-COUNTIFS($C$3:$C$11,I3,$D$3:$D$11,J3,$E$3:$E$11,$K$3,$F$3:$F$11,"Unassign")


Regards,

Faseeh

 

[martinpaiva]

Hi All, thanks for the help... really was very helpfull.. I believe i need a combinations of your suggestions...My idea is to create a list with the unique values for "Employee", "Product" and "Size" ... then when this "Keys" use the COUNTIFS to calculate "Assign" - "Unassign"


My question is its possible create a list with this unic values... I tried to follow a formuala i can't understand how do it for more than one item.


Data I have

-------------

Employee Stock Product Size

Perez, Garcia Jacket L

Perez, Garcia Pants M

Dadvison, Josh Pants L

Perez, Garcia Jacket L

Gonzales, Martin T-shirt L

Gonzales, Martin T-shirt L


List I want

------------

Employee Stock Product Size

Perez, Garcia Jacket L

Perez, Garcia Pants M

Dadvison, Josh Pants L

Gonzales, Martin T-shirt L


*Its possible, in the future, add more employees in the source data...


I believe if I get this "distinct" values i can calculate then to get the assigned stock for each employee by product/size.


PS: Really thanks for your help on this, hope I can learn more about excel :D!