Identify mismatches between columns (e.g., locations)
Does the row contain specific text?
If you get a Name error, replace the annoying “smart quotes” with regular quotes.
Identify duplicates
In the second cell of the first free column, enter the following, editing for whichever column has the duplicates:
=COUNTIF(B:B, B2)=1
Click back into that cell, then hover over the lower-right corner of the cell.
Double-click the plus.
Select everything in the column, then paste just the values in a new column, then delete the old column.
Now you can filter or sort or whatever to get what you need.
- Note: Duplicates are False.
The following variation identifies all the extraneous lines; that is, all but the first line for each value.
=COUNTIF($A$2:$A2, A2)>1
Count duplicates
=COUNTIF(A:A, A2)
Identify mismatches between columns (e.g., locations)
In the second cell of the first free column, enter the following, editing for whichever columns we are comparing:
=IF(A2<>B2,”mismatch”,””)
Click back into that cell, then hover over the lower-right corner of the cell.
Double-click the plus.
Select everything in the column, then paste just the values in a new column, then delete the old column.
Filter to see the mismatches.
Does the row contain specific text?
In the second cell of the first free column, enter the following, editing for whichever text we are searching for:
=COUNTIF(A2:W2,”Law*”)>0
Click back into that cell, then hover over the lower-right corner of the cell.
Double-click the plus.
Select everything in the column, then paste just the values in a new column, then delete the old column.
Filter to see the found/not found rows.
Concatenate fields
In the second cell of a blank column (usually to the right of the BibID column), enter the following, editing for whichever columns we are concatenating:
=CONCATENATE(A2,”_”,K2)
Click back into that cell, then hover over the lower-right corner of the cell.
Double-click the plus.
Select everything in the column, then paste just the values in a new column, then delete the old column.
Concatenate for Oak low shelves master
Run an Item Status Report in Caia.
insert an empty column A
A1: Shelf
A2: =CONCATENATE(H2,I2,J2,”:”,K2)
sort order is:
- Aisle
- Ladder
- Step
- Side
- Footprint
- Container