Trying to "Line up" rows across sheets.

[TommyVS]

Hey,

So - I've been an Excel user for years (Working only with functions) and a web programmer for years (Working with PHP and MySQL) and have been recently given the title of "The guy who needs to learn VBA to keep his job". I'm getting the hang of it but got stuck and am hoping this (Seemingly awesome) community can help out.

I've been tasked with creating an excel project (My boss calls them programs) that can run a bunch of comparisons across multiple CSV files. Most of the comparisons (Is this cell the same as this one?) are a snap. Each csv (Which we're having users copy/paste onto Sheet2 and Sheet3) has upwards of 20k rows - but are united via a single column with a "uid" row. Edit: I've uploaded a sample dataset. Data is in Sheet2 and Sheet3, manually curated output is in the 'Output' sheet.

What I'm looking to do is pull specific data from the rows - but ONLY where UID matches. Sorting by UID then assuming row numbers line up won't work -as some of the CSVs contain more rows than others (I've included this variable in my sample).

Essentially, I need a "Dump values of Sheet2, Columns B, C, D and Sheet3 columns A, B, D to Sheet4 when the values of the data in Sheet3, Column A and Sheet2, Column C match.

Any help would be greatly appreciated.

[CharlesH]

TommyVS,

HI,

Sheets 2 can have more data than Sheet3 and vise versa? Once copied over too "Output" is it necessary to keep the data in sheets 2 and 3?

[TommyVS]

TommyVS,

HI,

Sheets 2 can have more data than Sheet3 and vise versa? Once copied over too "Output" is it necessary to keep the data in sheets 2 and 3?

Hi Charles - thanks for your interest.

Yes, we never know what data is going to be in our CSVs as we pull them... Sometimes they have the same number of rows and that uid column matches perfectly. Most times, it doesn't. Manually, we've been sorting by uid, then grabbing data from the columns and copying/pasting it. They can take 10+ hours sometimes.

And no, the CSVs are kept in another location, so if Sheet2 and Sheet3 get ruined in the process, that's fine.

[CharlesH]

TommyVS,

Thanks for the info. However, I may not be able to finish what I started as too about ready to take off. If some one else has not responded to your request I should have some thing for you tomorrow.

[CharlesH]

TommyVS,


Based on your example this is what I came up with. When you open the attachment you will see a "Click" button when you click it the data in sheet2 and 3 will be moved to the Output sheet and at the same time the data will be removed from sheet2 and 3. You may need to change the sheet names to your actual sheet names. As always test on copies.

[mikerickson]

I think this will do what you want
Sub test()
Dim KeyColumn1 As Range, dataRange1 As Range
Dim KeyColumn2 As Range, dataRange2 As Range
Dim outputRange As Range
Dim critRange As Range
Dim formulaX As String

With ThisWorkbook
Set KeyColumn1 = .Sheets("Sheet2").Range("A:A")
Set KeyColumn2 = .Sheets("Sheet3").Range("C:C")
Set outputRange = .Sheets("Sheet4").Range("A1")
End With

With KeyColumn1.EntireColumn
Set KeyColumn1 = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With
With KeyColumn2.EntireColumn
Set KeyColumn2 = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

Set dataRange1 = KeyColumn1.CurrentRegion
Set dataRange2 = KeyColumn2.CurrentRegion

Rem copy data 1
With dataRange1
Set outputRange = outputRange.Resize(.Rows.Count, .Columns.Count)
outputRange.Parent.Cells.ClearContents
outputRange.Value = .Value
End With

Rem set criteria range
With outputRange
Set critRange = .Cells(1, .Columns.Count + 2).Resize(2, 1)
Set outputRange = .Cells(.Rows.Count + 1, .Columns.Count + 1)
End With
With critRange
.Cells(2, 1).FormulaR1C1 = "=ISNA(MATCH(" & KeyColumn2.Cells(2, 1).Address(False, False, xlR1C1, True, .Cells(2, 1)) & "," & KeyColumn1.Address(True, True, xlR1C1, True) & ",0))"
End With
Rem advancedFilter from data 2
With dataRange2
.AdvancedFilter action:=xlFilterCopy, criteriarange:=critRange, copytorange:=outputRange.Resize(1, .Columns.Count), unique:=True
critRange.ClearContents
Set outputRange = outputRange.Resize(.Rows.Count, .Columns.Count)
End With


With outputRange.EntireColumn.Resize(dataRange1.Rows.Count, dataRange1.Columns.Count)
.FormulaR1C1 = "=INDEX(" & dataRange2.Address(True, True, xlR1C1, True) _
& ",MATCH(" & .EntireRow.Cells(1, 1).Address(False, True, xlR1C1, True, .Cells) _
& "," & KeyColumn2.Address(True, True, xlR1C1, True) _
& ",0),COLUMN(" & .EntireRow.Cells(1, 1).Address(False, False, xlR1C1, True, .Cells) & "))"

On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
On Error GoTo 0
.Value = .Value
End With


With outputRange
With .Columns(KeyColumn2.Column)
.EntireRow.Columns(1).Value = .Value
.EntireColumn.Delete shift:=xlToLeft
End With
.EntireColumn.Rows(1).Value = .Rows(1).Value
.Rows(1).EntireRow.Delete shift:=xlUp
With .Parent.Cells(1, 1).CurrentRegion
.Sort Key1:=.Cells(1, 1), Header:=xlYes
End With
End With
End Sub