Request for help to rearrange data...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Greg Mouning - 26 Jun 2004 01:55 GMT

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

Reply to this Message

Doug Robbins - Word MVP - 26 Jun 2004 07:47 GMT

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

Signature

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

> 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"
[quoted text clipped - 5 lines]
> Regards,
> Greg

Reply to this Message

Greg Mouning - 28 Jun 2004 17:45 GMT

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

> Select all of the records, then run the following macro:
>
[quoted text clipped - 38 lines]
> > Regards,
> > Greg

Reply to this Message

Doug Robbins - Word MVP - 29 Jun 2004 09:15 GMT

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.

Signature

> 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
>
[quoted text clipped - 40 lines]
> > > Regards,
> > > Greg

Reply to this Message

Helmut Weber - 26 Jun 2004 10:15 GMT

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

Reply to this Message

Greg Mouning - 28 Jun 2004 17:48 GMT

Greetings Helmut,

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

Regards,
Greg

> Hi Greg,
> (much nicer to see a real name here)
[quoted text clipped - 11 lines]
> "red.sys" & chr(64) & "t-online.de"
> Word XP, Win 98

Reply to this Message

Greg - 30 Nov 2004 15:25 GMT

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

Reply to this Message

Rate this thread: