Mixing data from 2 different tables

[Dave]

OK, this is trickier than it sounds. Table A has a list of situations and a
step-by-step list of categories. Example:

Situation Order Ruleset
User Clicks OK 1 Ruleset A
User Clicks OK 2 Ruleset B
User Clicks OK 3 Ruleset D
User Clicks OK 4 Ruleset X
User Clicks OK 5 Ruleset A <== That's right, same ruleset can
show up twice in list

Only showing one "Situation", but there would be many.

Then, there is another table of Rules in each Ruleset. Example:

Ruleset Rule Details of Rule
RulesetA Rule 1A bla, bla
RulesetA Rule 2A yada yada
RulesetA Rule 3A ho hum
RulesetB Rule 1B you get the picture

OK, so my goal is to have these separate lists and then COMBINE THEM to look
something like:

Situation Order Ruleset
User Clicks OK 1 Ruleset A
RulesetA Rule 1A bla, bla
RulesetA Rule 2A yada yada
RulesetA Rule 3A ho hum
User Clicks OK 2 Ruleset B
RulesetB Rule 1B you get the picture
User Clicks OK 3 Ruleset D
User Clicks OK 4 Ruleset X
User Clicks OK 5 Ruleset A
RulesetA Rule 1A bla, bla
RulesetA Rule 2A yada yada
RulesetA Rule 3A ho hum

The list of Rules is something that will change alot and need to be updated.
I want to be able to generate the combined list. I was thinking there might
be a way to trick a Pivot Table to do it, but so far the solution eludes me.

 

[Chip Pearson]

I *think* that the following will do what you want. First, create a
defined name named First Situation that refers to the first cell of
data in the Situation table, which is the first of your example
tables.

Then, create a defined name named First Rule that refers to the first
cell in the Rules table, in your example the second table.

Then, create a defined name named Feistiest that refers to the first
cell of the new, combined, destination table. The results of the
merged tables will begin here.

It is assumed that (1) the destination table will be surrounded on all
sides by blanks cells or the edge of the worksheet. This is so that we
can clear it with CurrentRegion. Also, (2) it is assume that there
are no blank cells in the first column of either input table. This is
so that the code can take the presence of an empty cell in the
existing data tables to indicate the end of the table.

Email me at (e-mail address removed) if you want a copy of the workbook that
I used to develop and test the code.

Sub AAA()
Dim Dest As Range
Dim RuleSetName As String
Dim SitRng As Range
Dim RuleRng As Range
Dim SitListed As Boolean

Set SitRng = Range("FirstSituation")
Set RuleRng = Range("FirstRule")
Set Dest = Range("FirstDest")
Dest.CurrentRegion.ClearContents

Do Until SitRng.Value = vbNullString
SitListed = False
Set RuleRng = Range("FirstRule")
RuleSetName = SitRng(1, 3).Value
If StrComp(RuleSetName, SitRng(1, 3).Value, _
vbTextCompare) = 0 Then
Dest = SitRng(1, 1).Value
Dest(1, 2) = SitRng(1, 2).Value
Dest(1, 3) = SitRng(1, 3).Value
Set Dest = Dest(2, 1)
Set RuleRng = Range("FirstRule")
Do Until RuleRng.Value = vbNullString
If StrComp(RuleRng.Value, SitRng(1, 3).Value, _
vbTextCompare) = 0 Then
Dest(1, 2) = RuleRng.Value
Dest(1, 3) = RuleRng(1, 2).Value
Dest(1, 4) = RuleRng(1, 3).Value
Set Dest = Dest(2, 1)
End If
Set RuleRng = RuleRng(2, 1)
Loop
End If
Set SitRng = SitRng(2, 1)
Loop
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]