I have 2 datatables, say DT1 and DT2, I want to compare some columns of DT1 to DT2 and if the values match I want to update a new column “DATE” in DT1 with the value from a single column “due date” obtained from DT2 of the matching data set.
For example, columns to be compared in both DT are “Policy name, host name, server name, change” etc. if these columns match in both the DTs i want to pick up the respective " due date" value(present in the same matching row) from DT2 and update it in the “DATE” column of DT1.
For the non matching ones “N/A” is updated.
I have attached a sample excel containing 3 sheets DT1, DT2 and result.
The cells highlighted in green are matching thus the respective value is captured from DT2 and updated in DT1 new column “DATE”.
Yellow ones are the column which are only used for comparison Sample.xlsx (12.3 KB)
Required solution urgently. Any help is appreciated!!!
The date should be 02-02-2023 and not 05-01-2023 for the highlighted rows.
If it is what you have provided, then you are considering all the columns while matching.
Or you want the matching to be done on few columns, but while picking the date, all the columns should be matched and the date should be picked from the matched row.
When we match on few columns, there can be more than one rows that are matched, what would be the logic to pick the date date then.
I will check on the above point you raised about the data and revert with the correct file If any mistake is there.
Yes I want the matching to be done on the few columns only as I mentioned, The date to be picked is of that respective row item for example if 2 items match from 2 Datatables (based on few columns) then that row will also have a due date column that column value should be picked.
Yes, this can happen in this can we not just pick up the date mentioned in due date column of that respective matching row items?
Below is DT1 data and the highlighted part is the matching one
Below is DT2 data and the highlighted part is the matching one
Highlighted parts are the matching one and Cells D2 and D7 are not matching having opposite values(columns in yellow are considered for comparison)
Below is the Result data set highlighted with red are the matching ones and in the "DATE’ column values are added
Now from both the DT1 and DT2 4 rows appear to be matching based on column conditional matching and thus the due date from DT2 data is added in “DATE” column of DT1 and Result is prepared , where there is no matching “N/A” is updated in “DATE” column.
Thanks for this , I will try and update you further.
Currently as per business requirement I have to match rows based on columns I mentioned and then pick that respective row date if match occurs, matching can be one to many as we saw.
By this logic can we not get the expected output like get not getting the first date occured instead getting the date which is in that row.
Is anything missing here??
Now, as I mentioned earlier also, when the matching rows (matching is done on few columns) are more than one, in that scenario the logic to pick the date should be clear.
How to locate that particular row?
For this all column (apart from highlighted the other columns also) should be considered? Because then only we can have “that row”
in the example above, for the first of row of DT1, we have 4 matches in DT2 based on the highlighted columns but when we go for the DATE and if we take all the columns for the DATE column, then we cannot find “that row” in DT2.