Need solution for the below scenario...

[Sheshank Pydikondala]

Hello Team,

I have been a regular visitor of this site and you won't believe I am being called by my team for any issues in excel. You are the ones who made me get that... Thank you people for the support...

Now this is my first post in all these years. Cause I am stuck up with a problem in excel and unable to get the exact solution for that. I hope I get the solution here.

The scenario goes like this...

I have two excel files. One which contains more that 10 columns with field headers and the other one which contains multiple fields...

Ex: 1st sheet contains



1st sheet sample data:

Risk Ref IdOrgn IdBene IdCounter IdEntity IdRegionCurrencyAmountMsgs RcvdTRNX REF
INDJAN1700001AKKBKKCHPHMMTHHPMMASDFGOINDINR1451
INDJAN1700002DKKEKKFHHBHMTHHPMMAKJSHIINDINR45871
INDJAN1700003GKKFKKIGHOPHTHHPMMBGHDFRINDINR2511
INDJAN1700004JKKLKKMHPHMMTHHPMMJHUYIOINDINR1112
INDJAN1700005NKKOKKPMNBHUTHHPMMKLOIPHINDINR623
INDJAN1700006QKKRKKSADSRTTHHPMMSEDRFTINDINR3544
INDJAN1700007TKKUKKVSERFGTHHPMMCFVGHBINDINR9875
INDJAN1700008WKKXKKYHPHMMTHHPMMBHYTGFINDINR4582
INDJAN1700009ZKKAKKBOPKLHTHHPMMZAWSEDINDINR1471
INDJAN1700010CKKDKKEBNMHUTHHPMMCDERFUINDINR9991




2nd Sheet contains




2nd sheet sample data:

Orig OrgOrgn IdBene IdCounter IdEntity IdRegionCurrencyAmountTrnx Ref
???????12345HPHMMTHHPMMASDFGOINDINR100 to 500INDTRNX0001
999999923456HHBHMTHHPMMAKJSHIINDINR1K to 5KINDTRNX0002
AAAAAAAGKKFKKIGHOPHTHHPMMBGHDFRINDINR1 to 500INDTRNX0003
BBBBBBBBJKKLKKMHPHMMTHHPMMJHUYIOINDINR1 to 500INDTRNX0004
CCCCCCCCNKKOKKPMNBHUTHHPMMKLOIPHINDINR1 to 100INDTRNX0005
DDDDDDD78901ADSRTTHHPMMSEDRFTINDINR10 to 500INDTRNX0006
EEEEEEE34567SERFGTHHPMMCFVGHBINDINR500 to 2KINDTRNX0007
FFFFFFFWKKXKKYHPHMMTHHPMMBHYTGFINDINR300 to 500INDTRNX0008
GGGGGGGZKKAKKBOPKLHTHHPMMZAWSEDINDINR1 to 300INDTRNX0009
HHHHHHHCKKDKKEBNMHUTHHPMMCDERFUINDINR500 to 2KINDTRNX0010
???????89765HPHMMTHHPMMASDFGOINDINR1 to 300INDTRNX0011
9999999DKKEKKFHHBHMTHHPMMAKJSHIINDINR2500 to 10KINDTRNX0012
AAAAAAAGKKFKKIGHOPHTHHPMMBGHDFRINDINR100 to 300INDTRNX0013
BBBBBBBBJKKLKKMHPHMMTHHPMMJHUYIOINDINR100 to 300INDTRNX0014
CCCCCCCCNKKOKKPMNBHUTHHPMMKLOIPHINDINR10 to 100INDTRNX0015
DDDDDDDQKKRKKSADSRTTHHPMMSEDRFTINDINR100 to 500INDTRNX0016
EEEEEEETKKUKKVSERFGTHHPMMCFVGHBINDINR500 to 2KINDTRNX0017
FFFFFFFWKKXKKYHPHMMTHHPMMBHYTGFINDINR100 to 500INDTRNX0018
GGGGGGGZKKAKKBOPKLHTHHPMMZAWSEDINDINR100 to 300INDTRNX0019
HHHHHHH736938BNMHUTHHPMMCDERFUINDINR500 to 1KINDTRNX0020
???????AKKBKKCHPHMMTHHPMMASDFGOINDINR1 to 200INDTRNX0021
9999999DKKEKKFHHBHMTHHPMMAKJSHIINDINR1K to 5KINDTRNX0022
AAAAAAAGKKFKKIGHOPHTHHPMMBGHDFRINDINR1 to 300INDTRNX0023
BBBBBBBBJKKLKKMHPHMMTHHPMMJHUYIOINDINR1 to 300INDTRNX0024
CCCCCCCCNKKOKKPMNBHUTHHPMMKLOIPHINDINR1 to 300INDTRNX0025
DDDDDDDQKKRKKSADSRTTHHPMMSEDRFTINDINR1 to 300INDTRNX0026
EEEEEEETKKUKKVSERFGTHHPMMCFVGHBINDINR500 to 2KINDTRNX0027
FFFFFFFWKKXKKYHPHMMTHHPMMBHYTGFINDINR250 to 700INDTRNX0028
GGGGGGGZKKAKKBOPKLHTHHPMMZAWSEDINDINR1 to 500INDTRNX0029
HHHHHHHCKKDKKEBNMHUTHHPMMCDERFUINDINR100 to 2KINDTRNX0030
AAAAAAA34567SERFGTHHPMMCFVGHBINDINR500 to 2KINDTRNX0031
BBBBBBBB34567SERFGTHHPMMCFVGHBINDINR500 to 2KINDTRNX0032
CCCCCCCC34567SERFGTHHPMMCFVGHBINDINR500 to 2KINDTRNX0033
DDDDDDD34567SERFGTHHPMMCFVGHBINDINR500 to 2KINDTRNX0034
EEEEEEEQKKRKKSADSRTTHHPMMSEDRFTINDINR100 to 500INDTRNX0035
AAAAAAAQKKRKKSADSRTTHHPMMSEDRFTINDINR100 to 500INDTRNX0036
BBBBBBBBQKKRKKSADSRTTHHPMMSEDRFTINDINR100 to 500INDTRNX0037
CCCCCCCCQKKRKKSADSRTTHHPMMSEDRFTINDINR100 to 500INDTRNX0038
DDDDDDDTKKUKKVSERFGTHHPMMCFVGHBINDINR500 to 2KINDTRNX0039
EEEEEEETKKUKKVSERFGTHHPMMCFVGHBINDINR500 to 2KINDTRNX0040
FFFFFFFTKKUKKVSERFGTHHPMMCFVGHBINDINR500 to 2KINDTRNX0041
GGGGGGGTKKUKKVSERFGTHHPMMCFVGHBINDINR500 to 2KINDTRNX0042
HHHHHHH78901ADSRTTHHPMMSEDRFTINDINR10 to 500INDTRNX0043
???????78901ADSRTTHHPMMSEDRFTINDINR10 to 500INDTRNX0044
999999978901ADSRTTHHPMMSEDRFTINDINR10 to 500INDTRNX0045
AAAAAAA78901ADSRTTHHPMMSEDRFTINDINR10 to 500INDTRNX0046
???????NKKOKKPMNBHUTHHPMMKLOIPHINDINR1 to 100INDTRNX0047
9999999NKKOKKPMNBHUTHHPMMKLOIPHINDINR1 to 100INDTRNX0048
???????NKKOKKPMNBHUTHHPMMKLOIPHINDINR1 to 100INDTRNX0049
9999999JKKLKKMHPHMMTHHPMMJHUYIOINDINR1 to 500INDTRNX0050




