Trying to figure out an efficient to use way to enter and display data
that at present is just entered as text...

Situation:
Assign from one to as many as 10 or so awardees to a given scholarship
fund along with award amount of each, keeping a total award in another cell.

Present Practice:
Just string them together in almost free-form text of student, $award,
SYY/FYY (awardyear)

Fred Flintstone $200 18/19, Barney Rubble $150, 18/19, ...

It would be easy to just add sets of three columns but that's quickly up
to 30 columns or so, most of which are going to be empty because there's
only a handful of funds large enough for the many multiple awards so it
gets terribly bulky and there are already 50-some columns in the
workbook as is by time include the financial data from which to compute
the award values, etc., etc., ...

Just looking for organizational ideas here...if there were way to insert
table into a cell which could be easily queried would be nice like a
comment sorta' except again that requires parsing text and relying on
data input following a format rigidly that can't get the folks to
do--been through that exercise already on another issue...

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi,

Am Sat, 5 Sep 2020 08:54:54 -0500 schrieb dpb:

Trying to figure out an efficient to use way to enter and display data
that at present is just entered as text...

Situation:
Assign from one to as many as 10 or so awardees to a given scholarship
fund along with award amount of each, keeping a total award in another cell.

Present Practice:
Just string them together in almost free-form text of student, $award, SYY/FYY (awardyear)

Fred Flintstone $200 18/19, Barney Rubble $150, 18/19, ...

It would be easy to just add sets of three columns but that's quickly up
to 30 columns or so, most of which are going to be empty because there's
only a handful of funds large enough for the many multiple awards so it
gets terribly bulky and there are already 50-some columns in the
workbook as is by time include the financial data from which to compute
the award values, etc., etc., ...

Just looking for organizational ideas here...if there were way to insert table into a cell which could be easily queried would be nice like a
comment sorta' except again that requires parsing text and relying on
data input following a format rigidly that can't get the folks to
do--been through that exercise already on another issue...

I hope I understood your problem correctly.
Have a look:
https://1drv.ms/x/s!AqMiGBK2qniTge8JOESumXfAGc0Jmg?e=XUK2eH


Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/5/2020 8:54 AM, dpb wrote:

Trying to figure out an efficient to use way to enter and display data
that at present is just entered as text...

Situation:
Assign from one to as many as 10 or so awardees to a given scholarship
fund along with award amount of each, keeping a total award in another
cell.

Present Practice:
Just string them together in almost free-form text of student, $award, SYY/FYY (awardyear)

Fred Flintstone $200 18/19, Barney Rubble $150, 18/19, ...

It would be easy to just add sets of three columns but that's quickly up
to 30 columns or so, most of which are going to be empty because there's
only a handful of funds large enough for the many multiple awards so it
gets terribly bulky and there are already 50-some columns in the
workbook as is by time include the financial data from which to compute
the award values, etc., etc., ...

...

Obviously, one can collapse the columns when not wanted, just wondering
if there were some other more clever but terribly convoluted techniques possible.

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/5/2020 9:43 AM, Claus Busch wrote:

Hi,

Am Sat, 5 Sep 2020 08:54:54 -0500 schrieb dpb:

...

Just looking for organizational ideas here...if there were way to insert
table into a cell which could be easily queried would be nice like a
comment sorta' except again that requires parsing text and relying on
data input following a format rigidly that can't get the folks to
do--been through that exercise already on another issue...

I hope I understood your problem correctly.
Have a look:
https://1drv.ms/x/s!AqMiGBK2qniTge8JOESumXfAGc0Jmg?e=XUK2eH

Thanks, Claus...

