Data list in one column without empty cells if two conditions are met in two columns
If you often work in Excel, I believe you've had the situation that you have to display a list of all data (in this case, the names of people) without empty cells if two conditions from two columns match. In the picture below, notice that in the 'A' column we have a list of persons who are not in a continuous sequence. Between the rows of names of people, there are empty cells. The 'B' column contains a description of the job that the person concerned carries out (Decorator and Carpenter).
We want to show a list of all empty cells in one column if two conditions match.
Cells in the 'A' column must contain the name and surname of the person (worker).
The cell in column 'B' must contain the work title (Decorator, Carpenter).
To solve this problem I have added an auxiliary column (in the picture below 'C' column named 'helper').
The formula in 'C' column is next. (this formula creates the counting of ascending numbers, if the two terms I mentioned in the above text match).
In the formula above you can notice that I set the condition (criterion) in the "Determine Decorators With Name" in the 'D1' cell (In this cell I set the Data Validation List to select the job listings we are looking for). This nested formula is a key MID($D$1;11;9). Excel MID formula, extract the word "Decorator" from the sentence and this is our second condition. For each row containing a person's name and the term "Decorator", the formula above will return the ordinal number starting with 1. In the picture below, notice that there are three rows that meet the two conditions.
In the 'D2' cell is the following formula. (This formula is based on the sequence of ordinal numbers from 'C' column and returns the names of persons for which two conditions are met.
Another way to solve the same problem is a bit different from the first one. In fact, in 'D'1 cell we have a short list of jobs so we do not have to use the MID function as in the previous case.
How to create ordinal numbers if two conditions are met
So, in this second solution we have the following formula:
In the 'C2' cell is the formula: (note the difference in relation to the formula from the first solution. Also, note the sequence of row numbers for each row where two conditions are met).
In the 'D2' cell is the formula as in the first solution.
List, without blank cells in three columns, if two conditions are met from two columns
Please note that this second solution is not much different than the previous one but here we are listing the list in three columns. The 'C' column remains the formula as in the first case. The job description performed by the worker is extracted from the sentence (located in the 'E1' cell) and it serves as a condition for the 'B' column.
The formula in 'C' column is as follows:
The formula in 'E2' cell is the following: (copy down)
The formula in 'G2' cell is the following: (copy down)
The formula in 'I2' cell is the following: (copy down)
Considering that in the 'E, G and I' columns we want to have a list of 10 people, one formula is divided into three parts with small variations. Notice the nested ROW function in the formula. For each column it is different and the difference is 10 cells.
Another solution to this problem, it is possible by setting a Data Validation list in a word that indicates the type of work a worker performs. In this way, we avoided the MID function.
So the formula in 'C2' cell is the following:
Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.
Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.