How do i write a Macro that does following
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 |
Take a look at VLOOKUP in help.
-- HTH Bob Phillips "Gautam" wrote in message ... 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 |
Have a look in HELP index for TRANSPOSE
-- Don Guillett SalesAid Software "Gautam" wrote in message ... 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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Gautam" wrote in message ... 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 |
Here's a new link to the sample file
with the implemented construct: http://www.savefile.com/files/3671927 File: Gautam_newusers_1.xls -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
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 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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
All times are GMT +1. The time now is 02:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com