Hello from Steved


Below I've Columns A,B,C, What I would like please is a formula the would
find the Duplicates, if none found the put the number 1 in Column D, If
Dupicates found then goto the first put in 1 then the second Duplicate put in
2, if a third found then put in 3, if a fourth is found then put in 4 and so
on. for example if There is 8 duplicates found then I will see
1,2,3,4,5,6,7,8 in D1, I will see 1 D2, I will see 2 in D3 I will see 3 and
so on.

30/07/2007 1004 15:30
31/07/2007 1004 15:30
1/08/2007 1004 15:30
2/08/2007 1004 15:30
30/07/2007 1005 15:30
31/07/2007 1005 15:30
2/08/2007 1005 15:30
3/08/2007 1005 15:30
30/07/2007 1007 15:20
30/07/2007 1007 15:20
31/07/2007 1007 15:20
31/07/2007 1007 15:20
1/08/2007 1007 15:20
2/08/2007 1007 15:20
2/08/2007 1007 15:20
3/08/2007 1007 15:20

Thankyou.

I have no idea what you are asking. Do you speak English?

"Steved" wrote in message
...
Hello from Steved


Below I've Columns A,B,C, What I would like please is a formula the would
find the Duplicates, if none found the put the number 1 in Column D, If
Dupicates found then goto the first put in 1 then the second Duplicate put
in
2, if a third found then put in 3, if a fourth is found then put in 4 and
so
on. for example if There is 8 duplicates found then I will see
1,2,3,4,5,6,7,8 in D1, I will see 1 D2, I will see 2 in D3 I will see 3
and
so on.

30/07/2007 1004 15:30
31/07/2007 1004 15:30
1/08/2007 1004 15:30
2/08/2007 1004 15:30
30/07/2007 1005 15:30
31/07/2007 1005 15:30
2/08/2007 1005 15:30
3/08/2007 1005 15:30
30/07/2007 1007 15:20
30/07/2007 1007 15:20
31/07/2007 1007 15:20
31/07/2007 1007 15:20
1/08/2007 1007 15:20
2/08/2007 1007 15:20
2/08/2007 1007 15:20
3/08/2007 1007 15:20

Thankyou.

Hello Tryo from Steved

Tryo I have emailed to a worksheet that has 3 Colums Col A, Col B, Col C

The objective is in Col D which is empty I would Like to put add a count
function.

If duplicates are found then do a count for example if the next 5 rows
contain the same information the put value 1 to 5 Cell D1 will have value 1,
Cell D2 will have the Value 2, Cell D3 will have value 3 for example, as set
out below.

Col A Col B Col C Col D
30/07/2007, 1004, 15:30, 1
31/07/2007, 1004, 15:30, 2
1/08/2007, 1004, 15:30, 3
2/08/2007, 1004, 15:30, 4
30/07/2007, 1005, 15:30, 1
31/07/2007, 1005, 15:30, 2
2/08/2007, 1005, 15:30, 3
3/08/2007, 1005, 15:30, 4
30/07/2007, 1007, 15:20, 1
30/07/2007, 1007, 15:20, 2
31/07/2007, 1007, 15:20, 3
31/07/2007, 1007, 15:20, 4
1/08/2007, 1007, 15:20, 5
2/08/2007, 1007, 15:20, 6
2/08/2007, 1007, 15:20, 7
3/08/2007, 1007, 15:20, 8

Thankyou.









"Tyro" wrote:

I have no idea what you are asking. Do you speak English?

"Steved" wrote in message
...
Hello from Steved


Below I've Columns A,B,C, What I would like please is a formula the would
find the Duplicates, if none found the put the number 1 in Column D, If
Dupicates found then goto the first put in 1 then the second Duplicate put
in
2, if a third found then put in 3, if a fourth is found then put in 4 and
so
on. for example if There is 8 duplicates found then I will see
1,2,3,4,5,6,7,8 in D1, I will see 1 D2, I will see 2 in D3 I will see 3
and
so on.

