C
caldog
I download weekly information off the web that has been formatted as text
strings. I have set this up as a web query, so all I have now to do is
refresh the query each week to get the updated information. I also know that
some of you will just say use text to column function that is in Excel, using
the above method; I do not see that as an option, so have decided to just go
with a formula or formulas to speed this process up.
In most cases each line string is no longer then 90 characters in length.
This site uses a two character number that is associated with each salesman.
Therefore the number and the name are usually no longer then 12 characters in
length. The next set of data in this string is the number of contacts each
salesman makes. Following that is the numbers of sales. For this example I
will use three products. So a line can have something like this: 22 Mark S
6 0 0 0. So what this is telling us is that Mark S has made six contacts,
and has zero sales of all three products. Now for another salesman they
could have something like this: 58 John B 61 10 5 15. Now this is telling us
that John has made sixty one contacts, and has sold ten of product X, and
sold five of product Z, and sold fifteen of product Q.
Presenting I am just keying this information in by hand. What I want to do
is to have a formula or set of formulas that will take this information and
paste it on another spreadsheet in the appropriate column for that salesman
and product.
Can somebody help me with this formula?
Steve
strings. I have set this up as a web query, so all I have now to do is
refresh the query each week to get the updated information. I also know that
some of you will just say use text to column function that is in Excel, using
the above method; I do not see that as an option, so have decided to just go
with a formula or formulas to speed this process up.
In most cases each line string is no longer then 90 characters in length.
This site uses a two character number that is associated with each salesman.
Therefore the number and the name are usually no longer then 12 characters in
length. The next set of data in this string is the number of contacts each
salesman makes. Following that is the numbers of sales. For this example I
will use three products. So a line can have something like this: 22 Mark S
6 0 0 0. So what this is telling us is that Mark S has made six contacts,
and has zero sales of all three products. Now for another salesman they
could have something like this: 58 John B 61 10 5 15. Now this is telling us
that John has made sixty one contacts, and has sold ten of product X, and
sold five of product Z, and sold fifteen of product Q.
Presenting I am just keying this information in by hand. What I want to do
is to have a formula or set of formulas that will take this information and
paste it on another spreadsheet in the appropriate column for that salesman
and product.
Can somebody help me with this formula?
Steve