I want to write a sentence only using formulas, for instance, I am comparing many things about a home. I would like to say how things are similar, superior and inferior all in the same sentence if it applies. My version of excel is not really excel at all but a spreadsheet built into my software program that allows excel formulas but no VBA or outside worksheet references. I've been looking into Index match combined with concatenate functions to get this done. Eventually I need to build paragraphs based on the data. Here is an example of the first two sentences
Hopefully this makes some sense. I need to build a chart, based on the differences in features,
Comparable number
one two three four five six BEGIN SENTENCE ONE
is used as it is the most recent sale found
it is the next most recent sale found
although an older sale
is the oldest sale used
is an active listing
is a pending sale
and is the most proximate sale found near the subject
and is the next most proximate found near the subject
and is a quite distant sale from the subject
and is the furthest sale from the subject END SENTENCE ONE
It has BEGIN SENTENCE TWO
an identical lot area as the subject
a slightly
a much
smaller
larger
lot area versus the subject with
with
similar
superior
inferior
residential
industrial
commercial
busy road
waterfront
golf course
park
power line
landfill
public transit
OTHER...
location as the subject with
location versus the subject with
and
lot area versus the subject with
slightly less lot area versus the subject with
much less lot area versus the subject with
slightly more lot area versus the subject with
much more lot area versus the subject with
similar
superior
inferior
water
pastoral
wooded
park
golf course
city skyline
mountain
residential
city street
industrial
power line
limited sight
OTHER...
views versus the subject.
views as the subject. END SENTENCE TWO
HERE IS THE DATA
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Item[/TD]
[TD]Adjust[/TD]
[TD]Item[/TD]
[TD]Adjust[/TD]
[TD]Item[/TD]
[TD]Adjust[/TD]
[TD]Item[/TD]
[TD]Adjust[/TD]
[TD]Item[/TD]
[TD]Adjust[/TD]
[TD]Item[/TD]
[TD]Adjust[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Subject[/TD]
[TD]Comp 1[/TD]
[TD][/TD]
[TD]Comp 2[/TD]
[TD][/TD]
[TD]Comp 3[/TD]
[TD][/TD]
[TD]Comp 4[/TD]
[TD][/TD]
[TD]Comp 5[/TD]
[TD][/TD]
[TD]Comp 6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Sales Date[/TD]
[TD="align: right"]5/20/2019[/TD]
[TD][/TD]
[TD="align: right"]4/2/2019[/TD]
[TD][/TD]
[TD="align: right"]1/30/2019[/TD]
[TD][/TD]
[TD="align: right"]1/1/2019[/TD]
[TD][/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]Active[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Distance[/TD]
[TD].05 miles[/TD]
[TD][/TD]
[TD].08 miles[/TD]
[TD][/TD]
[TD].5 miles[/TD]
[TD][/TD]
[TD]1.4 miles[/TD]
[TD][/TD]
[TD].05 miles[/TD]
[TD][/TD]
[TD].10 miles[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]LOCATION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Residential[/TD]
[TD]Residential[/TD]
[TD][/TD]
[TD]Waterfront[/TD]
[TD="align: right"]-10000[/TD]
[TD]Commercial[/TD]
[TD="align: right"]10000[/TD]
[TD]Golf Course[/TD]
[TD="align: right"]-5000[/TD]
[TD]Residential[/TD]
[TD="align: right"]0[/TD]
[TD]Park[/TD]
[TD="align: right"]-2000[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]LOT AREA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]1 acre[/TD]
[TD]2 acre[/TD]
[TD="align: right"]-10000[/TD]
[TD]1 acre[/TD]
[TD][/TD]
[TD]1.2 acre[/TD]
[TD="align: right"]-2000[/TD]
[TD].5 acre[/TD]
[TD="align: right"]5000[/TD]
[TD]1 acre[/TD]
[TD][/TD]
[TD]6 acre[/TD]
[TD="align: right"]-50000[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]VIEW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Mountain[/TD]
[TD]Residential[/TD]
[TD="align: right"]5000[/TD]
[TD]Water[/TD]
[TD="align: right"]-10000[/TD]
[TD]Industrial[/TD]
[TD="align: right"]5000[/TD]
[TD]Golf Course[/TD]
[TD="align: right"]-5000[/TD]
[TD]Residential[/TD]
[TD="align: right"]5000[/TD]
[TD]Park[/TD]
[TD="align: right"]-2000[/TD]
[/TR]
</tbody>[/TABLE]
Using words like slightly, vastly or much would be based on the amount being adjusted, for instance a small adjustment for lot area would be under 5K, much would 5-20K and vastly would be over 20K.
Any help or pointers would be greatly appreciated. I do have even more complex sentences and phrases coming in future posts.
Hopefully this makes some sense. I need to build a chart, based on the differences in features,
Comparable number
one two three four five six BEGIN SENTENCE ONE
is used as it is the most recent sale found
it is the next most recent sale found
although an older sale
is the oldest sale used
is an active listing
is a pending sale
and is the most proximate sale found near the subject
and is the next most proximate found near the subject
and is a quite distant sale from the subject
and is the furthest sale from the subject END SENTENCE ONE
It has BEGIN SENTENCE TWO
an identical lot area as the subject
a slightly
a much
smaller
larger
lot area versus the subject with
with
similar
superior
inferior
residential
industrial
commercial
busy road
waterfront
golf course
park
power line
landfill
public transit
OTHER...
location as the subject with
location versus the subject with
and
lot area versus the subject with
slightly less lot area versus the subject with
much less lot area versus the subject with
slightly more lot area versus the subject with
much more lot area versus the subject with
similar
superior
inferior
water
pastoral
wooded
park
golf course
city skyline
mountain
residential
city street
industrial
power line
limited sight
OTHER...
views versus the subject.
views as the subject. END SENTENCE TWO
HERE IS THE DATA
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Item[/TD]
[TD]Adjust[/TD]
[TD]Item[/TD]
[TD]Adjust[/TD]
[TD]Item[/TD]
[TD]Adjust[/TD]
[TD]Item[/TD]
[TD]Adjust[/TD]
[TD]Item[/TD]
[TD]Adjust[/TD]
[TD]Item[/TD]
[TD]Adjust[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Subject[/TD]
[TD]Comp 1[/TD]
[TD][/TD]
[TD]Comp 2[/TD]
[TD][/TD]
[TD]Comp 3[/TD]
[TD][/TD]
[TD]Comp 4[/TD]
[TD][/TD]
[TD]Comp 5[/TD]
[TD][/TD]
[TD]Comp 6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Sales Date[/TD]
[TD="align: right"]5/20/2019[/TD]
[TD][/TD]
[TD="align: right"]4/2/2019[/TD]
[TD][/TD]
[TD="align: right"]1/30/2019[/TD]
[TD][/TD]
[TD="align: right"]1/1/2019[/TD]
[TD][/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]Active[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Distance[/TD]
[TD].05 miles[/TD]
[TD][/TD]
[TD].08 miles[/TD]
[TD][/TD]
[TD].5 miles[/TD]
[TD][/TD]
[TD]1.4 miles[/TD]
[TD][/TD]
[TD].05 miles[/TD]
[TD][/TD]
[TD].10 miles[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]LOCATION[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Residential[/TD]
[TD]Residential[/TD]
[TD][/TD]
[TD]Waterfront[/TD]
[TD="align: right"]-10000[/TD]
[TD]Commercial[/TD]
[TD="align: right"]10000[/TD]
[TD]Golf Course[/TD]
[TD="align: right"]-5000[/TD]
[TD]Residential[/TD]
[TD="align: right"]0[/TD]
[TD]Park[/TD]
[TD="align: right"]-2000[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]LOT AREA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]1 acre[/TD]
[TD]2 acre[/TD]
[TD="align: right"]-10000[/TD]
[TD]1 acre[/TD]
[TD][/TD]
[TD]1.2 acre[/TD]
[TD="align: right"]-2000[/TD]
[TD].5 acre[/TD]
[TD="align: right"]5000[/TD]
[TD]1 acre[/TD]
[TD][/TD]
[TD]6 acre[/TD]
[TD="align: right"]-50000[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]VIEW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Mountain[/TD]
[TD]Residential[/TD]
[TD="align: right"]5000[/TD]
[TD]Water[/TD]
[TD="align: right"]-10000[/TD]
[TD]Industrial[/TD]
[TD="align: right"]5000[/TD]
[TD]Golf Course[/TD]
[TD="align: right"]-5000[/TD]
[TD]Residential[/TD]
[TD="align: right"]5000[/TD]
[TD]Park[/TD]
[TD="align: right"]-2000[/TD]
[/TR]
</tbody>[/TABLE]
Using words like slightly, vastly or much would be based on the amount being adjusted, for instance a small adjustment for lot area would be under 5K, much would 5-20K and vastly would be over 20K.
Any help or pointers would be greatly appreciated. I do have even more complex sentences and phrases coming in future posts.
Last edited: