Requirement in excel Urgent

[linga]

Dear all

My data formats will be in sheet1 and sheet 2 as attached below

required output is to be like sheet3

Regards,

U.Linagnathan

 

[shrivallabha]

Please read forum rules:
http://chandoo.org/forum/threads/new-users-please-read.294/
(Rule No. #5 in Please Don't section)

 

[linga]

OK

 

[bosco_yip]

Hi,

Try...........

1] Helper1 Sheet1, C2 formula copy down :

=COUNTIF(Sheet2!A$2:A$7,A2)

2] Helper2 Sheet2, C2 formula copy down :

=COUNTIF(Sheet1!A$2:A$4,A2)

3] Result1 Sheet3, E2 formula copy down :

=LOOKUP(ROW(A1),SUMIF(OFFSET(Sheet2!$C$1,,,ROW($1:$7),),"<>")+1,Sheet2!A$2:A$10)&""

4] Result2 Sheet3, F2 formula copy down :

=LOOKUP(ROW(A2),SUMIF(OFFSET(Sheet1!C$1,,,ROW($1:$4),),"<>")+1,Sheet1!B$2:B$10)&""

5] Result3 Sheet3, G2 array formula (confirm enter with CTRL+SHIFT+ENTER) copy down :

=IFERROR(INDEX(Sheet2!$B$2:$B$7,SMALL(IF(Sheet2!$A$2:$A$7=E2,ROW(Sheet2!$A$2:$A$7)-ROW(Sheet2!$A$2)+1),COUNTIFS(E$2:E2,E2,F$2:F2,F2))),"")

Regards
Bosco