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>
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD]File#
[/TD]
[TD="width: 48, bgcolor: transparent"]Start
[/TD]
[TD="width: 54, bgcolor: transparent"]End
[/TD]
[/TR]
[TR]
[TD="width: 61, bgcolor: transparent"]123
[/TD]
[TD="width: 48, bgcolor: transparent"]1/1/12
[/TD]
[TD="width: 54, bgcolor: transparent"]3/12/14
[/TD]
[/TR]
[TR]
[TD="width: 61, bgcolor: transparent"]234
[/TD]
[TD="width: 48, bgcolor: transparent"]3/2/12
[/TD]
[TD="width: 54, bgcolor: transparent"]4/3/14
[/TD]
[/TR]
[TR]
[TD="width: 61, bgcolor: transparent"]345
[/TD]
[TD="width: 48, bgcolor: transparent"]5/3/12
[/TD]
[TD="width: 54, bgcolor: transparent"]5/23/15
[/TD]
[/TR]
</tbody>[/TABLE]
<o></o>
[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD]File#<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]Text<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]LName
[/TD]
[/TR]
[TR]
[TD="width: 61, bgcolor: transparent"]123<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]Red<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]Smith
[/TD]
[/TR]
[TR]
[TD="width: 61, bgcolor: transparent"]123
[/TD]
[TD="width: 54, bgcolor: transparent"]Blue
[/TD]
[TD="width: 54, bgcolor: transparent"]Smith
[/TD]
[/TR]
[TR]
[TD="width: 61, bgcolor: transparent"]234<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]Orange<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]Jones
[/TD]
[/TR]
[TR]
[TD="width: 61, bgcolor: transparent"]234
[/TD]
[TD="width: 54, bgcolor: transparent"]Green
[/TD]
[TD="width: 54, bgcolor: transparent"]Jones
[/TD]
[/TR]
[TR]
[TD="width: 61, bgcolor: transparent"]234
[/TD]
[TD="width: 54, bgcolor: transparent"]Red
[/TD]
[TD="width: 54, bgcolor: transparent"]Jones
[/TD]
[/TR]
[TR]
[TD="width: 61, bgcolor: transparent"]345<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]Yellow<o></o>
[/TD]
[TD="width: 54, bgcolor: transparent"]Johnson
[/TD]
[/TR]
[TR]
[TD="width: 61, bgcolor: transparent"]345
[/TD]
[TD="width: 54, bgcolor: transparent"]Green
[/TD]
[TD="width: 54, bgcolor: transparent"]Johnson
[/TD]
[/TR]
[TR]
[TD="width: 61, bgcolor: transparent"]345
[/TD]
[TD="width: 54, bgcolor: transparent"]Red
[/TD]
[TD="width: 54, bgcolor: transparent"]Johnson
[/TD]
[/TR]
</tbody>[/TABLE]
<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?