My requirement is:

The TRNX REF column (in Sheet1) should be filled with the Trnx Ref details (from Sheet2)

The Risk Ref Id which is in Sheet 1 is not present in Sheet2 (is it was there.. I would have VLOOKUP'ed easily...)

The columns in Sheet1 (Orgn Id, Bene Id, Counter Id and Entity Id) are present in Sheet2 and has multiple rows of the same data.

The values of 'Msgs Rcvd' column in Sheet 1.. for ex: 3... suggests that there are 3 Trnx Refs for that combination of Orgn Id, Bene Id, Counter Id and Entity Id.

So, what I need in the Sheet 1! TRNX REF column is the number of Ids for the corrresponding combination of Orgn Id, Bene Id, Counter Id and Entity Id.

Multiple column values need to be validated to get the Trnx Ref numbers into Sheet 1 from Sheet2.

Pls suggest any solution.

I have tried to Match the columns from both the sheets but didn't get any... May be I was not doing in the exact way. I would require help from you guys. The sample data is also given for your reference.

Waiting for a reply very soon. Thanks in advance.. Have a great day...


(Formulas would be better... VBA Code is also ok... but need formulas on how to do this... Thanks...)

 

[bosco_yip]

Try,

In Sheet1 J2, formula copy down :

=IFERROR(LOOKUP(2,1/((Sheet2!B$2:B$51=B2)*(Sheet2!C$2:C$51=C2)*(Sheet2!D$2:D$51=D2)*(Sheet2!E$2:E$51=E2)),Sheet2!I$2:I$51),"")

Regards
Bosco

 

[Sheshank Pydikondala]

Thank you so much Bosco for your formula. It indeed works...

There is another point which may be I missed.

There is the number of Msgs Rcvd... wherein I need to get all the Trnx Ref numbers based on the count of Msgs Rcvd.

Ex... if the Msgs Rcvd is '3'.. then the Trnx Ref ids should be displayed with 3 different reference numbers... ex..
INDTRNX0049,
INDTRNX0050,
INDTRNX0049

if the Msgs Rcvd is '5'.. then the Trnx Ref ids should be displayed with 5 different reference numbers... ex..
INDTRNX0021
INDTRNX0022
INDTRNX0023
INDTRNX0024
INDTRNX0025

Kindly assist in this please...

 

[bosco_yip]

Thank you so much Bosco for your formula. It indeed works...

There is another point which may be I missed.

There is the number of Msgs Rcvd... wherein I need to get all the Trnx Ref numbers based on the count of Msgs Rcvd.

Ex... if the Msgs Rcvd is '3'.. then the Trnx Ref ids should be displayed with 3 different reference numbers... ex..
INDTRNX0049,
INDTRNX0050,
INDTRNX0049

if the Msgs Rcvd is '5'.. then the Trnx Ref ids should be displayed with 5 different reference numbers... ex..
INDTRNX0021
INDTRNX0022
INDTRNX0023
INDTRNX0024
INDTRNX0025

Kindly assist in this please...

Revised formula,

In Sheet1 J2, copy across and down :

=IF(COLUMNS($A:A)<=$I2,IFERROR("INDTRNX"&TEXT(RIGHT(LOOKUP(2,1/((Sheet2!$B$2:$B$51=$B2)*(Sheet2!$C$2:$C$51=$C2)*(Sheet2!$D$2:$D$51=$D2)*(Sheet2!$E$2:$E$51=$E2)),Sheet2!$I$2:$I$51),4)+COLUMNS($A:A)-1,"0000"),""),"")

Regards
Bosco