Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 ---- |
#6
|
|||
|
|||
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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to write a macro to get the system time in excel 97 | Excel Discussion (Misc queries) | |||
how to write macro after autofilter is applied | Excel Worksheet Functions | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
How do I write a macro for... | Excel Discussion (Misc queries) |