How to find Common Values from Excel/DataTable

Help
1

Can you help me finding the solution like this -

image

2

you could try something like this.
excel application scope → read range-> output datatable
now, use a split on the output: output.Split(New Char() {","c}) which will result in an array of strings.
create a dictionary(of string, int)
using a for each on the array of string do the following:
if the object doesn’t exist then add to dictionary the object and the value of 0
else if the value already exists in the dictionary, increment by 1 the key of the value which matches the object.
finally, you will have a dictionary with all the names and all the number of occurences for each of them.
it might not be the most elegant solution, but it’s the first thing that came into my mind.

3

@PrankurJoshi

Let us assume your datatable as dta
First let us take a variable List A whose DataType is List (List< Of Strings >)

List B is the List of strings where it will contain the common values present in all the columns

ListA = (From p in dta.Columns.CastOf(System.Data.DataColumn)
             Select dta.AsEnumerable().Select(Function(x) x(p.ColumnName).ToString).ToList).ToList

List B= From p In ListA(0)
            where ( From q in ListA
                          where q.Contains(p)
                          Select q).ToList.Count=dta.Columns.Count
                Select p.ToString).ToList

Regards,
Mahesh

1 Like