Hi

With the imported data in Sheet 1, this macro will generate a list in sheet2
with the desired data:

Sub TransposeData()
Dim DestSh As Worksheet
Dim TargetSh As Worksheet

Set TargetSh = Sheets("Sheet1")
Set DestSh = Sheets("Sheet2")
DestRow = 2 ' Headings in row 1

TargetSh.Activate

lastrow = Range("A" & Rows.Count).End(xlUp).Row
Set f = Columns("A").Find(what:="G", After:=Range("A" & lastrow),
lookat:=xlWhole, MatchCase:=True)
Debug.Print f.Row
Do
DestSh.Cells(DestRow, 1) = Cells(f.Row + 21, 2).Value
DestSh.Cells(DestRow, 2) = Cells(f.Row + 23, 2).Value
DestSh.Cells(DestRow, 3) = Cells(f.Row + 24, 2).Value
DestSh.Cells(DestRow, 4) = Cells(f.Row + 26, 2).Value
DestSh.Cells(DestRow, 5) = Cells(f.Row + 47, 2).Value
DestRow = DestRow + 1
fRow = f.Row
Set f = Columns("A").Find(what:="G", After:=Range("A" & f.Row), _
lookat:=xlWhole, MatchCase:=True)
Loop Until f.Row = 1
End Sub

Regards,
Per

"zigman" skrev i meddelelsen
...
I am trying to transpose repeating records in excel. My data is exported
from a Lotus notes database. I have selected to export and use a "G" to
separate each record. I need to align similar classes of data in similar
columns so I can map fields when importing the records into Outlook. I
am
really only interested in the following data from each record [
streetname,
cityname, statecode, postcode, comname]. Of course when I exported the
data
from Lotus notes, I got a template with lots of generic data fields in
column
A and the unique data in column B. Here is a sample of the data.

G
ADRNO
ADR
COMPANYTYPE
ENDFLG 1
$ConflictAction 1
Startup 1
OldLocationCode E01
PreLocationCode E01
PreLocationName
Administrator [Manager]
Readers [Manager]
SalesPersonNotesID
InsidePersonNotesID
AreaManagerNotesID
UserList
CLDCode
SelectList
ComAdditionalName
LocationCode E01
LocationName
StreetName 600 Thomas Drive
POBox
CityName New York
StateCode NY
StateName New York
PostCode 10999
CountryCode USA
CountryName United States
Tel
Fax
CurrencyCode USD
CurrencyName US Dollar
LanguageCode ENG
LanguageName English
PayMethodCode
PayMethodName
PayTermCode
PayTermName
KeyPerson
SalesPersonCode
SpecialCustomer
InsidePersonCode
AreaManagerCode
WorkerNo
Budget
Authors ,,
ComName Humphry, Inc.
CustomerFlag 1
BlockFlag
BlockDate
BlockMemo
BlockHistory
Distributor
SalesPerson
InsidePerson
AreaManager
IndustryClass01Code
IndustryClass01Name
IndustryClass02Code
IndustryClass02Name
UpdateDate xxxx
UpdateName xxxx
ComType 1
OldComCode axxxx
PreComCode
ComCode axxxx
$UpdatedBy xxxx



G
ADRNO
ADR
COMPANYTYPE
ENDFLG 1
$ConflictAction 1
Startup 1
OldLocationCode E01
PreLocationCode E01
PreLocationName
Administrator [Manager]
Readers [Manager]
SalesPersonNotesID
InsidePersonNotesID
AreaManagerNotesID
UserList
CLDCode
SelectList
ComAdditionalName
LocationCode E01
LocationName
StreetName One Best Rd
POBox
CityName Lisbon
StateCode OH
StateName Ohio
PostCode 19203
CountryCode USA
CountryName United States
Tel 301-999-3911
Fax
CurrencyCode USD
CurrencyName US Dollar
LanguageCode ENG
LanguageName English
PayMethodCode
PayMethodName
PayTermCode
PayTermName
KeyPerson
SalesPersonCode
SpecialCustomer
InsidePersonCode
AreaManagerCode
WorkerNo
Budget
Authors ,,
ComName Lustor, Inc.
CustomerFlag 1
BlockFlag
BlockDate
BlockMemo
BlockHistory
Distributor
SalesPerson
InsidePerson
AreaManager
IndustryClass01Code
IndustryClass01Name
IndustryClass02Code
IndustryClass02Name
UpdateDate
UpdateName xxxxx
ComType 1
OldComCode xxxxx
PreComCode
ComCode xxxxx
$UpdatedBy xxxxx


Can anyone help me?


--
Zigman