software

I have ten sets of cells of ten cells each (each cell is on a different
line). Each cell may or may not contain data. I want to build a summary
sheet listing only data within the cells and the line number that that cell
is on. (omit all blank cells) I hope this makes sense.

The application is a budget worksheet that has ten categories with ten line
items in each category. Each category may contain blank lines. I want a
concise summary on a separate sheet, eliminating the category headings and
all blank lines. How do I do this?

Thanks in advance.

Are all these cells in one column?
When you say quot;eliminating the category headingsquot;, I take it that the
category headings are text and not numbers. Is that right? If that is
right, what are the contents of all the other cells that are not blank?
Numbers only? Text only? Some of each? If those cells can have text then
you need to furnish the exact text of all the categories so they can be
differentiated from the other text cells. HTH Otto
quot;Richard Walkerquot; gt; wrote in message
...
gt;I have ten sets of cells of ten cells each (each cell is on a different
gt; line). Each cell may or may not contain data. I want to build a summary
gt; sheet listing only data within the cells and the line number that that
gt; cell
gt; is on. (omit all blank cells) I hope this makes sense.
gt;
gt; The application is a budget worksheet that has ten categories with ten
gt; line
gt; items in each category. Each category may contain blank lines. I want a
gt; concise summary on a separate sheet, eliminating the category headings and
gt; all blank lines. How do I do this?
gt;
gt; Thanks in advance.
Thanks for your response, Otto.

Here is a sampling of some of the cells in question:

Transportation
cGas
cOil Change
cRepairs
cTires
cCar Insurance
cLicense and Taxes
mCar Replacement
cTolltag
Clothing
cJen's Clothing
cRichard's Clothing
cCleaning/LaundryMedical/Health
cDoctor Bills
cDentist
cOptometrist
cDrugs
cContacts
s24hr Fitness Membership
cAllergy Injections

There are headings: Transportation, Clothing, Medical/Health, etc. Below
these headings, and to the right, are the cells that I would like to look at.
All cells contain text, not numbers, and there are ten cells vertically for
every category. What I want to do is extract only the cells from this column
that actually contain text and list them in a continuous column with the
corresponding line numbers like this:

66Gas
67Oil Change
68Repairs
69Tires
70Car Insurance
71License and Taxes
72Car Replacement
73Tolltag
78Jen's Clothing
79Richard's Clothing
80Cleaning/Laundry
90Doctor Bills
91Dentist
92Optometrist
93Drugs
94Contacts
9524hr Fitness Membership
96Allergy Injections

Is this possible?

Thanks again.quot;Otto Moehrbachquot; wrote:

gt; Are all these cells in one column?
gt; When you say quot;eliminating the category headingsquot;, I take it that the
gt; category headings are text and not numbers. Is that right? If that is
gt; right, what are the contents of all the other cells that are not blank?
gt; Numbers only? Text only? Some of each? If those cells can have text then
gt; you need to furnish the exact text of all the categories so they can be
gt; differentiated from the other text cells. HTH Otto
gt;

Richard
This little macro does what you want. Note that this macro works on
Column B only. You had said that you didn't want the categories in Column A
picked up at all.
This macro loops through all the cells in Column B from B1 to the last entry
in the column. All blank cells are ignored.
For each occupied cell in Column B, this macro will put the row number
in Column A of a sheet named quot;Listquot;, and the contents of the cell in Column
B of the quot;Listquot; sheet. This macro should be placed in a standard module.
Please post back if you need more or you want to make some changes. HTH
Otto
Sub ListData()
Dim RngColB As Range
Dim i As Range
Dim Dest As Range
Set Dest = Sheets(quot;Listquot;).Range(quot;A1quot;)
Set RngColB = Range(quot;B1quot;, Range(quot;Bquot; amp; Rows.Count).End(xlUp))
For Each i In RngColB
If IsEmpty(i) Then GoTo NextCell
Dest.Value = i.Row
Dest.Offset(, 1).Value = i.Value
Set Dest = Dest.Offset(1)
NextCell:
Next i
End Sub
quot;Richard Walkerquot; gt; wrote in message
...
gt;I have ten sets of cells of ten cells each (each cell is on a different
gt; line). Each cell may or may not contain data. I want to build a summary
gt; sheet listing only data within the cells and the line number that that
gt; cell
gt; is on. (omit all blank cells) I hope this makes sense.
gt;
gt; The application is a budget worksheet that has ten categories with ten
gt; line
gt; items in each category. Each category may contain blank lines. I want a
gt; concise summary on a separate sheet, eliminating the category headings and
gt; all blank lines. How do I do this?
gt;
gt; Thanks in advance.
Thanks Otto, this strips and arranges the data quite well.

