Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rearranging a Very poorly constructed list
I have scanned some customer lists into excel via a lengthy process and it
has given me one column of data. the data is of 3 rows Name,address,phone number. I dont have a header but i can add if i need. I would like to transpose the data (the second and third lines) to column b and c respectivley, But for the enitre list which is 1201 rows long...i have tried a few macros i have found and tried to modify them to just 3 rows but i dont understand Visual basic very well. I appreciate and give many thanks for all the help you my give me in advance. Glenn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rearranging a Very poorly constructed list
Macro or even a formula will do..Before that a quick query?
If it is 3 bits it should be 1200 rows and not 1201. Do you have blank spaces inbetween? If this post helps click Yes --------------- Jacob Skaria "Lister" wrote: I have scanned some customer lists into excel via a lengthy process and it has given me one column of data. the data is of 3 rows Name,address,phone number. I dont have a header but i can add if i need. I would like to transpose the data (the second and third lines) to column b and c respectivley, But for the enitre list which is 1201 rows long...i have tried a few macros i have found and tried to modify them to just 3 rows but i dont understand Visual basic very well. I appreciate and give many thanks for all the help you my give me in advance. Glenn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rearranging a Very poorly constructed list
Yes i have been manually cleaning it up as i go, I have a few entries which
had 4 rows so i have changed those already. I am looking for a macro, but am unable to produce one myself and was wondering if someone may be so kind as to furnish me with one that i may copy into the worksheet. "Jacob Skaria" wrote: Macro or even a formula will do..Before that a quick query? If it is 3 bits it should be 1200 rows and not 1201. Do you have blank spaces inbetween? If this post helps click Yes --------------- Jacob Skaria "Lister" wrote: I have scanned some customer lists into excel via a lengthy process and it has given me one column of data. the data is of 3 rows Name,address,phone number. I dont have a header but i can add if i need. I would like to transpose the data (the second and third lines) to column b and c respectivley, But for the enitre list which is 1201 rows long...i have tried a few macros i have found and tried to modify them to just 3 rows but i dont understand Visual basic very well. I appreciate and give many thanks for all the help you my give me in advance. Glenn |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rearranging a Very poorly constructed list
A simple solution would be ......With your data in Column A starting from
Row1 try the below formula in cell B1. Then copy the formula to C1 and D1. Then copy down as required.... =INDEX($A:$A,(ROW()-1)*3+COLUMN(A1)) 'If you are really looking for a macro post back... If this post helps click Yes --------------- Jacob Skaria "Lister" wrote: Yes i have been manually cleaning it up as i go, I have a few entries which had 4 rows so i have changed those already. I am looking for a macro, but am unable to produce one myself and was wondering if someone may be so kind as to furnish me with one that i may copy into the worksheet. "Jacob Skaria" wrote: Macro or even a formula will do..Before that a quick query? If it is 3 bits it should be 1200 rows and not 1201. Do you have blank spaces inbetween? If this post helps click Yes --------------- Jacob Skaria "Lister" wrote: I have scanned some customer lists into excel via a lengthy process and it has given me one column of data. the data is of 3 rows Name,address,phone number. I dont have a header but i can add if i need. I would like to transpose the data (the second and third lines) to column b and c respectivley, But for the enitre list which is 1201 rows long...i have tried a few macros i have found and tried to modify them to just 3 rows but i dont understand Visual basic very well. I appreciate and give many thanks for all the help you my give me in advance. Glenn |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rearranging a Very poorly constructed list
This formula copied the name over to the columns in which i placed the
formula. I was really wanting to bring the address which is below the name to the next column beside the name (column B)and then move the phone number which is below the address to beside the address (Column C). The information i have read today regarding this is called "transposing" or an "array". Thanks again. "Jacob Skaria" wrote: A simple solution would be ......With your data in Column A starting from Row1 try the below formula in cell B1. Then copy the formula to C1 and D1. Then copy down as required.... =INDEX($A:$A,(ROW()-1)*3+COLUMN(A1)) 'If you are really looking for a macro post back... If this post helps click Yes --------------- Jacob Skaria "Lister" wrote: Yes i have been manually cleaning it up as i go, I have a few entries which had 4 rows so i have changed those already. I am looking for a macro, but am unable to produce one myself and was wondering if someone may be so kind as to furnish me with one that i may copy into the worksheet. "Jacob Skaria" wrote: Macro or even a formula will do..Before that a quick query? If it is 3 bits it should be 1200 rows and not 1201. Do you have blank spaces inbetween? If this post helps click Yes --------------- Jacob Skaria "Lister" wrote: I have scanned some customer lists into excel via a lengthy process and it has given me one column of data. the data is of 3 rows Name,address,phone number. I dont have a header but i can add if i need. I would like to transpose the data (the second and third lines) to column b and c respectivley, But for the enitre list which is 1201 rows long...i have tried a few macros i have found and tried to modify them to just 3 rows but i dont understand Visual basic very well. I appreciate and give many thanks for all the help you my give me in advance. Glenn |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rearranging a Very poorly constructed list
The formula does exactly what you want...Copy the formula to cell B1. Just
make sure you drag/copy formula to C1 and D1 which will bring address to column C and phone to column D. OR else try the macro.... Sub MyMacro() Dim lngRow As Long, lngNRow As Long For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row Step 3 lngNRow = lngNRow + 1 Range("B" & lngNRow & ":D" & lngNRow) = _ WorksheetFunction.Transpose(Range("A" & lngRow).Resize(3).Value) Next End Sub If this post helps click Yes --------------- Jacob Skaria "Lister" wrote: This formula copied the name over to the columns in which i placed the formula. I was really wanting to bring the address which is below the name to the next column beside the name (column B)and then move the phone number which is below the address to beside the address (Column C). The information i have read today regarding this is called "transposing" or an "array". Thanks again. "Jacob Skaria" wrote: A simple solution would be ......With your data in Column A starting from Row1 try the below formula in cell B1. Then copy the formula to C1 and D1. Then copy down as required.... =INDEX($A:$A,(ROW()-1)*3+COLUMN(A1)) 'If you are really looking for a macro post back... If this post helps click Yes --------------- Jacob Skaria "Lister" wrote: Yes i have been manually cleaning it up as i go, I have a few entries which had 4 rows so i have changed those already. I am looking for a macro, but am unable to produce one myself and was wondering if someone may be so kind as to furnish me with one that i may copy into the worksheet. "Jacob Skaria" wrote: Macro or even a formula will do..Before that a quick query? If it is 3 bits it should be 1200 rows and not 1201. Do you have blank spaces inbetween? If this post helps click Yes --------------- Jacob Skaria "Lister" wrote: I have scanned some customer lists into excel via a lengthy process and it has given me one column of data. the data is of 3 rows Name,address,phone number. I dont have a header but i can add if i need. I would like to transpose the data (the second and third lines) to column b and c respectivley, But for the enitre list which is 1201 rows long...i have tried a few macros i have found and tried to modify them to just 3 rows but i dont understand Visual basic very well. I appreciate and give many thanks for all the help you my give me in advance. Glenn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlinks fail when constructed using Autofil | Excel Programming | |||
Rearranging Alphabetical List | Excel Discussion (Misc queries) | |||
Reading in poorly formatted text file | Excel Discussion (Misc queries) | |||
Rearranging columns | Excel Programming | |||
vlookup with table_array constructed from mid formula | Excel Worksheet Functions |