transposing repeating data records in excel

Z

zigman

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?
 
P

Per Jessen

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 said:
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?
Click to expand...
 
Z

zigman

Hello Per Jessen. First, I want to thank you for taking time to answer my
question.
I tried to run the program in the excel vb editor and there is a syntax error
associated with the following line of the program

Set f = Columns("A").Find(what:="G", After:=Range("A" & lastrow),
lookat:=xlWhole, MatchCase:=True)

Do you know what the problem could be?
--
Zigman


Per Jessen said:
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 said:
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?
Click to expand...
Click to expand...
 
P

Per Jessen

Hello Zigman

The problem is word wrap in your news editor.

The code mentioned shall be entered as one line or with a " _ " as
line seperator as I did later in the code.


---
Per

Hello Per Jessen.  First, I want to thank you for taking time to answermy
question.    
I tried to run the program in the excel vb editor and there is a syntax error
associated with the following line of the program

Set f = Columns("A").Find(what:="G", After:=Range("A" & lastrow),
lookat:=xlWhole, MatchCase:=True)

Do you know what the problem could be?
--
Zigman



Per Jessen said:
Hi
Click to expand...
With the imported data in Sheet 1, this macro will generate a list in sheet2
with the desired data:
Click to expand...
Sub TransposeData()
Dim DestSh As Worksheet
Dim TargetSh As Worksheet
Click to expand...
Set TargetSh = Sheets("Sheet1")
Set DestSh = Sheets("Sheet2")
DestRow = 2 ' Headings in row 1
TargetSh.Activate
Click to expand...

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
Click to expand...

zigman said:
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 exportedthe
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?
Click to expand...
Click to expand...

- Vis tekst i anførselstegn -
Click to expand...
 
Z

zigman

Hi Per,
OH WOW! It works beautifully! Thank you so much!

I have similar export data type with slightly different info. Since the
data that I need to pull from this alternate format has different row
locations on the spreadsheet, I tried to modify the macro code to adjust for
this format by changing the following line of the macro (to pull the
telephone number data)

'DestSh.Cells(DestRow, 1) = Cells(f.Row + 11, 2).Value

I changed 21 to 11 because it was 11 rows below the 'G'. It created a bug.
It probably isn’t that simple anyway, because some of the records have
‘Precomcode’ in the row directly below the ‘G’ and some don’t. This count
method that I was thinking of changing would vary between records. So below
is a sample of this repeating customer data. I am interested in the
following fields [FirstName, LastName, Post, Tel, Fax, Email, ComName].

$ConflictAction 1
OldLocationCode E01
LocationCode E01
LocationName
DeptName
Sex 1
Title Mr.
FirstName Tony
LastName Thomason
Post Shipping supervisor
Tel 554-593-5451
Fax 994-592-5460
EMail (e-mail address removed)

PersonalTel
HomeAddress
HomeTel
Birthday
Hobby
Children
Memo
CreateDate
CreateName
DeptCode
ComName Larson Construction
FullName Tony Thomason
OldComCode
PreComCode
ComCode
$UpdatedBy

G
PreComCode
$ConflictAction 1
OldComCode A0705
OldLocationCode E01
LocationCode E01
LocationName
DeptName
Sex 1
Title Mr.
FirstName Paul
LastName Thompson
Post Purchasing Mgr
Tel 984-854-7700
Fax 498-592-5460
EMail (e-mail address removed)

PersonalTel
HomeAddress
HomeTel
Birthday
Hobby
Children
Memo
CreateDate
CreateName
ComCode
DeptCode
ComName Larson Construction
FullName Paul Thompson
$UpdatedBy
$Revisions 43 23 PM

G
$ConflictAction 1
OldComCode A0705
OldLocationCode E01
LocationCode E01
LocationName
DeptName
Sex 1
Title Mr.
FirstName Sally
LastName Valley
Post Machine Designer
Tel 444-548-7700
Fax 477-592-5433
EMail (e-mail address removed)

PersonalTel
HomeAddress
HomeTel
Birthday
Hobby
Children
Memo
CreateDate 09/22/2008 01 44 42 PM
CreateName
ComCode
DeptCode
ComName Larson Construction
FullName Sally Valley
$UpdatedBy
$Revisions 09/22/2008 01 45 13 PM

G
$ConflictAction 1
OldLocationCode E01
LocationCode E01
LocationName
DeptName
Sex 2
Title Ms.
FirstName Janice
LastName Miller
Post Purchasing
Tel 477-843-9428
Fax
EMail (e-mail address removed)

PersonalTel
HomeAddress
HomeTel
Birthday
Hobby
Children
Memo
CreateDate 23 59 PM
CreateName
DeptCode 1
ComName Standish Company
FullName
OldComCode
PreComCode
ComCode
$UpdatedBy

G


--
Zigman


Per Jessen said:
Hello Zigman

The problem is word wrap in your news editor.

The code mentioned shall be entered as one line or with a " _ " as
line seperator as I did later in the code.


---
Per

Hello Per Jessen. First, I want to thank you for taking time to answer my
question.
I tried to run the program in the excel vb editor and there is a syntax error
associated with the following line of the program

Set f = Columns("A").Find(what:="G", After:=Range("A" & lastrow),
lookat:=xlWhole, MatchCase:=True)