I was curious if it would be possible to tweak the way this works a bit. I
tried to do some mods to it, but was unsuccessful. I am not very familiar
with VB.

The application that I am using this in is a budget which contains a
worksheet for every month. Each sheet is labeled in the following format:
quot;Jan, Feb, Mar, etc.quot;. With this labelling scheme, I use
TEXT(MONTH(NOW()),quot;mmmquot;) in my formulas to access the current sheet for my
summary sheet (labeled quot;Summaryquot;). It is for this summary sheet that I want
the compiled list of names and line numbers for the current month. (All
months use the same sheet format.)

Is it possible to write the macro in such a way that when I access the sheet
quot;Summaryquot;, it automatically runs the macro, updating the summary list?
(Rather than having to click a button or go to Toolsgt;Macro)

Also, is it possible to format the list so that it displays in more than one
column depending on the number of entries? So if I have thirty or fewer
entries, it would just fill one column, but if it gets to be more than thirty
it would form a second column like this:

6Entry 146Entry 31
7Entry 251Entry 32
...
44Entry 2988
45Entry 3091

This would be a nice-to-have, but not an absolute necessity.

Thanks again for all your help. I really appreciate it!

quot;Otto Moehrbachquot; wrote:

gt; Richard
gt; This little macro does what you want. Note that this macro works on
gt; Column B only. You had said that you didn't want the categories in Column A
gt; picked up at all.
gt; This macro loops through all the cells in Column B from B1 to the last entry
gt; in the column. All blank cells are ignored.
gt; For each occupied cell in Column B, this macro will put the row number
gt; in Column A of a sheet named quot;Listquot;, and the contents of the cell in Column
gt; B of the quot;Listquot; sheet. This macro should be placed in a standard module.
gt; Please post back if you need more or you want to make some changes. HTH
gt; Otto
gt; Sub ListData()
gt; Dim RngColB As Range
gt; Dim i As Range
gt; Dim Dest As Range
gt; Set Dest = Sheets(quot;Listquot;).Range(quot;A1quot;)
gt; Set RngColB = Range(quot;B1quot;, Range(quot;Bquot; amp; Rows.Count).End(xlUp))
gt; For Each i In RngColB
gt; If IsEmpty(i) Then GoTo NextCell
gt; Dest.Value = i.Row
gt; Dest.Offset(, 1).Value = i.Value
gt; Set Dest = Dest.Offset(1)
gt; NextCell:
gt; Next i
gt; End Sub
gt; quot;Richard Walkerquot; gt; wrote in message
gt; ...
gt; gt;I have ten sets of cells of ten cells each (each cell is on a different
gt; gt; line). Each cell may or may not contain data. I want to build a summary
gt; gt; sheet listing only data within the cells and the line number that that
gt; gt; cell
gt; gt; is on. (omit all blank cells) I hope this makes sense.
gt; gt;
gt; gt; The application is a budget worksheet that has ten categories with ten
gt; gt; line
gt; gt; items in each category. Each category may contain blank lines. I want a
gt; gt; concise summary on a separate sheet, eliminating the category headings and
gt; gt; all blank lines. How do I do this?
gt; gt;
gt; gt; Thanks in advance.
gt;
gt;
gt;

