Getting Multiple Vlookup Values & Hightlight Duplicate row

Priyantha
2StarLounger
Posts: 116
Joined: 10 Oct 2022, 02:52

Getting Multiple Vlookup Values & Hightlight Duplicate row

Post by 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



,
You do not have the required permissions to view the files attached to this post.

Toranaga
3StarLounger
Posts: 257
Joined: 15 Aug 2016, 11:23

Re: Getting Multiple Vlookup Values & Hightlight Duplicate row

Post by Toranaga »


User avatar
DocAElstein
5StarLounger
Posts: 683
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Getting Multiple Vlookup Values & Hightlight Duplicate row

Post by DocAElstein »

I think you missed the link
I think you missed the link.JPG
You do not have the required permissions to view the files attached to this post.
Arnold: How did I get in here?
Taxi driver: The door opened, you got in.

Toranaga
3StarLounger
Posts: 257
Joined: 15 Aug 2016, 11:23

Re: Getting Multiple Vlookup Values & Hightlight Duplicate row

Post by Toranaga »


User avatar
HansV
Administrator
Posts: 79560
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Getting Multiple Vlookup Values & Hightlight Duplicate row

Post by 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?
Best wishes,
Hans

User avatar
p45cal
2StarLounger
Posts: 169
Joined: 11 Jun 2012, 20:37

Re: Getting Multiple Vlookup Values & Hightlight Duplicate row

Post by p45cal »


Priyantha
2StarLounger
Posts: 116
Joined: 10 Oct 2022, 02:52

Re: Getting Multiple Vlookup Values & Hightlight Duplicate row

Post by 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

User avatar
HansV
Administrator
Posts: 79560
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Getting Multiple Vlookup Values & Hightlight Duplicate row

Post by 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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Priyantha
2StarLounger
Posts: 116
Joined: 10 Oct 2022, 02:52

Re: Getting Multiple Vlookup Values & Hightlight Duplicate row

Post by Priyantha »

HansV wrote:
03 May 2024, 09:39
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 :clapping: :clapping: :clapping: .

Br,
Priyantha