Unique identifier

I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I now
need to be able to identify each row via a unique identifier, but am having
trouble working out what to do when new rows are added or when existing rows
are copied and paste.

New rows and copied rows should get new unique identifiers, but I can see no
way of doing this. Does anyone have any suggestions? There is nothing unique
about the data that I can hang on to and the "SheetChanged" event does not
fire for inserted rows in Excel 2000.

This spreadsheet is owned by one of our clients, so I can't add new
worksheets or columns to the existing workbook - I can just add cell
comments (unless you have a better suggestion).

Can anyone help?

Thanks
Steve

in a cell to the right somewhere in row 1 type =If(A1="","",row(a1)),and copy
down,as long as a has something in it you will get the row number,this will
work for al data as long as you dont insert rows.if you did insert a row say
between 12 and 13 then the new row would be blank 12 would remain 12 and 13
would become 14,you could copy the formula down each row would be indentified
but evrything below the inserted would be different than before...does that
help?
--
paul
remove nospam for email addy!



"Steve Barnett" wrote:

> I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I now
> need to be able to identify each row via a unique identifier, but am having
> trouble working out what to do when new rows are added or when existing rows
> are copied and paste.
>
> New rows and copied rows should get new unique identifiers, but I can see no
> way of doing this. Does anyone have any suggestions? There is nothing unique
> about the data that I can hang on to and the "SheetChanged" event does not
> fire for inserted rows in Excel 2000.
>
> This spreadsheet is owned by one of our clients, so I can't add new
> worksheets or columns to the existing workbook - I can just add cell
> comments (unless you have a better suggestion).
>
> Can anyone help?
>
> Thanks
> Steve
>
>
>

Hi Steve,

To cover all the scenarios you mention might be impractical, but for what
purpose/usage do you need unique row identifiers, in addition to the fact
you can always read row numbers.

Maybe you could define worksheet level named ranges, these would move as you
insert rows though would end up with meaningless names for deleted ranges
(#REF). Would you want 10k names though (but much better than 10k comments).

If you were "allowed" to insert an extra column (hidden perhaps) populate
with row numbers as values. Store the highest number somewhere (a cell or
named formula). To cater for row inserts & new rows at the end, in selection
and/or change events check the identifier cell has a value. If not increment
the stored highest number and place same as the new identifier.

But with the limitations imposed by your client you are a bit strapped!

Regards,
Peter T

"Steve Barnett" <[email protected]> wrote in message
news:[email protected]...
> I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I now
> need to be able to identify each row via a unique identifier, but am
having
> trouble working out what to do when new rows are added or when existing
rows
> are copied and paste.
>
> New rows and copied rows should get new unique identifiers, but I can see
no
> way of doing this. Does anyone have any suggestions? There is nothing
unique
> about the data that I can hang on to and the "SheetChanged" event does not
> fire for inserted rows in Excel 2000.
>
> This spreadsheet is owned by one of our clients, so I can't add new
> worksheets or columns to the existing workbook - I can just add cell
> comments (unless you have a better suggestion).
>
> Can anyone help?
>
> Thanks
> Steve
>
>

Afraid my suggestion about storing values etc doesn't cater for possibility
of entire row being copied ):-

Peter T

"Peter T" <peter_t@discussions> wrote in message
news:#Apk#[email protected]...
> Hi Steve,
>
> To cover all the scenarios you mention might be impractical, but for what
> purpose/usage do you need unique row identifiers, in addition to the fact
> you can always read row numbers.
>
> Maybe you could define worksheet level named ranges, these would move as
you
> insert rows though would end up with meaningless names for deleted ranges
> (#REF). Would you want 10k names though (but much better than 10k
comments).
>
> If you were "allowed" to insert an extra column (hidden perhaps) populate
> with row numbers as values. Store the highest number somewhere (a cell or
> named formula). To cater for row inserts & new rows at the end, in
selection
> and/or change events check the identifier cell has a value. If not
increment
> the stored highest number and place same as the new identifier.
>
> But with the limitations imposed by your client you are a bit strapped!
>
> Regards,
> Peter T
>
> "Steve Barnett" <[email protected]> wrote in message
> news:[email protected]...
> > I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I
now
> > need to be able to identify each row via a unique identifier, but am
> having
> > trouble working out what to do when new rows are added or when existing
> rows
> > are copied and paste.
> >
> > New rows and copied rows should get new unique identifiers, but I can
see
> no
> > way of doing this. Does anyone have any suggestions? There is nothing
> unique
> > about the data that I can hang on to and the "SheetChanged" event does
not
> > fire for inserted rows in Excel 2000.
> >
> > This spreadsheet is owned by one of our clients, so I can't add new
> > worksheets or columns to the existing workbook - I can just add cell
> > comments (unless you have a better suggestion).
> >
> > Can anyone help?
> >
> > Thanks
> > Steve
> >
> >
>
>

Hi

Not exactly what you described, but near enough:

You have a sheet with column ID, where you need unique identificators for
every row.

