requesting excel formula to compare columns but going in order and skipping blanks
Trying to setup a formula in excel to compare 2 colums, sample data set below. Considering there will be blanks in column B and we must go in order
| Column A | Column B | Column C |
|---|---|---|
| 3 | 3 | match |
| 4 | 3 | difference |
| 5 | blank | skipped |
| 6 | 5 | match |
| 6 | 5 | difference |
| 8 | 6 | match |
| blank | blank | skipped |
| blank | 8 | match |
Tried a few formulas but not giving the results required
=IF(B1="", "", IF(ISNUMBER(MATCH(A1, IF(B:B<>"", B:B), 0)), "Match", "Difference"))
=IF(AND(A1<>"", B1<>"", A1=B1), "Match", IF(B1="", "", "Difference"))
=IF(A1<>"", IFERROR(IF(ISBLANK(B1), IFERROR(INDEX(B:B, MATCH(FALSE, ISBLANK(B:B), 0)), ""), IF(A1=B1, "Match", "Difference")), ""), "")
Solution
Use a nested if with COUNTIF
=IF(B1="","skipped",IF(COUNTIF(A:A,B1)>=COUNTIF($B$1:B1,B1),"match","difference"))
- Convert Range to a table
- In Excel, I wonder that how to use VLOOKUP function in OR logic
- Simple VBA array join not working
- Excel Pivot Table - Turn two columns with same values into a matrix pivot table with column names as rows and value count in columns
- Excel: Transforming automatically a cell based on the input in another one
- Check Duplicate Data
- Finding hidden or system files containing ~ using Dir() in VBA
- Deleting rows that do not have specific value (text) - throwing Error Code 13
- Filter function Delivers the Blank cells in the Array as Zeros and Other Blank cells in time formated cells as 12:00
- How to disable Excel's automatic cell reference change after copy/paste?
- Retrieve article name and store of cheapest article for a given category in Excel
- Filter function or alternative for Vertical and Horizontal Criteria
- Calculate SUMPRODUCT with multiple column criteria and one row criteria
- Format smallest value in range of same category in Excel
- Calculate SUMPRODUCT with multiple column criteria and one row criteria (with empty columns in the data range)
- How to group an array of shapes
- Extract text after last delimiter but exclude last character
- How do i calculate difference between counts for each value in Excel
- Highlight cell in a range if the cell value is same as the cell value in a column A
- How to count specific values between other values
- Excel Chart: color data labels by categories - rows
- Get table dynamically with name in excel
- Excel Chart: color data labels by categories - columns
- VBA Error 1004 occurs when manipulating a table
- Store location of cell and loop recursively
- Excel - 3D interpolation
- Excel Data Validation with XLOOKUP
- Using a list (range) of column critiera (not single cells) in FILTER formula
- Yield to maturity / IRR in Power Query M
- How to convert a worksheet to a Data frame in Pandas?