Match value from column and assign status then again do same for duplicates with column value having difference of earlier

Help
1

Two excel sheets
SAMPLE1.xlsx (9.6 KB)
SAMPLE2.xlsx (8.2 KB)

Sample1 contains 3 columns Number ,RQ and Status.
Sample2 contains 2 columns Number and AQ.

To fill the status column:
If RQ<=AQ, Status = “available” else Status =" less available"
Sample1 sheet contains duplicate value which also needs to be assigned the Status based on the remaining AQ value.
Eg: firstly 3 is assigned Status = “available” then if 3 comes again(duplicate value), it will check for if and else condition for RQ and AQ where the AQ will be net AQ i.e. AQ-RQ.

I really need some help in solving this problem .Thanks a lot in advance.

This is SAMPLE1 sheet
image
This is SAMPLE2 sheet
image

2

Hi @Soniya215

Try this

(From p in dt.Select() where( From q in dt.Select() where string.Join(“,”,q.ItemArray).Equals(string.Join(“,”,p.ItemArray)) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

Thanks
Ashwin S

3

@Soniya215
Let me summarize my understanding:

  • RQ<=AQ, Status = “available” else Status =" less available"
  • AQ needs to be reduced with RQ for handling the duplicates.
  • duplicates will check as above on new reduced AQ

Find demo XAML here:
Soniya215_StatusUpdate2.xaml (12.4 KB)

Let us know your feedback.

4

Thanks for your help @ppr but i am getting an error
image

saying Column “Number” does not belong to the data table.

5

@Soniya215
Check your Excel the cell has an Space after the Term number. Remove IT and Then IT should work

6

Thanks @ppr The file run but no output of “status” is shown in the sheet. It still remained blank.

7

@Soniya215
When i was developping the Code, i Had even to fix the Excel for the blanks in the column Header row. After that IT was working fine. Are you running my xaml or a Port to your xaml. If so, please Share your xaml with me.

Writing Back to Excel is Not implemented. Just do IT by your own with a write Range activity

8

I am running your xaml and i was using write range workbook instead of excel application scope.Mistake was on my side.
Sorry for my mistake.

Thanks a lot for your help @ppr :slightly_smiling_face:

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.