Add a sheet ID, with columns FreeNr, Nr, Used, ID (headings in row 1)
ID!A2=IF($C2=TRUE,"",COUNTIF($C$2:$C2,FALSE))
ID!B2=ROW()-1
ID!C2=COUNTIF($B2,Sheet1!$A:$A)>0
ID!D2=IF(ISERROR(INDEX($B:$B,MATCH(ROW()-1,$A:$A,0),)),"",TEXT(INDEX($B:$B,MATCH(ROW()-1,$A:$A,0),),"00000"))
Copy cells A2:D2 down for some reasonable amount of rows (you can expand
this table later, whenever you run out of free ID's)

Define a named range
ID=OFFSET(ID!$D$1,1,,COUNTIF(ID!$D:$D,">"&"""")-1,1)

On your working sheet, select a range in ID column, and apply Data
Validation>List with source =ID

Whenever you add an entry, you are allowed only to enter (manually or from
dropdown) unused ID's .
NB! You can copy a non-unique value into ID column although (p.e. when
coping rows). You can use conditional formatting to indicate such non-unicue
ID values, p.e. through different font color - so you can change them.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )


"Steve Barnett" <[email protected]> wrote in message
news:[email protected]...
>I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I now
>need to be able to identify each row via a unique identifier, but am having
>trouble working out what to do when new rows are added or when existing
>rows are copied and paste.
>
> New rows and copied rows should get new unique identifiers, but I can see
> no way of doing this. Does anyone have any suggestions? There is nothing
> unique about the data that I can hang on to and the "SheetChanged" event
> does not fire for inserted rows in Excel 2000.
>
> This spreadsheet is owned by one of our clients, so I can't add new
> worksheets or columns to the existing workbook - I can just add cell
> comments (unless you have a better suggestion).
>
> Can anyone help?
>
> Thanks
> Steve
>

I also need to "keep" the unique identifier once it's been set.

The purpose behind this is that I need to copy some data from the
spreadsheet in to a database. There is nothing in the rows of the
spreadsheet that "uniquely" identifies it so there is nothing that I can
hold on to that makes the connection between the row in the spreadsheet and
the record in the database.

Theory said that, if I could put a unique identifier in each row and, once
set, that identifier didn't change (so I can't use row number) then I had
something I could make the connection with.

I wonder if I could fiddle it with named ranges? Wonder if this would
work... Initially give a cell in every row a "name" (Say row-nnnnn). Then,
when the user inserts rows and copies and pastes stuff around, the named
range shouldn't change - it'll stick with the original cell. Then, when I
close the spreadsheet, I scan down the column with the named ranges,
checking to make sure that every row has a range name (if that's possible).
If I find a cell without a name, I add one.

Must go and play...

Thanks
Steve




"Peter T" <peter_t@discussions> wrote in message
news:%[email protected]...
> Afraid my suggestion about storing values etc doesn't cater for
> possibility
> of entire row being copied ):-
>
> Peter T
>
> "Peter T" <peter_t@discussions> wrote in message
> news:#Apk#[email protected]...
>> Hi Steve,
>>
>> To cover all the scenarios you mention might be impractical, but for what
>> purpose/usage do you need unique row identifiers, in addition to the fact
>> you can always read row numbers.
>>
>> Maybe you could define worksheet level named ranges, these would move as
> you
>> insert rows though would end up with meaningless names for deleted ranges
>> (#REF). Would you want 10k names though (but much better than 10k
> comments).
>>
>> If you were "allowed" to insert an extra column (hidden perhaps) populate
>> with row numbers as values. Store the highest number somewhere (a cell or
>> named formula). To cater for row inserts & new rows at the end, in
> selection
>> and/or change events check the identifier cell has a value. If not
> increment
>> the stored highest number and place same as the new identifier.
>>
>> But with the limitations imposed by your client you are a bit strapped!
>>
>> Regards,
>> Peter T
>>
>> "Steve Barnett" <[email protected]> wrote in message
>> news:[email protected]...
>> > I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I
> now
>> > need to be able to identify each row via a unique identifier, but am
>> having
>> > trouble working out what to do when new rows are added or when existing
>> rows
>> > are copied and paste.
>> >
>> > New rows and copied rows should get new unique identifiers, but I can
> see
>> no
>> > way of doing this. Does anyone have any suggestions? There is nothing
>> unique
>> > about the data that I can hang on to and the "SheetChanged" event does
> not
>> > fire for inserted rows in Excel 2000.
>> >
>> > This spreadsheet is owned by one of our clients, so I can't add new
>> > worksheets or columns to the existing workbook - I can just add cell
>> > comments (unless you have a better suggestion).
>> >
>> > Can anyone help?
>> >
>> > Thanks
>> > Steve
>> >
>> >
>>
>>
>
>

Problem is the unique identifier needs to identify the row it was originally
attached to. When the user inserts new rows, the unique identifier must NOT
change. I'm using this as a way of connecting rows in a spreadsheet to
records in a database.

Thanks
Steve


"paul" <[email protected]> wrote in message
news:[email protected]...
> in a cell to the right somewhere in row 1 type =If(A1="","",row(a1)),and
> copy
> down,as long as a has something in it you will get the row number,this
> will
> work for al data as long as you dont insert rows.if you did insert a row
> say
> between 12 and 13 then the new row would be blank 12 would remain 12 and
> 13
> would become 14,you could copy the formula down each row would be
> indentified
> but evrything below the inserted would be different than before...does
> that
> help?
> --
> paul
> remove nospam for email addy!
>
>
>
> "Steve Barnett" wrote:
>
>> I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I now
>> need to be able to identify each row via a unique identifier, but am
>> having
>> trouble working out what to do when new rows are added or when existing
>> rows
>> are copied and paste.
>>
>> New rows and copied rows should get new unique identifiers, but I can see
>> no
>> way of doing this. Does anyone have any suggestions? There is nothing
>> unique
>> about the data that I can hang on to and the "SheetChanged" event does
>> not
>> fire for inserted rows in Excel 2000.
>>
>> This spreadsheet is owned by one of our clients, so I can't add new
>> worksheets or columns to the existing workbook - I can just add cell
>> comments (unless you have a better suggestion).
>>
>> Can anyone help?
>>
>> Thanks
>> Steve
>>
>>
>>

They won't let me add new sheets or columns. To be fair to them, this
spreadsheet has grown out of all control (it's over 50Mb now) and has been
messed with by a lot of people.Because of this, there are strict controls on
what you can do to it and you need strong justifications if you're to add
new sheets or columns.

Thanks
Steve


"Arvi Laanemets" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> Not exactly what you described, but near enough:
>
> You have a sheet with column ID, where you need unique identificators for
> every row.
>
> Add a sheet ID, with columns FreeNr, Nr, Used, ID (headings in row 1)
> ID!A2=IF($C2=TRUE,"",COUNTIF($C$2:$C2,FALSE))
> ID!B2=ROW()-1
> ID!C2=COUNTIF($B2,Sheet1!$A:$A)>0
> ID!D2=IF(ISERROR(INDEX($B:$B,MATCH(ROW()-1,$A:$A,0),)),"",TEXT(INDEX($B:$B,MATCH(ROW()-1,$A:$A,0),),"00000"))
> Copy cells A2:D2 down for some reasonable amount of rows (you can expand
> this table later, whenever you run out of free ID's)
>
> Define a named range
> ID=OFFSET(ID!$D$1,1,,COUNTIF(ID!$D:$D,">"&"""")-1,1)
>
> On your working sheet, select a range in ID column, and apply Data
> Validation>List with source =ID
>
> Whenever you add an entry, you are allowed only to enter (manually or from
> dropdown) unused ID's .
> NB! You can copy a non-unique value into ID column although (p.e. when
> coping rows). You can use conditional formatting to indicate such
> non-unicue ID values, p.e. through different font color - so you can
> change them.
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>
>
> "Steve Barnett" <[email protected]> wrote in message
> news:[email protected]...
>>I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I now
>>need to be able to identify each row via a unique identifier, but am
>>having trouble working out what to do when new rows are added or when
>>existing rows are copied and paste.
>>
>> New rows and copied rows should get new unique identifiers, but I can see
>> no way of doing this. Does anyone have any suggestions? There is nothing
>> unique about the data that I can hang on to and the "SheetChanged" event
>> does not fire for inserted rows in Excel 2000.
>>
>> This spreadsheet is owned by one of our clients, so I can't add new
>> worksheets or columns to the existing workbook - I can just add cell
>> comments (unless you have a better suggestion).
>>
>> Can anyone help?
>>
>> Thanks
>> Steve
>>
>
>

Hi

Then it's past time to redesign it!

P.e. when there are tables, which are altered occasionally only, and which
at same time contain a lot of formulas, then you can split them into
separate workbook. In working workbook, you keep replicas of them, which are
generated through ODBC queries, and are refreshed on open - as result those
replicas contain only values, what may improve perfomance a lot.


"Steve Barnett" <[email protected]> wrote in message
news:[email protected]...
> They won't let me add new sheets or columns. To be fair to them, this

You need to add a single sheet with 4 columns of formulas. On your working
sheet, you have the ID column, or you have to add ti anyway.



--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

I fully agree, it should be scrapped and rewritten as a proper database app,
which is what it always should have been. Unfortunately, it started life as
a couple of hundred rows and just grew. As with all such systems, no one
really noticed until it started getting painful to add new entries and it
was too late by then, as they were committed.

My latest solution required named ranges on every row - I'm just not sure
yet whether I can take a cell address and find the named range that contains
it.

Steve


"Arvi Laanemets" <[email protected]> wrote in message
news:%[email protected]...
> Hi
>
> Then it's past time to redesign it!
>
> P.e. when there are tables, which are altered occasionally only, and which
> at same time contain a lot of formulas, then you can split them into
> separate workbook. In working workbook, you keep replicas of them, which
> are generated through ODBC queries, and are refreshed on open - as result
> those replicas contain only values, what may improve perfomance a lot.
>
>
> "Steve Barnett" <[email protected]> wrote in message
> news:[email protected]...
>> They won't let me add new sheets or columns. To be fair to them, this
>
> You need to add a single sheet with 4 columns of formulas. On your working
> sheet, you have the ID column, or you have to add ti anyway.
>
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>

The "values" I suggested adding won't change. To initialise put the row
number as a value in each cell of a (hidden) column. In future events if
there is no value the relative cell maybe something like -

thisrowIDcellInColA = Application.Max(Columns("A")) + 1

But would need to figure something if user copy/pastes the entire row.

Regards,
Peter T

"Steve Barnett" <[email protected]> wrote in message
news:##[email protected]...
> I also need to "keep" the unique identifier once it's been set.
>
> The purpose behind this is that I need to copy some data from the
> spreadsheet in to a database. There is nothing in the rows of the
> spreadsheet that "uniquely" identifies it so there is nothing that I can
> hold on to that makes the connection between the row in the spreadsheet
and
> the record in the database.
>
> Theory said that, if I could put a unique identifier in each row and, once
> set, that identifier didn't change (so I can't use row number) then I had
> something I could make the connection with.
>
> I wonder if I could fiddle it with named ranges? Wonder if this would
> work... Initially give a cell in every row a "name" (Say row-nnnnn). Then,
> when the user inserts rows and copies and pastes stuff around, the named
> range shouldn't change - it'll stick with the original cell. Then, when I
> close the spreadsheet, I scan down the column with the named ranges,
> checking to make sure that every row has a range name (if that's
possible).
> If I find a cell without a name, I add one.
>
> Must go and play...
>
> Thanks
> Steve
>
>
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%[email protected]...
> > Afraid my suggestion about storing values etc doesn't cater for
> > possibility
> > of entire row being copied ):-
> >
> > Peter T
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:#Apk#[email protected]...
> >> Hi Steve,
> >>
> >> To cover all the scenarios you mention might be impractical, but for
what
> >> purpose/usage do you need unique row identifiers, in addition to the
fact
> >> you can always read row numbers.
> >>
> >> Maybe you could define worksheet level named ranges, these would move
as
> > you
> >> insert rows though would end up with meaningless names for deleted
ranges
> >> (#REF). Would you want 10k names though (but much better than 10k
> > comments).
> >>
> >> If you were "allowed" to insert an extra column (hidden perhaps)
populate
> >> with row numbers as values. Store the highest number somewhere (a cell
or
> >> named formula). To cater for row inserts & new rows at the end, in
> > selection
> >> and/or change events check the identifier cell has a value. If not
> > increment
> >> the stored highest number and place same as the new identifier.
> >>
> >> But with the limitations imposed by your client you are a bit strapped!
> >>
> >> Regards,
> >> Peter T
> >>
> >> "Steve Barnett" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I
> > now
> >> > need to be able to identify each row via a unique identifier, but am
> >> having
> >> > trouble working out what to do when new rows are added or when
existing
> >> rows
> >> > are copied and paste.
> >> >
> >> > New rows and copied rows should get new unique identifiers, but I can
> > see
> >> no
> >> > way of doing this. Does anyone have any suggestions? There is nothing
> >> unique
> >> > about the data that I can hang on to and the "SheetChanged" event
does
> > not
> >> > fire for inserted rows in Excel 2000.
> >> >
> >> > This spreadsheet is owned by one of our clients, so I can't add new
> >> > worksheets or columns to the existing workbook - I can just add cell
> >> > comments (unless you have a better suggestion).
> >> >
> >> > Can anyone help?
> >> >
> >> > Thanks
> >> > Steve
> >> >
> >> >
> >>
> >>
> >
> >
>
>

When your only tool is a hammer, every problem looks like a nail. You are
hammering at this with the wrong tool, and exceeding the design capabilities
of a spreadsheet. This needs to be converted to a table in a database.
--
Ted

If only clients listened.


"TedMi" <[email protected]> wrote in message
news:[email protected]...
> When your only tool is a hammer, every problem looks like a nail. You are
> hammering at this with the wrong tool, and exceeding the design
> capabilities
> of a spreadsheet. This needs to be converted to a table in a database.
> --
> Ted
>

Think I'm there.

I initialise the spreadsheet by going down every row and creating a range
name (on a single cell in the row). I also save the range name in the cell
comment, so they're both the same.

When they are ready to submit the spreadsheet to me for processing, they run
an update process that goes down each row and checks to see whether the
range identified by the cell comment matches the current cell address. If it
does, everything is great and this is the same (logical)row that it was
before. It copes with the user inserting and deleting rows because I'm using
a named range, which moves up and down accordingly.

If the user inserts a new row and then copies an existing row in to it, the
new row will not have a range name and the cell address referenced by the
cell comment will not match the current cell, so I know this is a new row
and I can generate a new unique identifier, a new range name and a new cell
comment.

I'm about half way through the code and it seems to be holding water so far.
It's proving easier to code than to explain, I'm afraid.

Thanks for all the help.
Steve



"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> The "values" I suggested adding won't change. To initialise put the row
> number as a value in each cell of a (hidden) column. In future events if
> there is no value the relative cell maybe something like -
>
> thisrowIDcellInColA = Application.Max(Columns("A")) + 1
>
> But would need to figure something if user copy/pastes the entire row.
>
> Regards,
> Peter T
>
> "Steve Barnett" <[email protected]> wrote in message
> news:##[email protected]...
>> I also need to "keep" the unique identifier once it's been set.
>>
>> The purpose behind this is that I need to copy some data from the
>> spreadsheet in to a database. There is nothing in the rows of the
>> spreadsheet that "uniquely" identifies it so there is nothing that I can
>> hold on to that makes the connection between the row in the spreadsheet
> and
>> the record in the database.
>>
>> Theory said that, if I could put a unique identifier in each row and,
>> once
>> set, that identifier didn't change (so I can't use row number) then I had
>> something I could make the connection with.
>>
>> I wonder if I could fiddle it with named ranges? Wonder if this would
>> work... Initially give a cell in every row a "name" (Say row-nnnnn).
>> Then,
>> when the user inserts rows and copies and pastes stuff around, the named
>> range shouldn't change - it'll stick with the original cell. Then, when I
>> close the spreadsheet, I scan down the column with the named ranges,
>> checking to make sure that every row has a range name (if that's
> possible).
>> If I find a cell without a name, I add one.
>>
>> Must go and play...
>>
>> Thanks
>> Steve
>>
>>
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:%[email protected]...
>> > Afraid my suggestion about storing values etc doesn't cater for
>> > possibility
>> > of entire row being copied ):-
>> >
>> > Peter T
>> >
>> > "Peter T" <peter_t@discussions> wrote in message
>> > news:#Apk#[email protected]...
>> >> Hi Steve,
>> >>
>> >> To cover all the scenarios you mention might be impractical, but for
> what
>> >> purpose/usage do you need unique row identifiers, in addition to the
> fact
>> >> you can always read row numbers.
>> >>
>> >> Maybe you could define worksheet level named ranges, these would move
> as
>> > you
>> >> insert rows though would end up with meaningless names for deleted
> ranges
>> >> (#REF). Would you want 10k names though (but much better than 10k
>> > comments).
>> >>
>> >> If you were "allowed" to insert an extra column (hidden perhaps)
> populate
>> >> with row numbers as values. Store the highest number somewhere (a cell
> or
>> >> named formula). To cater for row inserts & new rows at the end, in
>> > selection
>> >> and/or change events check the identifier cell has a value. If not
>> > increment
>> >> the stored highest number and place same as the new identifier.
>> >>
>> >> But with the limitations imposed by your client you are a bit
>> >> strapped!
>> >>
>> >> Regards,
>> >> Peter T
>> >>
>> >> "Steve Barnett" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > I have a spreadsheet (Excel 2000) that contains around 10,000 rows.
>> >> > I
>> > now
>> >> > need to be able to identify each row via a unique identifier, but am
>> >> having
>> >> > trouble working out what to do when new rows are added or when
> existing
>> >> rows
>> >> > are copied and paste.
>> >> >
>> >> > New rows and copied rows should get new unique identifiers, but I
>> >> > can
>> > see
>> >> no
>> >> > way of doing this. Does anyone have any suggestions? There is
>> >> > nothing
>> >> unique
>> >> > about the data that I can hang on to and the "SheetChanged" event
> does
>> > not
>> >> > fire for inserted rows in Excel 2000.
>> >> >
>> >> > This spreadsheet is owned by one of our clients, so I can't add new
>> >> > worksheets or columns to the existing workbook - I can just add cell
>> >> > comments (unless you have a better suggestion).
>> >> >
>> >> > Can anyone help?
>> >> >
>> >> > Thanks
>> >> > Steve
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

