Consolidate data from 2 worksheets

[Susan Glass]

Hi all,
I have an Excel workbook with 2 sheets in it that I want to
consolidate. The first sheet maps Requirements to Design Points:

Req Des
___ ___
Req#1 Des#1
Req#1 Des#2
Req#2 Des#3
Req#3 Des#3

The second sheet maps Design Points to Test Points:

Des Test
___ ___
Des#1 Test#1
Des#1 Test#2
Des#2 Test#3
Des#3 Test#3

There is a many-to-many relationship between the columns in each
worksheet.

I would like to consolidate the two sheets to look like this:

Req Des Test
___ ___ ____
Req#1 Des#1 Test#1
Req#1 Des#1 Test#2
Req#1 Des#2 Test#3
Req#2 Des#3 Test#3
Req#3 Des#3 Test#3

So far I have had no luck in creating a pivot table that does this. I
have tried adding a fake "Test" column to the first sheet (it has all
blank values in it) and adding a fake "Req" column to the second sheet
(also with all blank values), combining both sheets onto one sheet
with all 3 columns (Req/Des/Test), and creating a pivot table, but
this does not work to combine the columns. Since there is a
many-to-many relationship, I can't use VLOOKUP.

Any suggestions on how to approach this would be appreciated. I'm not
even sure how I would do this with VBA.

Thanks,
Susan

 

[Dave Peterson]

I think this works ok (it did on your test data):

Option Explicit
Sub testme()

Dim ReqWks As Worksheet
Dim DesWks As Worksheet
Dim NewWks As Worksheet

Dim oRow As Long
Dim TotalDes As Long
Dim TotalRows As Long

Dim iCtr As Long
Dim res As Variant
Dim myFormula As String

Set ReqWks = Worksheets("req")
Set DesWks = Worksheets("Des")
Set NewWks = Worksheets.Add

Dim ReqRng As Range
Dim DesRng As Range
Dim TopDesCell As Range
Dim BotDesCell As Range
Dim myReqCell As Range

With ReqWks
Set ReqRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With DesWks
Set TopDesCell = .Range("a1")
Set BotDesCell = .Cells(.Rows.Count, "A").End(xlUp)
Set DesRng = .Range(TopDesCell, BotDesCell)
End With

oRow = 1
For Each myReqCell In ReqRng.Cells
TotalDes = Application.CountIf(DesRng, myReqCell.Offset(0, 1).Value)
If TotalDes = 0 Then
TotalRows = 1
Else
TotalRows = TotalDes
End If

NewWks.Cells(oRow, "A").Resize(TotalRows, 2).Value _
= myReqCell.Resize(1, 2).Value

Set DesRng = DesWks.Range(TopDesCell, BotDesCell)
For iCtr = 1 To TotalDes
myFormula = "Match(True, (" _
& myReqCell.Offset(0, 1).Address(external:=True) _
& " = " & DesRng.Address(external:=True) & "), 0)"
res = Application.Evaluate(myFormula)

If IsError(res) Then
'shouldn't happen, 'cause we counted first!
Else
NewWks.Cells(oRow + iCtr - 1, "C").Value _
= DesRng(res, 2).Value
Set DesRng = DesWks.Range(DesRng(res + 1), BotDesCell)
End If
Next iCtr

oRow = oRow + TotalRows

Next myReqCell

End Sub

It looks for a match between column 2 of Req data and column 1 of design data.
If it finds one, it adjusts the range to look at--one cell down from the
previous match.

For example, it looks at A1:A4, then sees A1 as the first match. So it starts
looking for the next match in A2:A4.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Susan Glass]

Thanks very much, Dave! I had been hoping that a pivot table would do
it, and I appreciate your posting such in-depth code to solve this.

Thanks,
Susan