How to create unique item ID's from a list where items might be duplicate (or more)

[mikesch236]

Hi all,

I've googled and searched for 2 days now but am not finding the answers I am looking for ...

I am trying to build a customer history list for a small bike shop. With each sale, the customer's name, address, invoice number, items bought etc. get automatically added to a sheet in the workbook.
Given that there are return customers, I need to build a macro that will find the duplicates and assign a unique customer ID to each individual customer - I do not want to delete duplicates (I've found snippets on how to do that) but need to keep all as the items bought will typically be different

sample data:

[TABLE="width: 404"]
<tbody>[TR]
[TD]Kd.Eintrag[/TD]
[TD]Re.Nr.[/TD]
[TD]Datum[/TD]
[TD]Adresse 1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]150229[/TD]
[TD]20/02/2015[/TD]
[TD]Markus Vögl[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]150264[/TD]
[TD]24/02/2015[/TD]
[TD]Maria Beiwinkler-Stürzer[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]150281[/TD]
[TD]25/02/2015[/TD]
[TD]Franziska Schnell[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]150294[/TD]
[TD]26/02/2015[/TD]
[TD]Claudia Sahm[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]150316[/TD]
[TD]03/05/2015[/TD]
[TD]Mike Schroeder[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]150315[/TD]
[TD]03/05/2015[/TD]
[TD]Michaela Vollmer[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]150317[/TD]
[TD]03/05/2015[/TD]
[TD]Mike Schroeder[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]150318[/TD]
[TD]03/05/2015[/TD]
[TD]Mike Schroeder[/TD]
[/TR]
</tbody>[/TABLE]


Rows 1, 2, 3, 4 and 7 are unique and need to get a unique ID (eg. incrementally counting up from 1). Rows 6, 8 and 9 are the same customer so all 3 need to be assigned the same unique ID (ID's would be eg. in column E)

Any ideas on how to do this? I am fairly new at VBA programming so don't know what all is possible.

Thanks for any help!
Mike

PS: sorry, using Win 8.1 (but target is Win7) and Excel 2013

 

[SteveO59L]

have you considered using a pivot table?

how do you know that you don't have 2 customers with the same name?

 

[mikesch236]

sorry, error above .... obviously rows 1, 2, 3, 4 and 6 are unique, and 5, 7 and 8 are dupe's - I promise I'll learn to count soon

 

[mikesch236]

have you considered using a pivot table?

how do you know that you don't have 2 customers with the same name?

on the second question - there are more fields identifying the customer - street adress, town etc.

on #1 I've thought about a pivot, but given the sales customer list grows every day and will at some point (hopefully!) get very long, I'm not sure that's a viable solution long term

thanks!

 

[konew1]

Here is a solution, but be aware that all excel solutions for your problem are only as good as the consistency of the data entry. If Mike Schroeder is ever entered as Michael Schroeder it will get a different number

Excel Workbook
	A	B	C	D	E
1	Kd.Eintrag	Re.Nr.	Datum	Adresse 1
2	1	150229	20/02/2015	Markus Vgl	1
3	2	150264	24/02/2015	Maria Beiwinkler-Strzer	2
4	3	150281	25/02/2015	Franziska Schnell	3
5	4	150294	26/02/2015	Claudia Sahm	4
6	5	150316	3/05/2015	Mike Schroeder	5
7	6	150315	3/05/2015	Michaela Vollmer	6
8	7	150317	3/05/2015	Mike Schroeder	5
9	8	150318	3/05/2015	Mike Schroeder	5
Sheet1

 

[mikesch236]

thanks!

and yes I'm fully aware of the hickups that false data entry can introduce