Lining up Row ins columns

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)

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.

Hi jlrivera11:

Welcome to MrExcel Board!

Following is a formula based soution ...

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!

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

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
I hope this helps!

Hi
try

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.

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?

Click to expand...

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