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!

 

[MUHAMMAD IBRAHIM]

That link doesn't work.

It an excel file but its works when i click it open and downloads why the file . try again or tell another from which i can send you my this file

 

[FormR]

It an excel file but its works when i click it open and downloads why the file . try again or tell another from which i can send you my this file

The link doesn't work for me.

Try to explain what difficulties you are having implementing the formula. If your ranges / sheet names are different to the examples you have shown then explain what the actual ranges / sheet names are.

 

[MUHAMMAD IBRAHIM]

Okay i will show you my actual work

 

[MUHAMMAD IBRAHIM]

It an excel file but its works when i click it open and downloads why the file . try again or tell another from which i can send you my this file

ftp://C:\Users\Saad\Desktop\Royal Excel\Royal Stock Data.xlsx I HOPE THIS LINK WORK TRY THIS LINK PLZ WORK ON SHEET3! AND SHEET1!

 

[MUHAMMAD IBRAHIM]

Can i email you my work plz tell me how can i email you

 

[DocAElstein]

. @ Hallo Muhammad

. Maybe I bit help can.
. I no good with formulas are.
. But I try now to you help.

. ( I use XL 2007 now here )

. I you make now a Excel 2007 File. I that File name give is called MohamidAbraham.xlsm
. You here click on and you have can:
https://app.box.com/s/fwwmjqso125ei134q3bh522i39k263rm

. I this made for you did like this way:
. 1) I your data copied like this:

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/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][td][/td][/tr]

[tr][td]

2​

[/td][td]R#115[/td][td]

02.07.2014​

[/td][td]

129​

[/td][td]

4.5​

[/td][td]

525​

[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]R#117[/td][td]

02.07.2014​

[/td][td]

179​

[/td][td]

5​

[/td][td]

410​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]R#579[/td][td]

03.07.2014​

[/td][td]

267​

[/td][td]

25​

[/td][td]

285​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]R#579[/td][td]

05.07.2014​

[/td][td]

267​

[/td][td]

2​

[/td][td]

285​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]R#114[/td][td]

05.07.2014​

[/td][td]

93​

[/td][td]

1.25​

[/td][td]

180​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]R#114[/td][td]

05.07.2014​

[/td][td]

226​

[/td][td]

1.25​

[/td][td]

200​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]R#568[/td][td]

08.07.2014​

[/td][td]

227​

[/td][td]

3​

[/td][td]

200​

[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]R#120[/td][td]

08.07.2014​

[/td][td]

129​

[/td][td]

1​

[/td][td]

525​

[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]R#118[/td][td]

08.07.2014​

[/td][td]

104​

[/td][td]

3.5​

[/td][td]

450​

[/td][td][/td][/tr]

[tr][td]

11​

[/td][td]R#118[/td][td]

08.07.2014​

[/td][td]

159​

[/td][td]

7​

[/td][td]

450​

[/td][td][/td][/tr]

[tr][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet1[/td][/tr][/table]


…… and

Using Excel 2007
[Table="width:, class:grid"][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][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]R#114[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]R#118[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]R#118[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]R#579[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]R#579[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet2[/td][/tr][/table]



. 2 ) I FormR Formula from Post #4 copy it to Clipboard ( Ctrl C )

=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)))

( Do not copy {} )

. 3a ) I select ( click cell C2 )


<a href="http://s1065.photobucket.com/user/DocAElstein/media/B2Click_zpshe4fzwzl.jpg.html" target="_blank"><img src="http://i1065.photobucket.com/albums/u400/DocAElstein/B2Click_zpshe4fzwzl.jpg" border="0" alt=" photo B2Click_zpshe4fzwzl.jpg"/></a>

……………………………….
. 3b ) I it here paste ( Ctrl V ) in Formula Bar - I no paste it in B2

<a href="http://s1065.photobucket.com/user/DocAElstein/media/B2CrtlV_zpszgdwitsy.jpg.html" target="_blank"><img src="http://i1065.photobucket.com/albums/u400/DocAElstein/B2CrtlV_zpszgdwitsy.jpg" border="0" alt=" photo B2CrtlV_zpszgdwitsy.jpg"/></a>

…………………………………….
. 3c ) I Ctrl + Shift + Enter

----- It no work!!!!

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]ENTER ORDER NO. /NAME[/td][td]DATE[/td][td]ROLL.NO[/td][/tr]

[tr][td]

2​

[/td][td]R#114[/td][td]

