The 4th column D to be filler.

[Guest]

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.

 

[Tyro]

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

 

[Guest]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Harlan Grove]

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
A116, try

D1:
1

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

 

[Guest]

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.

 

[T. Valko]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

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.

 

[Harlan Grove]

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.

 

[Harlan Grove]

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))

 

[T. Valko]

...

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 are:

10015:30
10015:30

 

[Bob Phillips]

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>

 

[Guest]

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.

 

[David Biddulph]

....

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 are:

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

 

[Harlan Grove]

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.

 

[Guest]

Thankyou Harlan Just what I required.

....

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))