need help creating masterid's for duplicate records with unique id's.

[bke1323]

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel

Our unique identifier for creating an account is email address and people's email address tends to change often. So I've identified customers with the same name and address with multiple accounts. I would like to create a formula that will create a master id to connect these different accounts.

I have all the duplicates and they are grouped together correctly, but I am not sure how to create a formula that will create the same id per each dup group. Data is organized like so...

A2 = M (for first occurrence of the record)
A3 = S (for first duplicate occurrence of the record)
A4 = (blank row)
A5 = M (starts the series over for the next customer)

So basically row1 customer, row2 dup customer of row1, row3 blank row that shows a visual break before the next customer and it's duplicates.

85% of the time it goes customer, duplicate, blank, but in some cases there are multiple duplicates or "S" records.

Again I am trying to create a master id that would be the same for record "M" and it's duplicate "S" records which will be between 1 record and 5 records at the most.

Thanks for the help!

 

[John McGhie]

If you have less than about a thousand rows, it would be quicker to paste
the Customer IDs in.

Otherwise:

1) Decide which columns will make the record unique. E.g. Customer Surname
+ Customer First Name, + Customer Street

2) Add a column and concatenate the columns from step 1 into that column
(see CONCATENATE in the Excel Help).

3) Use Copy Unique Rows to copy just the unique values from the
concatenated column out to a separate table. (See the Excel help:
"Filter out the duplicate values and calculate the total number of unique
values")

4) Add a column to the new table.

5) Use Fill Down to number each of the unique rows with a number. That's
your Customer ID.

6) Add Another column to the main source table.

7) Now use VLOOKUP to bring the Customer ID you just generated back into
the new column in the main source table. Look up VLOOKUP in the Excel Help.

This will return the same Customer ID for each entry in the main table.

Hope this helps

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel

Our unique identifier for creating an account is email address and people's
email address tends to change often. So I've identified customers with the
same name and address with multiple accounts. I would like to create a formula
that will create a master id to connect these different accounts.

I have all the duplicates and they are grouped together correctly, but I am
not sure how to create a formula that will create the same id per each dup
group. Data is organized like so...

A2 = M (for first occurrence of the record)
A3 = S (for first duplicate occurrence of the record)
A4 = (blank row)
A5 = M (starts the series over for the next customer)

So basically row1 customer, row2 dup customer of row1, row3 blank row that
shows a visual break before the next customer and it's duplicates.

85% of the time it goes customer, duplicate, blank, but in some cases there
are multiple duplicates or "S" records.

Again I am trying to create a master id that would be the same for record "M"
and it's duplicate "S" records which will be between 1 record and 5 records at
the most.

Thanks for the help!

--

This email is my business email -- Please do not email me about forum
matters unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]