I follow what you're doing but 10k comments!!! Surely client would accept a
helper column, very significantly less file size in an already big one.

Lightly tested, but seems to work -

Populate a helper column with row numbers as far down as necessary, in this
eg col-A

Sub setup()
For i = 1 To 58
Cells(i, 1) = i
Cells(i, 1).Name = Chr(39) & ActiveSheet.Name & "'!rowID" & i
Cells(i, 2) = Chr(i + 64)
Next
End Sub

' in the worksheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim bApos As Boolean
Dim i As Long
Dim lastRow As Long
Dim nMid As Long
Dim x As Long
Dim sPrefix As String
Dim rng As Range
Dim ar As Range
Dim nm As Name

On Error Resume Next
With Me.UsedRange
lastRow = .Rows.Count + .Rows(1).Row - 1
End With

x = Application.Max(Columns("A"))
sPrefix = Chr(39) & Me.Name & "'!rowID"
nMid = Len(sPrefix) + 1
For Each ar In Target.Areas
With ar
For i = .Rows(1).Row To .Rows(1).Row + .Rows.Count - 1
If i > lastRow Then Exit For
With Cells(i, 1)
Set nm = .Name
If nm Is Nothing Then
x = x + 1
.Name = sPrefix & x
.Value = x
Else
If Not bApos Then
If InStr(1, nm.Name, "'") = 0 Then nMid = nMid - 2
bApos = True
End If
If .Value <> Mid(nm.Name, nMid, 5) Then
.Value = Val(Mid(nm.Name, nMid, 5))
End If
Set nm = Nothing
End If
End With
Next
End With
Next
End Sub

