Fill - Justify

[iyervsv]

Dear Chandoo


You have explained us to use Fill - Justify for bringing the data in a single row. Now I have a data wherein A3 to A6, A7 and A10, A12 to A16 and so on till A35454. Is there any way I can get all these data i.e. A3 to A6 in B3 and so on...


The option Fill - Justify can be performed for each and every segment separately.


Please give me your suggestion. Thanks in advance


The sample of the data is as under:

A

H.D.F.C. BANK LTD.

BANK & CASH CONTRA

being cash deposited in citi bank


cash book

BANK & CASH CONTRA

being cash deposited in hdfc


cash book

BANK & CASH CONTRA

being cash deposited in hdfc


THE BCB C.C A/C

BANK & CASH CONTRA

being cash deposited


cash book

BANK & CASH CONTRA

being cash deposited in bcb


V S Venkatraman

 

[Hui]

Copy this to B3:

=OFFSET($A$2,(COLUMN()-COLUMN($A$1))*4+(ROW()-ROW($A$2)),0)

Then copy from B3 down to B5

Then Copy B3:B5 accross as required

 

[iyervsv]

Dear Mr Hui


I want the data in A3:A5 in B3 (all the 3 rows data in single row)

the data in B3 should appear like this


cash book BANK & CASH CONTRA being cash deposited in hdfc


as the data is huge I need some quick tips for converting the same into single row.. the data are not in uniformity some have 3 rows and some have 4 rows.. Chandoo's tips for using Fill - Justify works fine but it is very tedious for huge data like this...


Your above formula is not giving me the desired result.


Thanks and looking forward for your suggestions...


V S Venkatraman

 

[vijaySharma]

Venkat,


This can be easily done using VBA code; is that something that would help you.


~Vijay

 

[Hui]

B3: =OFFSET($A$2,4*(ROW()-ROW($A$3))+(COLUMN()-COLUMN($A$1)),0)

Copy everywhere

This will only work with 3 rows and a space between them

 

[vijaySharma]

Venkat,


If VBA code is not an issue... do give a try to the code below...


Sub ConvertToCells()

Dim i As Integer

Dim myRange() As Variant

Dim myArr() As Variant, cntr As Long


ReDim myRange(1 To Range("A3", Range("A65536").End(xlUp)).Count, 1)

Dim strVal As String, RecComplete As Boolean


myRange = Range("A3", Range("A65536").End(xlUp)) 'This reads the data


strVal = ""

cntr = 0

For i = LBound(myRange) To UBound(myRange)

If myRange(i, 1) = "" Then

RecComplete = True

Else

strVal = strVal & " " & myRange(i, 1)

End If

If RecComplete Then

ReDim Preserve myArr(cntr)

myArr(cntr) = strVal

strVal = ""

RecComplete = False

cntr = cntr + 1

End If

Next

'Now string the last value in our array

ReDim Preserve myArr(cntr)

myArr(cntr) = strVal


Range("B3").Select

For i = LBound(myArr) To UBound(myArr)

ActiveCell.Offset(i, 0).Value = myArr(i)

Next

End Sub


HTH

~Vijay

sharma.vijay1-at-gmail.com

+91-9811996454

 

[Hui]

or

[pre]

Sub ConvertToCells()
Dim i As Integer
Dim myRange() As Variant
ReDim myRange(1 To Range("A2", Range("A65536").End(xlUp)).Count, 1)
Row = 0
Col = 0

myRange = Range("A3", Range("A65536").End(xlUp))
i = LBound(myRange)

Do

While myRange(i, 1) <> "" And i < UBound(myRange)
Col = Col + 1
Cells(3 + Row, 1 + Col) = myRange(i, 1)
i = i + 1
Wend

If i > UBound(myRange) Then Exit Do

If myRange(i, 1) = Nul Then
Row = Row + 1
Col = 0
End If
i = i + 1
If i > UBound(myRange) Then Exit Do

Loop

End Sub

[/pre]