DEV Community

Cover image for In Excel, Parse Hexadecimal Numbers And Make Queries
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

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 1st day and the 2nd day and is absent in the 3rd day and the 4th day.

original table

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 1st day and two people are present in the 3rd 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)

Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

result table with code entered
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.

Top comments (1)

Collapse
 
judith677 profile image
Judith-Excel-Sharing

esProc Desktop is now FREE to download and try the above solution by yourself: scudata.com/download-Desktop

💫Plus there's some information you may be intertested in:
Plugin Installation Method: c.scudata.com/article/1652061135502
References to other rich Excel operation cases:c.raqsoft.com/article/1651916536524
SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...