Request for help to rearrange data...

Hi,

I'm working with Word 97 and I wish to rearrange a data source file so that it is consistent. For example, I have the following sample data records:

"John Smith", "1 ABC Lane", "City State Zip"
"Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"

As you can see above the Jane Smith record has an additional field "Apt G" field. If I were doing a normal mail merge this would not be a problem. However, I need to export the data for another program rearranging the data using the following format:

"", "John Smith", "1 ABC Lane", "City State Zip"
"Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"

Is there a Find/Replace command I can use on this data or must I create a macro to handle this problem?

Thanks in advance for your assistance.

Regards,
Greg

Doug
Sat Jun 26 01:47:45 CDT 2004

Select all of the records, then run the following macro:

Dim i As Long, j As Long, data As Table, datarange As Range
Selection.ConvertToTable wdSeparateByCommas, , 4
Set data = Selection.Tables(1)
For i = 1 To data.Rows.Count
If Len(data.Cell(i, 4).Range) = 2 Then
For j = 4 To 2 Step -1
Set datarange = data.Cell(i, j - 1).Range
datarange.End = datarange.End - 1
data.Cell(i, j).Range = datarange
Next j
data.Cell(i, 1).Range = Left(data.Cell(i, 1).Range, 1) &
Left(data.Cell(i, 1).Range, 1)
End If
Next i
data.ConvertToText wdSeparateByCommas


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
"Greg Mouning" <Greg Mouning@discussions.microsoft.com> wrote in message
news:AE39E3C2-6528-4D68-9E64-B7D4A6856509@microsoft.com...
> Hi,
>
> I'm working with Word 97 and I wish to rearrange a data source file so
that it is consistent. For example, I have the following sample data
records:
>
> "John Smith", "1 ABC Lane", "City State Zip"
> "Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"
>
> As you can see above the Jane Smith record has an additional field "Apt G"
field. If I were doing a normal mail merge this would not be a problem.
However, I need to export the data for another program rearranging the data
using the following format:
>
> "", "John Smith", "1 ABC Lane", "City State Zip"
> "Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"
>
> Is there a Find/Replace command I can use on this data or must I create a
macro to handle this problem?
>
> Thanks in advance for your assistance.
>
> Regards,
> Greg

Helmut
Sat Jun 26 04:15:09 CDT 2004

Hi Greg,
(much nicer to see a real name here)
>"", "John Smith", "1 ABC Lane", "City State Zip"
>"Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"
is that really what you want?
Once the name in the second field of the record
and then again in the first field?
Shouldn't it be like this:
>"John Smith", "1 ABC Lane", "", "City State Zip"
>"Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"
---
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98

Greg
Mon Jun 28 11:45:02 CDT 2004

Thanks Doug, this is just what I needed and it works like a charm. However, can you explain why this macro adds an additional blank space after the commas?

-Greg

"Doug Robbins - Word MVP" wrote:

> Select all of the records, then run the following macro:
>
> Dim i As Long, j As Long, data As Table, datarange As Range
> Selection.ConvertToTable wdSeparateByCommas, , 4
> Set data = Selection.Tables(1)
> For i = 1 To data.Rows.Count
> If Len(data.Cell(i, 4).Range) = 2 Then
> For j = 4 To 2 Step -1
> Set datarange = data.Cell(i, j - 1).Range
> datarange.End = datarange.End - 1
> data.Cell(i, j).Range = datarange
> Next j
> data.Cell(i, 1).Range = Left(data.Cell(i, 1).Range, 1) &
> Left(data.Cell(i, 1).Range, 1)
> End If
> Next i
> data.ConvertToText wdSeparateByCommas
>
>
> --
> Please post any further questions or followup to the newsgroups for the
> benefit of others who may be interested. Unsolicited questions forwarded
> directly to me will only be answered on a paid consulting basis.
>
> Hope this helps
> Doug Robbins - Word MVP
> "Greg Mouning" <Greg Mouning@discussions.microsoft.com> wrote in message
> news:AE39E3C2-6528-4D68-9E64-B7D4A6856509@microsoft.com...
> > Hi,
> >
> > I'm working with Word 97 and I wish to rearrange a data source file so
> that it is consistent. For example, I have the following sample data
> records:
> >
> > "John Smith", "1 ABC Lane", "City State Zip"
> > "Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"
> >
> > As you can see above the Jane Smith record has an additional field "Apt G"
> field. If I were doing a normal mail merge this would not be a problem.
> However, I need to export the data for another program rearranging the data
> using the following format:
> >
> > "", "John Smith", "1 ABC Lane", "City State Zip"
> > "Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"
> >
> > Is there a Find/Replace command I can use on this data or must I create a
> macro to handle this problem?
> >
> > Thanks in advance for your assistance.
> >
> > Regards,
> > Greg
>
>

