Someone used Excel (2003) like an old fashioned paper spreadsheet. About 50
names on column A. Column headings in Row 1 are program numbers. Columns B to
whatever, about 60 columns, are populated by fractions representing each
person's percentage of time devoted to programs. A person's total time might
be distributed from one program to a maximum of 15 programs.


I think this is a huge waste of good spreadsheet real estate. Less than 10
percent of the cells are populated.

However, what I want to do is to extract the percentages, separated by comma
if more than one, for each person into a cell, say cell DA7 for the person
whose name is in cell A7, in the same row as the person's name. Also the
corresponding programs, also separated by comma if more than one, in say cell
DB7.

Is this possible?

If there are no cells that have spaces in them, then you could use:

=substitute(trim(b2&" "&c2&" "&d2&" "&e2&" "&f2&" "&g2&" "&h2)," ",", ")

Include all the columns you need (I got tired!).

Or you may want to use JE McGimpsey's UDF called MultiCat:
http://mcgimpsey.com/excel/udfs/multicat.html

With a minor tweak:

Public Function MultiCat( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") _
As String
Dim rCell As Range
For Each rCell In rRng.Cells
if isempty(rcell.value) then
'skip it
else
MultiCat = MultiCat & sDelim & rCell.Text
end if
Next rCell
if multicat < "" then
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
end if
End Function

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=multicat(b2:o2,", ")



abe1952 wrote:

Someone used Excel (2003) like an old fashioned paper spreadsheet. About 50
names on column A. Column headings in Row 1 are program numbers. Columns B to
whatever, about 60 columns, are populated by fractions representing each
person's percentage of time devoted to programs. A person's total time might
be distributed from one program to a maximum of 15 programs.

I think this is a huge waste of good spreadsheet real estate. Less than 10
percent of the cells are populated.

However, what I want to do is to extract the percentages, separated by comma
if more than one, for each person into a cell, say cell DA7 for the person
whose name is in cell A7, in the same row as the person's name. Also the
corresponding programs, also separated by comma if more than one, in say cell
DB7.

Is this possible?

--

Dave Peterson

Interesting points on extracting data, For extracting data i use
python for simple things,data extraction can be a time consuming
process
but for larger projects like documents, files, or the web i tried <a
href="http://www.extractingdata.com""extracting data"</a which
worked great, they build quick custom screen scrapers, extracting
data, and data parsing programs

Thanks, Dave. I will try your first solution.



"Dave Peterson" wrote:

If there are no cells that have spaces in them, then you could use:

=substitute(trim(b2&" "&c2&" "&d2&" "&e2&" "&f2&" "&g2&" "&h2)," ",", ")

Include all the columns you need (I got tired!).

Or you may want to use JE McGimpsey's UDF called MultiCat:
http://mcgimpsey.com/excel/udfs/multicat.html

With a minor tweak:

Public Function MultiCat( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") _
As String
Dim rCell As Range
For Each rCell In rRng.Cells
if isempty(rcell.value) then
'skip it
else
MultiCat = MultiCat & sDelim & rCell.Text
end if
Next rCell
if multicat < "" then
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
end if
End Function

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=multicat(b2:o2,", ")



abe1952 wrote:

Someone used Excel (2003) like an old fashioned paper spreadsheet. About 50
names on column A. Column headings in Row 1 are program numbers. Columns B to
whatever, about 60 columns, are populated by fractions representing each
person's percentage of time devoted to programs. A person's total time might
be distributed from one program to a maximum of 15 programs.

I think this is a huge waste of good spreadsheet real estate. Less than 10
percent of the cells are populated.

However, what I want to do is to extract the percentages, separated by comma
if more than one, for each person into a cell, say cell DA7 for the person
whose name is in cell A7, in the same row as the person's name. Also the
corresponding programs, also separated by comma if more than one, in say cell
DB7.

Is this possible?

--

Dave Peterson
.