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.

"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 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.

If I understand what the OP wants, I don't think that modification will work
unless the data is sorted by Col B, then Col C. Here is some data modified
to make it easier to see the problem with unsorted data in Col C (I changed
the time-looking entries to simple letters)...

A B C D E

7/30/2007 1004 a 1 1
7/31/2007 1004 a 2 2
8/1/2007 1004 b 1 1
8/2/2007 1004 a 1 3
7/30/2007 1005 c 1 1
7/31/2007 1005 a 1 1
8/2/2007 1005 a 2 2
8/3/2007 1005 c 1 2
7/30/2007 1007 a 1 1
7/30/2007 1007 c 1 1
7/31/2007 1007 c 2 2
7/31/2007 1007 b 1 1
8/1/2007 1007 a 1 2
8/2/2007 1007 b 1 2
8/2/2007 1007 c 1 3
8/3/2007 1007 c 2 4

Column D shows the results from the formulas you supplied and Column E shows
what I think they should be. If columns B and C are properly sorted, then
your formulas will work (but, given the OP's reluctance to give us info, who
knows if that is the condition of the original data or not). I would point
out that the COUNTIF solution I provided (even though it requires a helper
column) is not dependent on any of the rows being sorted.

Rick


"T. Valko" wrote in message
...
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.

I don't think that modification will work unless the data is sorted by Col
B, then Col C.

No it won't work on unsorted data but the sample posted by the OP is sorted.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
If I understand what the OP wants, I don't think that modification will
work unless the data is sorted by Col B, then Col C. Here is some data
modified to make it easier to see the problem with unsorted data in Col C
(I changed the time-looking entries to simple letters)...

A B C D E

7/30/2007 1004 a 1 1
7/31/2007 1004 a 2 2
8/1/2007 1004 b 1 1
8/2/2007 1004 a 1 3
7/30/2007 1005 c 1 1
7/31/2007 1005 a 1 1
8/2/2007 1005 a 2 2
8/3/2007 1005 c 1 2
7/30/2007 1007 a 1 1
7/30/2007 1007 c 1 1
7/31/2007 1007 c 2 2
7/31/2007 1007 b 1 1
8/1/2007 1007 a 1 2
8/2/2007 1007 b 1 2
8/2/2007 1007 c 1 3
8/3/2007 1007 c 2 4

Column D shows the results from the formulas you supplied and Column E
shows what I think they should be. If columns B and C are properly sorted,
then your formulas will work (but, given the OP's reluctance to give us
info, who knows if that is the condition of the original data or not). I
would point out that the COUNTIF solution I provided (even though it
requires a helper column) is not dependent on any of the rows being
sorted.

Rick


"T. Valko" wrote in message
...
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.

...but the sample posted by the OP is sorted.

True, but the entries in Column C are all identical for a given code in
Column B also... now the OP is telling us that is not the case. I only
posted my message to you to give the OP a head's-up in case the data is not
**really** sorted the way shown (here I am assuming the OP may have taken a
copy/paste shortcut when posting his initial question).

Rick

Steved has been posting questions here for years. I think he's from New
Zealand. That's why the "English" may be hard to follow. I guess we'll have
to wait to see if the data is sorted or not.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
...but the sample posted by the OP is sorted.

True, but the entries in Column C are all identical for a given code in
Column B also... now the OP is telling us that is not the case. I only
posted my message to you to give the OP a head's-up in case the data is
not **really** sorted the way shown (here I am assuming the OP may have
taken a copy/paste shortcut when posting his initial question).

Rick

"T. Valko" wrote...
Steved has been posting questions here for years. I think he's from New
Zealand. That's why the "English" may be hard to follow. I guess we'll
have to wait to see if the data is sorted or not.
....

Accent is one thing, but one would assume New Zealanders could write in
English so that other English speakers wouldn't have trouble understanding.

"T. Valko" wrote...
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)
....

Now you're assuming that all col B and C entries are similar to the sample
the OP provided. However, if both are strings of digits, there could be some
ambiguity to where the first ended and the second began when they're
concatenated. Therefore, safer to use

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

or, for the aesthetes,

=(D1+1)^((B2=B1)*(C2=C1))

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
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)
...

Now you're assuming that all col B and C entries are similar to the sample
the OP provided. However, if both are strings of digits, there could be
some ambiguity to where the first ended and the second began when they're
concatenated. Therefore, safer to use

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

or, for the aesthetes,

=(D1+1)^((B2=B1)*(C2=C1))


Yeah, that's true.

For those that might not understand what Harlan's talking about, consider
these 2 line entries:

100...15:30
1001...5:30

Those 2 lines are not equal but when concatenated they a

10015:30
10015:30

--
Biff
Microsoft Excel MVP

And they say Americans don't do irony, but here you get one suggesting that
a New Zealander's English might be off-kilter <g

"T. Valko" wrote in message
...
Steved has been posting questions here for years. I think he's from New
Zealand. That's why the "English" may be hard to follow. I guess we'll
have to wait to see if the data is sorted or not.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
...but the sample posted by the OP is sorted.

True, but the entries in Column C are all identical for a given code in
Column B also... now the OP is telling us that is not the case. I only
posted my message to you to give the OP a head's-up in case the data is
not **really** sorted the way shown (here I am assuming the OP may have
taken a copy/paste shortcut when posting his initial question).

Rick

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)

"T. Valko" wrote in message
...
....
Yeah, that's true.

For those that might not understand what Harlan's talking about, consider
these 2 line entries:

100...15:30
1001...5:30

Those 2 lines are not equal but when concatenated they a

10015:30
10015:30

But of course if the 15:30 and 5:30 are times, rather than text strings, the
concatenated values will be different:
1000.645833333333333
10010.229166666666667
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote...
....
But of course if the 15:30 and 5:30 are times, rather than text strings,
the concatenated values will be different:
1000.645833333333333
10010.229166666666667
....

True, but in addition to text or numbers between 0 and 1 formatted as time,
they could be numbers 1 formatted as time, and then you're back to
possible ambiguity.

I didn't say it was NECESSARY to compare col B and C values separately, I
said it's SAFER. Just like it's safer to wrap worksheet names inside single
quotes, e.g., "'"&worksheetname&"'!X99" rather then worksheetname&"!X99".
Again, it wouldn't be NECESSARY if worksheetname contained no spaces, but
it's SAFER just in case it did.

For some of us experience has shown (repeatedly!) the value of ALWAYS using
defensive coding techniques. Others may have enjoyed more blissful
spreadsheet experiences.

Thankyou Harlan Just what I required.

"Harlan Grove" wrote:

"T. Valko" wrote...
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)
....

Now you're assuming that all col B and C entries are similar to the sample
the OP provided. However, if both are strings of digits, there could be some
ambiguity to where the first ended and the second began when they're
concatenated. Therefore, safer to use

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

or, for the aesthetes,

=(D1+1)^((B2=B1)*(C2=C1))

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
"T. Valko" wrote in message
...
...
Yeah, that's true.

For those that might not understand what Harlan's talking about, consider
these 2 line entries:

100...15:30
1001...5:30

Those 2 lines are not equal but when concatenated they a

10015:30
10015:30

But of course if the 15:30 and 5:30 are times, rather than text strings,
the concatenated values will be different:
1000.645833333333333
10010.229166666666667
--
David Biddulph


Yeah, that wasn't the best data to use for an example but I think people
will understand the concept.

--
Biff
Microsoft Excel MVP

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.