Excel linq queries and flow required for complex data manipulation in Excel

Help Studio
, , , , , , , ,
1

Hi all,

I am junior developer, and learning a lot from community.
I have done data table manipulations to bring data in correct format from raw
however , further to that I am stuck as it is not getting possible with simple activities.
also data is huge. may i pls seek help on this small task?
this is very urgent for me

input n output sample added in same sheet, but op should be in separate sheet
Data.xlsx (12.4 KB)

2

Hi, @Shruthika_r .

Watch these videos. They can help you in your learning.

1 Like
3

@Shruthika_r , are you sure your sample output matches up with the sample input? For example lines 19, 23 and 24 don’t have TK: at the start…

1 Like
4

Thanks @sudster for reply!

Yes ideally there should be TK but those are exceptional cases…
In such case whatever below name we should take and mention in output.

U can also observe sometimes there are 2 TK, sometimes 1 TK, sometimes no TK (no tk means they belong to same family)

And sometimes the case you asking TK and some misc information …

We can separate. Name from.other name by checking type , location code …Because only line with the name have type location code… other line is tk which we need to club in other column with comma.

Also… name is ended with mr, mrs,miss but sometimes name will be without these word so cant rely on that criteria …So type location code can be checked.

Can we also keep this list, array dybamic.
Mr mrs miss dr proff… because this list is yet to get … so.

TIA!

5

@Shruthika_r , here is my solution. Can you confirm the area marked in red is ok? It is different to your output.

image
image1067×767 17.7 KB

Also I can see 2 guests have the same code 098G???

1 Like
6

hi @sudster

thanks a lot !

Your output is correct, my input file should have TRI in last 3 rows not MUM , however it will work if Input replaced by TRI. same code 098G is also a mistake

7

@Shruthika_r , thanks for confirming. Would you like the xaml file or are you ok to try and reproduce the process using screenshots of the workflow?

8

@sudster , .xaml will be helpful as at this level it will be complex for me
I anyway have to replicate it

I will go through each step in your xaml and will ask if I am not understanding anything .
pls add screenshot too, if file face dependency issue, or missing activity errors

thanks!

9

@Shruthika_r , here’s the xaml file. Please note that I haven’t reworked the process for optimisation etc; it could be possible to fine tune it to perform better.

Sequence.xaml (51.1 KB)

Screenshot for ref (click to open the image, then click again to enlarge it!):

image
image1249×8020 373 KB

1 Like
10

@sudster
Hats off !

I will replicate and will inform you here :slight_smile:

12

@sudster , solution provided by you is working. Gone through the logic applied , understood as well !

Thank you !

Hi @sudster , need manipulation in excel . You had already provided 1 sol. so it will be easy to get.

Is it possible for u to look into? Required Linq for complex excel, which has merged col also

13

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