Actually, it may not be a challange for you, but it is for me...

I have a worksheet that I am trying to clean and combine records.

In essence here is have I have:

ColumnBC ColumnCO ColumnCR
Pool | home1 | agent1
FirePlace | home1 | agent1
FirePlace,RV | home2 | agent1

What I want to do, is to step through column CO, and while Column CO = the
current value, to create a new column (CS) and put the combine value of BC
into the bottom most entry, and then mark the rows with incomplete entries
with a "X" in column CT (better would be a unique ID for that property)
ColumnCO is actually a cancatnation of about 10 different fields and then
being sorted. ColumnBC is different features. Sometimes one per line, and
other times multible. Any one of them may be included in items above or
below for the same home

ColumnBC ColumnCO ColumnCR ColumnCS ColumnCT
Pool | home1 | agent1 |
| X
FirePlace | home1 | agent1 | Pool, Fireplace|
FirePlace,RV | home2 | agent1 |Fireplace, RV |


This is pretty simplified, as I have to do this about six different levels,
but once I see how this is done once, I can copy it to work for the others.


Thanks
Mc

You need to put whatever it is you are drinking (or smoking) to one side and
try that explanation again when sober.

"Mctabish" wrote:

Actually, it may not be a challange for you, but it is for me...

I have a worksheet that I am trying to clean and combine records.

In essence here is have I have:

ColumnBC ColumnCO ColumnCR
Pool | home1 | agent1
FirePlace | home1 | agent1
FirePlace,RV | home2 | agent1

What I want to do, is to step through column CO, and while Column CO = the
current value, to create a new column (CS) and put the combine value of BC
into the bottom most entry, and then mark the rows with incomplete entries
with a "X" in column CT (better would be a unique ID for that property)
ColumnCO is actually a cancatnation of about 10 different fields and then
being sorted. ColumnBC is different features. Sometimes one per line, and
other times multible. Any one of them may be included in items above or
below for the same home

ColumnBC ColumnCO ColumnCR ColumnCS ColumnCT
Pool | home1 | agent1 |
| X
FirePlace | home1 | agent1 | Pool, Fireplace|
FirePlace,RV | home2 | agent1 |Fireplace, RV |


This is pretty simplified, as I have to do this about six different levels,
but once I see how this is done once, I can copy it to work for the others.


Thanks
Mc

Not smokin' or drinkin'! Just overwhelmed and not sure how to best describe
what I need to have done...

I am a REALTOR and like to know what is on or has been on the market. I
capture the new entries as they come on the market.
I have an excel spreadsheet of many thousands of homes. These are ones that
are on the market or recently (last 3 years) that have been on the market in
our area.
I want to consolidate the data into much fewer entries. Currently the info
is taking up about 4 sheets in my workbook, each about 55,000 rows.
A lot of these houses have been listed several times and different features
have been entered by different people and these people have selected
different features (or other issues dealing with the listings are in a
similar situation) The features set is the easiest to explain. But needless
to say, there is a MAJOR duplication. I don't need to know what each realtor
has posted, but basically a culmination of all of the different agents for a
given house.

So.... This is what I want to do.

I have created a "key" field that sorts each property as a unique item and
then I have been sorting on these.(this is columnCO - column heading is-KEY)
So what I want to do is to have one entry for each home that has ALL of the
features. I was thinking the best way would be to (after sorting by KEY) to
step through the worksheets, and process each record. As long as the key is
the same, create a string and keep adding the features , and then stuff this
into the last entry of that property.

I want to mark those that do not have the complete features, as later (after
more comparisons) will be deleted

conceptually, this is what I envision (if I can try to codize it)


sort on key

sub FeatureSet
goto row1
do while not last record
r=row()
teststring=""
test=COr
do while COr = test
r=row()
teststring=teststring & BCr '(I would really like to parse
out each feature and test them to see if they are already listed)
CTr="X"
move to next record
while
'stuff the results into new column
CSr=teststring
CTr="" 'make sure this row is NOT flagged
while

end sub

I do know some VBA and I know that this is not done right, but I did this so
as to be as clear to what I am trying to do.

Help me please.... I really would like to get this workbook smaller!
(hopefully small enough to fit on my pocket PC!)

TIA,
Mc


"JLGWhiz" wrote in message
...
You need to put whatever it is you are drinking (or smoking) to one side
and
try that explanation again when sober.

"Mctabish" wrote:

Actually, it may not be a challange for you, but it is for me...

I have a worksheet that I am trying to clean and combine records.

In essence here is have I have:

ColumnBC ColumnCO ColumnCR
Pool | home1 | agent1
FirePlace | home1 | agent1
FirePlace,RV | home2 | agent1

