Need Help combining 2 separate tables

[mdennis624]

I import a report into Excel that contains data in 2 separate tables. The tables are located one Above the other when imported into Excel. The Top table contains line item numbers and other miscellaneous data. The Bottom table also conatins the same line item numbers along with other associated data. However, the Bottom table might also contain various sub-line item numbers that Do Not have corresponding rows on the Top table. We need to combine these 2 tables into one single long table or worksheet so that all of the Line items and Sub-Line item rows are grouped together on the top table. Thanks in advance for your help.

[Barry Katcher]

Do the sub-line item numbers start with the same numbers as the line item numbers they are related to? If so, just remove any blank rows and sort on that column.

[Todd Bardoni]

If the sub-lines' line numbers are blank, then you can do the following to fill in that data:

1. Select the first cell of the column containing the line numbers in the second table with the sub-lines.
2. Select the cells from your current selection to your last record.
3. Go to Edit/Go to/Special/Blanks.
4. Click inside your Formula bar and type =TheCellAddressoftheFirstCellYouSelected.
5. Hit Ctrl+Enter.
6. Copy the formulas and Paste Special/Values.

Now you can do a clean sort.

[mdennis624]

The bottom table contatins the same Line number and sub line item columns as the top table. The top table only ever contains the main line item and the sub line item number is always 00. The bottom table ontains additional rows containing the sub line numbers. None of the cells in either table are blnk.

Example:
Top Table:
Cell A Cell B
001 00
002 00
003 00
004 00

Bottom Table:
Cell A Cell B
001 00
001 01
001 02
001 03
002 00
002 01
003 00
003 01
004 00

The sub line item data in table 2 is unique to corresponding top table line item so they must stay on separate rows. I need to move them to the top table and append them to the end. However I need to first insert the correct number of rows under each line item on the top table. Not all line items will have sub line items and the quantites os sub line iytes can vary. I was thinking of somehow scanning the bottom table for line items with matchng sub line items and then inserting the correct number of necessary rows beneath each corresponding line item on the top table. Then simply move the bottom table to to end fo the top table and continue my work from there. I can't figure out how to do the scanning and inserting the additional rows at the right places on the top table. I saw one tip that recommended using UNION but I can't find any info on that usage. Also I am using Excel 97. Thanks again.

[Barry Katcher]

I may be exhibiting my usual denseness in missing something, but from reading and re-reading your question, it appears that what you are asking for is what appears in the sample below:

******** ******************** ************************************************************************>

Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A1 =
	A	B	C	D	E
1	Before	*	*	After	*
2	Top	*	*	Acct.	Sub
3	001	00	*	001	00
4	002	00	*	001	00
5	003	00	*	001	01
6	004	00	*	001	02
7	*	*	*	001	03
8	Bottom	*	*	002	00
9	001	00	*	002	00
10	001	01	*	002	01
11	001	02	*	003	00
12	001	03	*	003	00
13	002	00	*	003	01
14	002	01	*	004	00
15	003	00	*	004	00
16	003	01	*	*	*
17	004	00	*	*	*
Sheet1 *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

It that's the case, just delete the rows between the bottom of the first table and the top of the second, and sort by 1) the first column, ascending and then by 2) the second column ascending.

Again, if my pea brain is missing something, please accept my appy polly loggies.

[mdennis624]

Hi Barry,
I can tell by the feedback that I didn't do a very good job explaining my request. The bottom table contains unique data columns that do not appear on the top table, except for the line item numbers themselves which is what is common between the 2 tables. We need to get all of the data from the bottm table appended to the end of the top table. The probelm is the bottom table can have additional sub line items that must be accounted for on the top table. If I just move the bottom table to the end of the top table, when ever a subline item occurs, that appended row of data will not be associated with the correct line item anymore. I hope this helps. Thanks for your help and patience.