Menu
  1. Accueil
  2. How To
  3. Software
  4. Office Software
  5. Excel
Thanks
0
Thanks

A few words of thanks would be greatly appreciated.

Excel - Unique text formula

Latest update on by Jean-François Pillou.


Issue


I have 2 columns of names in columns a & b and a list of dates in column c. Column A has a unique name in each cell. Column B had the same 5 or 6 names next to each name in column A.

Column C is the date the name was added to the list. I need a formula to tell me how many unique names in column a were added by a user in column B before the date in column C.

I've read on 'Count the number of unique values by using functions' & 'Count how often multiple number values occur by using functions' but can't get it. Any help offered would be greatly appreciated. Thanks in advance. 

client added by date added how many added before 'date added'  
george james 1/10/10 12:00 PM answer here should be 0  
royale james 1/11/10 2:00 PM answer here should be 1  
walt james 1/11/10 12:00 PM answer here should be 2  
aj john 1/13/10 4:00 PM answer here should be 0  
justin james 1/14/10 9:00 AM answer here should be 3  
tam john 1/15/10 10:00 AM answer here should be 1  


client  
george  
royale  
walt  
aj  
justin  
tam  

added by  
james  
james  
james  
john  
james  
john  

date added  
1/10/10 12:00 PM  
1/11/10 2:00 PM  
1/11/10 12:00 PM  
1/13/10 4:00 PM  
1/14/10 9:00 AM  
1/15/10 10:00 AM  

how many added before 'date added'  
answer here should be 0  
answer here should be 1  
answer here should be 2  
answer here should be 0  
answer here should be 3  
answer here should be 1

Solution


It will first sort on the name who added, then by date and then by client. If you want to see it back in old sort order, you can add rows number to each row before and once macro is done, re-sort of the row number.

But if sort is nothing big, then u can use it as is 

Cells.Select  
Selection.Sort _  
Key1:=Range("B2"), Order1:=xlAscending, _  
Key2:=Range("C2"), Order2:=xlAscending, _  
Key3:=Range("A2"), Order3:=xlAscending, _  
Header:=xlYes, OrderCustom:=1, _  
MatchCase:=False, Orientation:=xlTopToBottom, _  
DataOption1:=xlSortNormal, _  
DataOption2:=xlSortNormal, _  
DataOption3:=xlSortNormal  

lrow = 2  
AddedByName = Cells(lrow, 2)  
addCount = -1  
Do While Cells(lrow, 1) <> ""  
If (AddedByName <> Cells(lrow, 2)) Then  
addCount = -1  
AddedByName = Cells(lrow, 2)  
End If  

addCount = addCount + 1  
Cells(lrow, 4) = addCount  

lrow = lrow + 1  

Loop

Note


Thanks to RIZVISA1 for this tip on the forum.
Thanks
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - Unique text formula », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

0 Comments