How to copy non-contiguous columns to a text file

[Jennifer Murphy]

I would like to copy columns just B and J to a tab-delimited text file.
Excel will allow me to select those 2 columns and perform various
operations on them (bold, center, etc.). But when I try to copy (Ctrl-C)
and then paste into NotePad (Ctrl-V), I get all of the intervening
columns.

I know I can rearrange the columns, but I'd like to know if there is a
way to copy non-contiguous columns.

 

[Sixthsense]

Hi Jennifer,

Hold Cntrl and select the B Column Data and hold the Cntrl and select
the J Column data also. Now the B and J Column data only will be
getting selected. Do Cntrl+C to copy the data and paste it in the
notepad file.

Holding Cntrl and using the mouse will help to do multiple area
selection.

 

[GS]

One way...

I mocked up a wks as per your scenario to test this. It loads all the
data in cols B to J into a Variant type variable, which results in a
multi-dim array. The required data is written to a String type variable
which you can then write to a text file using standard file I/O methods
as in the sample procedure below.

Sub CopyTwoCols()
Dim vData1, i, sData As String
vData1 = Range("B1:J4")
For i = LBound(vData1) To UBound(vData1)
sData = sData & vData1(i, 1) & vbTab & vData1(i, 9) & vbCrLf
Next
sData = Mid$(sData, 1, InStrRev(sData, vbCrLf) - 1)
WriteTextFileContents sData, "C:\Copy2Cols.txt" '//write to file
End Sub


Sub WriteTextFileContents(Text As String, Filename As String, _
Optional AppendMode As Boolean = False)
' A reuseable procedure to write or append large amounts of data
' to a text file in one single step.
Dim iNum As Integer
On Error GoTo ErrHandler
iNum = FreeFile(): If AppendMode Then Open Filename For Append As
#iNum _
Else Open Filename For Output As
#iNum: Print #iNum, Text;

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Sub 'WriteTextFileContents()

 

[Jennifer Murphy]

Hi Jennifer,

Hold Cntrl and select the B Column Data and hold the Cntrl and select
the J Column data also. Now the B and J Column data only will be
getting selected. Do Cntrl+C to copy the data and paste it in the
notepad file.

As I tried to explain in my original post, I have no trouble selecting
the two non-adjacent columns. It's what happens next that it the
problem.

1. If I select column B (without the ctrl key) and then seelct column J
using the ctrl key, the copy works, but copies all of the intervening
columns.

2. If I select column B using the ctrl key (as I think you are
suggesting) and then select column J (again using the ctrl key), I get
an error when I try the Ctrl-C shortcut to copy the data. The error is

"That command cannot be sued on multiple selections."

I have Office 2007.

 

[joeu2004]

As I tried to explain in my original post, I have
no trouble selecting the two non-adjacent columns.
It's what happens next that it the problem.

I think that was perfectly clear in your original posting. For anyone who
does not understand, all they need to do is try it. I reproduced the
problem with Office 2003 on WinXP.

2. If I select column B using the ctrl key [...]
and then select column J (again using the ctrl key),
I get an error when I try the Ctrl-C shortcut to copy
the data. The error is
"That command cannot be sued on multiple selections."

In my experiments, that depends on the alignment of the separate selections.

If I select parallel data (e.g. B1:B10 and J1:J10), the paste into Notepad
misbehaves exactly as you described initially, to wit: all the intervening
data is also copied unintentionally.

If I select non-parallel data (e.g. B1:B10 and J11:J20), I get the error
that you describe above when I try to copy. We don't even get to the paste
problem.

Note that in both cases, the selection is a collection of ranges. You can
see that using MsgBox Selection.Address in VBA.

My take on this is: there is no workaround, other than a macro that copies
the non-contiguous data for you. (And in that case, you need to decide and
specify exactly how the non-parallel example should be handled. I can think
of two ways to do, at least.)

Off-hand, I do not know how to copy just the selected data to the clipboard
just as if you had done it with ctrl-C; that is, so that ctrl-V in Notepad
does what you intended. A Google search reveals a couple of methods for
getting VBA data (in contrast to Excel data) into the clipboard. But I am
not familiar enough with the clipboard architecture to know all the
ins-and-outs.

If you would like to use such a macro, I suggest that you repost your
inquiry using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel. It's
not that I like that forum. It's just that MS has ceased to support the
Usenet newsgroups; see
http://www.microsoft.com/communities/newsgroups/default.mspx. Hence,
participation here is limited to the sites that share a common newsgroup
mirror, which is no longer centralized at MS. Many knowledgable Excel
experts no longer look at the newsgroups.

PS: The phrasing of your original posting was "perfect", IMHO.

 

[Claus Busch]

Hi Jennifer,

Am Tue, 19 Jul 2011 06:36:27 -0400 schrieb Jennifer Murphy:

I know I can rearrange the columns, but I'd like to know if there is a
way to copy non-contiguous columns.

hide columns C:I before copying.


Regards
Claus Busch

 

[Gord]

Too easy<g>


Gord

Hi Jennifer,

Am Tue, 19 Jul 2011 06:36:27 -0400 schrieb Jennifer Murphy:


hide columns C:I before copying.


Regards
Claus Busch

 

[GS]

Alternative approach...

I often encounter situations where external data does not provide the
fields order I want, and so I have to reorder the columns accordingly.
This is the basis for creating the code I provided earlier.

The following code will allow you to write columns to a text file in
any order you specify. (My project hard codes the order and the
filename, but I've revised the procedure to allow user input for this)


Option Explicit

Sub CopyColsToTextFile()
Dim vData1, i, j, vFilename, vColsToCopy
Dim sData As String, sMsg As String

'Get the output filename
If Not GetFilename(vFilename) Then Exit Sub '//user cancels

'Get the cols to copy
sMsg = "Enter the labels of the columns you want to copy separated by
a comma." & vbCrLf & vbCrLf
sMsg = sMsg & "** Make sure to enter the labels in the order you
want the data to appear in the text file."
vColsToCopy = Application.InputBox(Prompt:=sMsg, Type:=2)
If Not vColsToCopy = "" Then '//proceed only if we have a list
vColsToCopy = Split(vColsToCopy, ",")
vData1 = ActiveSheet.UsedRange
For i = LBound(vData1) To UBound(vData1)
sData = sData & vbCrLf
For j = LBound(vColsToCopy) To UBound(vColsToCopy)
On Error GoTo ErrExit
sData = sData & vData1(i, Columns(vColsToCopy(j)).Column) &
vbTab
Next 'j
sData = Mid$(sData, 1, Len(sData) - 1)
Next 'i
sData = Mid$(sData, 3)
WriteTextFileContents sData, CStr(vFilename), True '//write to file
End If 'Not vColsToCopy = ""

NormalExit:
Exit Sub

ErrExit:
sMsg = "You have entered an invalid column label." & vbCrLf & vbCrLf
sMsg = sMsg & "Please try again!"
MsgBox sMsg, vbCritical, "Invalid Input !"
End Sub

Sub WriteTextFileContents(Text As String, Filename As String, _
Optional AppendMode As Boolean = False)
' A reuseable procedure to write or append
' large amounts of data to a text file in one single step.
Dim iNum As Integer
On Error GoTo ErrHandler
iNum = FreeFile()
If AppendMode Then
Open Filename For Append As #iNum
Print #iNum, Text '//adds a final blank line
Else
Open Filename For Output As #iNum
Print #iNum, Text; '//excludes the final blank line
End If

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Sub 'WriteTextFileContents()

Function GetFilename(Filename As Variant) As Boolean
Filename =
Application.GetSaveAsFilename(InitialFileName:="Data1.txt",
fileFilter:="Text Files (*.txt), *.txt")
GetFilename = (Filename <> False)
End Function

P.S.: Watch the wordwrap!

 

[Jennifer Murphy]

Hi Jennifer,

Am Tue, 19 Jul 2011 06:36:27 -0400 schrieb Jennifer Murphy:


hide columns C:I before copying.

Yep, that works perfectly. Thanks.

 

[Jennifer Murphy]

As I tried to explain in my original post, I have
no trouble selecting the two non-adjacent columns.
It's what happens next that it the problem.

I think that was perfectly clear in your original posting. For anyone who
does not understand, all they need to do is try it. I reproduced the
problem with Office 2003 on WinXP.

2. If I select column B using the ctrl key [...]
and then select column J (again using the ctrl key),
I get an error when I try the Ctrl-C shortcut to copy
the data. The error is
"That command cannot be sued on multiple selections."

In my experiments, that depends on the alignment of the separate selections.

If I select parallel data (e.g. B1:B10 and J1:J10), the paste into Notepad
misbehaves exactly as you described initially, to wit: all the intervening
data is also copied unintentionally.

If I select non-parallel data (e.g. B1:B10 and J11:J20), I get the error
that you describe above when I try to copy. We don't even get to the paste
problem.

Note that in both cases, the selection is a collection of ranges. You can
see that using MsgBox Selection.Address in VBA.

My take on this is: there is no workaround, other than a macro that copies
the non-contiguous data for you. (And in that case, you need to decide and
specify exactly how the non-parallel example should be handled. I can think
of two ways to do, at least.)

Off-hand, I do not know how to copy just the selected data to the clipboard
just as if you had done it with ctrl-C; that is, so that ctrl-V in Notepad
does what you intended. A Google search reveals a couple of methods for
getting VBA data (in contrast to Excel data) into the clipboard. But I am
not familiar enough with the clipboard architecture to know all the
ins-and-outs.

If you would like to use such a macro, I suggest that you repost your
inquiry using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel. It's
not that I like that forum. It's just that MS has ceased to support the
Usenet newsgroups; see
http://www.microsoft.com/communities/newsgroups/default.mspx. Hence,
participation here is limited to the sites that share a common newsgroup
mirror, which is no longer centralized at MS. Many knowledgable Excel
experts no longer look at the newsgroups.

I think I will look into a macro as I do need to do some other
processing. Garry has already posted a sample here, so I'll see if he is
able to suggest some code.

PS: The phrasing of your original posting was "perfect", IMHO.

Thanks, I try to be clear...;-)

 

[Jennifer Murphy]

One way...

I mocked up a wks as per your scenario to test this. It loads all the
data in cols B to J into a Variant type variable, which results in a
multi-dim array. The required data is written to a String type variable
which you can then write to a text file using standard file I/O methods
as in the sample procedure below.

Sub CopyTwoCols()
Dim vData1, i, sData As String
vData1 = Range("B1:J4")
For i = LBound(vData1) To UBound(vData1)
sData = sData & vData1(i, 1) & vbTab & vData1(i, 9) & vbCrLf
Next
sData = Mid$(sData, 1, InStrRev(sData, vbCrLf) - 1)
WriteTextFileContents sData, "C:\Copy2Cols.txt" '//write to file
End Sub


Sub WriteTextFileContents(Text As String, Filename As String, _
Optional AppendMode As Boolean = False)
' A reuseable procedure to write or append large amounts of data
' to a text file in one single step.
Dim iNum As Integer
On Error GoTo ErrHandler
iNum = FreeFile(): If AppendMode Then Open Filename For Append As
#iNum _
Else Open Filename For Output As
#iNum: Print #iNum, Text;

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Sub 'WriteTextFileContents()

I think a macro (UDF?) is the way to go as I also need to do some more
processing, which I was doing manually in Word. It would be handy to
have it all done at once.

Do you have time to post a sample macro that could perform these tasks:

1. Take a range as an argument. That is, I'd select a range of cells
(B1:J43928) and then invoke the macro.

2. The macro assumes that the first column contains text and the last
column contains integers (1-10 million). The intervening columns, if
any, are irrelevant and ignored.

3. The macro assumes that the rows are sorted on the first column.

4. The macro has a constant containing a list of invalid characters:

Const BadChars as String = "%$#@.&*"

5. Step through the rows one at a time.

5a. If the text in first column contains any characters in BadChars,
skip that row and continue with the next row.

5b. If the text in the first column is the same as (exactly equal to)
the text in the previous row, add the integer value to the previous
integer value. That is, add up the integers for all rows with the same
text.

5c. If the text in the first column is different from the text in the
previous row, write out the previous text and integer values separated
by a tab character. Start a new counter.



Here's some sample input with 5 columns. We only care about the data in
columns 1 and 5.

Col1 Col2 Col3 Col4 Col5
1 abc xx xx xx 100
2 abcd xx xx xx 120
3 abcd xx xx xx 130
4 abd xx xx xx 90
5 c$$d xx xx xx 250
6 def xx xx xx 100
7 def xx xx xx 200
8 def xx xx xx 300
9 g.xx xx xx xx 123


Here's the output in a text file. The "->" character indicated a tab.

abc->100
abcd->250
abd->90
def->600


Thanks

 

[GS]

Jennifer Murphy pretended :

I think a macro (UDF?) is the way to go as I also need to do some more
processing, which I was doing manually in Word. It would be handy to
have it all done at once.

Not a task for a UDF since it won't be used in cell formulas.

I can't imagine what part of this you were doing in Word since IMO
all of it can be more easily done in Excel where the data happens to
be.

Do you have time to post a sample macro that could perform these tasks:

I would only be able to work on it around/between other things. I
already have working projects that do most everything you want and so
will be a matter of 'snipping' out sections of code and
creating/revising code to fit your needs.

1. Take a range as an argument. That is, I'd select a range of cells
(B1:J43928) and then invoke the macro.

The alternate approach I posted assumes you want to process entire
columns of data in any order you wish by specifying the column labels
in the order you want to arrange the data. Are you now saying that you
may only want to process partial data, meaning less than entire column
(excluding skipped rows)? The way it works now you don't have to select
anything (except the sheet containing the data you want to process).

2. The macro assumes that the first column contains text and the last
column contains integers (1-10 million). The intervening columns, if
any, are irrelevant and ignored.

Are you saying the data will be validated before running the macro? Or
are you saying you want the macro to validate the data?

By 'integer' do you mean whole numbers only? How do you want to handle
non integer values?

3. The macro assumes that the rows are sorted on the first column.

Are you saying the data will be sorted before running the macro? Or are
you saying you want the macro to check that the data is sorted? Or are
you saying you want the macro to actually do a sort?

4. The macro has a constant containing a list of invalid characters:

Const BadChars as String = "%$#@.&*"

No problem.

5. Step through the rows one at a time.

5a. If the text in first column contains any characters in BadChars,
skip that row and continue with the next row.

Could the row be deleted? (This would help make other tasks easier and
more efficiently done)

5b. If the text in the first column is the same as (exactly equal to)
the text in the previous row, add the integer value to the previous
integer value. That is, add up the integers for all rows with the same
text.

5c. If the text in the first column is different from the text in the
previous row, write out the previous text and integer values separated
by a tab character. Start a new counter.

Are you saying you want only a unique set of resulting data, 1 line per
value in col1? (ie: consolidated by col1 text)

Here's some sample input with 5 columns. We only care about the data in
columns 1 and 5.

Col1 Col2 Col3 Col4 Col5
1 abc xx xx xx 100
2 abcd xx xx xx 120
3 abcd xx xx xx 130
4 abd xx xx xx 90
5 c$$d xx xx xx 250
6 def xx xx xx 100
7 def xx xx xx 200
8 def xx xx xx 300
9 g.xx xx xx xx 123

In my 2nd post, this would be indicated by entering a,e when prompted
for col labels.

Here's the output in a text file. The "->" character indicated a tab.

abc->100
abcd->250
abd->90
def->600

No problem. I'll wait for your answers to my Qs.

 

[Jennifer Murphy]

Jennifer Murphy pretended :

Not a task for a UDF since it won't be used in cell formulas.

OK, I keep getting the terms confused.

I can't imagine what part of this you were doing in Word

Actually, I was pasting the entire range into Word, deleting the
intervening columns, saving the result as a text (.txt) file, then
processing that with a general-purpose scripting language that I've used
for years. It's a messy procedure.

since IMO
all of it can be more easily done in Excel where the data happens to
be.

To be sure. I've just never been able to get very proficient at VBA.
I've just never found it natural to use.

I would only be able to work on it around/between other things. I
already have working projects that do most everything you want and so
will be a matter of 'snipping' out sections of code and
creating/revising code to fit your needs.

There is absolutely no urgency on this. I'd be very grateful for
anything you can do whenever you get around to it.

The alternate approach I posted assumes you want to process entire
columns of data in any order you wish by specifying the column labels
in the order you want to arrange the data. Are you now saying that you
may only want to process partial data, meaning less than entire column
(excluding skipped rows)? The way it works now you don't have to select
anything (except the sheet containing the data you want to process).

I just looked at your alternate approach again. I didn't understand what
you were proposing. A macro that takes column labels is fine and more
general than my suggestion. We'd just need a way to specify the first
row because the actual data starts on row 3 or 4. I assume it will
continue until it encounters a blank cell. The sheet can have as many as
500,000 rows.

