PROJECT MATERIAL Vs PENDING ITEMS

[Rajendar]

Hi,
Friends

Help me in formula. I trying to get all the information if we placed the item code in the column formula sheet C6:C7 i need all the information in one place

Please find the sample sheet i was attached.

Item code Vs = PO NUMBER , PO QTY IN DETAIL, SUPPLIER NAME

Or suggest me in a better way how to get all the information in one sheet

Thanks & Regards
Aelmareddy

 

[bosco_yip]

Try,

1] V6, copied down :

=IF($C6="","",INDEX('PENDING PO'!B$1:B$100,AGGREGATE(15,6,ROW('PENDING PO'!$A$1:$A$100)/('PENDING PO'!$A$1:$A$100=$C6),1))&IF(COUNTIF('PENDING PO'!$A$1:$A$100,$C6)>1,"/"&INDEX('PENDING PO'!B$1:B$100,AGGREGATE(15,6,ROW('PENDING PO'!$A$1:$A$100)/('PENDING PO'!$A$1:$A$100=$C6),2)),""))

2] W6, copied down :

=IF($C6="","",INDEX('PENDING PO'!V$1:V$100,AGGREGATE(15,6,ROW('PENDING PO'!$A$1:$A$100)/('PENDING PO'!$A$1:$A$100=$C6),1))&IF(COUNTIF('PENDING PO'!$A$1:$A$100,$C6)>1,"/"&INDEX('PENDING PO'!V$1:V$100,AGGREGATE(15,6,ROW('PENDING PO'!$A$1:$A$100)/('PENDING PO'!$A$1:$A$100=$C6),2)),""))

3] X6, copied down :

=IF($C6="","",INDEX('PENDING PO'!L$1:L$100,AGGREGATE(15,6,ROW('PENDING PO'!$A$1:$A$100)/('PENDING PO'!$A$1:$A$100=$C6),1))&IF(COUNTIF('PENDING PO'!$A$1:$A$100,$C6)>1,"/"&INDEX('PENDING PO'!L$1:L$100,AGGREGATE(15,6,ROW('PENDING PO'!$A$1:$A$100)/('PENDING PO'!$A$1:$A$100=$C6),2)),""))

Regards
Bosco

 

[Rajendar]

Dear Bosco,

I saw the attachment i was added the item code 15 times in a different PO numbers

But i am getting only 2 results only

Please see the attachment

Thanks & Regards

Aelamreddy

 

[bosco_yip]

Dear Bosco,

I saw the attachment i was added the item code 15 times in a different PO numbers

But i am getting only 2 results only

Please see the attachment

Thanks & Regards

Aelamreddy

If duplicate Lookup value more than 2, helper columns are required, and formula way as below :

1] "PENDING PO" sheet, helper col AA2, copied down :

=B2&IFERROR("/"&VLOOKUP($A2,$A3:$AC$100,27,),)

2] "PENDING PO" sheet, helper col AB2, copied down :

=V2&IFERROR("/"&VLOOKUP($A2,$A3:$AC$100,28,),)

3] "PENDING PO" sheet, helper col AC2, copied down :

=L2&IFERROR("/"&VLOOKUP($A2,$A3:$AC$100,29,),)

4] "FORMULA" sheet V6, copied across and down :

=IF($C6="","",VLOOKUP($C6,'PENDING PO'!$A$2:$AC$100,MATCH(V$5,'PENDING PO'!$A$1:$AC$1,0),0))

Regards
Bosco

 

[Rajendar]

Dear Bosco

Now i can see all the information in one cell. Its very useful for me

Thanks Lot

Best Regards
Reddy

 

[Rajendar]

Dear Bosco,

Hi,

Please see the attached report in the same way is possible i can see the data in report sheet ( in report sheet the data has to be auto fill )

 

[bosco_yip]

Dear Bosco,

Hi,

Please see the attached report in the same way is possible i can see the data in report sheet ( in report sheet the data has to be auto fill )

Duplicated-post

https://chandoo.org/forum/threads/auto-fill-the-data-in-report-sheet.38523/

p.s. the OP opened a new thread, so this thread was closed.

Regards
Bosco