can you help me with the following problem. Let me explain you,

[MUHAMMAD IBRAHIM]

Sheet1!

[TABLE="width: 633"]
<tbody>[TR]
[TD]ORDER NO. /NAME
[/TD]
[TD]DATE
[/TD]
[TD]ROLL.NO
[/TD]
[TD]QUANTITY OF CUTTING (YARDS)
[/TD]
[TD] SOLD RATE
[/TD]
[/TR]
[TR]
[TD]R#115
[/TD]
[TD]2/7/2014
[/TD]
[TD]129
[/TD]
[TD]4.50
[/TD]
[TD]525
[/TD]
[/TR]
[TR]
[TD]R#117
[/TD]
[TD]2/7/2014
[/TD]
[TD]179
[/TD]
[TD]5.00
[/TD]
[TD]410
[/TD]
[/TR]
[TR]
[TD]R#579
[/TD]
[TD]3/7/2014
[/TD]
[TD]267
[/TD]
[TD]25.00
[/TD]
[TD]285
[/TD]
[/TR]
[TR]
[TD]R#579
[/TD]
[TD]5/7/2014
[/TD]
[TD]267
[/TD]
[TD]2.00
[/TD]
[TD]285
[/TD]
[/TR]
[TR]
[TD]R#114
[/TD]
[TD]5/7/2014
[/TD]
[TD]93
[/TD]
[TD]1.25
[/TD]
[TD]180
[/TD]
[/TR]
[TR]
[TD]R#114
[/TD]
[TD]5/7/2014
[/TD]
[TD]226
[/TD]
[TD]1.25
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]R#568
[/TD]
[TD]8/7/2014
[/TD]
[TD]227
[/TD]
[TD]3.00
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]R#120
[/TD]
[TD]8/7/2014
[/TD]
[TD]129
[/TD]
[TD]1.00
[/TD]
[TD]525
[/TD]
[/TR]
[TR]
[TD]R#118
[/TD]
[TD]8/7/2014
[/TD]
[TD]104
[/TD]
[TD]3.50
[/TD]
[TD]450
[/TD]
[/TR]
[TR]
[TD]R#118
[/TD]
[TD]8/7/2014
[/TD]
[TD]159
[/TD]
[TD]7.00
[/TD]
[TD]450
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2!

[TABLE="width: 633, align: left"]
<tbody>[TR]
[TD]ENTER ORDER NO. /NAME
[/TD]
[TD]DATE
[/TD]
[TD]ROLL.NO
[/TD]
[TD]QUANTITY OF CUTTING (YARDS)
[/TD]
[TD] SOLD RATE
[/TD]
[/TR]
[TR]
[TD]R#114
[/TD]
[TD]5/7/2014
[/TD]
[TD]93
[/TD]
[TD]1.25
[/TD]
[TD]180
[/TD]
[/TR]
[TR]
[TD]R#114
[/TD]
[TD]5/7/2014
[/TD]
[TD]226
[/TD]
[TD]1.25
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]R#118
[/TD]
[TD]8/7/2014
[/TD]
[TD]104
[/TD]
[TD]3.50
[/TD]
[TD]450
[/TD]
[/TR]
[TR]
[TD]R#118
[/TD]
[TD]8/7/2014
[/TD]
[TD]159
[/TD]
[TD]7.00
[/TD]
[TD]450
[/TD]
[/TR]
[TR]
[TD]R#579
[/TD]
[TD]3/7/2014
[/TD]
[TD]267
[/TD]
[TD]25.00
[/TD]
[TD]285
[/TD]
[/TR]
[TR]
[TD]R#579
[/TD]
[TD]5/7/2014
[/TD]
[TD]267
[/TD]
[TD]2.00
[/TD]
[TD]285
[/TD]
[/TR]
</tbody>[/TABLE]















In sheet1! I have five columns . sheet1! Is my main sheet in which I enter my data . what I want to do is

In sheet2! Column name ”ENTER ORDER NO. /NAME” (“Which I have created as an example to show you what I want”) when I enter my enter my data it shows me the exact “DATE”, “ROLL.NO”, “QUANTITY OF CUTTING (YARDS)” ,” SOLD RATE”. Even if I have multiple SAME data in “ENTER ORDER NO. /NAME” . which I have shown you in sheet2!

 

