Excel UI With Java Flexibility: Merging Spreadsheet Data in Keikai

Introduction

There is a common end-goal to most sheet of formatted data. We want to analyze, compare, and use the full dataset that we have gathered.

If the data have been gathered in separate sheet and tables, sometimes with duplicated entries, we need to be able to merge all of it into a simple easy-to-use table.

This is where things usually get complicated. In the end, some will usually end-up copying and pasting rows of data from the individual sheets to the summary table.

Real-World Example

Take budgeting for example. Even if your organization only has four departments, you will end up with four different budget sheets. Some of them will have duplicated expenses between departments. For example, wages and salaries will be an item for any organizational units. Some of them will be unique for a specific team, such advertising being exclusive to the marketing department. And then some will be shared by only a few – but not all – of the teams, or even duplicated inside of a team’s sheet.

On top of that, each sheet must be filled by a different team manager, which usually ends up with multiple copies of the same file being passed around through emails.

This all culminates with the last person in the chain receiving five versions of the same file, some containing old data, some incomplete and one of them inexplicably blank. It takes a lot of effort and a significant amount of time to take this wildly divergent dataset and make it exploitable.

Requirements of the Summary Sheet

We want to improve it in two ways. First, we need to make sharing and collaborating much easier. The data-entering process should be streamlined, have single point of access, and be accessible to any designated users.

We also need to improve on the sheet workflow itself. Excel formulas can be powerful in many situations, but they also have their limits. We already have our formulas set up in this document to calculate sums, ratios, averages, etc. inside each department sheet. These, we will absolutely keep. This is where Excel’s formulas work at their best, and we will rely on Keikai to simply use what we already have.

On the other hand, Excel formulas are not ideal to perform dataset operations. Searching, merging and filtering are possible, but they take significant time to set up and execute. They are also fragile and often rely on static sheet layouts in order to be maintained. Keikai operates in a Java web server, so we will improve our workflow by delegating the data manipulation to this layer.

In this case, we need to generate two merged summaries of the data. One merged per period, and the other merged per department.

The “per period” table will need to merge every entry using the same label and sum their values depending on each of the yearly periods (Q1 to Q4).

The “per department” table will need to merge every entry using the same label and sum their values depending on the department declaring this expense. If we were to create these in Excel, we would either need complicated formulas checking and matching results from each of the source tables, or we would need to use custom merging queries.

Limitations of the Pure Excel Workflow

A staple of Excel “search and retrieve” formula is VLOOKUP. It is a reasonable formula to use in isolation, but for the purpose of filtering and merging a large set of values, it has a major issue: each cell using this formula will multiply the processing time. A simple way of thinking about it is that if a single VLOOKUP takes X milliseconds to complete, a sheet with N Cells using VLOOKUP will use (X)N milliseconds to complete.

It doesn’t seem much when your sheet only contains 10 rows, but we all know that in the real world, an expense sheet seldom contains “only ten rows”, and that at some point someone is bound to request a report on the last 10 years of activity. That would be the point where this exponential processing time will transition from obvious to crippling.

This is far from the only formula-based processing to suffer from complexity and efficiency ceilings in pure Excel. In the classic case, the formulas themselves are part of the worksheet, and can be affected by changes in the sheet, and must be protected to prevent the end-user from modifying them.

Keikai Java Workflow Improvements

First, we will make this existing document into a Keikai powered web page. The first benefit is immediate and obvious. No more emails, no more duplicated files. Everyone is working on the same document, and at the same time. Collaboration mode brings even more advantages since you can see other user’s activity in real time and adjust your entry accordingly.

As we are working with Keikai, there is a more powerful way to achieve this. Keikai relies on a Java server, and as such we can run Java code on specific sheet events.

We have access to server-side Java programming, which means that we can implement efficient algorithms instead of chaining functions. This will become more visible with each additional line of data.

#java #tutorial #spreadsheet #keikai #data

What is GEEK