30/07/2007 1004 15:30
31/07/2007 1004 15:30
1/08/2007 1004 15:30
2/08/2007 1004 15:30
30/07/2007 1005 15:30
31/07/2007 1005 15:30
2/08/2007 1005 15:30
3/08/2007 1005 15:30
30/07/2007 1007 15:20
30/07/2007 1007 15:20
31/07/2007 1007 15:20
31/07/2007 1007 15:20
1/08/2007 1007 15:20
2/08/2007 1007 15:20
2/08/2007 1007 15:20
3/08/2007 1007 15:20

Thankyou.

You haven't told us exactly what duplicates you are trying to count... Col
B, Col C or some combination of Col B and Col C. From your sample, it looks
like Col B and Col C, when duplicated, will always be duplicated (entry in
Col C the same for each duplicated value in Col B with Col A not figuring in
the counting process at all). Here is a formula to count Col B items
(assuming your first data row is 1)...

D1: =COUNTIF($B$1:B1,B1)

Then copy it down through all of your rows of data. If this is not what you
want, then you will need to clarify exactly what you are trying to count.

Rick



"Steved" wrote in message
...
Hello Tryo from Steved

Tryo I have emailed to a worksheet that has 3 Colums Col A, Col B, Col C

The objective is in Col D which is empty I would Like to put add a count
function.

If duplicates are found then do a count for example if the next 5 rows
contain the same information the put value 1 to 5 Cell D1 will have value
1,
Cell D2 will have the Value 2, Cell D3 will have value 3 for example, as
set
out below.

Col A Col B Col C Col D
30/07/2007, 1004, 15:30, 1
31/07/2007, 1004, 15:30, 2
1/08/2007, 1004, 15:30, 3
2/08/2007, 1004, 15:30, 4
30/07/2007, 1005, 15:30, 1
31/07/2007, 1005, 15:30, 2
2/08/2007, 1005, 15:30, 3
3/08/2007, 1005, 15:30, 4
30/07/2007, 1007, 15:20, 1
30/07/2007, 1007, 15:20, 2
31/07/2007, 1007, 15:20, 3
31/07/2007, 1007, 15:20, 4
1/08/2007, 1007, 15:20, 5
2/08/2007, 1007, 15:20, 6
2/08/2007, 1007, 15:20, 7
3/08/2007, 1007, 15:20, 8

Thankyou.









"Tyro" wrote:

I have no idea what you are asking. Do you speak English?

"Steved" wrote in message
...
Hello from Steved


Below I've Columns A,B,C, What I would like please is a formula the
would
find the Duplicates, if none found the put the number 1 in Column D, If
Dupicates found then goto the first put in 1 then the second Duplicate
put
in
2, if a third found then put in 3, if a fourth is found then put in 4
and
so
on. for example if There is 8 duplicates found then I will see
1,2,3,4,5,6,7,8 in D1, I will see 1 D2, I will see 2 in D3 I will see 3
and
so on.

30/07/2007 1004 15:30
31/07/2007 1004 15:30
1/08/2007 1004 15:30
2/08/2007 1004 15:30
30/07/2007 1005 15:30
31/07/2007 1005 15:30
2/08/2007 1005 15:30
3/08/2007 1005 15:30
30/07/2007 1007 15:20
30/07/2007 1007 15:20
31/07/2007 1007 15:20
31/07/2007 1007 15:20
1/08/2007 1007 15:20
2/08/2007 1007 15:20
2/08/2007 1007 15:20
3/08/2007 1007 15:20

Thankyou.

Hello Rick from Steved

Thankyou please excuse my igorance I was in a hurry to put this issue out
there.

=COUNTIF($B$1:B1,B1)

Using your formula Can it be modified to look in 2 Colums ( Col B and Col C )

Thankyou.


"Rick Rothstein (MVP - VB)" wrote:

You haven't told us exactly what duplicates you are trying to count... Col
B, Col C or some combination of Col B and Col C. From your sample, it looks
like Col B and Col C, when duplicated, will always be duplicated (entry in
Col C the same for each duplicated value in Col B with Col A not figuring in
the counting process at all). Here is a formula to count Col B items
(assuming your first data row is 1)...

D1: =COUNTIF($B$1:B1,B1)

Then copy it down through all of your rows of data. If this is not what you
want, then you will need to clarify exactly what you are trying to count.

Rick



"Steved" wrote in message
...
Hello Tryo from Steved

Tryo I have emailed to a worksheet that has 3 Colums Col A, Col B, Col C

The objective is in Col D which is empty I would Like to put add a count
function.

If duplicates are found then do a count for example if the next 5 rows
contain the same information the put value 1 to 5 Cell D1 will have value
1,
Cell D2 will have the Value 2, Cell D3 will have value 3 for example, as
set
out below.

Col A Col B Col C Col D
30/07/2007, 1004, 15:30, 1
31/07/2007, 1004, 15:30, 2
1/08/2007, 1004, 15:30, 3
2/08/2007, 1004, 15:30, 4
30/07/2007, 1005, 15:30, 1
31/07/2007, 1005, 15:30, 2
2/08/2007, 1005, 15:30, 3
3/08/2007, 1005, 15:30, 4
30/07/2007, 1007, 15:20, 1
30/07/2007, 1007, 15:20, 2
31/07/2007, 1007, 15:20, 3
31/07/2007, 1007, 15:20, 4
1/08/2007, 1007, 15:20, 5
2/08/2007, 1007, 15:20, 6
2/08/2007, 1007, 15:20, 7
3/08/2007, 1007, 15:20, 8

Thankyou.









"Tyro" wrote:

I have no idea what you are asking. Do you speak English?

"Steved" wrote in message
...
Hello from Steved


Below I've Columns A,B,C, What I would like please is a formula the
would
find the Duplicates, if none found the put the number 1 in Column D, If
Dupicates found then goto the first put in 1 then the second Duplicate
put
in
2, if a third found then put in 3, if a fourth is found then put in 4
and
so
on. for example if There is 8 duplicates found then I will see
1,2,3,4,5,6,7,8 in D1, I will see 1 D2, I will see 2 in D3 I will see 3
and
so on.

30/07/2007 1004 15:30
31/07/2007 1004 15:30
1/08/2007 1004 15:30
2/08/2007 1004 15:30
30/07/2007 1005 15:30
31/07/2007 1005 15:30
2/08/2007 1005 15:30
3/08/2007 1005 15:30
30/07/2007 1007 15:20
30/07/2007 1007 15:20
31/07/2007 1007 15:20
31/07/2007 1007 15:20
1/08/2007 1007 15:20
2/08/2007 1007 15:20
2/08/2007 1007 15:20
3/08/2007 1007 15:20

Thankyou.

Using your formula Can it be modified to look in 2
Colums ( Col B and Col C )

If you have a lot rows of data you'd be better off using Harlan's formula
slightly modified:

D1 = 1

D2: =IF(B2&C2=B1&C1,D1+1,1)

Copied down as needed.

--
Biff
Microsoft Excel MVP


"Steved" wrote in message
...
Hello Rick from Steved

Thankyou please excuse my igorance I was in a hurry to put this issue out
there.

=COUNTIF($B$1:B1,B1)

Using your formula Can it be modified to look in 2 Colums ( Col B and Col
C )

Thankyou.


"Rick Rothstein (MVP - VB)" wrote:

You haven't told us exactly what duplicates you are trying to count...
Col
B, Col C or some combination of Col B and Col C. From your sample, it
looks
like Col B and Col C, when duplicated, will always be duplicated (entry
in
Col C the same for each duplicated value in Col B with Col A not figuring
in
the counting process at all). Here is a formula to count Col B items
(assuming your first data row is 1)...

D1: =COUNTIF($B$1:B1,B1)

Then copy it down through all of your rows of data. If this is not what
you
want, then you will need to clarify exactly what you are trying to count.

