Import problem

[Ted Rogers]

I have been asked to try and solve a problem that a friend has. (Please try
and ignore the workflow - I have suggested other ways of doing it but he
won't have it!). He has a spreadsheet containing around 1500 entries. These
consist of club members with an exclusive reference number each. My friend
is responsible for the overall membership but sends a copy of the file to
another person who operates within it month to month. At the end of the
period the file is sent back to my friend with new members etc. and my
friend then wants to import it into his file (which he has been amending at
the same time)

What he would like to do is to import this file, avoid duplications and
highlight in some way the new members (by maybe colouring the lines) to
distinguish them from old ones (the entries are sorted by surname so new
entries could appear in the middle of the sheet).

I hope this is clear, I know he's be better off using Access but he's been
using this for years and wants to do it this way. Is it possible please?

Many thanks,

Ted

 

[ScottO]

Would this give you what you want ...

At the end of the month, your friend has the "official" copy of the file.
In a spare cell - say G1 - enter the last registered member ref#.
I say "enter" so that this is a fixed value, and not a formula like
=Max(MemberRefNum) which will increase if more members are added during the
month.
Then apply conditional formatting to highlight all members with ref# > the
entry in G1.
That way whenever your friend (or your friend's friend) adds members, they
will be formatted with whatever highlight you choose.

Rgds,
ScottO

| I have been asked to try and solve a problem that a friend has. (Please
try
| and ignore the workflow - I have suggested other ways of doing it but he
| won't have it!). He has a spreadsheet containing around 1500 entries.
These
| consist of club members with an exclusive reference number each. My friend
| is responsible for the overall membership but sends a copy of the file to
| another person who operates within it month to month. At the end of the
| period the file is sent back to my friend with new members etc. and my
| friend then wants to import it into his file (which he has been amending
at
| the same time)
|
| What he would like to do is to import this file, avoid duplications and
| highlight in some way the new members (by maybe colouring the lines) to
| distinguish them from old ones (the entries are sorted by surname so new
| entries could appear in the middle of the sheet).
|
| I hope this is clear, I know he's be better off using Access but he's been
| using this for years and wants to do it this way. Is it possible please?
|
| Many thanks,
|
| Ted
|
|

 

[Ted Rogers]

Many thanks for that. The problem is that the other person adds a member by
name and allocated a membership number which also doubles up as a login to
the club's website. This number is the person's surname plus a number
(smith123).

 

[ScottO]

OK....
Is there any harm in adding a helper column with serial numbers in it to use
as an alternate member reference number?
You could keep track of the highest serial number issued by another spare
cell - say H1 - with a formula like =Max(A:A).
Then use the method described in my last post, but based on the serial
number.
I know it's getting convoluted, but it's the best my feeble abilities will
allow ...
Rgds,
ScottO
| Many thanks for that. The problem is that the other person adds a member
by
| name and allocated a membership number which also doubles up as a login to
| the club's website. This number is the person's surname plus a number
| (smith123).
|
|
| | > Would this give you what you want ...
| >
| > At the end of the month, your friend has the "official" copy of the
file.
| > In a spare cell - say G1 - enter the last registered member ref#.
| > I say "enter" so that this is a fixed value, and not a formula like
| > =Max(MemberRefNum) which will increase if more members are added during
| > the
| > month.
| > Then apply conditional formatting to highlight all members with ref# >
the
| > entry in G1.
| > That way whenever your friend (or your friend's friend) adds members,
they
| > will be formatted with whatever highlight you choose.
| >
| > Rgds,
| > ScottO
| >
| > | > | I have been asked to try and solve a problem that a friend has.
(Please
| > try
| > | and ignore the workflow - I have suggested other ways of doing it but
he
| > | won't have it!). He has a spreadsheet containing around 1500 entries.
| > These
| > | consist of club members with an exclusive reference number each. My
| > friend
| > | is responsible for the overall membership but sends a copy of the file
| > to
| > | another person who operates within it month to month. At the end of
the
| > | period the file is sent back to my friend with new members etc. and my
| > | friend then wants to import it into his file (which he has been
amending
| > at
| > | the same time)
| > |
| > | What he would like to do is to import this file, avoid duplications
and
| > | highlight in some way the new members (by maybe colouring the lines)
to
| > | distinguish them from old ones (the entries are sorted by surname so
new
| > | entries could appear in the middle of the sheet).
| > |
| > | I hope this is clear, I know he's be better off using Access but he's
| > been
| > | using this for years and wants to do it this way. Is it possible
please?
| > |
| > | Many thanks,
| > |
| > | Ted
| > |
| > |
| >
| >
|
|

 

[Ted Rogers]

Thats an excellent suggestion. The only thing I've noticed is that when I
try to apply conditional formatting to the whole sheet based on your
suggestion, only the helper column colours up - is it possible to colour the
complete rows of each entry too?

Thank you for your continuing help!

Ted

 

[ScottO]

Your reference in the formula box in the Conditional Format dialog should be
relative for row and absolute for column.
Rgds,
ScottO

| Thats an excellent suggestion. The only thing I've noticed is that when I
| try to apply conditional formatting to the whole sheet based on your
| suggestion, only the helper column colours up - is it possible to colour
the
| complete rows of each entry too?
|
| Thank you for your continuing help!
|
| Ted
| | > OK....
| > Is there any harm in adding a helper column with serial numbers in it to
| > use
| > as an alternate member reference number?
| > You could keep track of the highest serial number issued by another
spare
| > cell - say H1 - with a formula like =Max(A:A).
| > Then use the method described in my last post, but based on the serial
| > number.
| > I know it's getting convoluted, but it's the best my feeble abilities
will
| > allow ...
| > Rgds,
| > ScottO
| > | > | Many thanks for that. The problem is that the other person adds a
member
| > by
| > | name and allocated a membership number which also doubles up as a
login
| > to
| > | the club's website. This number is the person's surname plus a number
| > | (smith123).
| > |
| > |
message
| > | | > | > Would this give you what you want ...
| > | >
| > | > At the end of the month, your friend has the "official" copy of the
| > file.
| > | > In a spare cell - say G1 - enter the last registered member ref#.
| > | > I say "enter" so that this is a fixed value, and not a formula like
| > | > =Max(MemberRefNum) which will increase if more members are added
| > during
| > | > the
| > | > month.
| > | > Then apply conditional formatting to highlight all members with ref#| > the
| > | > entry in G1.
| > | > That way whenever your friend (or your friend's friend) adds
members,
| > they
| > | > will be formatted with whatever highlight you choose.
| > | >
| > | > Rgds,
| > | > ScottO
| > | >
| > | > | > | > | I have been asked to try and solve a problem that a friend has.
| > (Please
| > | > try
| > | > | and ignore the workflow - I have suggested other ways of doing it
| > but
| > he
| > | > | won't have it!). He has a spreadsheet containing around 1500
| > entries.
| > | > These
| > | > | consist of club members with an exclusive reference number each.
My
| > | > friend
| > | > | is responsible for the overall membership but sends a copy of the
| > file
| > | > to
| > | > | another person who operates within it month to month. At the end
of
| > the
| > | > | period the file is sent back to my friend with new members etc.
and
| > my
| > | > | friend then wants to import it into his file (which he has been
| > amending
| > | > at
| > | > | the same time)
| > | > |
| > | > | What he would like to do is to import this file, avoid
duplications
| > and
| > | > | highlight in some way the new members (by maybe colouring the
lines)
| > to
| > | > | distinguish them from old ones (the entries are sorted by surname
so
| > new
| > | > | entries could appear in the middle of the sheet).
| > | > |
| > | > | I hope this is clear, I know he's be better off using Access but
| > he's
| > | > been
| > | > | using this for years and wants to do it this way. Is it possible
| > please?
| > | > |
| > | > | Many thanks,
| > | > |
| > | > | Ted
| > | > |
| > | > |
| > | >
| > | >
| > |
| > |
| >
| >
|
|

 

[Bill Ridgeway]

It is always bad practice to have a file 'open' for amendment to more than
one person at a time. It creates all sorts of problems around the question
"what is the latest".

As a compromise if 'another person' ONLY adds new members (with their unique
membership number) it should be easy to add these back into your friend's
database. There should, however, be a very clear working understanding that
your friend should not change any of the 'original' data (not one character
or digit) as any changes will not be transferred onto the main database and
be lost. It will be very difficult not to succumb to making a minor change,
the whole thing cannot be monitored and any lost changes will be the fault
of the other one. It is a compromise that is rather flaky and one which I
would definitely not recommend.

A much better solution would be for all updates to the database to be sent
to the one person who operates the database. In this way there is no
duplication or misunderstanding and responsibility for the database (and
also consider data protection requirements) rests squarely on one person.

Regards.

Bill Ridgeway
Computer Solutions

 

[Ted Rogers]

Thank you for that reply Bill. I have explained all that and it's fallen on
deaf ears!

I have established that the other person does not change the original data
in any way so she just adds new members. the problem is that due to the
sorting (by surname) it is not easy to see which ones have been added. Is it
possible to easily show which records have been added? (maybe by conditional
formatting?

Thank you for your kind help,

Ted

 

[gls858]

Thank you for that reply Bill. I have explained all that and it's fallen on
deaf ears!

I have established that the other person does not change the original data
in any way so she just adds new members. the problem is that due to the
sorting (by surname) it is not easy to see which ones have been added. Is it
possible to easily show which records have been added? (maybe by conditional
formatting?

Thank you for your kind help,

Ted

Maybe add a column for date added that way you could sort on the field
and cut and paste only those added since after it was sent. You could even
write a formula that checks to see if there is data in certain field then
todays date would be added in column whatever. So each time a new record
is added the date is automatically set.

gls858

 

[Bill Ridgeway]

Ted,

One of two (similar) ways.

First, I would presume that as new members are added they are given a
membership number in sequence. In which case it is easy to say that since
'you' had it I have added numbers 'x' through 'y'.

Second, add a URN (unique reference number). This is a very useful
addition. It is not always easy to sort on family names. Foreign names
come in various 'strange' formats which upset the system. The Scottish
Macs and Mcs, the Irish O', not to mention the French De come to mind not to
mention others from further afield. This can be addressed by adding a
field for sorting incorporating the first five significant characters of the
family name. The URN makes this much easier. Again it would be easy to say
that since 'you' had it I have added numbers 'x' through 'y'.

Regards.

Bill Ridgeway
Computer Solutions