Buddha Community

Data Validation in Excel

Data Validation in Excel

In this tutorial, let’s discuss what data validation is and how it can be implemented in MS-Excel. Let’s start!!!

What Is Data Validation in Excel?

Data Validation is one of the features in MS-Excel which helps in maintaining the consistency of the data in the spreadsheet. It controls the type of data that can enter in the data validated cells.

Data Validation in MS Excel

Now, let’s have a look at how data validation works and how to implement it in the worksheet:

To apply data validation for the cells, then follow the steps.

1: Choose to which all cells the validation of data should work.

2: Click on the DATA tab.

3: Go to the Data Validation option.

4: Choose the drop down option in it and click on the Data Validation.

Once you click on the data validation menu from the ribbon, a box appears with the list of data validation criteria, Input message and error message.

Let’s first understand, what is an input message and error message?

Once, the user clicks the cell, the input message appears in a small box near the cell.

If the user violates the condition of that particular cell, then the error message pops up in a box in the spreadsheet.

The advantage of both the messages is that the input and as well as the error message guide the user about how to fill the cells. Both the messages are customizable also.

Let us have a look at how to set it up and how it works with a sample

#ms excel tutorials #circle invalid data in excel #clear validation circles in excel #custom data validation in excel #data validation in excel #limitation in data validation in excel #setting up error message in excel #setting up input message in excel #troubleshooting formulas in excel #validate data in excel

Excel UI With Java Flexibility: Merging Spreadsheet Data in Keikai

Introduction

There is a common end-goal to most sheet of formatted data. We want to analyze, compare, and use the full dataset that we have gathered.

If the data have been gathered in separate sheet and tables, sometimes with duplicated entries, we need to be able to merge all of it into a simple easy-to-use table.

This is where things usually get complicated. In the end, some will usually end-up copying and pasting rows of data from the individual sheets to the summary table.

Real-World Example

Take budgeting for example. Even if your organization only has four departments, you will end up with four different budget sheets. Some of them will have duplicated expenses between departments. For example, wages and salaries will be an item for any organizational units. Some of them will be unique for a specific team, such advertising being exclusive to the marketing department. And then some will be shared by only a few – but not all – of the teams, or even duplicated inside of a team’s sheet.

On top of that, each sheet must be filled by a different team manager, which usually ends up with multiple copies of the same file being passed around through emails.

This all culminates with the last person in the chain receiving five versions of the same file, some containing old data, some incomplete and one of them inexplicably blank. It takes a lot of effort and a significant amount of time to take this wildly divergent dataset and make it exploitable.

Requirements of the Summary Sheet

We want to improve it in two ways. First, we need to make sharing and collaborating much easier. The data-entering process should be streamlined, have single point of access, and be accessible to any designated users.

We also need to improve on the sheet workflow itself. Excel formulas can be powerful in many situations, but they also have their limits. We already have our formulas set up in this document to calculate sums, ratios, averages, etc. inside each department sheet. These, we will absolutely keep. This is where Excel’s formulas work at their best, and we will rely on Keikai to simply use what we already have.

On the other hand, Excel formulas are not ideal to perform dataset operations. Searching, merging and filtering are possible, but they take significant time to set up and execute. They are also fragile and often rely on static sheet layouts in order to be maintained. Keikai operates in a Java web server, so we will improve our workflow by delegating the data manipulation to this layer.

In this case, we need to generate two merged summaries of the data. One merged per period, and the other merged per department.

The “per period” table will need to merge every entry using the same label and sum their values depending on each of the yearly periods (Q1 to Q4).

The “per department” table will need to merge every entry using the same label and sum their values depending on the department declaring this expense. If we were to create these in Excel, we would either need complicated formulas checking and matching results from each of the source tables, or we would need to use custom merging queries.

Limitations of the Pure Excel Workflow

