Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mixing data from 2 different tables
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. -- Much appreciated, Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mixing data from 2 different tables
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 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] On Thu, 4 Feb 2010 05:16:02 -0800, Dave wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mixing zip with zip+4 and then sorting | Excel Discussion (Misc queries) | |||
mixing up a list | Excel Discussion (Misc queries) | |||
Any way to programmatically make pivot tables behave more like data tables? | Excel Programming | |||
Mixing up the arguments | Excel Worksheet Functions | |||
Sorting & Filtering in Shared Workbook mixing up data | Excel Worksheet Functions |