How to group an excel data based on the name column and send each group data over email

Please find my use case below.

  1. I need to read an excel with metadata as below
    Capture1
Name Subject Professor Marks Internship Graduation Year Email ID
robin Maths Mark 87 No 2021 robinson@gmail.com
robin science Jose 98 No 2021 robinson@gmail.com
robin Social Steve 94 No 2021 robinson@gmail.com
matt Maths Mark 99 Yes 2021 yenigandlavineeth@gmail.com
matt science Jose 93 Yes 2021 matt@gmail.com
matt Social Steve 84 Yes 2021 matt@gmail.com
matt telugu sai 82 Yes 2021 matt@gmail.com
matt history Jean 78 Yes 2021 matt@gmail.com
shane Social Steve 78 No 2021 Shane@gmail.com
shane science Jose 93 No 2021 Shane@gmail.com
shane telugu sai 68 No 2021 Shane@gmail.com
shane history Jean 89 No 2021 Shane@gmail.com

2.I want to get each person details from the sheet and send an email. For instance, If I need to send details of robin in excel, the data would be
image

  1. Can any one please help me in the approach for the solution

Two ways:

The simplest way to do this without using anything fancy (such as extensive LINQ operations) may work this way:

  1. Load the list into a DataTable
  2. Get a list of distinct names from the email column - you could use Get Row Item to get all values of this column and then remove the duplicates .
  3. For each name on this list, use the Lookup Data Table to filter records on the email column and fetch the records into another DataTable - this would be the email, name, subject and marks of a particular person
  4. Use that DataTable as input to your email activity.

As I said, you could do this most likely in one or two steps using things such as LINQ, but that depends on how you want to approach the solution.

@himadeep.movva1
Have a Look Here

First Part ist about grouping data

@himadeep.movva1 - As an alternate…

  1. Read Range - Save the output as Dt

  2. For each ‘row’ in 'Dt.Defaultview.totable (True,“Name”)

  3. Inside For Each - Assign TmpDt = (From r In dt.select()
    where r(“Name”).tostring.equals(“Name”))
    Select r).copytodatable

  4. Inside For each use write Range → Give your output file → for sheet name use as row(“Name”).tostring.trim.Upper → tmpDt

This will write all the students names in separate sheet in your output file…

You can read this output file where each student name is on each sheet, based on your criteria you can send email.

Hope this helps…

Hi,
Quite simpe solution for you:

Directory:
image

Sample Workflow:
Main.xaml (13,9 KB)

3 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.