A staple of Excel “search and retrieve” formula is VLOOKUP. It is a reasonable formula to use in isolation, but for the purpose of filtering and merging a large set of values, it has a major issue: each cell using this formula will multiply the processing time. A simple way of thinking about it is that if a single VLOOKUP takes X milliseconds to complete, a sheet with N Cells using VLOOKUP will use (X)N milliseconds to complete.

It doesn’t seem much when your sheet only contains 10 rows, but we all know that in the real world, an expense sheet seldom contains “only ten rows”, and that at some point someone is bound to request a report on the last 10 years of activity. That would be the point where this exponential processing time will transition from obvious to crippling.

This is far from the only formula-based processing to suffer from complexity and efficiency ceilings in pure Excel. In the classic case, the formulas themselves are part of the worksheet, and can be affected by changes in the sheet, and must be protected to prevent the end-user from modifying them.

Keikai Java Workflow Improvements

First, we will make this existing document into a Keikai powered web page. The first benefit is immediate and obvious. No more emails, no more duplicated files. Everyone is working on the same document, and at the same time. Collaboration mode brings even more advantages since you can see other user’s activity in real time and adjust your entry accordingly.

As we are working with Keikai, there is a more powerful way to achieve this. Keikai relies on a Java server, and as such we can run Java code on specific sheet events.

We have access to server-side Java programming, which means that we can implement efficient algorithms instead of chaining functions. This will become more visible with each additional line of data.

#java #tutorial #spreadsheet #keikai #data

How to Install OpenJDK 11 on CentOS 8

What is OpenJDK?

OpenJDk or Open Java Development Kit is a free, open-source framework of the Java Platform, Standard Edition (or Java SE). It contains the virtual machine, the Java Class Library, and the Java compiler. The difference between the Oracle OpenJDK and Oracle JDK is that OpenJDK is a source code reference point for the open-source model. Simultaneously, the Oracle JDK is a continuation or advanced model of the OpenJDK, which is not open source and requires a license to use.

In this article, we will be installing OpenJDK on Centos 8.

#tutorials #alternatives #centos #centos 8 #configuration #dnf #frameworks #java #java development kit #java ee #java environment variables #java framework #java jdk #java jre #java platform #java sdk #java se #jdk #jre #open java development kit #open source #openjdk #openjdk 11 #openjdk 8 #openjdk runtime environment

Your Data Architecture: Simple Best Practices for Your Data Strategy

If you accumulate data on which you base your decision-making as an organization, you should probably think about your data architecture and possible best practices.

If you accumulate data on which you base your decision-making as an organization, you most probably need to think about your data architecture and consider possible best practices. Gaining a competitive edge, remaining customer-centric to the greatest extent possible, and streamlining processes to get on-the-button outcomes can all be traced back to an organization’s capacity to build a future-ready data architecture.

In what follows, we offer a short overview of the overarching capabilities of data architecture. These include user-centricity, elasticity, robustness, and the capacity to ensure the seamless flow of data at all times. Added to these are automation enablement, plus security and data governance considerations. These points from our checklist for what we perceive to be an anticipatory analytics ecosystem.

#big data #data science #big data analytics #data analysis #data architecture #data transformation #data platform #data strategy #cloud data platform #data acquisition

SKP's Algorithms and Data Structures

Continuing on the Quick Revision of Important Questions for My Interviews. These Are Good Puzzles or Questions Related to Data Structures.

My Article Series on Algorithms and Data Structures in a Sort of ‘Programming Language Agnostic Way’. Few of the Algorithms and Data Structures in C, Few in C++, and Others in Core Java. Assorted Collection for Learning, Revising, Revisiting, Quick Refresh, and a Quick Glance for Interviews. You May Even Include them Directly for Professional or Open Source Efforts. Have Included Explanation Only for Few of These! Hope these turn out to be Really Helpful as per the Author’s Intention.

Data Structure — Interview Questions

#java #core java #data structures #dijkstra #core java basics #data structure using java #algorithms and data structures #java code examples #linked list in java #circular linked list