Create Unique ID # for ALOT of Data

[cazadores]

Hello,

Have been struggling with this for some time.

Basically- we have land owners who own multiple properties across different counties.

I may not uncover all of what one land owner owns - and so assigning a unique ID # to them proves difficult.

I need to assign each individual NAME with an ID #.

I then wish to create a unique PROPERTY ID - that will incorporate the above NAME ID #.

So all together I need:

one ID # for the Individual Owner

one ID # for the properties (that incorporates the Individual Owner ID #)

I then need a method to check NEW lists for EXISTING owners.

Any help is appreciated. It has been a nightmare!

 

[kweaver]

Just a thought, if I understand part of the issue, to consider.
If you have the names (with duplicates) in column A (A1 header), and you create an initial ID for the first person (e.g., 1000) in B2, then this formula in B3 (and fill down) will retain the ID for that person and increment IDs for the others.

=IF(ISNA(MATCH(A3,A2:$A$2,0)),MAX(B2:$B$2)+1,VLOOKUP(A3,A2:$B$2,2,FALSE))

Any help?

 

[cazadores]

Yes that helped solving the Unique ID problem for the owners.

Now I need to create an ID for the individual properties they own. Basically:

"Owner 1" owns in towns "X,Y,Z"

I need an ID for each "X", "Y", "Z" that also includes the "Owner 1" ID.

"Owner 1-X"

"Owner 1-Y"

"Owner 1 -Z"

Something like this.

 

[kweaver]

Is this over-simplifying it?

Excel 2010
	A	B	C	D
1	Names	Unique ID	Property	By Owner ID
2	Owner 1	1000	Property X	Owner 1-1000-Property X
3	Owner 2	1001	Property A	Owner 2-1001-Property A
4	Owner 3	1002	Property W	Owner 3-1002-Property W
5	Owner 1	1000	Property Y	Owner 1-1000-Property Y
6	Owner 3	1002	Property V	Owner 3-1002-Property V
7	Owner 2	1001	Property B	Owner 2-1001-Property B
8	Owner 5	1003	Properdy D	Owner 5-1003-Properdy D
9	Owner 2	1001	Property C	Owner 2-1001-Property C
10	Owner 5	1003	Property E	Owner 5-1003-Property E
11	Owner 6	1004	Property P	Owner 6-1004-Property P
Sheet2

Cell Formulas
Range	Formula
B3	=IF(ISNA(MATCH(A3,A2:$A$2,0)),MAX(B2:$B$2)+1,VLOOKUP(A3,A2:$B$2,2,FALSE))
D2	=A2&"-"&B2&"-"&C2

 

[cazadores]

Yes and no. I would only use the Unique ID within the Property ID.

Basically- one owner can own many properties. Each Property needs to be unique - and also contain the singular Owner Unique ID.

 

[kweaver]

Compared to what I did, what SHOULD it be?