[FormR]

Something like this maybe: the formula in cell C2 is copied down and across.

Pay special attention to the Array Formula Entry instructions.

Excel 2012
	A	B	C	D	E
1	ENTER ORDER NO. /NAME	DATE	ROLL.NO	QUANTITY OF CUTTING (YARDS)	SOLD RATE
2	R#114	05/07/2014	93	1.25	180
3	R#114	05/07/2014	226	1.25	200
4	R#118	08/07/2014	104	3.5	450
5	R#118	08/07/2014	159	7	450
6	R#579	03/07/2014	267	25	285
7	R#579	05/07/2014	267	2	285
Sheet2

Cell Formulas
Range	Formula
C2	{=INDEX(Sheet1!C$2:C$11,SMALL(IF(Sheet1!$A$2:$A$11=$A2,IF(Sheet1!$B$2:$B$11=$B2,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1)),COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Excel 2012
	A	B	C	D	E
1	ORDER NO. /NAME	DATE	ROLL.NO	QUANTITY OF CUTTING (YARDS)	SOLD RATE
2	R#115	02/07/2014	129	4.5	525
3	R#117	02/07/2014	179	5	410
4	R#579	03/07/2014	267	25	285
5	R#579	05/07/2014	267	2	285
6	R#114	05/07/2014	93	1.25	180
7	R#114	05/07/2014	226	1.25	200
8	R#568	08/07/2014	227	3	200
9	R#120	08/07/2014	129	1	525
10	R#118	08/07/2014	104	3.5	450
11	R#118	08/07/2014	159	7	450
Sheet1

 

[MUHAMMAD IBRAHIM]

this is not working the i want . i want it to work like this . let me explain you again.

sheet1!

[TABLE="class: grid, width: 758"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ORDER NO. /NAME[/TD]
[TD]DATE[/TD]
[TD]ROLL.NO[/TD]
[TD]QUANTITY OF CUTTING (YARDS)
[/TD]
[TD] SOLD RATE
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]R#115[/TD]
[TD]2/7/2014[/TD]
[TD]129[/TD]
[TD]4.50
[/TD]
[TD]525
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]R#117[/TD]
[TD]2/7/2014[/TD]
[TD]179[/TD]
[TD]5.00
[/TD]
[TD]410
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]R#579[/TD]
[TD]3/7/2014[/TD]
[TD]267[/TD]
[TD]25.00
[/TD]
[TD]285
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]R#579[/TD]
[TD]5/7/2014[/TD]
[TD]267[/TD]
[TD]2.00
[/TD]
[TD]285
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]R#114[/TD]
[TD]5/7/2014[/TD]
[TD]93[/TD]
[TD]1.25
[/TD]
[TD]180
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]R#114[/TD]
[TD]5/7/2014[/TD]
[TD]226[/TD]
[TD]1.25
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]R#568[/TD]
[TD]8/7/2014[/TD]
[TD]227[/TD]
[TD]3.00
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]R#120[/TD]
[TD]8/7/2014[/TD]
[TD]129[/TD]
[TD]1.00
[/TD]
[TD]525
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]R#118[/TD]
[TD]8/7/2014[/TD]
[TD]104[/TD]
[TD]3.50
[/TD]
[TD]450
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]R#118[/TD]
[TD]8/7/2014[/TD]
[TD]159[/TD]
[TD]7.00
[/TD]
[TD]450
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2!


[TABLE="width: 611"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 767"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ENTER ORDER NO. /NAME
[/TD]
[TD]DATE
[/TD]
[TD]ROLL.NO
[/TD]
[TD]QUANTITY OF CUTTING (YARDS)
[/TD]
[TD] SOLD RATE
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD] R#114
[/TD]
[TD]5/7/2014
[/TD]
[TD]93
[/TD]
[TD]1.25
[/TD]
[TD]180
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]5/7/2014
[/TD]
[TD]226
[/TD]
[TD]1.25
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]R#118
[/TD]
[TD]8/7/2014
[/TD]
[TD]104
[/TD]
[TD]3.5
[/TD]
[TD]450
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]8/7/2014
[/TD]
[TD]159
[/TD]
[TD]7
[/TD]
[TD]450
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]R#579
[/TD]
[TD]3/7/2014
[/TD]
[TD]267
[/TD]
[TD]25
[/TD]
[TD]285
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD]5/7/2014
[/TD]
[TD]267
[/TD]
[TD]2
[/TD]
[TD]285
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