Richard
Yes, all that can be done. A few questions though. Will the Summary
sheet always be cleared (empty)? I ask this because you say you want this
to happen whenever the Summary sheet is selected. Do you want the code to
clear the sheet (less headers) before copying the data?
Another question. You say you want to copy into multiple columns when
the list is yea long. But the list is two columns (the row number and the
data) wide already. I just want to be sure I'm not missing something in
what you say.
What you want with the multiple columns is called quot;snakingquot; the columns,
usually done prior to printing. I would write the code to copy everything
into Columns A amp; B initially. Once that is done the code will look at
what's there and snake it if necessary. Post back with clarification. Otto
quot;Richard Walkerquot; gt; wrote in message
...
gt; Thanks Otto, this strips and arranges the data quite well.
gt;
gt; I was curious if it would be possible to tweak the way this works a bit.
gt; I
gt; tried to do some mods to it, but was unsuccessful. I am not very familiar
gt; with VB.
gt;
gt; The application that I am using this in is a budget which contains a
gt; worksheet for every month. Each sheet is labeled in the following format:
gt; quot;Jan, Feb, Mar, etc.quot;. With this labelling scheme, I use
gt; TEXT(MONTH(NOW()),quot;mmmquot;) in my formulas to access the current sheet for my
gt; summary sheet (labeled quot;Summaryquot;). It is for this summary sheet that I
gt; want
gt; the compiled list of names and line numbers for the current month. (All
gt; months use the same sheet format.)
gt;
gt; Is it possible to write the macro in such a way that when I access the
gt; sheet
gt; quot;Summaryquot;, it automatically runs the macro, updating the summary list?
gt; (Rather than having to click a button or go to Toolsgt;Macro)
gt;
gt; Also, is it possible to format the list so that it displays in more than
gt; one
gt; column depending on the number of entries? So if I have thirty or fewer
gt; entries, it would just fill one column, but if it gets to be more than
gt; thirty
gt; it would form a second column like this:
gt;
gt; 6 Entry 1 46 Entry 31
gt; 7 Entry 2 51 Entry 32
gt; ...
gt; 44 Entry 29 88
gt; 45 Entry 30 91
gt;
gt; This would be a nice-to-have, but not an absolute necessity.
gt;
gt; Thanks again for all your help. I really appreciate it!
gt;
gt; quot;Otto Moehrbachquot; wrote:
gt;
gt;gt; Richard
gt;gt; This little macro does what you want. Note that this macro works on
gt;gt; Column B only. You had said that you didn't want the categories in
gt;gt; Column A
gt;gt; picked up at all.
gt;gt; This macro loops through all the cells in Column B from B1 to the last
gt;gt; entry
gt;gt; in the column. All blank cells are ignored.
gt;gt; For each occupied cell in Column B, this macro will put the row
gt;gt; number
gt;gt; in Column A of a sheet named quot;Listquot;, and the contents of the cell in
gt;gt; Column
gt;gt; B of the quot;Listquot; sheet. This macro should be placed in a standard module.
gt;gt; Please post back if you need more or you want to make some changes. HTH
gt;gt; Otto
gt;gt; Sub ListData()
gt;gt; Dim RngColB As Range
gt;gt; Dim i As Range
gt;gt; Dim Dest As Range
gt;gt; Set Dest = Sheets(quot;Listquot;).Range(quot;A1quot;)
gt;gt; Set RngColB = Range(quot;B1quot;, Range(quot;Bquot; amp; Rows.Count).End(xlUp))
gt;gt; For Each i In RngColB
gt;gt; If IsEmpty(i) Then GoTo NextCell
gt;gt; Dest.Value = i.Row
gt;gt; Dest.Offset(, 1).Value = i.Value
gt;gt; Set Dest = Dest.Offset(1)
gt;gt; NextCell:
gt;gt; Next i
gt;gt; End Sub
gt;gt; quot;Richard Walkerquot; gt; wrote in
gt;gt; message
gt;gt; ...
gt;gt; gt;I have ten sets of cells of ten cells each (each cell is on a different
gt;gt; gt; line). Each cell may or may not contain data. I want to build a
gt;gt; gt; summary
gt;gt; gt; sheet listing only data within the cells and the line number that that
gt;gt; gt; cell
gt;gt; gt; is on. (omit all blank cells) I hope this makes sense.
gt;gt; gt;
gt;gt; gt; The application is a budget worksheet that has ten categories with ten
gt;gt; gt; line
gt;gt; gt; items in each category. Each category may contain blank lines. I want
gt;gt; gt; a
gt;gt; gt; concise summary on a separate sheet, eliminating the category headings
gt;gt; gt; and
gt;gt; gt; all blank lines. How do I do this?
gt;gt; gt;
gt;gt; gt; Thanks in advance.
gt;gt;
gt;gt;
gt;gt;
Richard

