Excel Export Question

[Palmyra]

If I have spreadsheet with data:

FirstName Middle Name Last Name

Mary Anne Smith
John J. Brown

is there a way to export it to text as

FirstName Mary
MiddleName Anne
LastName Smith
FirstName John
MiddleName Jay
LastName Brown

Thanks.

 

[Markus4]

Hi,

do a Word mail merge. How to do this depends on your versions of Word.

HTH.

M.

 

[SkipVought]

hi, Palmyra (Programmer)

You can use INDEX() function, along with the ROW() and INT() functions to 'group' 3 rows at a time and map to the table row and MOD() (remainder of 3), function to map the column. See what you can do with them.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Set up a sheet to 'export' and then SaveAs a TEXT file.

Here's how you can do this using formulas and generealzing to work with and TABLE with on row of headings, starting in A1.

1) In Insert > Name > Define Names, define the HEADING range and the DATA range using rHEAD and rTABLE respecitvely:
[tt]
rHEAD =OFFSET(Sheet1!$A$1,,,1,COUNTA(Sheet1!$1$1))
rTABLE =OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A$A)-1,COUNTA(Sheet1!$1$1))
[/tt]
2) Insert a sheet to hold the following 2 Named Range factors
[tt]
ColCnt =COLUMNS(rTable)
RowOff
[/tt]
RowOff is used in the formulas following.

Here are the formulas for the Repeating HEADINGS and DATA…
[tt]
=INDEX(rHead,1,MOD(ROW()-1,ColCnt)+1)
=INDEX(rTable,INT((ROW()-1-RowOff)/ColCnt)+1,MOD(ROW()-1-RowOff,ColCnt)+1)
[/tt]
Count the number of empty rows above your first row of formulas and enter in the Name Range cell RowOff


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}