What if I select the the range from the first data cell in the first
column to the first data cell in the second column? If my data is in
columns B and J and the data starts in Row 5, I'd select B5:J5 and
invoke the macro. The macro would then have the starting point for both
columns. The only question it would have to ask is whether B & J need to
be reversed. It could even figure that out itself since one is text and
one is numeric.

Are you saying the data will be validated before running the macro? Or
are you saying you want the macro to validate the data?

By 'integer' do you mean whole numbers only? How do you want to handle
non integer values?

The data should be valid. That is, of the two columns I care about, the
first column will contain text and the second column will contain
integers. Some of the text contains characters that are invalid for my
needs (see below). I want those rows filtered out (ignored). The
integers should be positive integers (whole numbers) and greater than
zero. However, adding a check and an error message if anything else is
encountered would be good.

I don't care what's in any other columns.

Are you saying the data will be sorted before running the macro? Or are
you saying you want the macro to check that the data is sorted? Or are
you saying you want the macro to actually do a sort?

The data comes sorted by the numeric column (descending order). The
sample data I provided (see below) would look like this in its native
form:

Col1 Col2 Col3 Col4 Col5
1 def xx xx xx 300
2 c$$d xx xx xx 250
3 def xx xx xx 200
4 abcd xx xx xx 130
5 g.xx xx xx xx 123
6 abcd xx xx xx 120
7 abc xx xx xx 100
8 def xx xx xx 100
9 abd xx xx xx 90

There are also a few rows above the data with headers. The actual data
might look like this:

A B C D E F G H I
1
2 h1 h2 h3 h4 h5 h6 h7 h8 h9
3 xx xx def xx xx xx 300 xx xx
4 xx xx c$$d xx xx xx 250 xx xx
5 xx xx def xx xx xx 200 xx xx
6 xx xx abcd xx xx xx 130 xx xx
7 xx xx g.xx xx xx xx 123 xx xx
8 xx xx abcd xx xx xx 120 xx xx
9 xx xx abc xx xx xx 100 xx xx
10 xx xx def xx xx xx 100 xx xx
11 xx xx abd xx xx xx 90 xx xx
...

In this example, I only care about C3:C11 & G3:G11. In the real data,
the integers (Col G) are much larger (1-1E8) and there can be up to
500,000 rows.

What I do now is manually sort the data. The sort is only to make the
detection of duplicate entries easier. I then resort it by the numbers.
If the macro can do the sort, so much the better. I would prefer not to
alter the source data at all. I usually make a copy of it and delete it
later.

Now that I've examined your macros a little more, I see that you load
all of the data into array variables and process it in the macro rather
than reading the data one row at a time. I imagine that that would be
faster. If the macro can do the while job, that would be great:

* Read the two columns
* Sort by the text column
* Do the processing
* Resort by the numeric column
* Write the results to a text file

No problem.


Could the row be deleted? (This would help make other tasks easier and
more efficiently done)

I would prefer not to alter the input file. If you are reading the data
into a variable, then deleting is OK, but seems unnecessary to me.

Are you saying you want only a unique set of resulting data, 1 line per
value in col1? (ie: consolidated by col1 text)

I tried to illustrate this in the sample data. The text column is not
unique. That is, there can be multiple rows with the same exact data. In
my sample data below, there are 2 rows with "abcd" and three with "def".
I want these rows consolidated into a single output row containing the
sum of the numeric values.

In my 2nd post, this would be indicated by entering a,e when prompted
for col labels.


No problem. I'll wait for your answers to my Qs.

How did I do? ;-)

 

[GS]

Jennifer Murphy laid this down on his screen :

Jennifer Murphy pretended :

Not a task for a UDF since it won't be used in cell formulas.

OK, I keep getting the terms confused.

I can't imagine what part of this you were doing in Word

Actually, I was pasting the entire range into Word, deleting the
intervening columns, saving the result as a text (.txt) file, then
processing that with a general-purpose scripting language that I've used
for years. It's a messy procedure.
Ugh!

since IMO
all of it can be more easily done in Excel where the data happens to
be.

To be sure. I've just never been able to get very proficient at VBA.
I've just never found it natural to use.

I was speaking about doing the same manually in Excel, then using Save
As... to a text file.

