Hello,
I have two tables that are maintained separately, but share common elements. Based on those common elements, I would like to merge the tables and extend them into a full data list.
The first table has headings across the top (let's call them location groups) that need to be looked up in the second table, which will contain multiple records (let's called them locations) of those headings associated with additional fields. After looking the value up, I want to create a third table that's a list of all of the source data and looked up values. Much easier to explain with visuals:
Table 1(source data):
<tbody>
</tbody>
Table 2(mapping table):
<tbody>
</tbody>
Table 3(output; for each fruit type and fruit variety, lookup location group and create a record for each location):
<tbody>
</tbody>
I have many more columns and rows of data, but this basic layout would solve my problem. I know how to look through the source table and then how to update the output table with those values, but I don't know how to find multiple values in the mapping table and return multiple values.
Say I've declared variables for the source table (Table1) and mapping table (Table2), including their rows, columns and data, and selected an output range for Table3 as a single cell (outRng); this would roughly be where I'm at (the code below isn't complete for all of the headings, but I can figure that part out...this is just for the mapping lookup):
This, obviously, only returns one value, as range.find only returns the range of the first found cell. This would be my resulting table:
<tbody>
</tbody>
What am I missing? It would be great if I could use a range.resize(foundrange.size) = foundrange.offset(1) kind of formula here, but maybe I can only do this with multiple loops?
Thanks in advance!
I have two tables that are maintained separately, but share common elements. Based on those common elements, I would like to merge the tables and extend them into a full data list.
The first table has headings across the top (let's call them location groups) that need to be looked up in the second table, which will contain multiple records (let's called them locations) of those headings associated with additional fields. After looking the value up, I want to create a third table that's a list of all of the source data and looked up values. Much easier to explain with visuals:
Table 1(source data):
| A | B | C | D | |
| 1 | Fruit Types | Stores | Ecommerce | Corporate |
| 2 | Apple | Gala | Gala | Fuji |
| 3 | Orange | Tangerine | Mandarin | Madarin |
<tbody>
</tbody>
Table 2(mapping table):
| A | B | |
| 1 | Location Groups | Locations |
| 2 | Stores | Store 100 |
| 3 | Stores | Store 200 |
| 4 | Stores | Store 300 |
| 5 | Ecommerce | Canada Website |
| 6 | Ecommerce | USA Website |
| 7 | Ecommerce | Global Website |
| 8 | Corporate | Head Office |
<tbody>
</tbody>
Table 3(output; for each fruit type and fruit variety, lookup location group and create a record for each location):
| A | B | C | D | |
| 1 | Fruit Types | Fruit Variety | Location Group | Location |
| 2 | Apple | Gala | Stores | Store 100 |
| 3 | Apple | Gala | Stores | Store 200 |
| 4 | Apple | Gala | Stores | Store 300 |
| 5 | Apple | Gala | Ecommerce | Canada Website |
| 6 | Apple | Gala | Ecommerce | USA Website |
| 7 | Apple | Gala | Ecommerce | Global Website |
| 8 | Apple | Fuji | Corporate | Head Office |
| 9 | Orange | Tangerine | Stores | Store 100 |
| 10 | Orange | Tangerine | Stores | Store 200 |
| 11 | Orange | Tangerine | Stores | Store 300 |
| 12 | Orange | Mandarin | Ecommerce | Canada Website |
| 13 | Orange | Mandarin | Ecommerce | USA Website |
| 14 | Orange | Mandarin | Ecommerce | Global Website |
| 15 | Orange | Mandarin | Corporate | Head Office |
<tbody>
</tbody>
I have many more columns and rows of data, but this basic layout would solve my problem. I know how to look through the source table and then how to update the output table with those values, but I don't know how to find multiple values in the mapping table and return multiple values.
Say I've declared variables for the source table (Table1) and mapping table (Table2), including their rows, columns and data, and selected an output range for Table3 as a single cell (outRng); this would roughly be where I'm at (the code below isn't complete for all of the headings, but I can figure that part out...this is just for the mapping lookup):
Code:
For i = 1 to Table1.rows.count
For j = 1 To Table1.columns.count
If Not Table1.body(i, j) = "" Then
Set foundRng = Table2Groups.Find(Table1Heading(,j), LookIn:=xlValues)
outRng.Offset(k - 1).Resize(foundRng.Rows.Count).Value = foundRng.Offset(, 1).Value
k = k + foundRng.Rows.Count
Else
End If
Next j
Next iThis, obviously, only returns one value, as range.find only returns the range of the first found cell. This would be my resulting table:
| A | B | C | D | |
| 1 | Fruit Types | Fruit Variety | Location Group | Location |
| 2 | Apple | Gala | Stores | Store 100 |
| 3 | Apple | Gala | Ecommerce | Canada Website |
| 4 | Apple | Fuji | Corporate | Head Office |
| 5 | Orange | Tangerine | Stores | Store 100 |
| 6 | Orange | Mandarin | Ecommerce | Canada Website |
| 7 | Orange | Mandarin | Corporate | Head Office |
<tbody>
</tbody>
What am I missing? It would be great if I could use a range.resize(foundrange.size) = foundrange.offset(1) kind of formula here, but maybe I can only do this with multiple loops?
Thanks in advance!
Last edited:
