Read values from mergerd cells

[MEGHA_cs]

Hi, I have a problem where I need to process the values from Excel into one form.
its in Reframework.

Here, I need to read 1st Company and get all the Models corresponding to company and check the checboxes for each model of same company…
Bot should repeat the same for all the companies.
Please help to solve this
New Microsoft Excel Worksheet.xlsx (8.3 KB)

[Saikarthik_Gajwel]

@MEGHA_cs
Hi Megha!!

You can get the distinct values from column “Company” and then you can loop through each of them and filter.

Query to find Distinct values:

InputDT.AsEnumerable.Select(Function(r) r(“Company”).ToString.Trim ).Distinct.ToArray

[ppr (Peter)]

we would assume that after the read range the resulting datatable will have some empty company values

In such case we can prepare the data entry by filling it up

Tricky Process, requires some smart thinking. Check duplicates

filter out the '@5C.... rows and have a look on the FillUp_BlockBlanks.xaml When account 200004 is refering to the above rows: 2-13 from excel then we need to do some modification on the filling up. For this modification one of my colleagues @kumar.varun2 can help you

[MEGHA_cs]

Thanks, But I need to get all the models from one company…

[Saikarthik_Gajwel]

@MEGHA_cs
You can try filling the datatable with previous value by some data manipulation and can use that datatable for the further logic.

PreviousValue= InputDT.Rows(InputDT.Rows.IndexOf(CurrentRow) -1).Item(1).ToString

Once the required datatable is acheived, You can get the distinct values from column “Company” and then you can loop through each of them and filter.

Query to find Distinct values:

InputDT.AsEnumerable.Select(Function(r) r(“Company”).ToString.Trim ).Distinct.ToArray

[MEGHA_cs]

@Saikarthik_Gajwel

InputDT.AsEnumerable.Select(Function(r) r(“Company”).ToString.Trim ).Distinct.ToArray

Thank you… Am not getting

[Saikarthik_Gajwel]

@MEGHA_cs
Did you able to get the datatable with your expected result ?

What error you are getting while trying to find unique/distinct values?

[MEGHA_cs]

No, am not able to get

[MEGHA_cs]

@Anil_G , can you please help me on this

TIA

[Anil_G (Anil Gorthi)]

@MEGHA_cs

As the cells are merged ideally you would get data in the first row only and the remaining merged cells will be empty

Follow the steps

Have a variable created str_Comp and initialize it with String.Empty

1. Read the data into datatable dt
2. For each row in datatable on dt
   3 now use a if condition with Not currentRow("Company").ToString.Trim.Equals(String.Empty)
3. On the then side use assign with str_Comp = currentRow("Company").ToString.Trim…Also on the then side include logic for start of nee company as only when new company comes in this then block is executed
4. Leave else side empty
5. Now after if and inside the loop use str_Comp to give the compny value and currentRow("Model").ToString for mode values

Cheers

[MEGHA_cs]

Thanks, I found the solution…