How to assign a number value to text within worksheet

[lizzae]

I need to count the number of times a value appears in a row and assign a number that tells which occurence of the text it is.
For example

Name Address
Jim 123 Smith Street
Jane 123 Smith Street
Bob 543 Apple Street
Mary 543 Apple Street

I would like a way to insert a column that puts a 1 next to Jim and a 2 next to Jane, a 1 next to Bob and a 2 next to Mary and so on.
Ultimately, I want to sort the list and delete all the twos, thus deleting the duplicate address entry.
Thank you

 

[mchel_27]

I think, to reach your ultimate goal here, it would be easier just to use Advanced Filter. Do you have all of the information you listed here in one single column?

 

[mvptomlinson]

Hi Lizzae,

Assuming your names are in column A (A2:Axx), and addresses in column B (B2:Bxx), then in C2 put the following formula:

=COUNTIF($B$2:B2,B2)

Fill that down as many rows as you have addresses. This formula will only show 2's (or higher) for EXACT address matches.. meaning spelling, punctuation, abbreviation, etc.

 

[Gerald Higgins]

OR, use a helper column.
Assuming address is in col B . . .
Put a 1 in C1.
Put this formula in C2

=if(b2=b1,C1+1,1)

and copy down as far as required. Then use Data, Filter to select all the 1s.

 

[lizzae]

For instance,
I have several hundred rows of data, with at least a dozen different columns, with personal information
I need to count how many times a number is occuring within one of these columns

Example:
Name Address Value
Jane Smith 123 Smith Street 345
Bill Smith 123 Smith Street 345
Sarah Doe 387 Elm St 356
Hank Doe 387 Elm St 356
John Zeller 98 Apple Road 879
Amy Zeller 98 Apple Road 879

This is a small example, my spreadsheet actually has several more columns of information. However, I need a formula that will let me identify which is the first occurence of a number in the "Value" field and which is the second. I need to remove all of the second occurences of that value.


Will the suggested formula still work on such a large spreadsheet?

 

[Gerald Higgins]

Will the suggested formula still work on such a large spreadsheet?

Yep.

 

[c_m_s_jr]

If you are using Excel 2007 there is an ability on the "Data" portion of the ribbon called "Remove dupliactes"

 

[lizzae]

It looks like the suggested formula does a good job of telling whether a column matches the one above it.
However, is there a way to just simply assign a number to each occurence of text, no matter the order in the sheet?
For instance,
Number Count
123 1
456 1
789 1
123 2
674 1
246 1
789 2
123 3

 

[c_m_s_jr]

Does it really matter what the number is as long as it is not 1, since you will be deleting all of the duplicates anyway?

Perhaps:

=IF(COUNTIF($B$2:$B$100,B2)>1,"DUPLICATE","")

 

[lizzae]

C M S -
This would work perfectly if it didn't also label the first occurence as a duplicate. I'd like to be able to retain 1 occurence and remove all the additional occurences. Being able to insert a count will allow me to sort and remove anything that appears more than once.