Try inserting rows, copy paste rows etc

Regards,
Peter T

"Steve Barnett" <[email protected]> wrote in message
news:[email protected]...
> Think I'm there.
>
> I initialise the spreadsheet by going down every row and creating a range
> name (on a single cell in the row). I also save the range name in the cell
> comment, so they're both the same.
>
> When they are ready to submit the spreadsheet to me for processing, they
run
> an update process that goes down each row and checks to see whether the
> range identified by the cell comment matches the current cell address. If
it
> does, everything is great and this is the same (logical)row that it was
> before. It copes with the user inserting and deleting rows because I'm
using
> a named range, which moves up and down accordingly.
>
> If the user inserts a new row and then copies an existing row in to it,
the
> new row will not have a range name and the cell address referenced by the
> cell comment will not match the current cell, so I know this is a new row
> and I can generate a new unique identifier, a new range name and a new
cell
> comment.
>
> I'm about half way through the code and it seems to be holding water so
far.
> It's proving easier to code than to explain, I'm afraid.
>
> Thanks for all the help.
> Steve
>
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:[email protected]...
> > The "values" I suggested adding won't change. To initialise put the row
> > number as a value in each cell of a (hidden) column. In future events if
> > there is no value the relative cell maybe something like -
> >
> > thisrowIDcellInColA = Application.Max(Columns("A")) + 1
> >
> > But would need to figure something if user copy/pastes the entire row.
> >
> > Regards,
> > Peter T
> >
> > "Steve Barnett" <[email protected]> wrote in message
> > news:##[email protected]...
> >> I also need to "keep" the unique identifier once it's been set.
> >>
> >> The purpose behind this is that I need to copy some data from the
> >> spreadsheet in to a database. There is nothing in the rows of the
> >> spreadsheet that "uniquely" identifies it so there is nothing that I
can
> >> hold on to that makes the connection between the row in the spreadsheet
> > and
> >> the record in the database.
> >>
> >> Theory said that, if I could put a unique identifier in each row and,
> >> once
> >> set, that identifier didn't change (so I can't use row number) then I
had
> >> something I could make the connection with.
> >>
> >> I wonder if I could fiddle it with named ranges? Wonder if this would
> >> work... Initially give a cell in every row a "name" (Say row-nnnnn).
> >> Then,
> >> when the user inserts rows and copies and pastes stuff around, the
named
> >> range shouldn't change - it'll stick with the original cell. Then, when
I
> >> close the spreadsheet, I scan down the column with the named ranges,
> >> checking to make sure that every row has a range name (if that's
> > possible).
> >> If I find a cell without a name, I add one.
> >>
> >> Must go and play...
> >>
> >> Thanks
> >> Steve
> >>
> >>
> >>
> >>
> >> "Peter T" <peter_t@discussions> wrote in message
> >> news:%[email protected]...
> >> > Afraid my suggestion about storing values etc doesn't cater for
> >> > possibility
> >> > of entire row being copied ):-
> >> >
> >> > Peter T
> >> >
> >> > "Peter T" <peter_t@discussions> wrote in message
> >> > news:#Apk#[email protected]...
> >> >> Hi Steve,
> >> >>
> >> >> To cover all the scenarios you mention might be impractical, but for
> > what
> >> >> purpose/usage do you need unique row identifiers, in addition to the
> > fact
> >> >> you can always read row numbers.
> >> >>
> >> >> Maybe you could define worksheet level named ranges, these would
move
> > as
> >> > you
> >> >> insert rows though would end up with meaningless names for deleted
> > ranges
> >> >> (#REF). Would you want 10k names though (but much better than 10k
> >> > comments).
> >> >>
> >> >> If you were "allowed" to insert an extra column (hidden perhaps)
> > populate
> >> >> with row numbers as values. Store the highest number somewhere (a
cell
> > or
> >> >> named formula). To cater for row inserts & new rows at the end, in
> >> > selection
> >> >> and/or change events check the identifier cell has a value. If not
> >> > increment
> >> >> the stored highest number and place same as the new identifier.
> >> >>
> >> >> But with the limitations imposed by your client you are a bit
> >> >> strapped!
> >> >>
> >> >> Regards,
> >> >> Peter T
> >> >>
> >> >> "Steve Barnett" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > I have a spreadsheet (Excel 2000) that contains around 10,000
rows.
> >> >> > I
> >> > now
> >> >> > need to be able to identify each row via a unique identifier, but
am
> >> >> having
> >> >> > trouble working out what to do when new rows are added or when
> > existing
> >> >> rows
> >> >> > are copied and paste.
> >> >> >
> >> >> > New rows and copied rows should get new unique identifiers, but I
> >> >> > can
> >> > see
> >> >> no
> >> >> > way of doing this. Does anyone have any suggestions? There is
> >> >> > nothing
> >> >> unique
> >> >> > about the data that I can hang on to and the "SheetChanged" event
> > does
> >> > not
> >> >> > fire for inserted rows in Excel 2000.
> >> >> >
> >> >> > This spreadsheet is owned by one of our clients, so I can't add
new
> >> >> > worksheets or columns to the existing workbook - I can just add
cell
> >> >> > comments (unless you have a better suggestion).
> >> >> >
> >> >> > Can anyone help?
> >> >> >
> >> >> > Thanks
> >> >> > Steve
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Frustrating, previous code aimed to cater for possibility of needing
apostrophe's in worksheet names, required in case certain characters exist
in ther sheet name. But it's not reliable as is, eg in a sheet named
"Steve's" the name would include a total of 4 apostrophe's, not 3.

