Report from multiple worksheets?

[sammipd]

I must create a report from two worksheets. Can someone point me to a site with how-to’s?
<o></o>
Column A in both sheets is the File# and the only common piece of info.
Sheet1 has one row of data per file number.
Sheet2 has between 1 and 8 rows of data per file number.
How can I create a report to pull the data in Sheet1 and the associated File# text from Sheet2?
<o></o>
I tried combining all into one worksheet but VLookup only provides the first text cell per File#. Is there a way to combine all the Text data into one cell per File#?
<o></o>

File#	Start	End
123	1/1/12	3/12/14
234	3/2/12	4/3/14
345	5/3/12	5/23/15

<tbody>
</tbody>

<o></o>

File#<o></o>	Text<o></o>	LName
123<o></o>	Red<o></o>	Smith
123	Blue	Smith
234<o></o>	Orange<o></o>	Jones
234	Green	Jones
234	Red	Jones
345<o></o>	Yellow<o></o>	Johnson
345	Green	Johnson
345	Red	Johnson

<tbody>
</tbody>

<o></o>

 

[Smitty]

Why won't VLOOKUP in sheet 2 against sheet 1 work?

You could use a PivotTable to consolidate by file # from there.

 

[sammipd]

Thanks Smitty. I thought of that after I posted last night but would prefer going the other way due to the amount of columns to be moved. File#1 goes to CY2097, File #2 to M6377. Thanks!

 

[Smitty]

I didn't see CY2097/M6377 in the data you posted?

If you have Excel 2010+ you can use PowerPivot to create a relationship on File# between the two sheets, then Pivot it however you want.

 

[sammipd]

My fault, sorry about that. I was trying to simplify the question and didn't give enough data. I'm using Excel 2007.

The end result is a report based on a word search of several of the columns of this combined data.

I use Conditional Format to change the fill on all cells with the word I'm searching for, filter by color and copy/paste the rows to another sheet. Then pivot that data for an end report.

Is there an easier way?