# Excel 2007 : Data comparison

1. ## Data comparison

Hi, first time using this forum, so here goes..
I have a large data sheet containing a column of numbers, some of which are repeated, i am trying to identify these as 'multiples' as some will appear twice, three times or more and some just once.

I need to keep the matches in the order they originally appear on so that the data in other columns remains in place.

I have seen once before a way to comapre the data using a simple formula so that when a number appears more than once it is shown in another column 1, 2, 3 etc depending on the number of times it appears.

Anyone able to help how this is achieved?

2. ## Re: Data comparison

Maybe Countif() ?

e.g.

=COUNTIF(A\$1:A1,A1) copied down.

This counts cumulatively as you go down the column... so it will say 1 at the first appearance, 2 at the second appearance, 3 at the third, etc...

or

=COUNTIF(A:A,A1) copied down

This will give the total count in the whole column each time an occurance happens... so if the list has the same id twice, both id's will be marked with a 2...

Hopefully one of these is what you were after.

3. ## Re: Data comparison

These did not appear to work, some were showing 2 when there was no second entry ?

4. ## Re: Data comparison

Did you adjust the ranges or refences to suit?

So if you are beginning in C2 and checking for matches in column B, then you would need to change to

=COUNTIF(B\$2:B2,B2) copied down

or

=COUNTIF(B:B,B2) copied down.

5. ## Re: Data comparison

Yes I copied in the formula you showed in your first reply above, all the data was in column A the formula in column B

6. ## Re: Data comparison

Go to Tools|Options and in the Calculation tab, make sure Automatic is set on.

If it is, then attach a sample showing how it is not working.

7. ## Re: Data comparison

here is a sample showing the result using the top formula,
09/275472M 1
09/275472M 1
09/275472M 2
09/278402X 3

as the numbers are in numerical order how is it showing 3 against 09/278402X when it appears only once? but the number above appears 3 times?

8. ## Re: Data comparison

I don't know what you are doing, but see attached....

9. ## Re: Data comparison

Thanks, see that works, so I shall try again, in the morning, thanks very much for you help.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1