Adapting VBA - aligning two datasets

[Dr. Demento]

I've found code that works great (thanks to Derek here), however, I'm wondering if someone could assist me to make it a little more user friendly. Rather than hard-code the original location, number of columns in each list, and the output destination, I'm wondering if it's possible to simply define two named tables and ask the user to choose the output destination?

Thanks for the assist!

Sub Align()
For Each cell In Range("A1", Range("A65536").End(xlUp))
Range(cell, cell.Offset(0, 19)).Cut Destination:=Range("AP65536").End(xlUp).Offset(1, 0)
On Error Resume Next
Columns("U:U").Select
Selection.Find(What:=cell.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
).Select
Range(ActiveCell, ActiveCell.Offset(0, 19)).Cut Destination:=Range("AP65536").End(xlUp).Offset(0, 20)
Next
End Sub

 

[ThauThème]

Hi Demento,

First of all, please pardon my poor english... I am irremediably french... G. translator helps me but sometimes it's very funny...

If I don't misunderstood, maybe this way :

Sub Align()
Dim TBLI As Variant 'déclare the TBLI variable (TaBLe of Input)
Dim TBLO As Variant 'déclare the TBLO variable (TaBLe of Output)
Dim CEL As Range 'déclare the CEL variable (CELl)
Dim FR As Range 'déclare the FR variable (Found Range)

On Error Resume Next 'if [Cancel] button in the inputbox
Set TBLI = Application.InputBox("Select the entire column of the INPUT table", Type:=8) 'inputbox
If TBLI Is Nothing Then 'condition : if [Cancel] button
    MsgBox "No INPUT column has been selected !" 'message
    Exit Sub 'end the macro
End If 'end of condition
Set TBLI = TBLI(1).Resize(Application.WorksheetFunction.CountA(TBLI)) 'resize TBLI
Set TBLO = Application.InputBox("Select the entire column of OUTPUT table", Type:=8) 'inputbox
If TBLO Is Nothing Then 'condition : if [Cancel] button
    MsgBox "No OUTPUT column has been selected !" 'message
    Exit Sub 'end the macro
End If 'end of condition
On Error GoTo 0 'cancels error handling
For Each CEL In TBLI 'loop in all  the CEL cells in TBLI
    If CEL.Value <> "" Then 'condition : if not CEL is empty
        Range(CEL, CEL.Offset(0, 19)).Cut Destination:=Range("AP65536").End(xlUp).Offset(1, 0) 'copy/cut
        Set FR = TBLO.Find(CEL.Value, , xlValues, xlWhole) 'define FR variable
        If Not FR Is Nothing Then Range(FR, FR.Offset(0, 19)).Cut Destination:=Range("AP65536").End(xlUp).Offset(0, 20) 'copy/cut
    End If 'end of condition
Next CEL
End Sub

 

[Dr. Demento]

ThauThème.

Thank you for your code, but it's not quite what I was hoping for. I've attached an example (which I should have done in the first place; my apologies). What I'm hoping to do is hard-code in Table1 & Table2 into the VBA code as the inputs (but leave how these tables are defined independent of the code - i.e., no reference to offsets, etc.) and just choose the single cell of the output generation in the format shown.

For example, I could use this macro to construct Table1 from two tables containing denominator data from 2014 & 2015; from there, I would take that resultant 2014-2015 denominator table and add the 2015 numerator data. The inputs would not always be two columns each (i.e., create a table that has denominator data from 2000-2015 and add 2015 numerator data).

The macro would pre-suppose that there are only unique values for the matching variable (presumably in the first column of each table), although if someone could help me understand what would be needed to ask user input regarding which columns should be compared (i.e., Col 1 of Table1 and Col 3 of Table2), that would be very helpful.

Thanks again.

 

[ThauThème]

Arf Demento ! I really was "à coté de la plaque"...
Worse ! It's now, with a concret example and its G... translation, that I'm lost... Sorry !

 

[Dr. Demento]

I don't think you were way off the mark; your response was a significant improvement by allowing user interface outside of the VBA coding. I appreciate it, man.

I hope my explanation was not the source of your confusion!

Ciao