Need help with a chart and formulas for making sentences based on similarity or differences, proximity and date of sales.

Stumpped

New Member
Joined
Dec 3, 2009
Messages
36
Office Version
  1. 2019
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>
ABCDEFGHIJKLM
1ItemAdjustItemAdjustItemAdjustItemAdjustItemAdjustItemAdjust
2SubjectComp 1Comp 2Comp 3Comp 4Comp 5Comp 6
3Sales Date5/20/20194/2/20191/30/20191/1/2019PendingActive
4Distance.05 miles.08 miles.5 miles1.4 miles.05 miles.10 miles
5LOCATION
6ResidentialResidentialWaterfront-10000Commercial10000Golf Course-5000Residential0Park-2000
7LOT AREA
81 acre2 acre-100001 acre1.2 acre-2000.5 acre50001 acre6 acre-50000
9VIEW
10MountainResidential5000Water-10000Industrial5000Golf Course-5000Residential5000Park-2000

<tbody>
</tbody>


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:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
are you going to say you want FUZZY logic also at some stage.

you could use LOOKUP in stages to populate a sheet and then use concatenate to build the sentence.

Whilst it might seem better to start small, in this case i think knowing the finishing point will be easier
 
Upvote 0
Sorry I don't know how to format the first post to make it make more sense. With indenting it would have made much more sense. lets see if this makes it more readable.
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
Comparable number

onetwothreefourfivesixBEGIN 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 hasBEGIN 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


<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>
 
Upvote 0
I see what you mean by doing the section in smaller segments. I was not really looking for a single formula to get this done but rather a suggestion as to a formula that requires as little processing power as possible. Wondering if index match or your suggestion of lookup would be easier.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top