software

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 quot;SheetChangedquot; 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=quot;quot;,quot;quot;,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!
quot;Steve Barnettquot; wrote:

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

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 quot;allowedquot; 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 amp; 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

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

Peter T

quot;Peter Tquot; lt;peter_t@discussionsgt; wrote in message
...
gt; Hi Steve,
gt;
gt; To cover all the scenarios you mention might be impractical, but for what
gt; purpose/usage do you need unique row identifiers, in addition to the fact
gt; you can always read row numbers.
gt;
gt; Maybe you could define worksheet level named ranges, these would move as
you
gt; insert rows though would end up with meaningless names for deleted ranges
gt; (#REF). Would you want 10k names though (but much better than 10k
comments).
gt;
gt; If you were quot;allowedquot; to insert an extra column (hidden perhaps) populate
gt; with row numbers as values. Store the highest number somewhere (a cell or
gt; named formula). To cater for row inserts amp; new rows at the end, in
selection
gt; and/or change events check the identifier cell has a value. If not
increment
gt; the stored highest number and place same as the new identifier.
gt;
gt; But with the limitations imposed by your client you are a bit strapped!
gt;
gt; Regards,
gt; Peter T
gt;
gt; quot;Steve Barnettquot; gt; wrote in message
gt; ...
gt; gt; I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I
now
gt; gt; need to be able to identify each row via a unique identifier, but am
gt; having
gt; gt; trouble working out what to do when new rows are added or when existing
gt; rows
gt; gt; are copied and paste.
gt; gt;
gt; gt; New rows and copied rows should get new unique identifiers, but I can
see
gt; no
gt; gt; way of doing this. Does anyone have any suggestions? There is nothing
gt; unique
gt; gt; about the data that I can hang on to and the quot;SheetChangedquot; event does
not
gt; gt; fire for inserted rows in Excel 2000.
gt; gt;
gt; gt; This spreadsheet is owned by one of our clients, so I can't add new
gt; gt; worksheets or columns to the existing workbook - I can just add cell
gt; gt; comments (unless you have a better suggestion).
gt; gt;
gt; gt; Can anyone help?
gt; gt;
gt; gt; Thanks
gt; gt; Steve
gt; gt;
gt; gt;
gt;
gt;
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,quot;quot;,COUNTIF($C$2:$C2,FALSE))
ID!B2=ROW()-1
ID!C2=COUNTIF($B2,Sheet1!$A:$A)gt;0
ID!D2=IF(ISERROR(INDEX($B:$B,MATCH(ROW()-1,$A:$A,0),)),quot;quot;,TEXT(INDEX($B:$B,MATCH(ROW()-1,$A:$A,0),),quot;00000quot;))
Copy cells A22 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,quot;gt;quot;amp;quot;quot;quot;quot;)-1,1)

On your working sheet, select a range in ID column, and apply Data
Validationgt;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.laanemetslt;atgt;tarkon.ee )quot;Steve Barnettquot; gt; wrote in message
...
gt;I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I now
gt;need to be able to identify each row via a unique identifier, but am having
gt;trouble working out what to do when new rows are added or when existing
gt;rows are copied and paste.
gt;
gt; New rows and copied rows should get new unique identifiers, but I can see
gt; no way of doing this. Does anyone have any suggestions? There is nothing
gt; unique about the data that I can hang on to and the quot;SheetChangedquot; event
gt; does not fire for inserted rows in Excel 2000.
gt;
gt; This spreadsheet is owned by one of our clients, so I can't add new
gt; worksheets or columns to the existing workbook - I can just add cell
gt; comments (unless you have a better suggestion).
gt;
gt; Can anyone help?
gt;
gt; Thanks
gt; Steve
gt;
I also need to quot;keepquot; 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 quot;uniquelyquot; 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 quot;namequot; (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

quot;Peter Tquot; lt;peter_t@discussionsgt; wrote in message
...
gt; Afraid my suggestion about storing values etc doesn't cater for
gt; possibility
gt; of entire row being copied ):-
gt;
gt; Peter T
gt;
gt; quot;Peter Tquot; lt;peter_t@discussionsgt; wrote in message
gt; ...
gt;gt; Hi Steve,
gt;gt;
gt;gt; To cover all the scenarios you mention might be impractical, but for what
gt;gt; purpose/usage do you need unique row identifiers, in addition to the fact
gt;gt; you can always read row numbers.
gt;gt;
gt;gt; Maybe you could define worksheet level named ranges, these would move as
gt; you
gt;gt; insert rows though would end up with meaningless names for deleted ranges
gt;gt; (#REF). Would you want 10k names though (but much better than 10k
gt; comments).
gt;gt;
gt;gt; If you were quot;allowedquot; to insert an extra column (hidden perhaps) populate
gt;gt; with row numbers as values. Store the highest number somewhere (a cell or
gt;gt; named formula). To cater for row inserts amp; new rows at the end, in
gt; selection
gt;gt; and/or change events check the identifier cell has a value. If not
gt; increment
gt;gt; the stored highest number and place same as the new identifier.
gt;gt;
gt;gt; But with the limitations imposed by your client you are a bit strapped!
gt;gt;
gt;gt; Regards,
gt;gt; Peter T
gt;gt;
gt;gt; quot;Steve Barnettquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I
gt; now
gt;gt; gt; need to be able to identify each row via a unique identifier, but am
gt;gt; having
gt;gt; gt; trouble working out what to do when new rows are added or when existing
gt;gt; rows
gt;gt; gt; are copied and paste.
gt;gt; gt;
gt;gt; gt; New rows and copied rows should get new unique identifiers, but I can
gt; see
gt;gt; no
gt;gt; gt; way of doing this. Does anyone have any suggestions? There is nothing
gt;gt; unique
gt;gt; gt; about the data that I can hang on to and the quot;SheetChangedquot; event does
gt; not
gt;gt; gt; fire for inserted rows in Excel 2000.
gt;gt; gt;
gt;gt; gt; This spreadsheet is owned by one of our clients, so I can't add new
gt;gt; gt; worksheets or columns to the existing workbook - I can just add cell
gt;gt; gt; comments (unless you have a better suggestion).
gt;gt; gt;
gt;gt; gt; Can anyone help?
gt;gt; gt;
gt;gt; gt; Thanks
gt;gt; gt; Steve
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;
gt;
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
Stevequot;paulquot; gt; wrote in message
...
gt; in a cell to the right somewhere in row 1 type =If(A1=quot;quot;,quot;quot;,row(a1)),and
gt; copy
gt; down,as long as a has something in it you will get the row number,this
gt; will
gt; work for al data as long as you dont insert rows.if you did insert a row
gt; say
gt; between 12 and 13 then the new row would be blank 12 would remain 12 and
gt; 13
gt; would become 14,you could copy the formula down each row would be
gt; indentified
gt; but evrything below the inserted would be different than before...does
gt; that
gt; help?
gt; --
gt; paul
gt; remove nospam for email addy!
gt;
gt;
gt;
gt; quot;Steve Barnettquot; wrote:
gt;
gt;gt; I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I now
gt;gt; need to be able to identify each row via a unique identifier, but am
gt;gt; having
gt;gt; trouble working out what to do when new rows are added or when existing
gt;gt; rows
gt;gt; are copied and paste.
gt;gt;
gt;gt; New rows and copied rows should get new unique identifiers, but I can see
gt;gt; no
gt;gt; way of doing this. Does anyone have any suggestions? There is nothing
gt;gt; unique
gt;gt; about the data that I can hang on to and the quot;SheetChangedquot; event does
gt;gt; not
gt;gt; fire for inserted rows in Excel 2000.
gt;gt;
gt;gt; This spreadsheet is owned by one of our clients, so I can't add new
gt;gt; worksheets or columns to the existing workbook - I can just add cell
gt;gt; comments (unless you have a better suggestion).
gt;gt;
gt;gt; Can anyone help?
gt;gt;
gt;gt; Thanks
gt;gt; Steve
gt;gt;
gt;gt;
gt;gt;
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
Stevequot;Arvi Laanemetsquot; gt; wrote in message
...
gt; Hi
gt;
gt; Not exactly what you described, but near enough:
gt;
gt; You have a sheet with column ID, where you need unique identificators for
gt; every row.
gt;
gt; Add a sheet ID, with columns FreeNr, Nr, Used, ID (headings in row 1)
gt; ID!A2=IF($C2=TRUE,quot;quot;,COUNTIF($C$2:$C2,FALSE))
gt; ID!B2=ROW()-1
gt; ID!C2=COUNTIF($B2,Sheet1!$A:$A)gt;0
gt; ID!D2=IF(ISERROR(INDEX($B:$B,MATCH(ROW()-1,$A:$A,0),)),quot;quot;,TEXT(INDEX($B:$B,MATCH(ROW()-1,$A:$A,0),),quot;00000quot;))
gt; Copy cells A22 down for some reasonable amount of rows (you can expand
gt; this table later, whenever you run out of free ID's)
gt;
gt; Define a named range
gt; ID=OFFSET(ID!$D$1,1,,COUNTIF(ID!$D:$D,quot;gt;quot;amp;quot;quot;quot;quot;)-1,1)
gt;
gt; On your working sheet, select a range in ID column, and apply Data
gt; Validationgt;List with source =ID
gt;
gt; Whenever you add an entry, you are allowed only to enter (manually or from
gt; dropdown) unused ID's .
gt; NB! You can copy a non-unique value into ID column although (p.e. when
gt; coping rows). You can use conditional formatting to indicate such
gt; non-unicue ID values, p.e. through different font color - so you can
gt; change them.
gt;
gt;
gt; --
gt; Arvi Laanemets
gt; ( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )
gt;
gt;
gt; quot;Steve Barnettquot; gt; wrote in message
gt; ...
gt;gt;I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I now
gt;gt;need to be able to identify each row via a unique identifier, but am
gt;gt;having trouble working out what to do when new rows are added or when
gt;gt;existing rows are copied and paste.
gt;gt;
gt;gt; New rows and copied rows should get new unique identifiers, but I can see
gt;gt; no way of doing this. Does anyone have any suggestions? There is nothing
gt;gt; unique about the data that I can hang on to and the quot;SheetChangedquot; event
gt;gt; does not fire for inserted rows in Excel 2000.
gt;gt;
gt;gt; This spreadsheet is owned by one of our clients, so I can't add new
gt;gt; worksheets or columns to the existing workbook - I can just add cell
gt;gt; comments (unless you have a better suggestion).
gt;gt;
gt;gt; Can anyone help?
gt;gt;
gt;gt; Thanks
gt;gt; Steve
gt;gt;
gt;
gt;
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.quot;Steve Barnettquot; gt; wrote in message
...
gt; 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.laanemetslt;atgt;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.

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