Rick



"Steved" wrote in message
...
Hello Tryo from Steved

Tryo I have emailed to a worksheet that has 3 Colums Col A, Col B, Col
C

The objective is in Col D which is empty I would Like to put add a
count
function.

If duplicates are found then do a count for example if the next 5 rows
contain the same information the put value 1 to 5 Cell D1 will have
value
1,
Cell D2 will have the Value 2, Cell D3 will have value 3 for example,
as
set
out below.

Col A Col B Col C Col D
30/07/2007, 1004, 15:30, 1
31/07/2007, 1004, 15:30, 2
1/08/2007, 1004, 15:30, 3
2/08/2007, 1004, 15:30, 4
30/07/2007, 1005, 15:30, 1
31/07/2007, 1005, 15:30, 2
2/08/2007, 1005, 15:30, 3
3/08/2007, 1005, 15:30, 4
30/07/2007, 1007, 15:20, 1
30/07/2007, 1007, 15:20, 2
31/07/2007, 1007, 15:20, 3
31/07/2007, 1007, 15:20, 4
1/08/2007, 1007, 15:20, 5
2/08/2007, 1007, 15:20, 6
2/08/2007, 1007, 15:20, 7
3/08/2007, 1007, 15:20, 8

Thankyou.









"Tyro" wrote:

I have no idea what you are asking. Do you speak English?

"Steved" wrote in message
...
Hello from Steved


Below I've Columns A,B,C, What I would like please is a formula the
would
find the Duplicates, if none found the put the number 1 in Column D,
If
Dupicates found then goto the first put in 1 then the second
Duplicate
put
in
2, if a third found then put in 3, if a fourth is found then put in
4
and
so
on. for example if There is 8 duplicates found then I will see
1,2,3,4,5,6,7,8 in D1, I will see 1 D2, I will see 2 in D3 I will
see 3
and
so on.

30/07/2007 1004 15:30
31/07/2007 1004 15:30
1/08/2007 1004 15:30
2/08/2007 1004 15:30
30/07/2007 1005 15:30
31/07/2007 1005 15:30
2/08/2007 1005 15:30
3/08/2007 1005 15:30
30/07/2007 1007 15:20
30/07/2007 1007 15:20
31/07/2007 1007 15:20
31/07/2007 1007 15:20
1/08/2007 1007 15:20
2/08/2007 1007 15:20
2/08/2007 1007 15:20
3/08/2007 1007 15:20

Thankyou.

You seem very reluctant to respond to the question being raised by
responders to your posting. You have to remember, we know **nothing** about
what you are trying to do unless you tell us what it is you are trying to
do. We can make guesses as to what you want, but it is not really fair to
make us guess like that when you can quite easily tell us what you have and
what you need.

With that said, can I assume from your latest question that if Col B has
several repeated codes in it (say, 1004 for example), that the corresponding
entries in Col C do not all have to be the same value (15:30 in the case of
the data you posted earlier)? If so, my off-the-top-of-the-head response
would be to employ an 'helper' column. Put this formula in E1

=B1&" "&C1

and copy it down through all of your data rows. Then use this formula in D1

=COUNTIF($E$1:E1,E1)

and copy it down through all of your data rows. If this is not what you are
looking for, then you **MUST** provide us with data that matches your actual
conditions (do not simply copy the same line over and over again as you
appear to have done in your first posting... it gave us a false impression
of what you wanted) and show the values you actually want in Col D for them.

Rick


"Steved" wrote in message
...
Hello Rick from Steved

Thankyou please excuse my igorance I was in a hurry to put this issue out
there.

=COUNTIF($B$1:B1,B1)

Using your formula Can it be modified to look in 2 Colums ( Col B and Col
C )

Thankyou.


"Rick Rothstein (MVP - VB)" wrote:

You haven't told us exactly what duplicates you are trying to count...
Col
B, Col C or some combination of Col B and Col C. From your sample, it
looks
like Col B and Col C, when duplicated, will always be duplicated (entry
in
Col C the same for each duplicated value in Col B with Col A not figuring
in
the counting process at all). Here is a formula to count Col B items
(assuming your first data row is 1)...

D1: =COUNTIF($B$1:B1,B1)

Then copy it down through all of your rows of data. If this is not what
you
want, then you will need to clarify exactly what you are trying to count.

Rick



"Steved" wrote in message
...
Hello Tryo from Steved

Tryo I have emailed to a worksheet that has 3 Colums Col A, Col B, Col
C

The objective is in Col D which is empty I would Like to put add a
count
function.

If duplicates are found then do a count for example if the next 5 rows
contain the same information the put value 1 to 5 Cell D1 will have
value
1,
Cell D2 will have the Value 2, Cell D3 will have value 3 for example,
as
set
out below.

Col A Col B Col C Col D
30/07/2007, 1004, 15:30, 1
31/07/2007, 1004, 15:30, 2
1/08/2007, 1004, 15:30, 3
2/08/2007, 1004, 15:30, 4
30/07/2007, 1005, 15:30, 1
31/07/2007, 1005, 15:30, 2
2/08/2007, 1005, 15:30, 3
3/08/2007, 1005, 15:30, 4
30/07/2007, 1007, 15:20, 1
30/07/2007, 1007, 15:20, 2
31/07/2007, 1007, 15:20, 3
31/07/2007, 1007, 15:20, 4
1/08/2007, 1007, 15:20, 5
2/08/2007, 1007, 15:20, 6
2/08/2007, 1007, 15:20, 7
3/08/2007, 1007, 15:20, 8

Thankyou.









"Tyro" wrote:

I have no idea what you are asking. Do you speak English?

"Steved" wrote in message
...
Hello from Steved


Below I've Columns A,B,C, What I would like please is a formula the
would
find the Duplicates, if none found the put the number 1 in Column D,
If
Dupicates found then goto the first put in 1 then the second
Duplicate
put
in
2, if a third found then put in 3, if a fourth is found then put in
4
and
so
on. for example if There is 8 duplicates found then I will see
1,2,3,4,5,6,7,8 in D1, I will see 1 D2, I will see 2 in D3 I will
see 3
and
so on.

30/07/2007 1004 15:30
31/07/2007 1004 15:30
1/08/2007 1004 15:30
2/08/2007 1004 15:30
30/07/2007 1005 15:30
31/07/2007 1005 15:30
2/08/2007 1005 15:30
3/08/2007 1005 15:30
30/07/2007 1007 15:20
30/07/2007 1007 15:20
31/07/2007 1007 15:20
31/07/2007 1007 15:20
1/08/2007 1007 15:20
2/08/2007 1007 15:20
2/08/2007 1007 15:20
3/08/2007 1007 15:20

Thankyou.

"Steved" wrote...
....
The objective is in Col D which is empty I would Like to put add a count
function.

I think one frustration Tyro might have is that you seem to be writing too
quickly, which might explain the phrase 'to put add', which doesn't make
sense but which maybe you meant 'to put or add'.

If duplicates are found then do a count for example if the next 5 rows
contain the same information the put value 1 to 5 Cell D1 will have value
1, Cell D2 will have the Value 2, Cell D3 will have value 3 for example,
as set out below.

And here, 'the put value' perhaps should have been 'then put value'.

Col A Col B Col C Col D
30/07/2007, 1004, 15:30, 1
31/07/2007, 1004, 15:30, 2
1/08/2007, 1004, 15:30, 3
2/08/2007, 1004, 15:30, 4
30/07/2007, 1005, 15:30, 1
31/07/2007, 1005, 15:30, 2
2/08/2007, 1005, 15:30, 3
3/08/2007, 1005, 15:30, 4
30/07/2007, 1007, 15:20, 1
30/07/2007, 1007, 15:20, 2
31/07/2007, 1007, 15:20, 3
31/07/2007, 1007, 15:20, 4
1/08/2007, 1007, 15:20, 5
2/08/2007, 1007, 15:20, 6
2/08/2007, 1007, 15:20, 7
3/08/2007, 1007, 15:20, 8

