Lining up Row ins columns

[jlrivera11]

Hello,
I am working on a project in excel and hope I can get a little help here,

The problem is I have several columns of data two of which are the same, however in column A there is 1000 entries and in column D there are 400 entries, columns B, C, E and F are different. Is there a way I can line up the rows of columns A and D keeping the data which is part of this particular data here’ an example below of what I want to do:

Columns A and D are sorted however they are not lined up to each other.

Column A Column B Column C Column D Column E Column F
DXX02114 327184 974487 DXX02114 327183 974488
DXX02115 326910 974636 DXX02201 328405 972770
DXX02118 327379 972798 DXX02203 328293 9722251
DXX02122 326865 973269 DXX02204 328506 9716100
DXX02200 328407 973630 DXX02206 329690 9740993
DXX02201 328405 972773
DXX02202 328808 971546
DXX02203 328293 972225
DXX02204 328504 971614
DXX02205 328399 970741
DXX02206 329695 974093



The following has the rows of columns A and D manually lined up. And kept associated data in rows to columns A and D.

Column A Column B Column C Column D Column E Column F
DXX02114 327184 974487 DXX02114 327183 974488
DXX02115 326910 974636
DXX02118 327379 972798
DXX02122 326865 973269
DXX02200 328407 973630
DXX02201 328405 972773 DXX02201 328405 972770
DXX02202 328808 971546
DXX02203 328293 972225 DXX02203 328293 9722251
DXX02204 328504 971614 DXX02204 328506 9716100
DXX02205 328399 970741
DXX02206 329695 974093 DXX02206 329690 9740993


Is there a macro which can help me do this?

 

[Peter_SSs]

jlrivera11

Welcome to the Mr Excel board!

See if this is any help.

1. Insert 3 new columns between your existing columns C and D
2. In the new cell D1, put the formula =IF(ISNUMBER(MATCH($A1,$G:$G,0)),INDEX($G:$I,MATCH($A1,$G:$G,0),COLUMN()-COLUMN($C1)),"")
3. Copy this formula across to new cell F1 and then all down to the bottom of your data.
4. You could hide columns G:I (old data if required)

Mr Excel.xls
	A	B	C	D	E	F	G	H	I	J
1	DXX02114	327184	974487	DXX02114	327183	974488	DXX02114	327183	974488
2	DXX02115	326910	974636				DXX02201	328405	972770
3	DXX02118	327379	972798				DXX02203	328293	9722251
4	DXX02122	326865	973269				DXX02204	328506	9716100
5	DXX02200	328407	973630				DXX02206	329690	9740993
6	DXX02201	328405	972773	DXX02201	328405	972770
7	DXX02202	328808	971546
8	DXX02203	328293	972225	DXX02203	328293	9722251
9	DXX02204	328504	971614	DXX02204	328506	9716100
10	DXX02205	328399	970741
11	DXX02206	329695	974093	DXX02206	329690	9740993
12
Sheet1

 

[Barry Katcher]

Also, using Peter's 3 inserted columns, you can use these VLOOKUP formulas, copied down.

In D1, =IF(ISNA(VLOOKUP($A1,$G$1:$I$5,1,FALSE)),"",VLOOKUP($A1,$G$1:$I$5,1,FALSE))

In E1, =IF(ISNA(VLOOKUP($A1,$G$1:$I$5,2,FALSE)),"",VLOOKUP($A1,$G$1:$I$5,2,FALSE))

In F1, =IF(ISNA(VLOOKUP($A1,$G$1:$I$5,3,FALSE)),"",VLOOKUP($A1,$G$1:$I$5,3,FALSE))

Whichever formulas you use, after you copy D1:F1 down as far as needed, select those three columns, copy them to the clipboard and then use Edit>Paste Special>Values to convert the formulas to values. You can then get rid of columns G:I.

 

[Yogi Anand]

Hi jlrivera11:

Welcome to MrExcel Board!

Following is a formula based soution ...

y060304h1.xls
	A	B	C	D	E	F	G	H	I	J
1	ColumnA	ColumnB	ColumnC	ColumnD	ColumnE	ColumnF	ColumnD	ColumnE	ColumnF
2	DXX02114	327184	974487	DXX02114	327183	974488	DXX02114	327183	974488
3	DXX02115	326910	974636	DXX02201	328405	972770
4	DXX02118	327379	972798	DXX02203	328293	9722251
5	DXX02122	326865	973269	DXX02204	328506	9716100
6	DXX02200	328407	973630	DXX02206	329690	9740993	DXX02201	328405	972770
7	DXX02201	328405	972773				DXX02201	328405	972770
8	DXX02202	328808	971546
9	DXX02203	328293	972225				DXX02203	328293	9722251
10	DXX02204	328504	971614				DXX02204	328506	9716100
11	DXX02205	328399	970741
12	DXX02206	329695	974093				DXX02206	329690	9740993
13
Sheet4

Array Formula in cell G2 is ...

