Vlookup? that returns all values

[Tarvalon]

Hello,

I have a spreadsheet (raw data from an access database) that shows multiple jobs for the same day. Looks like this:

7/2/2012 Job #1 other data blah blah
7/2/2012 Job #2 other data blah blah
7/2/2012 Job #3 other data blah blah

What kind of function can I use to bring it over to the pretty spreadsheet without having to copy/paste everyday? I want it to look like this:

7/2/2012 (today function-1)

Job #1 other data blah blah
Job #2 other data blah blah
Job #3 other data blah blah

I only need the list for the day before today.

Thanks In Advance,

Tarv

 

[Robert Mika]

Excel 2010

	A	B	C	D
1
2	7/1/2012	Job #1	other data	blah blah
3	7/1/2012	Job #2	other data	blah blah
4	7/1/2012	Job #3	other data	blah blah
5	7/2/2012	Job #1	other data	blah blah
6	7/2/2012	Job #2	other data	blah blah
7	7/2/2012	Job #3	other data	blah blah

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheest1​

Excel 2010
	A	B	C	D
1	7/2/2012
2	3	Job #1	other data	blah blah
3		Job #2	other data	blah blah
4		Job #3	other data	blah blah
Sheet2

Cell Formulas
Range	Formula
A2	=COUNTIF(Sheest1!$A$2:$A$8,A1)
B2	{=IF(ROWS(Sheest1!$F$2:$F2)<=$A$2,INDEX(Sheest1!B$2:B$200,SMALL(IF(Sheest1!$A$2:$A$200=$A$1,ROW(Sheest1!$F$2:$F$200)-ROW(Sheest1!$A$2)+1),ROWS(Sheest1!$F$2:$F2))),"")}
C2	{=IF(ROWS(Sheest1!$F$2:$F2)<=$A$2,INDEX(Sheest1!C$2:C$200,SMALL(IF(Sheest1!$A$2:$A$200=$A$1,ROW(Sheest1!$F$2:$F$200)-ROW(Sheest1!$A$2)+1),ROWS(Sheest1!$F$2:$F2))),"")}
D2	{=IF(ROWS(Sheest1!$F$2:$F2)<=$A$2,INDEX(Sheest1!D$2:D$200,SMALL(IF(Sheest1!$A$2:$A$200=$A$1,ROW(Sheest1!$F$2:$F$200)-ROW(Sheest1!$A$2)+1),ROWS(Sheest1!$F$2:$F2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Drag down.

 

[Tarvalon]

What does "entered with Ctrl+Shift+Enter do? Are $F$2:$F2 and $F$2:$F$200 ranges? If so, I don't see how they play into this.

I tried to apply it to what I have and it's not working.

It's giving me the very top result.

 

[Tarvalon]

this is what I have:

Today +1 function is in M1 on 'Report'

I need Job (from 'Daily Job Yield Raw Data' column B), OD (from 'Daily Job Yield Raw Data' column C), Wall (from 'Daily Job Yield Raw Data' column D), Grade (from 'Daily Job Yield Raw Data' column E) for date ('Report', M1) all to appear on 'Report' range A7:D9 in same order as previously mentioned.

Thanks for your help.

 

[Special-K99]

What does "entered with Ctrl+Shift+Enter do?

Google Array Formulas

 

[Robert Mika]

What does "entered with Ctrl+Shift+Enter do? Are $F$2:$F2 and $F$2:$F$200 ranges? If so, I don't see how they play into this.

I tried to apply it to what I have and it's not working.

It's giving me the very top result.

The first F range is a count.
When you dragiing down it return 1,2,3.... so when the row number(or number) is equal or greater then the Count of days 7/2/2012( the reference to A2) the the result is ""(nothing),This prevents returnnin #NUM error.
The second F range (up to 200) is an argument for the SMALL function.
As SMALL will return FALLS/TRUE statment we need replace them with numbers.
Use Evaluate Formula toll from Excel option to see how this work

As Special-K99 mention google for CSE formulas.
One example:
http://www.cpearson.com/excel/ArrayFormulas.aspx



Which part is not working?
Apply as on my examle and it should work.
Do you have any one day data at given time?
Then there is no need for this formula.
Please post large example.

 

[lundbhaiz]

Robert mika. this is very nice

 

[Tarvalon]

Thank you for your help and I'm sorry for the delayed response.

This is what I have typed:

=IF(ROWS('Daily Job Yield Raw Data'!$J$2:$J2)<=Report!$O$5,INDEX('Daily Job Yield Raw Data'!B$2:B$200,SMALL(IF('Daily Job Yield Raw Data'!$A$2:$A$200=Report!$M$1,ROW('Daily Job Yield Raw Data'!$J$2:$J2)-ROW('Daily Job Yield Raw Data'!$A$2)+1)-ROWS('Daily Job Yield Raw Data'!$J$2:$J2))),"")

It's not working.

Report:

	A	B	C	D
	Job	OD	Wall	Grade
7
8
9

<TBODY>
</TBODY>

AND​

	M	N	O
1	Today Function-1
5			=COUNTIF('Daily Job Yield Raw Data'!A:H,Report!M1)

<TBODY>
</TBODY>

Daily Job Yield Raw Data:

	A	B	C	D	E
1	Yield Date	Job	Diameter	Thickness	Grade
2	7/3/2012
3	7/3/2012
4	7/4/2012

<TBODY>
</TBODY>

I need a function that basically copies/ pastes all Jobs, OD (Diameter), Thickness (Wall), Grade from 'Daily Job Yield Raw Data' to it's designated place in 'Report'.

Thanks so much for your help!!!

Tarv

 

[Skybluekid]

Did you just press enter or hold down Control and Shift and the press Enter. You need to do the latter because you have an Array formula. If you just press enter, it will throw up an error.

 

[Tarvalon]

When I press Ctrl, Shift, Enter, it says, "You've entered too few arguments for this function."