Need to define Worksheet level names correctly.
ActiveSheet.Names.Add "rowID" & i, Cells(i, 1)

In event code , amend

With Cells(i, 1)
Set nm = .Name
If nm Is Nothing Then
x = x + 1
Me.Names.Add "rowID" & x, Cells(i, 1)
.Value = x
Else
If Not bApos Then
nMid = InStr(1, nm.Name, "!") + 6
bApos = True
End If

If .Value <> Mid(nm.Name, nMid, 6) Then
.Value = Val(Mid(nm.Name, nMid, 6))
End If
Set nm = Nothing
End If
End With

Regards,
Peter T

"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> I follow what you're doing but 10k comments!!! Surely client would accept
a
> helper column, very significantly less file size in an already big one.
>
> Lightly tested, but seems to work -
>
> Populate a helper column with row numbers as far down as necessary, in
this
> eg col-A
>
> Sub setup()
> For i = 1 To 58
> Cells(i, 1) = i
> Cells(i, 1).Name = Chr(39) & ActiveSheet.Name & "'!rowID" & i
> Cells(i, 2) = Chr(i + 64)
> Next
> End Sub
>
> ' in the worksheet module
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim bApos As Boolean
> Dim i As Long
> Dim lastRow As Long
> Dim nMid As Long
> Dim x As Long
> Dim sPrefix As String
> Dim rng As Range
> Dim ar As Range
> Dim nm As Name
>
> On Error Resume Next
> With Me.UsedRange
> lastRow = .Rows.Count + .Rows(1).Row - 1
> End With
>
> x = Application.Max(Columns("A"))
> sPrefix = Chr(39) & Me.Name & "'!rowID"
> nMid = Len(sPrefix) + 1
> For Each ar In Target.Areas
> With ar
> For i = .Rows(1).Row To .Rows(1).Row + .Rows.Count - 1
> If i > lastRow Then Exit For
> With Cells(i, 1)
> Set nm = .Name
> If nm Is Nothing Then
> x = x + 1
> .Name = sPrefix & x
> .Value = x
> Else
> If Not bApos Then
> If InStr(1, nm.Name, "'") = 0 Then nMid = nMid - 2
> bApos = True
> End If
> If .Value <> Mid(nm.Name, nMid, 5) Then
> .Value = Val(Mid(nm.Name, nMid, 5))
> End If
> Set nm = Nothing
> End If
> End With
> Next
> End With
> Next
> End Sub
>
> Try inserting rows, copy paste rows etc
>
> Regards,
> Peter T
>
> "Steve Barnett" <[email protected]> wrote in message
> news:[email protected]...
> > Think I'm there.
> >
> > I initialise the spreadsheet by going down every row and creating a
range
> > name (on a single cell in the row). I also save the range name in the
cell
> > comment, so they're both the same.
> >
> > When they are ready to submit the spreadsheet to me for processing, they
> run
> > an update process that goes down each row and checks to see whether the
> > range identified by the cell comment matches the current cell address.
If
> it
> > does, everything is great and this is the same (logical)row that it was
> > before. It copes with the user inserting and deleting rows because I'm
> using
> > a named range, which moves up and down accordingly.
> >
> > If the user inserts a new row and then copies an existing row in to it,
> the
> > new row will not have a range name and the cell address referenced by
the
> > cell comment will not match the current cell, so I know this is a new
row
> > and I can generate a new unique identifier, a new range name and a new
> cell
> > comment.
> >
> > I'm about half way through the code and it seems to be holding water so
> far.
> > It's proving easier to code than to explain, I'm afraid.
> >
> > Thanks for all the help.
> > Steve
> >
> >
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:[email protected]...
> > > The "values" I suggested adding won't change. To initialise put the
row
> > > number as a value in each cell of a (hidden) column. In future events
if
> > > there is no value the relative cell maybe something like -
> > >
> > > thisrowIDcellInColA = Application.Max(Columns("A")) + 1
> > >
> > > But would need to figure something if user copy/pastes the entire row.
> > >
> > > Regards,
> > > Peter T
> > >
> > > "Steve Barnett" <[email protected]> wrote in message
> > > news:##[email protected]...
> > >> I also need to "keep" the unique identifier once it's been set.
> > >>
> > >> The purpose behind this is that I need to copy some data from the
> > >> spreadsheet in to a database. There is nothing in the rows of the
> > >> spreadsheet that "uniquely" identifies it so there is nothing that I
> can
> > >> hold on to that makes the connection between the row in the
spreadsheet
> > > and
> > >> the record in the database.
> > >>
> > >> Theory said that, if I could put a unique identifier in each row and,
> > >> once
> > >> set, that identifier didn't change (so I can't use row number) then I
> had
> > >> something I could make the connection with.
> > >>
> > >> I wonder if I could fiddle it with named ranges? Wonder if this would
> > >> work... Initially give a cell in every row a "name" (Say row-nnnnn).
> > >> Then,
> > >> when the user inserts rows and copies and pastes stuff around, the
> named
> > >> range shouldn't change - it'll stick with the original cell. Then,
when
> I
> > >> close the spreadsheet, I scan down the column with the named ranges,
> > >> checking to make sure that every row has a range name (if that's
> > > possible).
> > >> If I find a cell without a name, I add one.
> > >>
> > >> Must go and play...
> > >>
> > >> Thanks
> > >> Steve
> > >>
> > >>
> > >>
> > >>
> > >> "Peter T" <peter_t@discussions> wrote in message
> > >> news:%[email protected]...
> > >> > Afraid my suggestion about storing values etc doesn't cater for
> > >> > possibility
> > >> > of entire row being copied ):-
> > >> >
> > >> > Peter T
> > >> >
> > >> > "Peter T" <peter_t@discussions> wrote in message
> > >> > news:#Apk#[email protected]...
> > >> >> Hi Steve,
> > >> >>
> > >> >> To cover all the scenarios you mention might be impractical, but
for
> > > what
> > >> >> purpose/usage do you need unique row identifiers, in addition to
the
> > > fact
> > >> >> you can always read row numbers.
> > >> >>
> > >> >> Maybe you could define worksheet level named ranges, these would
> move
> > > as
> > >> > you
> > >> >> insert rows though would end up with meaningless names for deleted
> > > ranges
> > >> >> (#REF). Would you want 10k names though (but much better than 10k
> > >> > comments).
> > >> >>
> > >> >> If you were "allowed" to insert an extra column (hidden perhaps)
> > > populate
> > >> >> with row numbers as values. Store the highest number somewhere (a
> cell
> > > or
> > >> >> named formula). To cater for row inserts & new rows at the end, in
> > >> > selection
> > >> >> and/or change events check the identifier cell has a value. If not
> > >> > increment
> > >> >> the stored highest number and place same as the new identifier.
> > >> >>
> > >> >> But with the limitations imposed by your client you are a bit
> > >> >> strapped!
> > >> >>
> > >> >> Regards,
> > >> >> Peter T
> > >> >>
> > >> >> "Steve Barnett" <[email protected]> wrote in message
> > >> >> news:[email protected]...
> > >> >> > I have a spreadsheet (Excel 2000) that contains around 10,000
> rows.
> > >> >> > I
> > >> > now
> > >> >> > need to be able to identify each row via a unique identifier,
but
> am
> > >> >> having
> > >> >> > trouble working out what to do when new rows are added or when
> > > existing
> > >> >> rows
> > >> >> > are copied and paste.
> > >> >> >
> > >> >> > New rows and copied rows should get new unique identifiers, but
I
> > >> >> > can
> > >> > see
> > >> >> no
> > >> >> > way of doing this. Does anyone have any suggestions? There is
> > >> >> > nothing
> > >> >> unique
> > >> >> > about the data that I can hang on to and the "SheetChanged"
event
> > > does
> > >> > not
> > >> >> > fire for inserted rows in Excel 2000.
> > >> >> >
> > >> >> > This spreadsheet is owned by one of our clients, so I can't add
> new
> > >> >> > worksheets or columns to the existing workbook - I can just add
> cell
> > >> >> > comments (unless you have a better suggestion).
> > >> >> >
> > >> >> > Can anyone help?
> > >> >> >
> > >> >> > Thanks
> > >> >> > Steve
> > >> >> >
> > >> >> >
> > >> >>
> > >> >>
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>
>