There is absolutely no urgency on this. I'd be very grateful for
anything you can do whenever you get around to it.

I appreciate that, but don't think it will take too long since I
already have working code to use that is buried in several projects.

I just looked at your alternate approach again. I didn't understand what
you were proposing. A macro that takes column labels is fine and more
general than my suggestion. We'd just need a way to specify the first
row because the actual data starts on row 3 or 4. I assume it will
continue until it encounters a blank cell. The sheet can have as many as
500,000 rows.

This, I see, is because you use blank rows for spacing. This is not
good practice when working with records of data since, technically, it
results in blank records in the data table AND any recordset you load
from that data table. Better to use RowHeight for spacing and keep the
records contiguous. Same holds true for your data file, which should
always include headers (a.k.a. field names) and no blank lines within
the data or at the end of the file.

What if I select the the range from the first data cell in the first
column to the first data cell in the second column? If my data is in
columns B and J and the data starts in Row 5, I'd select B5:J5 and
invoke the macro. The macro would then have the starting point for both
columns. The only question it would have to ask is whether B & J need to
be reversed. It could even figure that out itself since one is text and
one is numeric.

As I already explained, the alternate approach code allows you to
specify which columns to process AND the order you want them in. So if
you wanted ColJ data followed by ColB data then just enter j,b when
prompted.

What I propose is to include the headers (as field names) and ignore
blank rows. Better yet if you delete blank rows prior to running the
process.

<FYI>
Just to clarify:
I highly recommend employing the same rules that apply to databases
when using spreadsheets or text files as data stores. That way, the
standard data tools can be used (like ADO, for example) to work with
the data. Also, this makes the data store 'provider friendly' when the
data is required to be used by other systems.

Each worksheet in a workbook represents a data table; each row
represents a record.

Each text file represents a data table; each line represents a record.

Both should ALWAYS include field names (column headings).

The data should be valid. That is, of the two columns I care about, the
first column will contain text and the second column will contain
integers. Some of the text contains characters that are invalid for my
needs (see below). I want those rows filtered out (ignored). The
integers should be positive integers (whole numbers) and greater than
zero. However, adding a check and an error message if anything else is
encountered would be good.

What do you want to do with integers that contain decimals? (ignore,
round or truncate)

I don't care what's in any other columns.

Neither will the macro!

The data comes sorted by the numeric column (descending order). The
sample data I provided (see below) would look like this in its native
form:

Col1 Col2 Col3 Col4 Col5
1 def xx xx xx 300
2 c$$d xx xx xx 250
3 def xx xx xx 200
4 abcd xx xx xx 130
5 g.xx xx xx xx 123
6 abcd xx xx xx 120
7 abc xx xx xx 100
8 def xx xx xx 100
9 abd xx xx xx 90

There are also a few rows above the data with headers. The actual data
might look like this:

A B C D E F G H I
1
2 h1 h2 h3 h4 h5 h6 h7 h8 h9
3 xx xx def xx xx xx 300 xx xx
4 xx xx c$$d xx xx xx 250 xx xx
5 xx xx def xx xx xx 200 xx xx
6 xx xx abcd xx xx xx 130 xx xx
7 xx xx g.xx xx xx xx 123 xx xx
8 xx xx abcd xx xx xx 120 xx xx
9 xx xx abc xx xx xx 100 xx xx
10 xx xx def xx xx xx 100 xx xx
11 xx xx abd xx xx xx 90 xx xx

In the case of storing data in a text file or spreadsheet, the first
line/row MUST contain ColHeadings/FieldNames. This is not optional and
I won't be providing any code that handles this any other way.

So I suggest you delete any blank rows in the UsedRange. This can be
done by the macro using a copy of the specified original data on a temp
wks to work with.

In this example, I only care about C3:C11 & G3:G11. In the real data,
the integers (Col G) are much larger (1-1E8) and there can be up to
500,000 rows.

What I do now is manually sort the data. The sort is only to make the
detection of duplicate entries easier. I then resort it by the numbers.
If the macro can do the sort, so much the better. I would prefer not to
alter the source data at all. I usually make a copy of it and delete it
later.

Now that I've examined your macros a little more, I see that you load
all of the data into array variables and process it in the macro rather
than reading the data one row at a time. I imagine that that would be
faster. If the macro can do the while job, that would be great:

* Read the two columns
* Sort by the text column
* Do the processing
* Resort by the numeric column
* Write the results to a text file

The macro will handle the processing in the most efficient manner to
produce the desired result. The steps I'll use will not likely follow
your list for all except the last two.<g> I will revise the code
prompting for column labels to allow you to specify the final sort
column in case this will be something that's likely to vary in future.
Example: "b,j:j" ..where the sort col is preceeded by a colon AFTER
specifying the cols to include. If you want the col order the other way
around: "j,b:j"!

I would prefer not to alter the input file. If you are reading the data
into a variable, then deleting is OK, but seems unnecessary to me.

I'll be copying the specified columns to a temp wks and so will not
affect the original in any way. The temp wks will be deleted.

I tried to illustrate this in the sample data. The text column is not
unique. That is, there can be multiple rows with the same exact data. In
my sample data below, there are 2 rows with "abcd" and three with "def".
I want these rows consolidated into a single output row containing the
sum of the numeric values.


How did I do? ;-)

You did really well! Very clear descriptions of what you have AND what
you want. Let me know any responses you have to my above comments.

 

[Jennifer Murphy]

Jennifer Murphy laid this down on his screen :

Jennifer Murphy pretended :
I think a macro (UDF?) is the way to go as I also need to do some more
processing, which I was doing manually in Word. It would be handy to
have it all done at once.

Not a task for a UDF since it won't be used in cell formulas.

OK, I keep getting the terms confused.

I can't imagine what part of this you were doing in Word

Actually, I was pasting the entire range into Word, deleting the
intervening columns, saving the result as a text (.txt) file, then
processing that with a general-purpose scripting language that I've used
for years. It's a messy procedure.
Ugh!

since IMO
all of it can be more easily done in Excel where the data happens to
be.

To be sure. I've just never been able to get very proficient at VBA.
I've just never found it natural to use.

I was speaking about doing the same manually in Excel, then using Save
As... to a text file.

There is absolutely no urgency on this. I'd be very grateful for
anything you can do whenever you get around to it.

I appreciate that, but don't think it will take too long since I
already have working code to use that is buried in several projects.

I just looked at your alternate approach again. I didn't understand what
you were proposing. A macro that takes column labels is fine and more
general than my suggestion. We'd just need a way to specify the first
row because the actual data starts on row 3 or 4. I assume it will
continue until it encounters a blank cell. The sheet can have as many as
500,000 rows.

This, I see, is because you use blank rows for spacing.

The only blank rows for spacing are at the top. Once the data rows
start, there are no blank rows until the end of the data.

This is not
good practice when working with records of data since, technically, it
results in blank records in the data table AND any recordset you load
from that data table. Better to use RowHeight for spacing and keep the
records contiguous. Same holds true for your data file, which should
always include headers (a.k.a. field names) and no blank lines within
the data or at the end of the file.

This is not my data. I get it from another source, so I have no control
over the format.

As I already explained, the alternate approach code allows you to
specify which columns to process AND the order you want them in. So if
you wanted ColJ data followed by ColB data then just enter j,b when
prompted.

What I propose is to include the headers (as field names) and ignore
blank rows. Better yet if you delete blank rows prior to running the
process.

<FYI>
Just to clarify:
I highly recommend employing the same rules that apply to databases
when using spreadsheets or text files as data stores. That way, the
standard data tools can be used (like ADO, for example) to work with
the data. Also, this makes the data store 'provider friendly' when the
data is required to be used by other systems.

Each worksheet in a workbook represents a data table; each row
represents a record.

Each text file represents a data table; each line represents a record.

Both should ALWAYS include field names (column headings).
</FYI>

It's not my data. It's in the format that it's in when I get it.

What do you want to do with integers that contain decimals? (ignore,
round or truncate)

I'm 99.999% sure that all of the numbers will be perfect integers. If
the macro encounters one that isn't, I suggest it generate an error
message and stop.

Neither will the macro!


In the case of storing data in a text file or spreadsheet, the first
line/row MUST contain ColHeadings/FieldNames. This is not optional and
I won't be providing any code that handles this any other way.

Hmmm... Seems a little rigid.

So I suggest you delete any blank rows in the UsedRange. This can be
done by the macro using a copy of the specified original data on a temp
wks to work with.