41825​

[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]R#114[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]R#118[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]R#118[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet2[/td][/tr][/table]

. 4a) I do this:


<a href="http://s1065.photobucket.com/user/DocAElstein/media/ChangeToText_zpsxvukijyi.jpg.html" target="_blank"><img src="http://i1065.photobucket.com/albums/u400/DocAElstein/ChangeToText_zpsxvukijyi.jpg" border="0" alt=" photo ChangeToText_zpsxvukijyi.jpg"/></a>
……………………..



….. then try again!!...


. 5a) ------ .3a)
. 5b) ------ .3b)
. 5c) ------ .3c)

------ It work !!!!!


Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/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][/tr]

[tr][td]

2​

[/td][td]R#114[/td][td]5/7/2014[/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]R#114[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]R#118[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]R#118[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]R#579[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]R#579[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet2[/td][/tr][/table]


.6 ) I this do ( Drag Down and across )

<a href="http://s1065.photobucket.com/user/DocAElstein/media/DragDownAndAcross_zpsmser1apn.jpg.html" target="_blank"><img src="http://i1065.photobucket.com/albums/u400/DocAElstein/DragDownAndAcross_zpsmser1apn.jpg" border="0" alt=" photo DragDownAndAcross_zpsmser1apn.jpg"/></a>


. 7)


It work

Using Excel 2007
[Table="width:, class:grid"][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]=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)))[/td][td]

=INDEX(Sheet1!C$2:C$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)))​

[/td][td]

=INDEX(Sheet1!D$2:D$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)))​

[/td][td]

=INDEX(Sheet1!E$2:E$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)))​

[/td][/tr]

[tr][td]

3​

[/td][td]R#114[/td][td]=INDEX(Sheet1!B$2:B$11,SMALL(IF(Sheet1!$A$2:$A$11=$A3,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A3,$A3)))[/td][td]

=INDEX(Sheet1!C$2:C$11,SMALL(IF(Sheet1!$A$2:$A$11=$A3,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A3,$A3)))​

[/td][td]

=INDEX(Sheet1!D$2:D$11,SMALL(IF(Sheet1!$A$2:$A$11=$A3,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A3,$A3)))​

[/td][td]

=INDEX(Sheet1!E$2:E$11,SMALL(IF(Sheet1!$A$2:$A$11=$A3,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A3,$A3)))​

[/td][/tr]

[tr][td]

4​

[/td][td]R#118[/td][td]=INDEX(Sheet1!B$2:B$11,SMALL(IF(Sheet1!$A$2:$A$11=$A4,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A4,$A4)))[/td][td]

=INDEX(Sheet1!C$2:C$11,SMALL(IF(Sheet1!$A$2:$A$11=$A4,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A4,$A4)))​

[/td][td]

=INDEX(Sheet1!D$2:D$11,SMALL(IF(Sheet1!$A$2:$A$11=$A4,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A4,$A4)))​

[/td][td]

=INDEX(Sheet1!E$2:E$11,SMALL(IF(Sheet1!$A$2:$A$11=$A4,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A4,$A4)))​

[/td][/tr]

[tr][td]

5​

[/td][td]R#118[/td][td]=INDEX(Sheet1!B$2:B$11,SMALL(IF(Sheet1!$A$2:$A$11=$A5,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A5,$A5)))[/td][td]

=INDEX(Sheet1!C$2:C$11,SMALL(IF(Sheet1!$A$2:$A$11=$A5,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A5,$A5)))​

[/td][td]

=INDEX(Sheet1!D$2:D$11,SMALL(IF(Sheet1!$A$2:$A$11=$A5,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A5,$A5)))​

[/td][td]

=INDEX(Sheet1!E$2:E$11,SMALL(IF(Sheet1!$A$2:$A$11=$A5,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A5,$A5)))​

[/td][/tr]

[tr][td]

6​

[/td][td]R#579[/td][td]=INDEX(Sheet1!B$2:B$11,SMALL(IF(Sheet1!$A$2:$A$11=$A6,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A6,$A6)))[/td][td]

=INDEX(Sheet1!C$2:C$11,SMALL(IF(Sheet1!$A$2:$A$11=$A6,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A6,$A6)))​

[/td][td]

=INDEX(Sheet1!D$2:D$11,SMALL(IF(Sheet1!$A$2:$A$11=$A6,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A6,$A6)))​

[/td][td]