What I want to do, is to step through column CO, and while Column CO =
the
current value, to create a new column (CS) and put the combine value of
BC
into the bottom most entry, and then mark the rows with incomplete
entries
with a "X" in column CT (better would be a unique ID for that property)
ColumnCO is actually a cancatnation of about 10 different fields and then
being sorted. ColumnBC is different features. Sometimes one per line, and
other times multible. Any one of them may be included in items above or
below for the same home

ColumnBC ColumnCO ColumnCR ColumnCS ColumnCT
Pool | home1 | agent1 |
| X
FirePlace | home1 | agent1 | Pool, Fireplace|
FirePlace,RV | home2 | agent1 |Fireplace, RV |


This is pretty simplified, as I have to do this about six different
levels,
but once I see how this is done once, I can copy it to work for the
others.


Thanks
Mc

Let's see if I have the gist of your needs.

1. In column CO the same property might be listed several times.
2. The features for the house are listed in column BC and might vary
for each listing.
3. Column Cr identifies the Agent that made the entry.
4. You want to reduce the listing to one entry per property which will
accumulate the various features and list them in column CS.
5. All of the rows that previously listed features, but were moved to the
last occurence row are to be marked with x in column CT.

That is what I now understand that you are attempting. The problems that I
can see are that the features are not standardized so that they can be easily
checked for duplication and ignored if previously copied to column CS. That
means that whatever is in column BC for each occurence of the property would
be copied and added to column CS. It might be just as easy to sort on column
CO as the primary key and column BC as the secondary sort. That would put
all the features consecutively grouped for each property and allow for
elimination of duplicate features.

If there is nothing in the rows to the right of column CR, then you could
copy and pastespecial transpose the range from column BC to the row with
the last occurence for each property.

"Mctabish" wrote:

Not smokin' or drinkin'! Just overwhelmed and not sure how to best describe
what I need to have done...

I am a REALTOR and like to know what is on or has been on the market. I
capture the new entries as they come on the market.
I have an excel spreadsheet of many thousands of homes. These are ones that
are on the market or recently (last 3 years) that have been on the market in
our area.
I want to consolidate the data into much fewer entries. Currently the info
is taking up about 4 sheets in my workbook, each about 55,000 rows.
A lot of these houses have been listed several times and different features
have been entered by different people and these people have selected
different features (or other issues dealing with the listings are in a
similar situation) The features set is the easiest to explain. But needless
to say, there is a MAJOR duplication. I don't need to know what each realtor
has posted, but basically a culmination of all of the different agents for a
given house.

So.... This is what I want to do.

I have created a "key" field that sorts each property as a unique item and
then I have been sorting on these.(this is columnCO - column heading is-KEY)
So what I want to do is to have one entry for each home that has ALL of the
features. I was thinking the best way would be to (after sorting by KEY) to
step through the worksheets, and process each record. As long as the key is
the same, create a string and keep adding the features , and then stuff this
into the last entry of that property.

I want to mark those that do not have the complete features, as later (after
more comparisons) will be deleted

conceptually, this is what I envision (if I can try to codize it)


sort on key

sub FeatureSet
goto row1
do while not last record
r=row()
teststring=""
test=COr
do while COr = test
r=row()
teststring=teststring & BCr '(I would really like to parse
out each feature and test them to see if they are already listed)
CTr="X"
move to next record
while
'stuff the results into new column
CSr=teststring
CTr="" 'make sure this row is NOT flagged
while

end sub

I do know some VBA and I know that this is not done right, but I did this so
as to be as clear to what I am trying to do.

Help me please.... I really would like to get this workbook smaller!
(hopefully small enough to fit on my pocket PC!)

TIA,
Mc


"JLGWhiz" wrote in message
...
You need to put whatever it is you are drinking (or smoking) to one side
and
try that explanation again when sober.

"Mctabish" wrote:

Actually, it may not be a challange for you, but it is for me...

I have a worksheet that I am trying to clean and combine records.

In essence here is have I have:

ColumnBC ColumnCO ColumnCR
Pool | home1 | agent1
FirePlace | home1 | agent1
FirePlace,RV | home2 | agent1

What I want to do, is to step through column CO, and while Column CO =
the
current value, to create a new column (CS) and put the combine value of
BC
into the bottom most entry, and then mark the rows with incomplete
entries
with a "X" in column CT (better would be a unique ID for that property)
ColumnCO is actually a cancatnation of about 10 different fields and then
being sorted. ColumnBC is different features. Sometimes one per line, and
other times multible. Any one of them may be included in items above or
below for the same home

ColumnBC ColumnCO ColumnCR ColumnCS ColumnCT
Pool | home1 | agent1 |
| X
FirePlace | home1 | agent1 | Pool, Fireplace|
FirePlace,RV | home2 | agent1 |Fireplace, RV |


This is pretty simplified, as I have to do this about six different
levels,
but once I see how this is done once, I can copy it to work for the
others.


