Excel formula to 'append' information together?

[Exceliott]

Hi all -

I am attempting to use Excel to find information from one table and assign it to other tables....which is normally easy....but there is a conundrum I have run across.

For example:


[TABLE="width: 0"]
<tbody>[TR]
[TD="colspan: 2"]I currently have this information:[/TD]
[/TR]
[TR]
[TD]Standard Section[/TD]
[TD]Activity[/TD]
[/TR]
[TR]
[TD]200.01-14[/TD]
[TD]627[/TD]
[/TR]
[TR]
[TD]200.15-17[/TD]
[TD]644[/TD]
[/TR]
[TR]
[TD]200.15-17[/TD]
[TD]1098[/TD]
[/TR]
[TR]
[TD]200.15-17[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]200.15-17[/TD]
[TD]788[/TD]
[/TR]
[TR]
[TD]200.18[/TD]
[TD]628[/TD]
[/TR]
</tbody>[/TABLE]





[TABLE="width: 0"]
<tbody>[TR]
[TD]Separately, I also currently have this info:[/TD]
[/TR]
[TR]
[TD]Standard Section[/TD]
[/TR]
[TR]
[TD]200.01-14[/TD]
[/TR]
[TR]
[TD]200.01-14[/TD]
[/TR]
[TR]
[TD]200.01-14[/TD]
[/TR]
[TR]
[TD]200.01-14[/TD]
[/TR]
[TR]
[TD]200.15-17[/TD]
[/TR]
[TR]
[TD]200.15-17[/TD]
[/TR]
[TR]
[TD]200.15-17[/TD]
[/TR]
[TR]
[TD]200.18[/TD]
[/TR]
[TR]
[TD]200.18[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 0"]
<tbody>[TR]
[TD="colspan: 5"]I am attempting to do something like this (Using the rows from Table 2, but assign the 'Activity' numbers from Table 1):

[/TD]
[/TR]
[TR]
[TD]Standard Section[/TD]
[TD="colspan: 4"]Activity[/TD]
[/TR]
[TR]
[TD]200.01-14[/TD]
[TD]627[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]200.01-14[/TD]
[TD]627[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]200.01-14[/TD]
[TD]627[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]200.01-14[/TD]
[TD]627[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]200.15-17[/TD]
[TD]644; 1098; 1099; 788[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]200.15-17[/TD]
[TD] - "Or the 'Activity' Numbers could be in separate columns…and that would be ok....similar to the below"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]200.15-17[/TD]
[TD]644[/TD]
[TD]1098[/TD]
[TD]1099[/TD]
[TD]788[/TD]
[/TR]
[TR]
[TD]200.18[/TD]
[TD]628[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]200.18[/TD]
[TD]628[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




Essentially I am trying to pull information from one table and assign it to a second table. But, there are multiple 'Activity' numbers in Table 1 within a particular 'Standard Section'.

I attempted within MS Access, but the primary key problem halted me, as the two tables may have duplicate fields of what could be the primary key.


Any help is greatly appreciated. Thanks in advance.

Eliott

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this is any use. Table below in columns A:B is "Table1" while "Table2" is in columns E:F

Enter the shown formula in F2 & it should fill down the table.
It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Excel Workbook
	A	B	C	D	E	F
1	Standard Section	Activity			Standard Section	Activity
2	200.01-14	627			200.01-14	,Table1,""))}]627
3	200.15-17	644			200.01-14	627
4	200.15-17	1098			200.01-14	627
5	200.15-17	1099			200.01-14	627
6	200.15-17	788			200.15-17	644; 1098; 1099; 788
7	200.18	628			200.15-17	644; 1098; 1099; 788
8					200.15-17	644; 1098; 1099; 788
9					200.18	628
10					200.18	628
11
Lookup