=IF(ISNA(MATCH(LEFT($B2,5),LEFT($E$2:$E$6,5),0)),"",INDEX(D$2:D$6,MATCH(LEFT($B2,5),LEFT($E$2:$E$6,5),0)))

this is then copied down and across to cells G2:I12.

You may optionally hide columns D:F, or first paste values in cells G2:I12, and then delete columns D:F

I hope this helps! If you must have a macro based solution, please post back!

 

[jlrivera11]

Thanks All, This really helps, Is there also a way I can express this in a VBA macro?

 

[Yogi Anand]

Hi jlrivera11:

Using the same data layout as in my last post, for the VBA solution, I temporarily use three extra columns G, H, and I

Sub yJeshuatree()
        Range("G2").FormulaArray = "=IF(ISNA(MATCH(LEFT($B2,5),LEFT($E$2:$E$6,5),0)),"""",INDEX(D$2:D$6,MATCH(LEFT($B2,5),LEFT($E$2:$E$6,5),0)))"
        Range("H2").FormulaArray = "=IF(ISNA(MATCH(LEFT($B2,5),LEFT($E$2:$E$6,5),0)),"""",INDEX(E$2:E$6,MATCH(LEFT($B2,5),LEFT($E$2:$E$6,5),0)))"
        Range("I2").FormulaArray = "=IF(ISNA(MATCH(LEFT($B2,5),LEFT($E$2:$E$6,5),0)),"""",INDEX(F$2:F$6,MATCH(LEFT($B2,5),LEFT($E$2:$E$6,5),0)))"
        Range("G2:I2").Copy Range("G3:G12")
        [g2:i12].Copy
        [g2:i12].PasteSpecial Paste:=xlValues
        Application.CutCopyMode = False
        'now you may optionally delete or hide columns D:F
        'Columns("D:F").Delete
End Sub

I hope this helps!

 

[jindon]

Hi
try

Sub test()
Dim dic As Object, a, i As Long, result(), temp
Set dic = CreateObject("scripting.dictionary")
a = Range("a1").CurrentRegion.Resize(, 6).Value
For i = 1 To UBound(a, 1)
    If Not IsEmpty(a(i, 1)) Then
        If Not dic.exists(a(i, 1)) Then
            dic.Add a(i, 1), i
        End If
    End If
Next
For i = 1 To UBound(a, 1)
    If dic.exists(a(i, 4)) Then
        x = dic(a(i, 4))
        For ii = 1 To 3
            temp = a(x, 3 + ii)
            a(x, 3 + ii) = a(i, 3 + ii)
            a(i, 3 + ii) = temp
        Next
    End If
Next
Range("a1").CurrentRegion.Resize(, 6).Value = a
Set dic = Nothing: Erase a
End Sub

 

[Tom Urtis]

jindon - -

Can you help me understand something please, I'm not asking rehtorically, really just curious as to your method and thinking, what is your reason for creating a scripting dictionary object as you often do in your codes, just to then delete it. I agree that an optimal solution using VBA (not that VBA is the optimal alternative) would include not needing to insert rows or columns to keep the user's environment as unchanged as possible as yours does. Is a scripting object an advantage to just using Find or Match per the following two examples that also accomplish this task:



Sub Test1()
Application.ScreenUpdating = False
Dim xRow&, aFind As Variant
For xRow = Cells(Rows.Count, 1).End(xlUp).row To 2 Step -1
Set aFind = _
Columns(4).Find(What:=Cells(xRow, 1).Value, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not aFind Is Nothing Then _
Range(Cells(aFind.row, 4), Cells(aFind.row, 6)).Cut Cells(xRow, 4)
Next xRow
Application.ScreenUpdating = True
End Sub



Sub Test2()
Application.ScreenUpdating = False
Dim xRow&, var As Variant
For xRow = Cells(Rows.Count, 1).End(xlUp).row To 2 Step -1
var = Application.Match(Cells(xRow, 1).Value, Columns(4), 0)
If Not IsError(var) Then _
Range(Cells(var, 4), Cells(var, 6)).Cut Cells(xRow, 4)
Next xRow
Application.ScreenUpdating = True
End Sub



I recognize that VBA is not required here. Manual insertion of columns and native formulas for a non-VBA approach are do-able as have been shown. This is a general VBA question for anyone, what advantage or disadvantage is there to a scripting object creation and deletion versus simple finding or matching or filtering, and then cutting and pasting. I would think that a scripting object is not necessary, just adding extra steps but I see it used often enough to wonder if it's more advantageous than I've thought.

 

[jindon]

Hi, Tom

I've just posted the code only for

Thanks All, This really helps, Is there also a way I can express this in a VBA macro?

and I don't think dictionary object is advanced.
If somebody want to use it, it is quite clearly explained in the help.
actually, I got it accidentally while I was exploring help file...

the main reason I often use it with an array is the speed of process.
Unlike Access or other database tools, excel is slow, I'm not saying
excel is bad. it is actually too excelent tool!

As you say, it would be not adequate to use dictionary object in this case....

rgds,
jindon