"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
>I follow what you're doing but 10k comments!!! Surely client would accept a
> helper column, very significantly less file size in an already big one.
>

The client has a tight change control system. Adding macros and a comments
to a cell will be waved through as it's not perceived to add much to the
worksheet. Adding a column to an existing worksheet will require a full
review possibly taking anything up to six weeks to go through.

The person who would have to put this through change control has, very
honestly, admitted that he no longer has the will to even try as past
experience shows that the protection afforded to changing this spreadsheet
means that the first attempt to get the change agreed would almost certainly
fail.

It's frustrating, but very understandable. This spreadsheet has become key
to one of their major processes and they're utterly paranoid about it's
contents (comments and range names are not thought of as "contents").

Killer huh?
Steve

Understood. The event routine I posted can work same way with cell comments
with only slight modification. Obviously read and, if necessary add new
comment(s) or change the text in an existing comments in lieu of the cells.
Only other change would be instead of the MAX formula, maintain a counter
that only increments. This counter could be in a hidden shape or as a
"named" value.

In your first post you said you were looking for events to handle the
changes(subject to testing the event code I posted appears to work), or is
that effectively a macro that the client will not accept.

FWIW, a named array can store 10k elements (values), though don't think that
would serve any additional purpose.

For my curiosity and only if non sensitive, are you able to describe why
this 50mb DB can't be converted to something more conventional (and safe)
and the "process" it is used for.

