Read values from mergerd cells

Help Activities
, ,
1

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

image

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)

2

@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

image
image1891×598 58 KB

3

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

grafik
grafik751×582 30.7 KB

4

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

5

@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

image
image1866×936 108 KB

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

6

Thank you… Am not getting

7

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

8

No, am not able to get

9

@Anil_G , can you please help me on this

TIA

10

@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

11

Thanks, I found the solution…

1 Like