Previous Topic Next Topic

Unique text formula question in excel

Solved/Closed
verbatim - Jan 27, 2010 at 01:55 AM
 RIZVISA1 - Jan 29, 2010 at 04:26 PM
Hello,
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 cant 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

1 reply

Answer 1 / 1
RIZVISA1
Jan 29, 2010 at 04:26 PM
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
1