Extract Data From excel sheet that changes dynamically


#1

Hey guys,
I am trying to read a sheet in excel that has the following form :

\ | A | B | C | D
E | x |_ | x |_
F | _ | x |_ |x
G| x | _ | x |_

It basically matches elements to abilities. For example element A has the abilities E and G.
As you can see if a cell has the “x” value it means that an element has the ability, and if the cell is null it doesn’t. I want to read the sheet and create lists like {A-> E, G} etc, in order to use them later in my process. I cannot find how to traverse through the sheet. Like an outer loop for the elements, an inner loop for the abilities and if you find “x” add it to the list. Note that I do not have a fixed range in order to use “read range” from cell A to cell D, the number of elements and abilities changes dynamically.

Can anyone help?


#2

I had tough time imagining your excel lay out. But I assumed some thing and wrote the logic, hope it helps.

Excel Layout:

Logic:


#3

Thank you for your reply it really helps! One more thing, just to know if it is possible, can you do the above straight in the excel without using a data table?


#4

Problem.zip (8.5 KB)

Here is the solution the way you have asked for…
Plz be revert with the response


#5

Well done Mr. @sankar.kuna


#6

Thank you vvaidya…:slight_smile:


#7

User requested for A-EG B-EF and your ouput is other way around. Let see if he is satisfied with that.


#8

Thank you very much! Great job, I did not know you could convert chars from ASCII.