Excel Extraction

[anjali2111993]

Hi ,
I am trying to extract values from excel which looks like this :

I want to get the values(x or o) for a specific alphabet (a,b,c… so on) based on the current date, for eg: if today it’s 04/19 I should get value “o” for C and D in the same way “x” for other alphabets.
Thanks,
Anjali

 

[VJR]

Hi Anjali,

It is unclear to me of the requirements and I have the below questions-
i. Is the 1st line the week start and end?
If yes should it have been 6/18 - 6/22 from Monday to Friday?
I also checked for 4/17 April month but even then it is not matching with the above screenshot and hence confusing to understand.

ii. You mentioned - "eg: if today it’s 04/19 I should get value “o” for C and D"
In your above example,
- Even though 19th date is passed, only values for Wed and Thu are returned.
Is it going to be the case always?

iii. In this example, you mentioned "o" should be returned for 4/19. This is only 1 data item.
Are there going to be cases where there is one "o" and one "x" for C? If so then multiple data items or even a collection needs to be thought of.

iv. What are you going to pass to the Excel sheet from Blue Prism?
Are you going to pass just any one alphabet out of A,B,C or D?

v. This requires a lot of research and analysis. In order to come up with a good architectural design do tell what all dates and weeks are included in the sheet? How does the entire sheet look like?

Please share the answers to the queries below each of the questions in order to get a proper understanding.

 

[anjali2111993]

It is unclear to me of the requirements and I have the below questions-
i. Is the 1st line the week start and end?
If yes should it have been 6/18 - 6/22 from Monday to Friday? -- Yes
I also checked for 4/17 April month but even then it is not matching with the above screenshot and hence confusing to understand.

ii. You mentioned - "eg: if today it’s 04/19 I should get value “o” for C and D"
In your above example,
- Even though 19th date is passed, only values for Wed and Thu are returned.
Is it going to be the case always? -- 6/18 6/19 6/20 6/21 6/22, so if today it's 6/19 I should be able to access only 2nd column values

iii. In this example, you mentioned "o" should be returned for 4/19. This is only 1 data item.
Are there going to be cases where there is one "o" and one "x" for C? If so then multiple data items or even a collection needs to be thought of.
--Always one value
iv. What are you going to pass to the Excel sheet from Blue Prism?
Are you going to pass just any one alphabet out of A,B,C or D? --No

v. This requires a lot of research and analysis. In order to come up with a good architectural design do tell what all dates and weeks are included in the sheet? How does the entire sheet look like? -- for every week data is there in same way

 

[VJR]

Hi anjali2111993,

This can simply be done by making use of the powerful excel formulas since your data is already in Excel.

View attachment 1529553786448.png

Above is a manual way of finding the Result. Notice the Formula in the formula bar at the top. You can even expand the range of the formula if you have more weeks.
The same Result cell can be achieved by setting the formula and the values for D11 and D12 from Blue Prism. Also the Alphabet and the Date can come from a Date Item.

I have also added a condition in the formula in case it does not get a match.

View attachment 1529553813438.png


Once you get the result in (D13) you can read that cell and use it in Blue Prism.

- You can either clear all these 3 cells
- OR close the workbook without saving
- OR open the Workbook as Readonly and insert the formula.
..so that these 3 cells won't get saved each time. You can save it if you want.

No code stage needed for any of these.

If you choose to go by this approach then let me know if you have any questions and would like to explore it further.