Do you know what the problem could be?
--
Zigman



Per Jessen said:
Hi
Click to expand...
With the imported data in Sheet 1, this macro will generate a list in sheet2
with the desired data:
Click to expand...
Sub TransposeData()
Dim DestSh As Worksheet
Dim TargetSh As Worksheet
Click to expand...
Set TargetSh = Sheets("Sheet1")
Set DestSh = Sheets("Sheet2")
DestRow = 2 ' Headings in row 1
TargetSh.Activate
Click to expand...

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
Click to expand...

"zigman" <[email protected]> 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.
Click to expand...
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
Click to expand...
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
Click to expand...
Can anyone help me?
Click to expand...

- Vis tekst i anførselstegn -
Click to expand...
Click to expand...
 
P

Per Jessen

Hi Zigman,

Thanks for your reply.

The change you made was right, it's very simple :) I don't see any bug,
just a single quote sign starting the line indicating that it's a comment.

This solution doesn't care about differences in data structure, and more
fields can eaisily be added if needed:

Option Base 1
Sub TransposeData()
Dim DestSh As Worksheet
Dim TargetSh As Worksheet
Dim Posts

Posts = Array("FirstName", "LastName", "Post", "Tel", "Fax", "Email",
"ComName")

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)
Do
For x = LBound(Posts) To UBound(Posts)
Set p = Columns("A").Find(What:=Posts(x), after:=Range("A" & f.Row))
DestSh.Cells(DestRow, x) = Cells(p.Row, 2).Value
Next
DestRow = DestRow + 1
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 said:
Hi Per,
OH WOW! It works beautifully! Thank you so much!

I have similar export data type with slightly different info. Since the
data that I need to pull from this alternate format has different row
locations on the spreadsheet, I tried to modify the macro code to adjust
for
this format by changing the following line of the macro (to pull the
telephone number data)

'DestSh.Cells(DestRow, 1) = Cells(f.Row + 11, 2).Value

I changed 21 to 11 because it was 11 rows below the 'G'. It created a
bug.
It probably isn’t that simple anyway, because some of the records have
‘Precomcode’ in the row directly below the ‘G’ and some don’t. This count
method that I was thinking of changing would vary between records. So
below
is a sample of this repeating customer data. I am interested in the
following fields [FirstName, LastName, Post, Tel, Fax, Email, ComName].

$ConflictAction 1
OldLocationCode E01
LocationCode E01
LocationName
DeptName
Sex 1
Title Mr.
FirstName Tony
LastName Thomason
Post Shipping supervisor
Tel 554-593-5451
Fax 994-592-5460
EMail (e-mail address removed)

PersonalTel
HomeAddress
HomeTel
Birthday
Hobby
Children
Memo
CreateDate
CreateName
DeptCode
ComName Larson Construction
FullName Tony Thomason
OldComCode
PreComCode
ComCode
$UpdatedBy

G
PreComCode
$ConflictAction 1
OldComCode A0705
OldLocationCode E01
LocationCode E01
LocationName
DeptName
Sex 1
Title Mr.
FirstName Paul
LastName Thompson
Post Purchasing Mgr
Tel 984-854-7700
Fax 498-592-5460
EMail (e-mail address removed)

PersonalTel
HomeAddress
HomeTel
Birthday
Hobby
Children
Memo
CreateDate
CreateName
ComCode
DeptCode
ComName Larson Construction
FullName Paul Thompson
$UpdatedBy
$Revisions 43 23 PM

G
$ConflictAction 1
OldComCode A0705
OldLocationCode E01
LocationCode E01
LocationName
DeptName
Sex 1
Title Mr.
FirstName Sally
LastName Valley
Post Machine Designer
Tel 444-548-7700
Fax 477-592-5433
EMail (e-mail address removed)

PersonalTel
HomeAddress
HomeTel
Birthday
Hobby
Children
Memo
CreateDate 09/22/2008 01 44 42 PM
CreateName
ComCode
DeptCode
ComName Larson Construction
FullName Sally Valley
$UpdatedBy
$Revisions 09/22/2008 01 45 13 PM

G
$ConflictAction 1
OldLocationCode E01
LocationCode E01
LocationName
DeptName
Sex 2
Title Ms.
FirstName Janice
LastName Miller
Post Purchasing
Tel 477-843-9428
Fax
EMail (e-mail address removed)

PersonalTel
HomeAddress
HomeTel
Birthday
Hobby
Children
Memo
CreateDate 23 59 PM
CreateName
DeptCode 1
ComName Standish Company
FullName
OldComCode
PreComCode
ComCode
$UpdatedBy

G


--
Zigman


Per Jessen said:
Hello Zigman

The problem is word wrap in your news editor.

The code mentioned shall be entered as one line or with a " _ " as
line seperator as I did later in the code.


---
Per

Hello Per Jessen. First, I want to thank you for taking time to answer
my
question.
I tried to run the program in the excel vb editor and there is a syntax
error
associated with the following line of the program

Set f = Columns("A").Find(what:="G", After:=Range("A" & lastrow),
lookat:=xlWhole, MatchCase:=True)

Do you know what the problem could be?
--
Zigman



:
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" <[email protected]> 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?
Click to expand...
Click to expand...
Click to expand...
 
Top