Looks like col D depends on col B. If this table began in row 1, so spanned
A1:D16, try

D1:
1

D2:
=IF(B2=B1,D1+1,1)

Hello folks from Steved.

The answer firstly to Rick is that it is 4 hours since I've left work

Ok I now thankyou all, as it will be another 12 hours before I'm back at
work I shall give you my response.

ps Yes I did a Data Sort.

"Harlan Grove" wrote:

"Steved" wrote...
....
The objective is in Col D which is empty I would Like to put add a count
function.

I think one frustration Tyro might have is that you seem to be writing too
quickly, which might explain the phrase 'to put add', which doesn't make
sense but which maybe you meant 'to put or add'.

If duplicates are found then do a count for example if the next 5 rows
contain the same information the put value 1 to 5 Cell D1 will have value
1, Cell D2 will have the Value 2, Cell D3 will have value 3 for example,
as set out below.

And here, 'the put value' perhaps should have been 'then put value'.

Col A Col B Col C Col D
30/07/2007, 1004, 15:30, 1
31/07/2007, 1004, 15:30, 2
1/08/2007, 1004, 15:30, 3
2/08/2007, 1004, 15:30, 4
30/07/2007, 1005, 15:30, 1
31/07/2007, 1005, 15:30, 2
2/08/2007, 1005, 15:30, 3
3/08/2007, 1005, 15:30, 4
30/07/2007, 1007, 15:20, 1
30/07/2007, 1007, 15:20, 2
31/07/2007, 1007, 15:20, 3
31/07/2007, 1007, 15:20, 4
1/08/2007, 1007, 15:20, 5
2/08/2007, 1007, 15:20, 6
2/08/2007, 1007, 15:20, 7
3/08/2007, 1007, 15:20, 8

Looks like col D depends on col B. If this table began in row 1, so spanned
A1:D16, try

D1:
1

D2:
=IF(B2=B1,D1+1,1)

I found these subs on this DG a while back. Try either; try both.

Sub Uniques()
Dim i As Integer
i = 1
Do Until Cells(i, 1).Value = "" '(as long as your data is in column 1)
If Cells(i, 1) = Cells(i + 1, 1) Then
Else
Cells(i, 1).Copy
Cells(i, 5).PasteSpecial xlValues '(this pastes into column E)
End If
i = i + 1

Loop
Range("E5:E1000").Sort Key1:=Range("E5"), Order1:=xlAscending

Columns("E:E").Select
Selection.Sort Key1:=Range("E1"), Order1:=xlAscending
Range("A1").Select

End Sub


Sub ExtractUnique()
Sheets("Find Dupes").Range("A1:A20").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("C1"), _
Unique:=True
End Sub

Hope this helps.
Regards,
Ryan--




"Steved" wrote:

Hello from Steved


Below I've Columns A,B,C, What I would like please is a formula the would
find the Duplicates, if none found the put the number 1 in Column D, If
Dupicates found then goto the first put in 1 then the second Duplicate put in
2, if a third found then put in 3, if a fourth is found then put in 4 and so
on. for example if There is 8 duplicates found then I will see
1,2,3,4,5,6,7,8 in D1, I will see 1 D2, I will see 2 in D3 I will see 3 and
so on.

30/07/2007 1004 15:30
31/07/2007 1004 15:30
1/08/2007 1004 15:30
2/08/2007 1004 15:30
30/07/2007 1005 15:30
31/07/2007 1005 15:30
2/08/2007 1005 15:30
3/08/2007 1005 15:30
30/07/2007 1007 15:20
30/07/2007 1007 15:20
31/07/2007 1007 15:20
31/07/2007 1007 15:20
1/08/2007 1007 15:20
2/08/2007 1007 15:20
2/08/2007 1007 15:20
3/08/2007 1007 15:20

Thankyou.