The 3 macros below do what you want. The first macro is a sheet
macro and must be placed in the sheet module for the quot;Summaryquot; sheet. When
this macro fires, it will call the other 2 macros. Note that this macro
will fire every time you select the quot;Summaryquot; sheet. Every time. To access
the quot;Summaryquot; sheet module, right-click on the quot;Summaryquot; sheet tab, select
View Code. Paste the first macro into that module. You may find it
somewhat of a nuisance for this macro to fire (and set off the other two)
every time you select the Summary sheet while you are setting up your file.
To prevent this macro from firing, access the Summary sheet module and
remark out all 3 lines of code.

The other two macros go in a standard module.

When you select the quot;Summaryquot; sheet, the following will take place:

The used range of the quot;Summaryquot; sheet will be cleared.

The code will figure out which sheet is for the current month (you must have
12 sheets named Jan, Feb, Mar, etc).

The data in this month's sheet will be copied to the quot;Summaryquot; sheet as we
said before.

If Columns A:B of the quot;Summaryquot; sheet (used range) exceeds row 30, the code
will snake the data into neighboring columns.

Note that the code will not insert a blank column between the snaked
columns. I didn't know if you wanted that or not. Come back if you want
that.

If you are unsure of where to put what macros, email me and I'll
send you a small file that has everything placed properly. My email address
is . Remove the quot;nopquot; from this address. HTH OttoPrivate Sub Worksheet_Activate()

Call GetSummary

End Sub
Sub GetSummary()

Dim RngColB As Range

Dim i As Range

Dim Dest As Range

Application.ScreenUpdating = False

ActiveSheet.UsedRange.ClearContents

Set Dest = Range(quot;A1quot;)

With Sheets(Format(Date, quot;mmmquot;))

Set RngColB = .Range(quot;B1quot;, .Range(quot;Bquot; amp; Rows.Count).End(xlUp))

For Each i In RngColB

If IsEmpty(i) Then GoTo NextCell

Dest.Value = i.Row

Dest.Offset(, 1).Value = i.Value

Set Dest = Dest.Offset(1)

NextCell:

Next i

End With

Call SnakeSum

Application.ScreenUpdating = True

MsgBox quot;Summary is complete.quot;

End Sub
Sub SnakeSum()

Dim HowMany As Long

Dim RngCopy As Range

Dim Dest As Range

HowMany = 30

If Range(quot;Aquot; amp; Rows.Count).End(xlUp).Row lt;= HowMany Then Exit Sub

Set Dest = Range(quot;C1quot;)

Set RngCopy = Cells(1, 1)

Do

RngCopy.Resize(HowMany, 2).Copy Dest

Set RngCopy = RngCopy.Offset(HowMany)

Set Dest = Dest.Offset(, 2)

Loop Until IsEmpty(RngCopy.Value)

Columns(quot;A:Bquot;).Delete

End Sub

