Lookup, find duplicates and generate next available number

Help Activities
, ,
1

image
image1918×579 60.1 KB

LEFT SIDE - INPUT FILE
RIGHT SIDE - MASTER FILE

I have attached input file, master file, script, project file.

Files.zip (66.1 KB)

The yellow highlighted are the expected results. I have entered it and highlighted just for your understanding.

Division types :

  1. BSD. The format of the Customer Code is 5 digits suffixed with “BS”, which looks like 97710BS.

  2. CED. The format of the Customer Code is just 5 digits, which looks like 97710.

Customer Type :

  1. SA. The code looks like SA194BS. When the “Country” is “South Africa”, it’s SA type.

Conditions :

  1. Look for matches and add remark as “Duplicate Of Customer”.

First I compare the columns “Customer Name”(Input File) with “Name 1”(Master File) and “Division” (Input File) with “Division” (Master File).

If a match is found, then remark “Duplicate Of Customer” should be added in the Input file.

As you can see, “Nesto Supermarket” & “BSD” in both the file matches. So I have to added remark in the Input file as “Duplicate Of Customer” in the input file.

  1. If “Customer Name”(Input File) with “Name 1”(Master File) and “Division” (Input File) with “Division” (Master File) didn’t match, just check if only the “Customer Name”(Input File) matches with “Name 1”(Master File).

As you can see the 1st row, “Customer Name”(Input File) matches with “Name 1”(Master File). The “Division” in the master file is “CED”. So the “Customer” is 5 digit numbers, which is “97711”. The “Division” in the input file is “BSD”. So I want the “Customer”(Master File) suffixed with “BS”, which is “97711BS” to be written in the input file.

As you can see the 2nd row, “Customer Name”(Input File) matches with “Name 1”(Master File). The “Division” in the master file is “BSD”. So the “Customer” is 5 digit numbers suffixed with “BS”, which is “97712BS”. The “Division” in the input file is “CED”. So I want the “Customer”(Master File) without the suffix “BS”, which is “97712” to be written in the input file.

For 3rd row, I have to give a “Customer Code”(Input File). I check if the “Customer Name”(Input File) which is “GP Rental Cars” present in the “Customer”(Master File). It is not present and the “Division”(Input File) is “CED”. I have to find the last used number in the series under “CED” division. Since “97713” and “97714” are already used in the master file. I can’t take “97715” because “97715BS” is used already for “Nesto Supermarket”(Master File) under “BSD” division and “97715” will be reserved for future usage of “Nesto Supermarket” under “CED” division. The next available number is “97716”. Since the “Division” of “GP Rental Cars” is “CED”, I will use it. If the “Division” of “GP Rental Cars” is “BSD”, I will have to use “97716BS”.

  1. When the “Country”(Input File) is “South Africa”, it’s SA type. I have to find last used “Customer”(Master File) where the “Country”(Master File) is “South Africa” and pick the next available number.

As you can see the last used “Customer”(Master File) is “SA194BS”. So I have to write “SA195BS” in the “Customer Code”(Input File).

I have achieved few conditions. But failed generating customer code for customer name which doesn’t exist in the master file. Instead of “97716”, “97715” is getting written in my input file.

image
image1918×562 59.8 KB

Since 97715BS has been already used for “Nesto Supermarket” under BSD division, 97715 should be reserved for “Nesto Supermarket” under CED division.

I want somebody help me fix this.

Thanks,
Uio

2

Ok, what exactly is the help needed here?

3

oops, I missed few things. I have updated my requirements clearly.