Data arrangement

**kushalprakash** · 11-18-2006, 12:00 AM

Please help me for data arrangement as required.
ColA ColB ColC ColD
Ven_Code Ven_Name Narration Amount
Doc No.
101101 MAX INFOTECH
55001 Purchase-Mouse 10000.00
55002 Purchase-Keyboard 5000.00
101102 TECH HARDWARE
66001 Pur - SS Pipe 6mm 5000.00
Data should be arrange as follows after processing any macros
or other ms excel function.
ColA ColB ColC ColD ColE
Ven_Code Ven_Name Doc.No. Narration amount
101101 MAX INFOTECH 55001 Purchase-Mouse 10000.00
101101 MAX INFOTECH 55002 Purchase-Keyboard 5000.00
101102 TECH HARDWARE 66001 Pur - SS Pipe 6mm 5000.00

You are requested to please guide me for this. I am not knowing
macros. I am very thankful to you if you help me.

regards

kpsrajput

**mrice** · 11-18-2006, 02:44 PM

Try this macro - it assumes that your vendor code starts with 101 - you'll need to edit it if it doesn't.

Sub Test()
Set DataRange = Range(Cells(2, 1), Cells(Cells(65536, 1).End(xlUp).Row, 1))
For Each Cell In DataRange
If Left(Cell, 3) = "101" Then
CurrentVendorCode = Cell
CurrentVendorName = Cell.Offset(0, 1)
Else
Cells(65536, 7).End(xlUp).Offset(1, 0) = CurrentVendorCode
Cells(65536, 7).End(xlUp).Offset(0, 1) = CurrentVendorName
Cells(65536, 7).End(xlUp).Offset(0, 2) = Cell
Cells(65536, 7).End(xlUp).Offset(0, 3) = Cell.Offset(0, 1)
Cells(65536, 7).End(xlUp).Offset(0, 4) = Cell.Offset(0, 2)
End If
Next Cell
End Sub

**kushalprakash** · 11-19-2006, 04:01 AM

Dear Mr. Mrice,

I am very thankful to you for your valuable guidance about macros. my task is running but some problem still there.

my excel raw data having thousands vendor and this macros not going in ooping till all vendors completed. Also Amount column not merging.

Can I sent you Excel file containg my whole data.

I am very hungry about this task. Please help me.

with warm regards

kps rajput
email: rajputkps@gmail.com

**Bryan Hessey** · 11-19-2006, 05:05 AM

Originally Posted by kushalprakash

Dear Mr. Mrice,

I am very thankful to you for your valuable guidance about macros. my task is running but some problem still there.

my excel raw data having thousands vendor and this macros not going in ooping till all vendors completed. Also Amount column not merging.

Can I sent you Excel file containg my whole data.

I am very hungry about this task. Please help me.

with warm regards

kps rajput
email: rajputkps@gmail.com

Hi,

Your problem is that you have not really specified how to determine row types that are to be removed from row types that are to be retained.

IF (and only if) the determination is that A len = 6 is to be removed, and others are to be retained then

Sub Macro7()
Dim sCol1 As String, sCol2 As String
Dim iLastRow As Integer, i As Integer
iLastRow = ActiveSheet.Range("A65536").End(xlUp).Row
Range("A1").Select
ActiveCell.EntireColumn.Insert
ActiveCell.EntireColumn.Insert
i = 3 ' Header and blank row ignored
Do
If Len(Cells(i, 3)) = 6 Then
    sCol1 = Cells(i, 3)
    sCol2 = Cells(i, 4)
    Cells(i, 1).EntireRow.Delete
    iLastRow = iLastRow - 1
    Else
    Cells(i, 1) = sCol1
    Cells(i, 2) = sCol2
    i = i + 1
    End If
Loop Until i > iLastRow
End Sub

will do that for you.

hth
---