Yeah, you understand the issue correctly; unfortunately that solution is
the one outlined above that to work requires religious adherence to the
format on data entry which I simply haven't been able to train them to
stick to... :(

The other is one still has to then parse the string to get the award $$
amount to calculate the running sum as award to compare to the total
available from a given fund.

Agreed, one can do similar text conversion to get the values but still
has the issue re: the formatting between entries to make it work reliably.

I'm thinking the best solution is probably to just add the needed
columns and then group them so can just hide them when the awarding is
done -- really don't need to see the individual data after awards are
made; then the section that records the paid amounts by month for the
year becomes the significant portion for continuing through the year.

I'm not really an Excel guy; MATLAB that I used extensively in the
consulting gig has a table that allows a composite entity in a "cell" in
its 2D matrix; that could then be another table when open it there...it
doesn't have the interactive nature of the spreadsheet interface Excel
has builtin, though. Was just wondering if there were any such ability
in Excel I wasn't aware of...

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/5/2020 12:06 PM, dpb wrote:

On 9/5/2020 9:43 AM, Claus Busch wrote:

Hi,

Am Sat, 5 Sep 2020 08:54:54 -0500 schrieb dpb:

...

Just looking for organizational ideas here...if there were way to insert >>> table into a cell which could be easily queried would be nice like a
comment sorta' except again that requires parsing text and relying on
data input following a format rigidly that can't get the folks to
do--been through that exercise already on another issue...

I hope I understood your problem correctly.
Have a look:
https://1drv.ms/x/s!AqMiGBK2qniTge8JOESumXfAGc0Jmg?e=XUK2eH

Thanks, Claus...

Yeah, you understand the issue correctly; unfortunately that solution is
the one outlined above that to work requires religious adherence to the format on data entry which I simply haven't been able to train them to
stick to... :(

The other is one still has to then parse the string to get the award $$ amount to calculate the running sum as award to compare to the total available from a given fund.

Agreed, one can do similar text conversion to get the values but still
has the issue re: the formatting between entries to make it work reliably.

...

In doing searching for ideas online I discovered the Alt+Enter trick to
insert newline in cell. Really slick for presentation; unfortunately it requires a delimiter as well to then parse the data as well...too bad MS
hasn't implemented the "table in a cell" idea...like comment sorta,
except be able to define the data content and retrieve field
content...or an array in a cell instead of only an array of cells.

Oh! Another stray thought/idea..

Is there a way to make a click on cell focus take one to another
location for data entry that could be the start point for the award data
for the fund? Ideal would be to be able to direct to next available
cell for the fund, but just the start would be workable...

Ah well, guess I'll just go add the 30 columns and be done with it... :)

--

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi again,

Am Mon, 7 Sep 2020 10:39:13 -0500 schrieb dpb:

In doing searching for ideas online I discovered the Alt+Enter trick to insert newline in cell. Really slick for presentation; unfortunately it requires a delimiter as well to then parse the data as well...too bad MS hasn't implemented the "table in a cell" idea...like comment sorta,
except be able to define the data content and retrieve field
content...or an array in a cell instead of only an array of cells.

Oh! Another stray thought/idea..

Is there a way to make a click on cell focus take one to another
location for data entry that could be the start point for the award data
for the fund? Ideal would be to be able to direct to next available
cell for the fund, but just the start would be workable...

try it with RegExp

Your data in A1. Then the parts of the string will be listed to B:D

Sub SplitData()
Dim re As Object
Dim ptrn, Match, Matches
Dim strText As String
Dim i As Integer, n As Integer
Dim varData() As Variant

Set re = CreateObject("vbscript.regexp")
strText = Range("A1")

ptrn = "\w+"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Matches = re.Execute(strText)

n = 1
For i = 0 To Matches.Count - 5 Step 5
Cells(n, "B") = Matches(i) & " " & Matches(i + 1)
Cells(n, "C") = CInt(Matches(i + 2))
Cells(n, "D") = Matches(i + 3) & "/" & Matches(i + 4)
n = n + 1
Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/7/2020 11:12 AM, Claus Busch wrote:

Hi again,

Am Mon, 7 Sep 2020 10:39:13 -0500 schrieb dpb:

In doing searching for ideas online I discovered the Alt+Enter trick to
insert newline in cell. Really slick for presentation; unfortunately it
requires a delimiter as well to then parse the data as well...too bad MS
hasn't implemented the "table in a cell" idea...like comment sorta,
except be able to define the data content and retrieve field
content...or an array in a cell instead of only an array of cells.

Oh! Another stray thought/idea..

Is there a way to make a click on cell focus take one to another
location for data entry that could be the start point for the award data
for the fund? Ideal would be to be able to direct to next available
cell for the fund, but just the start would be workable...

try it with RegExp

Your data in A1. Then the parts of the string will be listed to B:D

Sub SplitData()
Dim re As Object
Dim ptrn, Match, Matches
Dim strText As String
Dim i As Integer, n As Integer
Dim varData() As Variant

Set re = CreateObject("vbscript.regexp")
strText = Range("A1")

ptrn = "\w+"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Matches = re.Execute(strText)

n = 1
For i = 0 To Matches.Count - 5 Step 5
Cells(n, "B") = Matches(i) & " " & Matches(i + 1)
Cells(n, "C") = CInt(Matches(i + 2))
Cells(n, "D") = Matches(i + 3) & "/" & Matches(i + 4)
n = n + 1
Next
End Sub

....

Ewww....I wasn't aware of regexp in VBA; hadn't found that yet. I'm not
a regexp whizard, but use it fairly regularly w/ MATLAB for such things;
if the patterns get complex I struggle.... :)

Thanks for the code; that may be the ticket...

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/7/2020 11:12 AM, Claus Busch wrote:

Hi again,

Am Mon, 7 Sep 2020 10:39:13 -0500 schrieb dpb:

In doing searching for ideas online I discovered the Alt+Enter trick to
insert newline in cell. Really slick for presentation; unfortunately it
requires a delimiter as well to then parse the data as well...too bad MS
hasn't implemented the "table in a cell" idea...like comment sorta,
except be able to define the data content and retrieve field
content...or an array in a cell instead of only an array of cells.

Oh! Another stray thought/idea..

Is there a way to make a click on cell focus take one to another
location for data entry that could be the start point for the award data
for the fund? Ideal would be to be able to direct to next available
cell for the fund, but just the start would be workable...

try it with RegExp

Your data in A1. Then the parts of the string will be listed to B:D

Sub SplitData()
Dim re As Object
Dim ptrn, Match, Matches
Dim strText As String
Dim i As Integer, n As Integer
Dim varData() As Variant

Set re = CreateObject("vbscript.regexp")
strText = Range("A1")

ptrn = "\w+"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Matches = re.Execute(strText)

n = 1
For i = 0 To Matches.Count - 5 Step 5
Cells(n, "B") = Matches(i) & " " & Matches(i + 1)
Cells(n, "C") = CInt(Matches(i + 2))
Cells(n, "D") = Matches(i + 3) & "/" & Matches(i + 4)
n = n + 1
Next
End Sub

Regards
Claus B.

What if went with three columns only (well maybe four, add the
institution SID would be another I see useful, potentially) but don't
really care so much about splitting the string data; if just see it in
the once cell that may be enough...altho guess that would mean adjusting
row height sufficiently.

Anyway, what if don't spread and have

A:D as SID, StudName, $Award, DateCode

What would =SUM($Award) values look like -- something like

Sub SumSplitData()
Dim re As Object
Dim ptrn, Match, Matches
Dim strText As String
Dim i As Integer, n As Integer
Dim varData() As Variant

Set re = CreateObject("vbscript.regexp")
strText = Range("C1")

ptrn = "\w+"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Matches = re.Execute(strText)

totalAward=0
For i = 1 To Matches.Count
totalAware=totalAward+CDbl(Matches(i)
Next
End Sub

? I don't know enough VBA w/o really digging to know how to convert to function that could use in cell to reference the input cell, though.

Something like that could really be the workaround for now I think...the existing data is from last year and we're starting a new year billing
cycle since we operate on calendar instead of academic year, so what few
awards are there that carry forward (as a 2-year school, there's only
the freshman that are back) and I've already got those identified so for
the most part we're entering new data, and I can let the past year's
data go.

I was just trying to avoid keeping on doing the same thing this year in redesigning the input form to segregate the subfields.

Thanks again...

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi,

Am Mon, 7 Sep 2020 13:02:59 -0500 schrieb dpb:

What if went with three columns only (well maybe four, add the
institution SID would be another I see useful, potentially) but don't
really care so much about splitting the string data; if just see it in
the once cell that may be enough...altho guess that would mean adjusting
row height sufficiently.

Anyway, what if don't spread and have

A:D as SID, StudName, $Award, DateCode

What would =SUM($Award) values look like -- something like

Sub SumSplitData()
Dim re As Object
Dim ptrn, Match, Matches
Dim strText As String
Dim i As Integer, n As Integer
Dim varData() As Variant

Set re = CreateObject("vbscript.regexp")
strText = Range("C1")

ptrn = "\w+"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Matches = re.Execute(strText)

totalAward=0
For i = 1 To Matches.Count
totalAware=totalAward+CDbl(Matches(i)
Next
End Sub

? I don't know enough VBA w/o really digging to know how to convert to function that could use in cell to reference the input cell, though.

Something like that could really be the workaround for now I think...the existing data is from last year and we're starting a new year billing
cycle since we operate on calendar instead of academic year, so what few awards are there that carry forward (as a 2-year school, there's only
the freshman that are back) and I've already got those identified so for
the most part we're entering new data, and I can let the past year's
data go.

I was just trying to avoid keeping on doing the same thing this year in redesigning the input form to segregate the subfields.

have a look:
https://1drv.ms/x/s!AqMiGBK2qniTge8JPuyoOVSMVs3Wlg?e=TuplIq

Is that what you want?


Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi,

Am Mon, 7 Sep 2020 14:51:56 -0500 schrieb dpb:

A3:C3 display
A3 B3 C3
"123.34 "123.34 432.10" #NAME?
432.10"

to split the data of A3 to B3:C3 try:

Sub SplitData()
Dim varData As Variant

varData = Split(Range("A3"), Chr(10))
Range("B3").Resize(, UBound(varData) + 1) = varData
End Sub


Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi,

Am Mon, 7 Sep 2020 15:22:01 -0500 schrieb dpb:

How to write a function that can be entered in B3:B221 that returns sum
of the split values in A3:A221 respectively? That'd be the trick...

have a test to look if the first or the second line works better for
you.

Function SplitData(myRng As Range)
'SplitData = Split(myRng, Chr(10))
SplitData = Join(Split(myRng, Chr(10)), " ")
End Function


Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/7/2020 3:11 PM, Claus Busch wrote:

Hi,

Am Mon, 7 Sep 2020 14:51:56 -0500 schrieb dpb:

A3:C3 display
A3 B3 C3
"123.34 "123.34 432.10" #NAME?
432.10"

to split the data of A3 to B3:C3 try:

Sub SplitData()
Dim varData As Variant

varData = Split(Range("A3"), Chr(10))
Range("B3").Resize(, UBound(varData) + 1) = varData
End Sub

...

How to write a function that can be entered in B3:B221 that returns sum
of the split values in A3:A221 respectively? That'd be the trick...

I munged on the sheet you linked to before to show what have in
mind...there instead of just in text form here.

What's wrong w/ the function above; why isn't it available if it has
some logic error? It runs as a SUB with fixed address but that doesn't
really solve the problem.

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/7/2020 1:30 PM, Claus Busch wrote:

Hi,

Am Mon, 7 Sep 2020 13:02:59 -0500 schrieb dpb:

What if went with three columns only (well maybe four, add the
institution SID would be another I see useful, potentially) but don't
really care so much about splitting the string data; if just see it in
the once cell that may be enough...altho guess that would mean adjusting
row height sufficiently.

Anyway, what if don't spread and have

A:D as SID, StudName, $Award, DateCode

What would =SUM($Award) values look like -- something like

Sub SumSplitData()
Dim re As Object
Dim ptrn, Match, Matches
Dim strText As String
Dim i As Integer, n As Integer
Dim varData() As Variant

Set re = CreateObject("vbscript.regexp")
strText = Range("C1")

ptrn = "\w+"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Matches = re.Execute(strText)

totalAward=0
For i = 1 To Matches.Count
totalAware=totalAward+CDbl(Matches(i)
Next
End Sub

? I don't know enough VBA w/o really digging to know how to convert to
function that could use in cell to reference the input cell, though.

Something like that could really be the workaround for now I think...the
existing data is from last year and we're starting a new year billing
cycle since we operate on calendar instead of academic year, so what few
awards are there that carry forward (as a 2-year school, there's only
the freshman that are back) and I've already got those identified so for
the most part we're entering new data, and I can let the past year's
data go.

I was just trying to avoid keeping on doing the same thing this year in
redesigning the input form to segregate the subfields.

have a look:
https://1drv.ms/x/s!AqMiGBK2qniTge8JPuyoOVSMVs3Wlg?e=TuplIq

Is that what you want?

I could make that work if could count on the girls being that consistent
in data entry, Claus.

What I had in mind now was to revert to the "Alt+Enter" scheme with
three/four columns where each column is studentName, SID, $Award, Year.

I believe when they have to have an entry on a line with a delimiter
that's enough visual clue can a) get them to do it, and b) can find it
pretty quickly if they don't.

From there, for right now I can just live with the text fields being
left that way; just have a function to split the $Award column for the
specific cell for each fund I can paste into the TotalAward column and
go on from there.

I got your SUB to work; I can see how to modify it to build an external
table that would split the individual students into row/columns for
visual inspection so that part I see how to proceed--thanks, very
helpful! to know the way to access regexp in VBA.

I learned during debugging the matches() array is 0-based so my code
above is off that way, I modified your example to use [^ ]+ as the
search pattern and CDbl() and was able to sum the values in a column
containing just the award numbers so that part worked as a Sub()

I failed in the attempt to convert to a Function however; somehow the
function isn't visible as I get a #NAME? error. Not sure what's up with that...

Public Function SumSplitData(strText As String) As Double
Dim re As Object
Dim ptrn, Match, Matches
Dim strText As String
Dim i As Integer
Dim varData() As Variant

Set re = CreateObject("vbscript.regexp")

ptrn = "[^ ]+"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Matches = re.Execute(strText)

SumSplitData = 0
For i = 0 To Matches.Count - 1
SumSplitData = SumSplitData + CDbl(Matches(i))
Next
End Function

Can you see where I went wrong? Or why the function isn't visible?

A3:C3 display
A3 B3 C3
"123.34 "123.34 432.10" #NAME?
432.10"

B3 contains "=A3"

C3 contains "=Sumsplitdata(A3)"

As can see, the text entered into A3 via Alt+Enter has a blank on the
first row before the \n; it's not visible in A3 but shows up in the
string displayed in B3.

I'd use a "real" delimiter in anger; this was just a test to see about splitting -- it works with your earlier code to put the two values in
separate cells; could work around having to do that, but would be much
cleaner to be able to use as a user-defined function and just return the
total.

Thanks again,

--dpb

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi again,

Am Mon, 7 Sep 2020 23:14:58 +0200 schrieb Claus Busch:

Function SplitData(myRng As Range)
'SplitData = Split(myRng, Chr(10))
SplitData = Join(Split(myRng, Chr(10)), " ")
End Function

have a look:
https://1drv.ms/x/s!AqMiGBK2qniTge8JPuyoOVSMVs3Wlg?e=TZSYqm


Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/7/2020 4:22 PM, Claus Busch wrote:

Hi again,

Am Mon, 7 Sep 2020 23:14:58 +0200 schrieb Claus Busch:

Function SplitData(myRng As Range)
'SplitData = Split(myRng, Chr(10))
SplitData = Join(Split(myRng, Chr(10)), " ")
End Function

have a look:
https://1drv.ms/x/s!AqMiGBK2qniTge8JPuyoOVSMVs3Wlg?e=TZSYqm

Regards
Claus B.

Thanks, Claus...I'll see which form turns out better -- those data will
be user entered almost entirely though, although there is some from last
year that I can pre-populate for them that this will definitely help with.

I did get the summation function to work, still using regexp(); I'll
play with SPLIT() going forward.

I had to completely delete the stupid PERSONAL workbook MS created and
start over -- something was munged there that no matter what I did it wouldn't/couldn't see user defined functions.

I'll take a sample of this to them in the AM and see how they think
it'll work...presuming I can get the rest of the stuff munged in there.

I can always revert to the gazillion columns if have to if this turns
out too klunky this way...think only trying to use it will answer that Q?

Thanks again, appreciate the coaching...

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/7/2020 4:33 PM, dpb wrote:

...snip...

I can always revert to the gazillion columns if have to if this turns
out too klunky this way...think only trying to use it will answer that Q?

...

Still, would be nice if there were a table object contained in a cell a
la MATLAB. :)

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 9/7/2020 4:33 PM, dpb wrote:

..snip...

I did get the summation function to work, still using regexp(); I'll
play with SPLIT() going forward.

...

The following seems pretty robust and somewhat forgiving for input
formatting to get the total awards...

Public Function SumSplitData(strText)
Dim re As Object
Dim ptrn, Tokens
Dim i As Integer
Dim SumSplitData As Double

Set re = CreateObject("vbscript.regexp")
' allow separators of semicolon, comma, blank, \n
ptrn = "[^;, " + Chr(10) + "]+"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Tokens = re.Execute(strText)

SumSplitData = 0
For i = 0 To Tokens.Count - 1
SumSplitData = SumSplitData + CDbl(Tokens(i))
Next
End Function

Doesn't seem to be a version of SPLIT() that allows multiple delimiters
to give a little cushion on the data entry. Looks like as long as they
stick to entering only the award data in this cell and the names and
year codes in their appropriate columns as well can make this work.

As noted, have to see if it's too much of a burden to follow the input restriction of having to edit existing cell to add new awardee. But,
generally one awards all of a given fund at same time so think shouldn't
be too bad.

Time will tell...again, thanks! Muchly appreciated and big help. I'd'a
never knowed could use regexp() without the advice.

--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)