Searching values and arranging rows in Excel

[nokumutku]

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.

[nokumutku]

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.

[ppr (Peter Preuss)]

@nokumutku
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

[nokumutku]

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.

[ppr (Peter Preuss)]

have a look here on how to deal with all columns without explicit definition of compare rows:

[HowTo] - Find Duplicate / NonDuplicate rows - checking all columns - without explicit listing the colnames/index

Introduction In the majority of scenarios duplicated / Non duplicated rows can be identified by a few columns grace to the key column concept. In order to retrieve it, the group by approach helps It can be done e.g. with some nice components from Connect or LINQ In some scenarios all duplicated / non duplicated rows should be detected by checking all columns. Setting up an approach without explicit listing all column names or column index would speed up the implementation. Implementat…

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