CODEWITHSUNDEEP
excelexcel-2007vba
babasimana
babasimana

Reputation: 9

Excel VBA to match and line up rows

I have an Excel document with columns A to J. I have columns K to N with related data, but not aligned.

I need to match value from value in column F with value in column K so they are lined up. When I shift K, I have to shift L, M, N together.

I cannot sort columns A to J - they must remain in place.

Example before:

A     B     C     D     E     F        G     H     I     J     K       L     M     N

data  data  data  data  data  record1  data  data  data  data  record3 data  data  data

data  data  data  data  data  record2  data  data  data  data  record1 data  data  data

data  data  data  data  data  record3  data  data  data  data  

data  data  data  data  data  record4  data  data  data  data

Example after:

A     B     C     D     E     F        G     H     I     J     K       L     M     N

data  data  data  data  data  record1  data  data  data  data  record1 data  data  data

data  data  data  data  data  record2  data  data  data  data  

data  data  data  data  data  record3  data  data  data  data  record3 data  data  data

data  data  data  data  data  record4  data  data  data  data

Upvotes: 0

Views: 6082

Answers (2)

Fionnuala
Fionnuala

Reputation: 91366

The easiest way would probably be ADO. 

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

''This is not the best way to refer to the workbook
''you want, but it is very convenient for notes
''It is probably best to use the name of the workbook.

strFile = ActiveWorkbook.FullName

''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used.
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

''Late binding, so no reference is needed

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * " _
       & "FROM [Sheet2$A1:J5] a " _
       & "LEFT JOIN [Sheet2$K1:N5] b " _
       & "ON a.F=b.k "

rs.Open strSQL, cn, 3, 3


''Pick a suitable empty worksheet for the results

Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Upvotes: 5

John Alexiou
John Alexiou

Reputation: 29244

Sort columns K - M and move them further away (like in X-Z)

In columns K-M add a VLOOKUP() function to pull data from X-Z based on column F.

To make it pretty pull out the row# with the INDEX() function and only if found copy the data from X-Z to K-L with the MATCH() function. Otherwise return an empty string.

Columns A-J with data, with column F containing the lookup values

Column X-Z with reference table with X containing the loopup match values

Add a column N with =MATCH((Value in F),(XYZ Table),FALSE) this produces either a #N/A or a row number

Column K with =IF( NOT( ISNA(Value in N) ), INDEX((X Table), (Value in N) ), "")

Column L with =IF( NOT( ISNA(Value in N) ), INDEX((Y Table), (Value in N) ), "")

Column M with =IF( NOT( ISNA(Value in N) ), INDEX((Z Table), (Value in N) ), "")

Unless you want to do it in VBA, this works.

Upvotes: 2

Related Questions