How do i write a Macro that does following

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a sheet which contains following information

Name Last name
Address Title
City Telephone
Zip e mail
Country
of about 100nds of addresses

What i want to do in here is to get this information on another sheet stored
in columns i.e

Name Address City Zip Country Title Telephone e-mail
 
Perhaps one way to try
(dependent on data structure) ..

Assuming source data is in cols A and B, in groups of 5 lines each, w/o any
blank rows in-between groups, viz.:
(Link to a sample file is provided below)

Name1 Last name1
Address1 Title1
City1 Telephone1
Zip1 e mail1
Country1
Name2 Last name2
Address2 Title2
City2 Telephone2
Zip2 e mail2
Country2
Name3 Last name3
Address3 Title3
City3 Telephone3
Zip3 e mail3
Country3
etc

Put in D1: =OFFSET($A$1,ROWS($A$1:A1)*5-5+COLUMNS($A$1:A1)-1,)
Copy D1 across to H1

Put in B1: =OFFSET($B$1,ROWS($A$1:A1)*5-5+COLUMNS($A$1:B1)-1,)
Copy B1 across to K1

Select D1:K1, fill down until zeros appear,
signalling exhaustion of data from cols A and B

Freeze the results in cols D to K
with a copy > paste special > values > OK
either in-place or elsewhere

The results will be returned in the desired format:
Name1 Address1 City1 Zip1 Country1 Title1 Telephone1 e mail1
Name2 Address2 City2 Zip2 Country2 Title2 Telephone2 e mail2
Name3 Address3 City3 Zip3 Country3 Title3 Telephone3 e mail3
etc

Here's a sample file with the implemented construct:
http://flypicture.com/p.cfm?id=52841
(Right-click on the link: "Download File"
at the top in the page, just above the ads)
File: Gautam_newusers_1.xls
 
Sorry, typo in the 2 lines just discovered:
Put in B1: =OFFSET($B$1,ROWS($A$1:A1)*5-5+COLUMNS($A$1:B1)-1,)
Copy B1 across to K1
Click to expand...

Should read as:
Put in I1: =OFFSET($B$1,ROWS($A$1:A1)*5-5+COLUMNS($A$1:B1)-1,)
Copy I1 across to K1
 
Back
Top