Compare two lists through code

Class library usage, coding and language questions.
Posts: 33
Joined: Thu Jun 02, 2016 5:42 pm

Compare two lists through code

Post by npanag » Wed Nov 16, 2016 7:38 pm

Hi guys,
I have the following problem, and I hope to describe it in an understandable way. Basicaslly I don't know how to do it:

The heavy web app I try to test handles funds that have operations of various types. Each operation includes some data in a list, for example a list of investors, or a list of GL (general ledger).

I will be given some GL tables in Excel that correspond to a fund, on a specific date and of specific type. For example Fund1 has operation "Distribution" on a specific date and this includes let's say 5 detailed GL items. I must compare the GL from Excel that contains the 5 items and the GL from the web app with the 5 items. Could be 4 items, or whatever.

In order to increase difficulty, I don't have any primary key. Additionally, for a specific fund, on a specific day and a specific type, I met 2 records in the same day, so (fund name + date + operation type) is not unique.Of course the listed items of these 2 operations are totally different and the one list would be the correct GL in sync with the Excel GL.

One first idea is to put the web app GL data to a c# list control with field names, the Excel GL data to another list control, and somehow compare the two list controls for validation.

I suppose that I must start from the Excel file, put it to iterate in a test case and take from it initially the fund name. But here is the first problem. I select the specific fund, I click the command to show operations and I end up with 70 operations (70 operations with 20 columns) of the last years. This means that I must go to the code, recognize this list with the Xpaths, iterate then and locate the correct operation with the type and date from the variables of the iteration.

Reading all this table with XPaths can be time consuming. On the other side the application has a very efficient command Export to Excel that transfers the operations list to an Excel File. So, is this an idea, to transfer the list to Excel and then through the code search it to locate quickly the correct operation?

The next problem is the two GLs. I will end up with one or two records and I must find what is the correct operation going to the detailed GL and starting comparing field by field all the fields in the detailed data.
This means that I take the fund name through the iteration of the Excel file, but after that, I must get the detailed GL from Excel in a user module, isn't so?
I just saw in the forum that we can open Excel in a user module and handle a range. Any ideas much appreciated.

Other question: Is it preferable to work with c# data tables or ADO than with the c# list controls? Are the list controls in C# flexible enough in comparing concurrently multiple fields?

Where are all the commands that I can use with the Excel plugin?

OK, I know that I am expressed in a general way, but I don't know the exact possibilities I have loading Excel interop in the code. In next answers I can be more specific.


Posts: 254
Joined: Tue Mar 24, 2015 5:05 pm
Location: Des Moines, Iowa, USA

Re: Compare two lists through code

Post by Vaughan.Douglas » Thu Dec 01, 2016 4:06 pm

Ok, first of all, you need to clear up your design. What is it you're trying to do? You're comparing two lists, but what does that look like?
  • One way comparison: Make sure each item from list A appears in list B
  • Two way comparison: Make sure each item from list A appears in list B AND make sure all entries in list B appear in list A
There are all sorts of ways you can accomplish either one of these, but I prefer to use datatables in .net and use linq to compare them.

Based on what you're saying I think your best option would be to export the lists into Excel and then read the Excel files directly into datatables using ACE. I deal in VB more than C# so all of my examples are going to be in VB, but there are a lot of resources online that will help you out on setting up your connection string.

You can create a single Dataset object to contain both lists in individual datatables. Just make sure the lists are in separate datatable objects.

Then you do create a LINQ to pull out all of the orphans.
Dim myNotInListA = (From f As myStronglyTypeDataTableRow In Linq.Enumerable.AsEnumerable(myStronglyTypeDataTable).Distinct()
                 Select New With {Key  .DataPoint1 = f.DataPoint1, .DataPoint2 = f.DataPoint2, .DataPoint3 = f.DataPoint3}).Except(From p As myOtherStronglyTypeDataTableRow In Linq.Enumerable.AsEnumerable(myOtherStronglyTypeDataTable).Distinct()
                                                                                                                      Join f As myStronglyTypeDataTableRow In Linq.Enumerable.AsEnumerable(myStronglyTypeDataTable).Distinct() On p.DataPoint1 Equals f.DataPoint1
                                                                                                                      Select New With {Key  f.DataPoint1, f.DataPoint2, .DataPoint3 = f.DataPoint3})
This is from a utility I created to perform such tasks. I use strongly typed datasets for ease of use, but you don't need to.
Doug Vaughan