Greg
Mon Jun 28 11:48:02 CDT 2004

Greetings Helmut,

I appreciate your comment but yes the macro Doug created is what I wanted.

Regards,
Greg

"Helmut Weber" wrote:

> Hi Greg,
> (much nicer to see a real name here)
> >"", "John Smith", "1 ABC Lane", "City State Zip"
> >"Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"
> is that really what you want?
> Once the name in the second field of the record
> and then again in the first field?
> Shouldn't it be like this:
> >"John Smith", "1 ABC Lane", "", "City State Zip"
> >"Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"
> ---
> Greetings from Bavaria, Germany
> Helmut Weber, MVP
> "red.sys" & chr(64) & "t-online.de"
> Word XP, Win 98
>
>

Doug
Tue Jun 29 03:15:48 CDT 2004

It's not the macro that does it, its to conversion of the table to text. It
happens even if that step is performed by using that item on the Tables
menu. If it causes a problem, you could use Edit>Replace to get rid of that
space.

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
"Greg Mouning" <Greg Mouning@discussions.microsoft.com> wrote in message
news:313443DF-0797-4582-B1D4-FF9888A85E24@microsoft.com...
> Thanks Doug, this is just what I needed and it works like a charm.
However, can you explain why this macro adds an additional blank space after
the commas?
>
> -Greg
>
> "Doug Robbins - Word MVP" wrote:
>
> > Select all of the records, then run the following macro:
> >
> > Dim i As Long, j As Long, data As Table, datarange As Range
> > Selection.ConvertToTable wdSeparateByCommas, , 4
> > Set data = Selection.Tables(1)
> > For i = 1 To data.Rows.Count
> > If Len(data.Cell(i, 4).Range) = 2 Then
> > For j = 4 To 2 Step -1
> > Set datarange = data.Cell(i, j - 1).Range
> > datarange.End = datarange.End - 1
> > data.Cell(i, j).Range = datarange
> > Next j
> > data.Cell(i, 1).Range = Left(data.Cell(i, 1).Range, 1) &
> > Left(data.Cell(i, 1).Range, 1)
> > End If
> > Next i
> > data.ConvertToText wdSeparateByCommas
> >
> >
> > --
> > Please post any further questions or followup to the newsgroups for the
> > benefit of others who may be interested. Unsolicited questions
forwarded
> > directly to me will only be answered on a paid consulting basis.
> >
> > Hope this helps
> > Doug Robbins - Word MVP
> > "Greg Mouning" <Greg Mouning@discussions.microsoft.com> wrote in message
> > news:AE39E3C2-6528-4D68-9E64-B7D4A6856509@microsoft.com...
> > > Hi,
> > >
> > > I'm working with Word 97 and I wish to rearrange a data source file so
> > that it is consistent. For example, I have the following sample data
> > records:
> > >
> > > "John Smith", "1 ABC Lane", "City State Zip"
> > > "Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"
> > >
> > > As you can see above the Jane Smith record has an additional field
"Apt G"
> > field. If I were doing a normal mail merge this would not be a problem.
> > However, I need to export the data for another program rearranging the
data
> > using the following format:
> > >
> > > "", "John Smith", "1 ABC Lane", "City State Zip"
> > > "Jane Smith", "2 DEF Blvd", "Apt G", "City State Zip"
> > >
> > > Is there a Find/Replace command I can use on this data or must I
create a
> > macro to handle this problem?
> > >
> > > Thanks in advance for your assistance.
> > >
> > > Regards,
> > > Greg
> >
> >

Greg
Tue Nov 30 09:25:04 CST 2004

Hi,

I have data in the following address label format:

Name
Address Line 1
Address Line 2
Address Line 3
City State Zip
Foreign

Dear Salutation,

Because I'm importing this data into a different program, I need to collapse
all blank lines and re-insert them above the Name. In other words, I have to
re-arrange the data so that it consistently contains 8 lines per record.

Any advice you can give on how to accomplish this is greatly appreciated!

Regards,
Greg