Getting Multiple Vlookup Values & Hightlight Duplicate row

[Priyantha]

DearAll,

I am trying to enter employees bank loan interest recover detailes in excel sheet ("3-2 Reimbursment" Sheet). For this purpose i use two sheets, one "3-2 Reimbursment" , other "Loan Details". in Account Details sheet, there are many emloyees details and some amployees may have more Loan numbers. (My Excel version is 2016)

While this purpose, have two issues,

01). I tried to get multiple loan numbers belongs to the emploee (PF No.) in to dropdown list using vlookup function (in to "3-2 Reimbursment" Sheet colum "D" in to dropdown list) can do this?

02. I try to prevent duplicate entries. this purpos i captured Employee number (Colum A), Loan number (ColumnD) & Loan period (Colum G & H). if i want, Loan period can get one colume like this 2023/10/02 - 2024/02/05.

Specialy in this case, should seems as duplicate (red clour) same loan period or part of loan period. as example, first time i enter,
PF No. Loan Number loan period
36224 10292 2022/05/03 - 2022/12/06

Second time While enrering:
36224 10292 2022/05/03 - 2022/12/06 (Same Period - Should Duplicate)
or
36224 10292 2022/01/03 - 2022/12/06 (Part of period - Should Duplicate)
or
36224 10292 2022/05/03 - 2024/03/09 (Part of period - Should Duplicate)
or
36224 10292 2020/01/03 - 2024/02/10 (Part of period - Should Duplicate)


Hope your help.

Thanks,

Priyantha



,

[Toranaga]

Cross Post

Getting Multiple Vlookup Values & Hight ... licate row

[DocAElstein]

I think you missed the link

I think you missed the link.JPG

[Toranaga]

https://www.mrexcel.com/board/threads/h ... t.1258282/

[HansV]

@Priyantha: You haven't received replies in either forum. I suspect that nobody really understands your question.
Could you try to explain it more clearly?

[p45cal]

Also https://www.excelforum.com/excel-genera ... -list.html

[Priyantha]

Dear All,

Ok , Thanks I'll explanne, I have two work sheet, one "3-2 Reimbursment" , other "Loan Details". In "Loan Details sheets will be, Colum A _ Employee Number like 37224, 36224 ....... ect, Colum D _ Loan Numes like 10292, 231026 .... ect. Some employees may have multiple loan numbeers. (Eg. EMP No. 37224 have 3 Loan Numbers like 10292, 231026, 102922323)

In "3-2 Reimbursment" sheet will be in Colum A _ Employee Numbee like 37224, 36224 ....... ect, in colum B Employee Name, in colum D _ Loan numbers, in Colum G _ start date (like 2023/10/02), in colum H _ end date (like 2024/02/10) & in colum I _ Amount.

I try to get loan numbers concerned employee in to colum D as drop down list (using vlookup or any methord) from "Loan Details" sheet Colum D (Eg. in A2 _ employee number 37224, his all loan numbers _ 10292, 231026, 102922323 should display in D2 as dropdown list, next A3 _ 36224, his Loan numbers 108024, 22222222, should display in D3 as dropdown list ........... ect)

My other issue is to prevent duplicate entrys in "3-2 Reimbursment" . in This purpos i want to captured Employee number (Colum A), Loan number (Column D) & Loan period (Colum G & H). If i want, Loan period can get one colume like this 2023/10/02 - 2024/02/05.

Specialy in this case, should seems as duplicate (red clour) same loan period or part of loan period Colum G & H) with Employee number (Colum A), Loan number (Column D)

As example, first time I type in "3-2 Reimbursment" sheet, A2 _ EMP No. 37224 B2 _ Name, D2 _ Loan number 10292, G2 _ Start date , 2022/10/02, H2 _ End date, 2023/12/10, I2 _ Amount , 2500.00,
second time i type A3 _ EMP No. 37224 B3 _ Name, D3 _ Loan number 10292, G3 _ Start date , 2021/12/02, H3 _ End date, 2023/12/10, I3 _ Amount 15,000.00 this should seems as dulicate. Because the first scenario is covered by the duration of the second senario. this is in the fist instance, some payment has been made up to 2023/10/01 to 2023/12/10 and in the second instance, the payment should be only up to 2023/12/02 to 2022/10/01.

Please help to solve this issues.

Tanks,

Priyantha

[HansV]

See the attached workbook. The Loan Number column doesn't contain a formula anymore - you cannot have a drop-down list and a formula at the same time.

Bank Interest 3_2 Reimbursment .xlsx

[Priyantha]

See the attached workbook. The Loan Number column doesn't contain a formula anymore - you cannot have a drop-down list and a formula at the same time.


Bank Interest 3_2 Reimbursment .xlsx

Dear Hans,

It is working correctly, just i expected.Despite many searches for this, no answer was received. Thank You soo much, for helping me .

Br,
Priyantha