Building third large table which consists of matched rows of two smaller tables

[cool_runnin]

Hi all,


I've got a table in Sheet1 with data of for example 100 columns X 10000 rows.
I've got a table in Sheet2 with data of for example 60 columns X 50000 rows.

InbBoth tables I have same strings like: PAB10CL401 (lets say column A in Sheet1 and column B in Sheet2...however those strings are in different orders (rows) in both tables.

I've got one row with PAB10CL401 and in other cell of this row is "+" value and I've got second row with the same string PAB10CL401 and in the same row other cell has "-" value. All in one table. The same thing is in second table. So every string is two times in this one column A or B).

I want to match both tables so that the strings (here: PAB10CL401) and values "+" and "-" will be the same so it will end up in thrid larger table which is consisting of those 2 sorted tables....

What sholud algorithm look a like ?

I believe the 1st criteria should be the string and second the values of +/- from other cell in the row.

From the input data it is clearly visible that not all rows will be matched (2nd table is bigger) but I want to only match what is possible.

[Bob Phillips]

Post a workbook with the two tables and a sample of required third table.

[cool_runnin]

an example attached.

Sheet3 is the bigger (wider) table of tables Sheet1 + Sheet2.

[Bob Phillips]

What about an example of where one sheet has a row not on the other.

Where one sheet has multiples of a code, will the other always have none or the same number?

[cool_runnin]

Now fine ?

[cool_runnin]

I cannot attach file and dont know why...

[cool_runnin]

I must do 5 post to add a link so this is fifth ;-)

[cool_runnin]

[vba]http://speedy.sh/xuRwF/Clear-DATA-sample2.xlsx[/vba]

[p45cal]

In the attached file is this macro. It's not especially efficient. It works by first copying sheet2 then adding to the copied sheet the matching rows from sheet1.
If there are no matching rows, nothing is added to the new sheet. Where/If there is more than one match on sheet1 (I've added a couple of duplicates on sheet1 for testing), rows are inserted in the new sheet.
[VBA]Sub blah()
Set Sht1 = Sheets("Sheet1")
Sht1LR = Sht1.Cells(Sht1.Rows.Count, "A").End(xlUp).Row
Sheets("Sheet2").Copy After:=Sheets(Sheets.Count)
Set ResultSheet = ActiveSheet
ResultSheet.Name = "Results"
With ResultSheet
For S2rw = .Cells(.Rows.Count, "A").End(xlUp).Row To 1 Step -1
OneAlreadyFound = False
myCode = .Cells(S2rw, "A").Value
mySign = .Cells(S2rw, "B").Value
For S1rw = 1 To Sht1LR
If Sht1.Cells(S1rw, "C").Value = myCode And Sht1.Cells(S1rw, "E").Value = mySign Then
If OneAlreadyFound Then
'insert row:
.Rows(S2rw + 1).Insert
Sht1.Cells(S1rw, "A").Resize(, 45).Copy .Cells(S2rw + 1, "CF")
Else
Sht1.Cells(S1rw, "A").Resize(, 45).Copy .Cells(S2rw, "CF")
OneAlreadyFound = True
End If
End If
Next S1rw
Next S2rw
End With
End Sub
[/VBA]

[cool_runnin]

Thanks... I will test it.