quot;Richard Walkerquot; gt; wrote in message
...
gt; Thanks Otto, this strips and arranges the data quite well.
gt;
gt; I was curious if it would be possible to tweak the way this works a bit.
gt; I
gt; tried to do some mods to it, but was unsuccessful. I am not very familiar
gt; with VB.
gt;
gt; The application that I am using this in is a budget which contains a
gt; worksheet for every month. Each sheet is labeled in the following format:
gt; quot;Jan, Feb, Mar, etc.quot;. With this labelling scheme, I use
gt; TEXT(MONTH(NOW()),quot;mmmquot;) in my formulas to access the current sheet for my
gt; summary sheet (labeled quot;Summaryquot;). It is for this summary sheet that I
gt; want
gt; the compiled list of names and line numbers for the current month. (All
gt; months use the same sheet format.)
gt;
gt; Is it possible to write the macro in such a way that when I access the
gt; sheet
gt; quot;Summaryquot;, it automatically runs the macro, updating the summary list?
gt; (Rather than having to click a button or go to Toolsgt;Macro)
gt;
gt; Also, is it possible to format the list so that it displays in more than
gt; one
gt; column depending on the number of entries? So if I have thirty or fewer
gt; entries, it would just fill one column, but if it gets to be more than
gt; thirty
gt; it would form a second column like this:
gt;
gt; 6 Entry 1 46 Entry 31
gt; 7 Entry 2 51 Entry 32
gt; ...
gt; 44 Entry 29 88
gt; 45 Entry 30 91
gt;
gt; This would be a nice-to-have, but not an absolute necessity.
gt;
gt; Thanks again for all your help. I really appreciate it!
gt;
gt; quot;Otto Moehrbachquot; wrote:
gt;
gt;gt; Richard
gt;gt; This little macro does what you want. Note that this macro works on
gt;gt; Column B only. You had said that you didn't want the categories in
gt;gt; Column A
gt;gt; picked up at all.
gt;gt; This macro loops through all the cells in Column B from B1 to the last
gt;gt; entry
gt;gt; in the column. All blank cells are ignored.
gt;gt; For each occupied cell in Column B, this macro will put the row
gt;gt; number
gt;gt; in Column A of a sheet named quot;Listquot;, and the contents of the cell in
gt;gt; Column
gt;gt; B of the quot;Listquot; sheet. This macro should be placed in a standard module.
gt;gt; Please post back if you need more or you want to make some changes. HTH
gt;gt; Otto
gt;gt; Sub ListData()
gt;gt; Dim RngColB As Range
gt;gt; Dim i As Range
gt;gt; Dim Dest As Range
gt;gt; Set Dest = Sheets(quot;Listquot;).Range(quot;A1quot;)
gt;gt; Set RngColB = Range(quot;B1quot;, Range(quot;Bquot; amp; Rows.Count).End(xlUp))
gt;gt; For Each i In RngColB
gt;gt; If IsEmpty(i) Then GoTo NextCell
gt;gt; Dest.Value = i.Row
gt;gt; Dest.Offset(, 1).Value = i.Value
gt;gt; Set Dest = Dest.Offset(1)
gt;gt; NextCell:
gt;gt; Next i
gt;gt; End Sub
gt;gt; quot;Richard Walkerquot; gt; wrote in
gt;gt; message
gt;gt; ...
gt;gt; gt;I have ten sets of cells of ten cells each (each cell is on a different
gt;gt; gt; line). Each cell may or may not contain data. I want to build a
gt;gt; gt; summary
gt;gt; gt; sheet listing only data within the cells and the line number that that
gt;gt; gt; cell
gt;gt; gt; is on. (omit all blank cells) I hope this makes sense.
gt;gt; gt;
gt;gt; gt; The application is a budget worksheet that has ten categories with ten
gt;gt; gt; line
gt;gt; gt; items in each category. Each category may contain blank lines. I want
gt;gt; gt; a
gt;gt; gt; concise summary on a separate sheet, eliminating the category headings
gt;gt; gt; and
gt;gt; gt; all blank lines. How do I do this?
gt;gt; gt;
gt;gt; gt; Thanks in advance.
gt;gt;
gt;gt;
gt;gt;