Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Data Sorting Technique
Dear All,
I have data in the following format (One Column With All the Data. Each Row Contains Different Data as mentioned below): See Attachments for This 360-Lifestyle (Specialized) P.O.Box71813 Tel04-3383310 LocationNext to BMW Show Room, Sh Zayed Road, Dubai Send Enquiry Abdul Majeed Bicycle P.O.Box9586 Tel02-5542517 LocationM 6, Mussafah, Abu Dhabi Send Enquiry Abdulla Bilal Bilal Bicycle Trading P.O.Box23978 Tel04-3497118 LocationAl Dhiyafa Road, Satwa, Dubai Send Enquiry and I need this data in below mentioned format (: 360-Lifestyle (Specialized) 71813 +9714-3383310 Next to BMW Show Room, Sh Zayed Road, Dubai Send Enquiry Abdul Majeed Bicycle 9586 +9712-5542517 M 6, Mussafah, Abu Dhabi Send Enquiry Abdulla Bilal Bilal Bicycle Trading 23978 +9714-3497118 Al Dhiyafa Road, Satwa, Dubai Send Enquiry |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Sorting Technique
On Wed, 10 Oct 2012 07:18:03 +0000, swattoo wrote:
Dear All, I have data in the following format (One Column With All the Data. Each Row Contains Different Data as mentioned below): See Attachments for This 360-Lifestyle (Specialized) P.O.Box71813 Tel04-3383310 LocationNext to BMW Show Room, Sh Zayed Road, Dubai Send Enquiry Abdul Majeed Bicycle P.O.Box9586 Tel02-5542517 LocationM 6, Mussafah, Abu Dhabi Send Enquiry Abdulla Bilal Bilal Bicycle Trading P.O.Box23978 Tel04-3497118 LocationAl Dhiyafa Road, Satwa, Dubai Send Enquiry and I need this data in below mentioned format (: 360-Lifestyle (Specialized) 71813 +9714-3383310 Next to BMW Show Room, Sh Zayed Road, Dubai Send Enquiry Abdul Majeed Bicycle 9586 +9712-5542517 M 6, Mussafah, Abu Dhabi Send Enquiry Abdulla Bilal Bilal Bicycle Trading 23978 +9714-3497118 Al Dhiyafa Road, Satwa, Dubai Send Enquiry You can do this with formulas or with a macro. I note that your data sets each have exactly five rows and starts in A1 So you can enter a formula like B1: =INDEX($A$1:$A$n,(ROWS($1:1)-1)*5+INT((COLUMNS($A:A)-1/5))+1) where the n in $A$1:$A$n represents the last row of data Then fill right for five rows; select B1:F1 and fill down as far as needed. This can also be done with a VBA macro, if needed. To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. ============================== Option Explicit Sub Rearrange() 'assumes data is as shown: ' starts in A1 and is contiguous ' no empty rows ' no header row ' five (5) lines per entry 'results start in C1 'columns C:G are empty Dim vSrc As Variant, vRes() As Variant Dim rDest As Range Dim i As Long, j As Long, k As Long vSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp)) ReDim vRes(1 To UBound(vSrc) / 5, 1 To 5) Set rDest = Range("C1").Resize(rowsize:=UBound(vRes, 1), _ columnsize:=UBound(vRes, 2)) rDest.EntireColumn.Clear For i = 1 To UBound(vSrc) j = Int((i - 1) / 5) + 1 k = (i - 1) Mod 5 + 1 vRes(j, k) = vSrc(i, 1) Next i rDest = vRes End Sub =================================== |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Sorting Technique
On Wed, 10 Oct 2012 08:20:02 -0400, Ron Rosenfeld wrote:
Then fill right for five rows; select B1:F1 and fill down as far as needed. That should read, fill right for five COLUMNS (not rows). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Technique for switching to the VBE | Excel Programming | |||
Paste Special (or some other technique) | Excel Discussion (Misc queries) | |||
Which sorting technique does Excel use in the sort function? | Excel Discussion (Misc queries) | |||
Which sorting technique does Excel use in the sort function? | Excel Programming | |||
Hide Password Technique ??? | Excel Programming |