Reading from one Excel Sheet and write it into another Sheet

Help Activities
, ,
1

Good evening everyone,
first of all, thank you. Although I was not active on the forum, I read so many posts of people who posted solutions to different problems which I used to build steps for my automation.
Alas, I reached a line where I can’t think of an option for what I can do with the next step.
Can you please advise me on what I can do here?
I have an Excel Test.xlsx. In it there are 2 Sheets: TestList and TestTable. In Test.xlsx there are 2 Rows: RESET and ATM.
There are 4 types of reset that the ATM can have:
The first one "RESET NIEUDANY, because it only contains the words RESET NIEUDANY:
20:40:10* CDM - RESET NIEUDANY - BLAD URZADZENIA

The second one “INIT BNA RESET NIEUDANY”, because it contains the words “INIT BNA” and “RESET NIEUDANY”:
15:43:25URUCHOMIONO INIT BNA - KASETA RETRAKTOWA
15:43:25 CDM - RESET NIEUDANY - BLAD WYPLATY

The third one “RESET UDANY”, because it only contains the words RESET UDANY:
07:29:31* CDM - RESET UDANY

And the fourth one “INIT BNA RESET UDANY”, because it contains the words “INIT BNA” and “RESET UDANY”:
17:29:42URUCHOMIONO INIT BNA - KASETA RETRAKTOWA
17:29:42ROZPOCZETO RETRACT DO RETRACT BIN
17:30:15* CDM - RESET UDANY

I’m trying to figure out how I can read the data from TestTable and write it to Sheet TestTable how many resets happened for the ATMs.
In Sheet HowItShouldLook I wrote it manually how the output should look.

Any advice on how can I achieve that?
Thank you very much.

Test.xlsx (10.1 KB)

How to Transform Data from from one Excel Sheet into another
2

Hi,

Can you try the following sample?

dt.AsEnumerable.GroupBy(Function(r) r("ATM").ToString).Select(Function(g) dtResult.LoadDataRow({CObj(g.Key),
g.Count(Function(r) r("RESET").ToString.Contains("RESET NIEUDANY") AndAlso Not r("RESET").ToString.Contains("INIT BNA")),
g.Count(Function(r) r("RESET").ToString.Contains("RESET UDANY") AndAlso Not r("RESET").ToString.Contains("INIT BNA")),
g.Count(Function(r) r("RESET").ToString.Contains("RESET NIEUDANY") AndAlso r("RESET").ToString.Contains("INIT BNA")),
g.Count(Function(r) r("RESET").ToString.Contains("RESET UDANY") AndAlso r("RESET").ToString.Contains("INIT BNA")),
g.Count()},False)).CopyToDataTable()

Sample
Sample20240526-1a.zip (10.4 KB)
Note : Please check result sheet

Regards,

1 Like
3

OMG Yoichi,
thanks a ton, works like a charm. I added the whole list that I have, and it works with your solution.

I have some questions concerning the last steps left in my project, but I will open another topic for that :slightly_smiling_face:.

Thank you, thank you so much.
Huge Regards,

1 Like
4

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