I have two columns with values (double-type). Only one column per row has a value. I need to check if there are pairs and arrange the rows with their pairs below each others. If there are more than two rows with the same value those rows need to be separated below the pairs-rows. Also there are rows that don’t have a pair and must be separated below the multiple-rows.
I forgot to mention that the pairs must be made with one “First” and one “Second” -column values. For example, if we have two 1,01 values in “First” column and no 1,01 values in the “Second” column, it is not a pair, instead it belongs to the NO PAIRS section.
in general it can be done with Join Data Table.
readin in first column only into a datatable
readin in second column only into a datatable
do a full join
pairs are identifiable when both join cols are not empty
multirows can be detected on its occurence
no pairs can be found when first / or second join column is empty
Thanks for your answer! Unfortunately in the real data I have 10 more columns, with values in each row, and those columns need to be included in the output. I guess I could tag the rows somehow and use your method, but right now I’m looking for a more simple way. I have gotten pretty far by creating a new column (lets call it “NEW”), which has the “not empty” value from either of the columns (“First”, “Second”). Then with For Each Row -activity and IF-loop with a condition: dt.Select(“NEW= '”+row.Item(“NEW”).ToString+"’").Count = 2 gives me the pairs. But the problem with this is that if there is two same values in the “First” column and no values of the same in the “Second” column, it will make them a pair. This has been my main problem.
have a look here on how to deal with all columns without explicit definition of compare rows:
sometimes GroupBy can be helpfully and in a merged datatble the different datatable sources can be marked in an extra column
However I would suggest to work out a sample data set more representing the complexity of your case. Then the options for the solution approach can be cross checked and evaluated