=INDEX(Sheet1!E$2:E$11,SMALL(IF(Sheet1!$A$2:$A$11=$A6,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A6,$A6)))​

[/td][/tr]

[tr][td]

7​

[/td][td]R#579[/td][td]=INDEX(Sheet1!B$2:B$11,SMALL(IF(Sheet1!$A$2:$A$11=$A7,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A7,$A7)))[/td][td]

=INDEX(Sheet1!C$2:C$11,SMALL(IF(Sheet1!$A$2:$A$11=$A7,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A7,$A7)))​

[/td][td]

=INDEX(Sheet1!D$2:D$11,SMALL(IF(Sheet1!$A$2:$A$11=$A7,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A7,$A7)))​

[/td][td]

=INDEX(Sheet1!E$2:E$11,SMALL(IF(Sheet1!$A$2:$A$11=$A7,ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),COUNTIF($A$2:$A7,$A7)))​

[/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet2[/td][/tr][/table]

Using Excel 2007
[Table="width:, class:grid"][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]R#114[/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]R#118[/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]R#120[/td][td]8/7/2014[/td][td]

129​

[/td][td]

1​

[/td][td]

525​

[/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet2[/td][/tr][/table]


Using Excel 2007
[Table="width:, class:grid"][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]R#114[/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]R#114[/td][td]

#ZAHL!​

[/td][td]

#ZAHL!​

[/td][td]

#ZAHL!​

[/td][td]

#ZAHL!​

[/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]R#120[/td][td]8/7/2014[/td][td]

129​

[/td][td]

1​

[/td][td]

525​

[/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet2[/td][/tr][/table]

. I sleep now.
. Maybe we speak and work together Tomorrow
.. Good Night. You good Sleep

Regards
Alan
( Mustavea Leek )


P.s. If you ask, then I make you a VBA code that do will what you want. I quite good with VBA. I not good with Formulas. You me tell if I make you VBA code should. Then I try to write a VBA code for you tomorrow.

………………………………………………………………..


@ FormR
. Hi, I caught this Thread as I was scouring everywhere to solve my outstanding problem here:
Post #1
http://www.mrexcel.com/forum/excel-...iate-some-help-form-board-formula-master.html

. If I can help here further tomorrow I will. I having been learning VBA for a few months and am OK ( for a Part Time Novice ). But I got bogged down answering some Threads on formulas so started that Thread above to try to get some support . No Luck yet. If you had the chance sometime to check that out I would be grateful – You obviously Know your Formula stuff!!

Thanks
Alan Elston

……………………………………………………………….

 

[FormR]

Can i email you my work plz tell me how can i email you

It would be better if you could explain your set up directly in the thread, else you are limiting your potential helpers to those that are willing to download files from the internet - but as a last resort you can upload your workbook to a file sharing site like dropbox and share the link here.

 

[MUHAMMAD IBRAHIM]

thankyou so much DocAElstein can it be done in more advanced way using vba . like for ex if i enter my order no "R#114" so it give all records of "R#114" automatically spreaded column and row automatically even if i have unlimited records of "R#114".

 

[MUHAMMAD IBRAHIM]

P.s. If you ask, then I make you a VBA code that do will what you want. I quite good with VBA. I not good with Formulas. You me tell if I make you VBA code should. Then I try to write a VBA code for you tomorrow.



……………………………………………………………….

yes.......... plz can you make vba for me

 

[DocAElstein]

Hi Mohamid

yes.......... plz can you make vba for me

. I you code written have :

. It do , for example
. For you to show I try this test data:

Using Excel 2007
[Table="width:, class:grid"][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.5​

[/td][td]

525​

[/td][/tr]

[tr][td]

3​

[/td][td]R#117[/td][td]2/7/2014[/td][td]

179​

[/td][td]

5​

[/td][td]

410​

[/td][/tr]

[tr][td]

4​

[/td][td]R#114[/td][td]3/7/2014[/td][td]

267​

[/td][td]

25​

[/td][td]

285​

[/td][/tr]

[tr][td]

5​

[/td][td]R#579[/td][td]5/7/2014[/td][td]

267​

[/td][td]

2​

[/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​

[/td][td]

200​

[/td][/tr]

[tr][td]

9​

[/td][td]R#118[/td][td]8/7/2014[/td][td]

104​

[/td][td]

3.5​

[/td][td]

850​

[/td][/tr]

[tr][td]

10​

[/td][td]R#118[/td][td]8/7/2014[/td][td]

79​

[/td][td]

6​

[/td][td]

1175​

[/td][/tr]

[tr][td]

11​

[/td][td]Abduhl[/td][td]8/7/2015[/td][td]

54​

[/td][td]

8.5​

[/td][td]

1500​

[/td][/tr]

[tr][td]

12​

[/td][td]Mohamid[/td][td]8/7/2016[/td][td]

29​

[/td][td]

11​

[/td][td]

1825​

[/td][/tr]

[tr][td]

13​

[/td][td]Peschraw[/td][td]8/7/2017[/td][td]

4​

[/td][td]

13.5​

[/td][td]

2150​

[/td][/tr]

[tr][td]

14​

[/td][td]OLE[/td][td]8/7/2018[/td][td]

-21​

[/td][td]

16​

[/td][td]

2475​

[/td][/tr]

[tr][td]

15​

[/td][td]F-Tang[/td][td]8/7/2019[/td][td]

-46​

[/td][td]

18.5​

[/td][td]

2800​

[/td][/tr]

[tr][td]

16​

[/td][td]F-Tang[/td][td]8/7/2020[/td][td]

-71​

[/td][td]

21​

[/td][td]

3125​

[/td][/tr]

[tr][td]

17​

[/td][td]Wigy-Wam[/td][td]8/7/2021[/td][td]

-96​

[/td][td]

23.5​

[/td][td]

3450​

[/td][/tr]

[tr][td]

18​

[/td][td]Biscuit[/td][td]8/7/2022[/td][td]

-121​

[/td][td]

26​

[/td][td]

3775​

[/td][/tr]

[tr][td]

19​

[/td][td]Barrel[/td][td]8/7/2023[/td][td]

-146​

[/td][td]

28.5​

[/td][td]

4100​

[/td][/tr]

[tr][td]

20​

[/td][td]DuWolly[/td][td]8/7/2024[/td][td]

-171​

[/td][td]

31​

[/td][td]

4425​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet1[/td][/tr][/table]

…

.. I do run VBA Code with this for input

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]ENTER ORDER NO. /NAME[/td][td]DATE[/td][/tr]

[tr][td]

2​

[/td][td]R#115[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]R#114[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]F-Tang[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet2VBA[/td][/tr][/table]

.. after code run is , I do this get

Using Excel 2007
[Table="width:, class:grid"][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#115[/td][td]2/7/2014[/td][td]129[/td][td]4,5[/td][td]525[/td][/tr]

[tr][td]

3​

[/td][td]R#114[/td][td]3/7/2014[/td][td]267[/td][td]25[/td][td]285[/td][/tr]

[tr][td]

4​

[/td][td]R#114[/td][td]5/7/2014[/td][td]93[/td][td]1,25[/td][td]180[/td][/tr]

[tr][td]

5​

[/td][td]R#114[/td][td]5/7/2014[/td][td]226[/td][td]1,25[/td][td]200[/td][/tr]

[tr][td]

6​

[/td][td]F-Tang[/td][td]8/7/2019[/td][td]-46[/td][td]18,5[/td][td]2800[/td][/tr]

[tr][td]

7​

[/td][td]F-Tang[/td][td]8/7/2020[/td][td]-71[/td][td]21[/td][td]3125[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet2VBA[/td][/tr][/table]

………………

Or if I do run VBA Code again with this for input

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]ENTER ORDER NO. /NAME[/td][td]DATE[/td][/tr]

[tr][td]

2​

[/td][td]Abduhl[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]Mohamid[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]Peschraw[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]OLE[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]F-Tang[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Wigy-Wam[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]Biscuit[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]Barrel[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]DuWolly[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet2VBA[/td][/tr][/table]

And then after code run is , I do this get

Using Excel 2007
[Table="width:, class:grid"][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]Abduhl[/td][td]8/7/2015[/td][td]54[/td][td]8,5[/td][td]1500[/td][/tr]

[tr][td]

3​

[/td][td]Mohamid[/td][td]8/7/2016[/td][td]29[/td][td]11[/td][td]1825[/td][/tr]

[tr][td]

4​

[/td][td]Peschraw[/td][td]8/7/2017[/td][td]4[/td][td]13,5[/td][td]2150[/td][/tr]

[tr][td]

5​

[/td][td]OLE[/td][td]8/7/2018[/td][td]-21[/td][td]16[/td][td]2475[/td][/tr]

[tr][td]

6​

[/td][td]F-Tang[/td][td]8/7/2019[/td][td]-46[/td][td]18,5[/td][td]2800[/td][/tr]

[tr][td]

7​

[/td][td]F-Tang[/td][td]8/7/2020[/td][td]-71[/td][td]21[/td][td]3125[/td][/tr]

[tr][td]

8​

[/td][td]Wigy-Wam[/td][td]8/7/2021[/td][td]-96[/td][td]23,5[/td][td]3450[/td][/tr]

[tr][td]

9​

[/td][td]Biscuit[/td][td]8/7/2022[/td][td]-121[/td][td]26[/td][td]3775[/td][/tr]

[tr][td]

10​

[/td][td]Barrel[/td][td]8/7/2023[/td][td]-146[/td][td]28,5[/td][td]4100[/td][/tr]

[tr][td]

11​

[/td][td]DuWolly[/td][td]8/7/2024[/td][td]-171[/td][td]31[/td][td]4425[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet2VBA[/td][/tr][/table]




…………………………………………………………………………………………

. It not perfect, but well described so you modify can.
. I no lots test did. I no more time Today. You try!!

. I maybe do another next week. I away go must now

Code:

[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Sub[/color] Abdul()
[color=blue]Dim[/color] ws1 [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws1 = ThisWorkbook.Worksheets("sheet1")
[color=blue]Dim[/color] ws2 [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws2 = ThisWorkbook.Worksheets("Sheet2VBA")
[color=blue]Dim[/color] r2 [color=blue]As[/color] [color=blue]Long[/color], r1 [color=blue]As[/color] [color=blue]Long[/color], rCnt [color=blue]As[/color] [color=blue]Long[/color], rU [color=blue]As[/color] [color=blue]Long[/color] [color=lightgreen]'variables for "rows" or "horizontal" count to be used in various loopings for row or first co ordinate in arrays'( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) -Most smaller numbers, Byte, Integer, Single are converted in computer to long so no advantage of Dim to smaller Type here)[/color]
[color=blue]Dim[/color] lr2 [color=blue]As[/color] Long: [color=blue]Let[/color] lr2 = ws2.Range("A" & Rows.Count).End(xlUp).Row [color=lightgreen]'Go to last row in  Column 1, come back up to last entry and get the row there..allows for differnt versions of Excel with different number of rows.[/color]
[color=blue]Dim[/color] lr1 [color=blue]As[/color] Long: [color=blue]Let[/color] lr1 = ws1.Cells.Find(What:="*", after:=ws1.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row [color=lightgreen]'Alternative Method. Get last Row with entry anywhere for Sheet2. Method: You start at first cell then go backwards (which effectively starts at end of sheet), sercching for anything ( = * ) by rows, then get the row number. This allows for different excel versions with different available Row numbers) Just a different method here for fun- finds last row in sheet rather than row for last entry in particular cell[/color]
 
[color=lightgreen]'Get Unique values from column A, Sheet1 using a Tempory column[/color]
[color=blue]Dim[/color] lshtc [color=blue]As[/color] Long: [color=blue]Let[/color] lshtc = ws1.Columns.Count [color=lightgreen]'Number of Columns in sheet (May be differtent for different Excel versions[/color]
[color=lightgreen]'Let lshtc = 7 'For debugging to see the array bring it in "view"[/color]
[color=blue]Let[/color] ws1.Cells(1, lshtc) = "Unique" [color=lightgreen]'The last Column inn the sheet is used. (This has an advantage of not  interfering with our Method for getting lc). hee just for fun we give the array, that is to say the tempory column, a heading[/color]
    [color=blue]For[/color] r1 = 2 [color=blue]To[/color] lr1 [color=blue]Step[/color] 1 [color=lightgreen]'Going down all rows  from just after heading in sheet1[/color]
    [color=blue]On[/color] [color=blue]Error[/color] [color=blue]Resume[/color] [color=blue]Next[/color] [color=lightgreen]'This error handler is for the predicted error if no match, so below line errors in which case we go on at the line just after the next[/color]
        [color=blue]If[/color] ws1.Cells(r1, 1) <> "" And Application.WorksheetFunction.Match(ws1.Cells(r1, 1), ws1.Columns(lshtc), 0) = -1234 [color=blue]Then[/color] [color=lightgreen]'provided something is there, we check to see if that value is already in our vLook Up Array by looking to see for a match. If it is not there then, the predicted error occurs.......[/color]
        ws1.Cells(ws1.Rows.Count, lshtc).End(xlUp).Offset(1).Value = ws1.Cells(r1, 1).Value [color=lightgreen]'.....so we come here and put value in our tempory column for the unique values[/color]
        [color=blue]Else[/color] [color=lightgreen]'Else do nothing, we come here if no error, but match number was not -1234 - so .Match gave some co ordinate indicating value was alrerady there[/color]
        [color=blue]End[/color] [color=blue]If[/color]
    [color=blue]Next[/color] r1
 
Dim arrUniques() [color=blue]As[/color] [color=blue]Variant[/color] [color=lightgreen]'We must leave this array as dynamic, and of type variant to allow the Object Range in the below "VBA allowed "One Liner" screen value to Array "Capture"#### assignmet in next line to work.[/color]
[color=blue]Let[/color] arrUniques() = ws1.Range(ws1.Cells(2, lshtc), ws1.Cells(ws1.Cells(ws1.Rows.Count, lshtc).End(xlUp).Row, lshtc)).Value [color=lightgreen]'Array for unique values returned from assigning dynamic array to the last column temporary range[/color]
 
[color=lightgreen]'End of making an Array----------------------------------------[/color]
 
[color=lightgreen]'Increase size of Unique Array for, and obtain info for entry occurance (CountIf stuff)-----[/color]
[color=blue]Re[color=blue]Dim[/color][/color] [color=blue]Preserve[/color] arrUniques(1 [color=blue]To[/color] [color=blue]UBound[/color](arrUniques(), 1), 1 [color=blue]To[/color] 3) [color=lightgreen]'Add extra columns, whilst not deleting the original values, to the unique array. These are for a count of its occurrance and a check if it has been selected later[/color]
    [color=blue]For[/color] rU = 1 [color=blue]To[/color] [color=blue]UBound[/color](arrUniques(), 1) [color=blue]Step[/color] 1 [color=lightgreen]'For each unique value, ...[/color]
    [color=blue]Let[/color] arrUniques(rU, 2) = Application.WorksheetFunction.CountIf(ws1.Range("A1:A" & lr1 & ""), arrUniques(rU, 1)) [color=lightgreen]'Count how many times the value is in colimn A of sheet 1[/color]
    [color=blue]Next[/color] rU
ws1.Columns(lshtc).Delete [color=lightgreen]'Delete the temporary Column as we are finished with it now. (Delete is usually better than Clear.. >>  http://www.mrexcel.com/forum/excel-questions/787428-clear-delete-shift-%3Dxlup-let-y-%3D-y-%96-1-usedrange-rows-count-anomale.html[/color]
[color=lightgreen]'End of bit to increase unique Array size and fill with any extra info----------------------[/color]
 
[color=lightgreen]'Main loopy looping, to check for match in Column A and produce output Array---------------[/color]
[color=blue]Dim[/color] arrIn() [color=blue]As[/color] Variant: [color=blue]Let[/color] arrIn = ws2.Range("A1").CurrentRegion.Value [color=lightgreen]'Dynamic Array assignment to range Valuse with "VBA allowed One Liner"####. Brings in Range connected to A1[/color]
[color=blue]Dim[/color] arrLkUp() [color=blue]As[/color] Variant: [color=blue]Let[/color] arrLkUp() = ws1.Range("A1").CurrentRegion.Value [color=lightgreen]'From Sheet 1 the entire table, effectivelly a look Up table[/color]
[color=blue]Dim[/color] arrLkUp1() [color=blue]As[/color] Variant: [color=blue]Let[/color] arrLkUp1 = Application.WorksheetFunction.Index(arrLkUp(), 0, 1) [color=lightgreen]'Returns format type (1,1) (2,1) (3,1) (etc) >> Index Function with second argument (row co - ordinate) set to 0 will return the entire column given by third argument ( column - co ordinate ), applied to the first argument which is the Array or grid, ( Array or Grid , Row_Number, Column_Number)[/color]
[color=blue]Dim[/color] arrResults() [color=blue]As[/color] [color=blue]String[/color] [color=lightgreen]'Array for output results. We are filling this loop in line by line, and know the type , so can define the type....also[/color]
Dim c [color=blue]As[/color] [color=blue]Long[/color], lc [color=blue]As[/color] Long: [color=blue]Let[/color] lc = 5 [color=lightgreen]'column variables for output results[/color]
[color=blue]ReDim[/color] arrResults(1 [color=blue]To[/color] lr1 - 1, 1 [color=blue]To[/color] lc) [color=lightgreen]'...we do not know it's excact row length, but we give it the maximum which would occur if all values in Look Up Table were unique. We use ReDim rather than Dim as Dim only takes actual numbers, not variables as ReDim does[/color]
Dim rR [color=blue]As[/color] [color=blue]Long[/color] [color=lightgreen]'Count for rows in results[/color]
 
    [color=blue]For[/color] rU = 1 [color=blue]To[/color] [color=blue]UBound[/color](arrUniques(), 1) [color=blue]Step[/color] 1 [color=lightgreen]'Loop 1.-For each unique value, ...[/color]
        [color=blue]For[/color] rCnt = 1 [color=blue]To[/color] arrUniques(rU, 2) [color=lightgreen]'Loop 2.-...for as many times as it could occur[/color]
           
            [color=blue]For[/color] r2 = 2 [color=blue]To[/color] [color=blue]UBound[/color](arrIn(), 1) [color=lightgreen]'Loop 3.-Look at the look up value..[/color]
                [color=blue]If[/color] arrUniques(rU, 1) = arrIn(r2, 1) [color=blue]Then[/color] [color=lightgreen]'I have the match condition 1, so see[/color]
                    [color=lightgreen]'MsgBox "Match 1 Unique and Look Up Value" & arrUniques(rU, 1) & " " & arrIn(r2, 1) ' Some stuff used inn debuging to check matches etc.[/color]
                    [color=blue]For[/color] r1 = 2 [color=blue]To[/color] [color=blue]UBound[/color](arrLkUp(), 1) [color=lightgreen]'.. go down the Look Up Table , sheet 1[/color]
                        [color=blue]If[/color] arrLkUp1(r1, 1) = arrIn(r2, 1) [color=blue]Then[/color] [color=lightgreen]'Original or next match found[/color]
                       
                            [color=lightgreen]'MsgBox "Match 1 Unique and  Look Up Value and LookUpTable" & arrLkUp(r1, 1) & " " & arrIn(r2, 1)[/color]
                            [color=blue]Let[/color] rR = rR + 1 [color=lightgreen]'Need to go to next line (the one aftzer last filled) inn the output array[/color]
                            [color=blue]For[/color] c = 1 [color=blue]To[/color] lc [color=lightgreen]'Match conditions met, so we put simple loop to put entire row info into output array (Unfortunately there appears to be no One Liner for this in Arrays as there is in a spreadsheet >>   http://www.mrexcel.com/forum/excel-questions/815460-visual-basic-applications-copy-multiple-array-columns-one-line.html[/color]
                            [color=blue]Let[/color] arrResults(rR, c) = arrLkUp(r1, c)
                            [color=blue]Next[/color] c
                        [color=blue]Let[/color] arrLkUp1(r1, 1) = "GotThisOne" [color=lightgreen]'This is a bit of a bodge to prevent the entry being found again..[/color]
                        [color=blue]Else[/color] [color=lightgreen]'No match found so no output results for this loop. Redundant code line[/color]
                        [color=blue]End[/color] [color=blue]If[/color]
                    [color=blue]Next[/color] r1
                [color=blue]Else[/color] [color=lightgreen]'No Match, no action. Redundant code line[/color]
                [color=blue]End[/color] [color=blue]If[/color]
 
            [color=blue]Next[/color] r2 [color=lightgreen]'Go to next Look up Value Row (If there is one)[/color]
          
        [color=blue]Next[/color] rCnt [color=lightgreen]'For more than one occurrance (CountIf woz > 1) go and repeat Loop1[/color]
    [color=blue]Next[/color] rU [color=lightgreen]'Main loop. It all started with a unique value, so go to the next value[/color]
[color=lightgreen]'End of main Loopy looping---------------------------------------------------------[/color]
 
[color=lightgreen]'Output results to spreadsheet----------------------------[/color]
[color=blue]Let[/color] ws2.Range("A2").Resize(UBound(arrResults, 1), UBound(arrResults, 2)).Value = arrResults() [color=lightgreen]'Simple VBA allowed "One-Liner" to output values from an Array to a Range. - Rezize cell in top left corner of where you want output to size of array(), and then make that range equal to that Array[/color]
[color=lightgreen]'---------------------------------------------------------[/color]
[color=blue]End[/color] [color=blue]Sub[/color]

And monochrome without ‘Green Description comments

Sub Abdula()
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("sheet1")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Worksheets("Sheet2VBA")
Dim r2 As Long, r1 As Long, rCnt As Long, rU As Long
Dim lr2 As Long: Let lr2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
Dim lr1 As Long: Let lr1 = ws1.Cells.Find(What:="*", after:=ws1.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 
Dim lshtc As Long: Let lshtc = ws1.Columns.Count
 
Let ws1.Cells(1, lshtc) = "Unique"
    For r1 = 2 To lr1 Step 1
    On Error Resume Next
        If ws1.Cells(r1, 1) <> "" And Application.WorksheetFunction.Match(ws1.Cells(r1, 1), ws1.Columns(lshtc), 0) = -1234 Then
        ws1.Cells(ws1.Rows.Count, lshtc).End(xlUp).Offset(1).Value = ws1.Cells(r1, 1).Value
        Else
        End If
    Next r1
 
Dim arrUniques() As Variant
Let arrUniques() = ws1.Range(ws1.Cells(2, lshtc), ws1.Cells(ws1.Cells(ws1.Rows.Count, lshtc).End(xlUp).Row, lshtc)).Value
 
ReDim Preserve arrUniques(1 To UBound(arrUniques(), 1), 1 To 3)
    For rU = 1 To UBound(arrUniques(), 1) Step 1
    Let arrUniques(rU, 2) = Application.WorksheetFunction.CountIf(ws1.Range("A1:A" & lr1 & ""), arrUniques(rU, 1))
    Next rU
ws1.Columns(lshtc).Delete
 
Dim arrIn() As Variant: Let arrIn = ws2.Range("A1").CurrentRegion.Value
Dim arrLkUp() As Variant: Let arrLkUp() = ws1.Range("A1").CurrentRegion.Value
Dim arrLkUp1() As Variant: Let arrLkUp1 = Application.WorksheetFunction.Index(arrLkUp(), 0, 1)
Dim arrResults() As String
Dim c As Long, lc As Long: Let lc = 5
ReDim arrResults(1 To lr1 - 1, 1 To lc)
Dim rR As Long
    For rU = 1 To UBound(arrUniques(), 1) Step 1
        For rCnt = 1 To arrUniques(rU, 2)
            For r2 = 2 To UBound(arrIn(), 1)
                If arrUniques(rU, 1) = arrIn(r2, 1) Then
                    For r1 = 2 To UBound(arrLkUp(), 1)
                        If arrLkUp1(r1, 1) = arrIn(r2, 1) Then
                            Let rR = rR + 1
                            For c = 1 To lc
                            Let arrResults(rR, c) = arrLkUp(r1, c)
                            Next c
                        Let arrLkUp1(r1, 1) = "GotThisOne"
                        Else
                        End If
                    Next r1
                Else
                End If
            Next r2
        Next rCnt
    Next rU
 
Let ws2.Range("A2").Resize(UBound(arrResults, 1), UBound(arrResults, 2)).Value = arrResults()
 
End Sub

Brief Code Description ( English )
. This is a “VBA Array” Type method so I “Capture spreadsheet” ranges into Arrays and try to minimize interaction with the spreadsheet” Various preliminary stages take care of this then I go into a main section of looping.. briefly as follows

. 1) I take each unique ORDER NO. /NAME value in turn, and repeat the following steps for as many times as it could occur
.- Loop 1 and loop 2
…………………….

. 2) With each of these unique values I look through the Look up Value to see if I have it
.- Loop 3
……………………..

. 3) If ( Only if ) I have it
.- Match condition 1 : a valid entry
……………………….

. 4) I go through an array for the first column in the Look Up Table
.. looking for that Look Up Value
.-Loop 4
……………………..

. 5) If (and only if ) I have it (I Note I would have it at least once, or it would not appear in the unique list)
.- Match condition 2 : Again this is a valid entry, either that from .3) or if there are multiply entries of the value, then this will be the next one along.
………………………..

. 6 ) At this point we have all match criteria satisfied, and take the appropriate action ( As I have written this using the “VBA Array “ type method, I place an entry in a fresh “Row” in the Array being used to store the output. ( And as a bit of a bodge as I was tired and could not think at this point of something more elegant, I replace the entry in the array for the first column of the LookUpTable with “Done”, so it does not get found again)