Thanks
Mc

"JLGWhiz" wrote in message
...
Let's see if I have the gist of your needs.

1. In column CO the same property might be listed several times.
2. The features for the house are listed in column BC and might vary
for each listing.
3. Column Cr identifies the Agent that made the entry.
4. You want to reduce the listing to one entry per property which will
accumulate the various features and list them in column CS.
5. All of the rows that previously listed features, but were moved to
the
last occurence row are to be marked with x in column CT.
Yup!


That is what I now understand that you are attempting. The problems that
I
can see are that the features are not standardized so that they can be
easily
checked for duplication and ignored if previously copied to column CS.
That
means that whatever is in column BC for each occurence of the property
would
be copied and added to column CS. It might be just as easy to sort on
column
CO as the primary key and column BC as the secondary sort. That would put
all the features consecutively grouped for each property and allow for
elimination of duplicate features.
Each item is standardized, and the line may contain several items,
septerated with a simicolon, so this could be tested for and is the feature
is listed, throw it aout, it is is not, then add it to the strings. I could
do this either in the code while combining or at a later time. I did mention
this, but this is something I know how to do, so I did not belabor the
point.

If there is nothing in the rows to the right of column CR, then you could
copy and pastespecial transpose the range from column BC to the row with
the last occurence for each property.
I am not following you here. There is much more data after the CR column
though and I still need to do this process for several more items (including
notes, contact info etc)

I hope this makes it a little clearer.

Thanks
Mc


"Mctabish" wrote:

Not smokin' or drinkin'! Just overwhelmed and not sure how to best
describe
what I need to have done...

I am a REALTOR and like to know what is on or has been on the market. I
capture the new entries as they come on the market.
I have an excel spreadsheet of many thousands of homes. These are ones
that
are on the market or recently (last 3 years) that have been on the market
in
our area.
I want to consolidate the data into much fewer entries. Currently the
info
is taking up about 4 sheets in my workbook, each about 55,000 rows.
A lot of these houses have been listed several times and different
features
have been entered by different people and these people have selected
different features (or other issues dealing with the listings are in a
similar situation) The features set is the easiest to explain. But
needless
to say, there is a MAJOR duplication. I don't need to know what each
realtor
has posted, but basically a culmination of all of the different agents
for a
given house.

So.... This is what I want to do.

I have created a "key" field that sorts each property as a unique item
and
then I have been sorting on these.(this is columnCO - column heading
is-KEY)
So what I want to do is to have one entry for each home that has ALL of
the
features. I was thinking the best way would be to (after sorting by KEY)
to
step through the worksheets, and process each record. As long as the key
is
the same, create a string and keep adding the features , and then stuff
this
into the last entry of that property.

I want to mark those that do not have the complete features, as later
(after
more comparisons) will be deleted

conceptually, this is what I envision (if I can try to codize it)


sort on key

sub FeatureSet
goto row1
do while not last record
r=row()
teststring=""
test=COr
do while COr = test
r=row()
teststring=teststring & BCr '(I would really like to parse
out each feature and test them to see if they are already listed)
CTr="X"
move to next record
while
'stuff the results into new column
CSr=teststring
CTr="" 'make sure this row is NOT flagged
while

end sub

I do know some VBA and I know that this is not done right, but I did this
so
as to be as clear to what I am trying to do.

Help me please.... I really would like to get this workbook smaller!
(hopefully small enough to fit on my pocket PC!)

TIA,
Mc


"JLGWhiz" wrote in message
...
You need to put whatever it is you are drinking (or smoking) to one
side
and
try that explanation again when sober.

"Mctabish" wrote:

Actually, it may not be a challange for you, but it is for me...

I have a worksheet that I am trying to clean and combine records.

In essence here is have I have:

ColumnBC ColumnCO ColumnCR
Pool | home1 | agent1
FirePlace | home1 | agent1
FirePlace,RV | home2 | agent1

What I want to do, is to step through column CO, and while Column CO =
the
current value, to create a new column (CS) and put the combine value
of
BC
into the bottom most entry, and then mark the rows with incomplete
entries
with a "X" in column CT (better would be a unique ID for that
property)
ColumnCO is actually a cancatnation of about 10 different fields and
then
being sorted. ColumnBC is different features. Sometimes one per line,
and
other times multible. Any one of them may be included in items above
or
below for the same home

ColumnBC ColumnCO ColumnCR ColumnCS ColumnCT
Pool | home1 | agent1 |
| X
FirePlace | home1 | agent1 | Pool, Fireplace|
FirePlace,RV | home2 | agent1 |Fireplace, RV |


This is pretty simplified, as I have to do this about six different
levels,
but once I see how this is done once, I can copy it to work for the
others.


Thanks
Mc