Regards,
Peter T

"Steve Barnett" <[email protected]> wrote in message
news:eMAov#[email protected]...
> "Peter T" <peter_t@discussions> wrote in message
> news:[email protected]...
> >I follow what you're doing but 10k comments!!! Surely client would accept
a
> > helper column, very significantly less file size in an already big one.
> >
>
> The client has a tight change control system. Adding macros and a comments
> to a cell will be waved through as it's not perceived to add much to the
> worksheet. Adding a column to an existing worksheet will require a full
> review possibly taking anything up to six weeks to go through.
>
> The person who would have to put this through change control has, very
> honestly, admitted that he no longer has the will to even try as past
> experience shows that the protection afforded to changing this spreadsheet
> means that the first attempt to get the change agreed would almost
certainly
> fail.
>
> It's frustrating, but very understandable. This spreadsheet has become key
> to one of their major processes and they're utterly paranoid about it's
> contents (comments and range names are not thought of as "contents").
>
> Killer huh?
> Steve
>
>

My original logic said that I should intercept the change event for the
selected worksheet and, if new rows were inserted, I could pre-set whatever
markers and unique identifiers I wanted in the new rows. By doing this, I
could create the named ranges as the rows are added rather than having to
rely on a user to run a macro before they send us the spreadsheet.

