In Excel, Parse Hexadecimal Numbers And Make Queries

Problem description & analysis:

In the following table, value of cell A1 is made up of names of several people and their attendances in four days. For example, c is 1100 expressed in hexadecimal notation, meaning the corresponding person has attendance in the 1^st day and the 2^nd day and is absent in the 3^rd day and the 4^th day.

We need to find the number of people who has the attendance in the day input in A2. For example, three people are present in the 1^st day and two people are present in the 3^rd day.

Solution:

Use SPL XLL to type in the following formula:

=spl("=theDay=shift(1,?2-4),?1.split@c().step(2,2).count(and(bits@h(~),theDay)>0)",A1,A2)

As shown in the picture below:

Explanation:

shift()function performs a shift operation on an integer. step(2,2) gets members at the even positions. bits@h parses a hexadecimal number. The and operator represents and "AND" operation with bits() function, and ~ represents the current member.

esProc Desktop is now FREE to download and try this solution by yourself: http://www.scudata.com/download-Desktop