in sheet1! i have my stocks data which is my main sheet. what i want to do is when i enter my data in column "A" sheet2! (ENTER ORDER NO. /NAME ). only one time so it automatically give me the data of column"A" sheet1! (ORDER NO. /NAME) which are coulmn "B" ,column"C", column "D", column "E" of sheet1! ... for ex if i enter (column"A" sheet2!"ENTER ORDER NO. /NAME") R#114 only one time so it show me it dates more than one time same thing must go with coulmn "B" ,column"C", column "D", column "E" of sheet2! . as i have in shown you as an example sheet2!. but still i thankyou to answer my previous question . i just want in simple if i enter my ordername "R#114" so it tells me on what dates, rollno and so on several time as i have fed my data in sheet 1! thankyou in advance.

 

[FormR]

This is not exactly as you asked, as you need to enter the "Order No / Name" to each row in sheet2; but maybe it's acceptable.

If you have Excel 2010 or higher:

Excel 2012
	A	B	C	D	E
1	ENTER ORDER NO. /NAME	DATE	ROLL.NO	QUANTITY OF CUTTING (YARDS)	SOLD RATE
2	R#114	05/07/2014	93	1.25	180
3	R#114	05/07/2014	226	1.25	200
4	R#118	08/07/2014	104	3.5	450
5	R#118	08/07/2014	159	7	450
6	R#579	03/07/2014	267	25	285
7	R#579	05/07/2014	267	2	285
Sheet2

Cell Formulas
Range	Formula
B2	=INDEX(Sheet1!B$2:B$11,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1)/(Sheet1!$A$2:$A$11=$A2),COUNTIF($A$2:$A2,$A2)))

Or lower versions:

Excel 2012
	A	B	C	D	E
1	ENTER ORDER NO. /NAME	DATE	ROLL.NO	QUANTITY OF CUTTING (YARDS)	SOLD RATE
2	R#114	05/07/2014	93	1.25	180
3	R#114	05/07/2014	226	1.25	200
4	R#118	08/07/2014	104	3.5	450
5	R#118	08/07/2014	159	7	450
6	R#579	03/07/2014	267	25	285
7	R#579	05/07/2014	267	2	285
Sheet2

Cell Formulas
Range	Formula
B2	{=INDEX(Sheet1!B$2:B$11,SMALL(IF(Sheet1!$A$2:$A$11=$A2,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A2,$A2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MUHAMMAD IBRAHIM]

WHY CANT I ENTER "Order No / Name" ONE TIME IT SHOWS ME AUTOMATICALLY

 

[MUHAMMAD IBRAHIM]

I have microsoft 2013

 

[MUHAMMAD IBRAHIM]

What does aggregate(15,6 means

 

[FormR]

WHY CANT I ENTER "Order No / Name" ONE TIME IT SHOWS ME AUTOMATICALLY

Please don't shout - It just adds a whole level of extra complication

What does aggregate(15,6 means

The 15 tells the aggregate function to return the smallest k value from the array argument, the 6 means to ignore errors.

https://support.office.com/en-sg/article/AGGREGATE-function-c8caed56-07df-4aeb-9741-23693ffbe525

 

[MUHAMMAD IBRAHIM]

Oh my god i will die . I am send a link of my excel file plz download it and apply the formula by yourself and then send me . I am not getting it by my self plz dont sayy noooo!!! Its my humble request to you thankyou.https://mail.google.com/mail/u/0/?u...&attid=0.1&disp=safe&realattid=f_i9gyw4gy0&zw . WORK ON SHEET3! AND SHEET1!

 

[FormR]

That link doesn't work.