This worked great on my PC, which runs Excel 2003. When I ran the same
macros on Excel 2000 I found that the worksheet change event wasn't being
fired when you insert rows - very irritating. Sometime around that
discovery, I decided that worksheet events were a waste of time in this
case.

The reason they won't convert is because there is just no time to do a
conversion and no experience within the department of anything other than
Excel. It really is a simple case of "the devil you know". As with so many
of these spreadsheets that I come across, they start out as a simple record
and grow massively as people find that they contain useful information. By
the time you discover that Excel isn't the most appropriate tool, it's too
late to switch to something else without a formal investment of time and
cash from the IT department.

Steve


"Peter T" <peter_t@discussions> wrote in message
news:%[email protected]...
> Understood. The event routine I posted can work same way with cell
> comments
> with only slight modification. Obviously read and, if necessary add new
> comment(s) or change the text in an existing comments in lieu of the
> cells.
> Only other change would be instead of the MAX formula, maintain a counter
> that only increments. This counter could be in a hidden shape or as a
> "named" value.
>
> In your first post you said you were looking for events to handle the
> changes(subject to testing the event code I posted appears to work), or is
> that effectively a macro that the client will not accept.
>
> FWIW, a named array can store 10k elements (values), though don't think
> that
> would serve any additional purpose.
>
> For my curiosity and only if non sensitive, are you able to describe why
> this 50mb DB can't be converted to something more conventional (and safe)
> and the "process" it is used for.
>
> Regards,
> Peter T
>
> "Steve Barnett" <[email protected]> wrote in message
> news:eMAov#[email protected]...
>> "Peter T" <peter_t@discussions> wrote in message
>> news:[email protected]...
>> >I follow what you're doing but 10k comments!!! Surely client would
>> >accept
> a
>> > helper column, very significantly less file size in an already big one.
>> >
>>
>> The client has a tight change control system. Adding macros and a
>> comments
>> to a cell will be waved through as it's not perceived to add much to the
>> worksheet. Adding a column to an existing worksheet will require a full
>> review possibly taking anything up to six weeks to go through.
>>
>> The person who would have to put this through change control has, very
>> honestly, admitted that he no longer has the will to even try as past
>> experience shows that the protection afforded to changing this
>> spreadsheet
>> means that the first attempt to get the change agreed would almost
> certainly
>> fail.
>>
>> It's frustrating, but very understandable. This spreadsheet has become
>> key
>> to one of their major processes and they're utterly paranoid about it's
>> contents (comments and range names are not thought of as "contents").
>>
>> Killer huh?
>> Steve
>>
>>
>
>