The macro will handle the processing in the most efficient manner to
produce the desired result. The steps I'll use will not likely follow
your list for all except the last two.<g> I will revise the code
prompting for column labels to allow you to specify the final sort
column in case this will be something that's likely to vary in future.
Example: "b,j:j" ..where the sort col is preceeded by a colon AFTER
specifying the cols to include. If you want the col order the other way
around: "j,b:j"!


I'll be copying the specified columns to a temp wks and so will not
affect the original in any way. The temp wks will be deleted.


You did really well! Very clear descriptions of what you have AND what
you want. Let me know any responses you have to my above comments.

I just want a simple macro that only I will use. I am not looking for a
six sigma project.

 

[joeu2004]

I just want a simple macro that only I will use.

Considering Claus's suggestion ("hide columns C:I before copying"), the
macro should be relatively simple to design without taking content into
account.

The macro I have in mind would look at the selected range collection, hide
institial columns as needed, do Selection.Copy, then restore any columns
hidden by the algorithm. (Caveat: some columns might have been hidden
already; they should not be unhidden.)

I don't have time to implement the macro. But I think this is the solution
you should be looking for.

Note: I believe this will only work for parallel selected cells. As you
discovered and I confirmed, Selection.Copy does not work at all for
non-parallel selections.

I am not looking for a six sigma project.

What's that?

 

[GS]

Jennifer Murphy wrote on 7/20/2011 :

I just want a simple macro that only I will use. I am not looking for a
six sigma project.

Simple is what Claus suggested (as joeu2004 stated). What you asked for
was a macro that "did it all in one click". The number of tasks you
asked to be done no longer qualifies the solution as 'a simple macro'.

I will give you a no nonsense, get the job done solution that will also
allow you to choose what gets written to file, and in what order it
gets written, and how you want it sorted. The flexibility doesn't add
to the workload of the project, but it does add to the
user-friendliness of using it.

No point doing anything if you only want half the job done!<g>
(no offense intended)

 

[joeu2004]

Jennifer Murphy wrote on 7/20/2011 :

I just want a simple macro that only I will use.

[....]
Simple is what Claus suggested (as joeu2004 stated).
What you asked for was a macro that "did it all in
one click". The number of tasks you asked to be done
no longer qualifies the solution as 'a simple macro'.

Sorry. I have not been following this thread closely, and I might have
missed where Jennifer requested something more than her original inquiry, as
I understood it. I thought she would be content with copying just the
selected non-contiguous columns and manually pasting them into a text file
using Notepad.

PS: Although I suggested a macro that hides unwanted columns, there might
be a relatively easier and more flexible way to go. By "flexible", I mean:
an approach that avoids the copy limitation for non-parallel ranges. But
I'm not sure Jennifer ever explained how she wants that operation to behave.
She merely noted that it produces a copy error. (Or did I?)

Oh well, I don't have time for this. Have fun!

 

[GS]

Jennifer Murphy wrote on 7/20/2011 :

I just want a simple macro that only I will use.

[....]
Simple is what Claus suggested (as joeu2004 stated).
What you asked for was a macro that "did it all in
one click". The number of tasks you asked to be done
no longer qualifies the solution as 'a simple macro'.

Sorry. I have not been following this thread closely, and I might have
missed where Jennifer requested something more than her original inquiry, as
I understood it. I thought she would be content with copying just the
selected non-contiguous columns and manually pasting them into a text file
using Notepad.

PS: Although I suggested a macro that hides unwanted columns, there might be
a relatively easier and more flexible way to go. By "flexible", I mean: an
approach that avoids the copy limitation for non-parallel ranges. But I'm
not sure Jennifer ever explained how she wants that operation to behave. She
merely noted that it produces a copy error. (Or did I?)

Oh well, I don't have time for this. Have fun!

Thanks for your comments. I believe Jennifer wants a robust solution
based on the extent of detail she provided. Fortunately, what she's
trying to do is not a stranger to me and so I can piece together a
solution from working projects. As it goes, each working project has
its unique features, and some share much of the same code with/without
customizations for context of application. The time consumer is pulling
the code out of dialogs and revising it to run in a standard module
without the benefit of setting parameters via a userform. This means
use of MsgBox and InputBox to prompt for parameters. No big deal,
really!

 

[Sixthsense]

Hi Jennifer,

Remove the Filter and Try. I am sure the Auto Filter is getting
applied in your data.