Extracting specific values present in four columns

[SomethngWicked]

Hi All -

I have a range of data spanning four columns. In these four columns, there are values that are similar.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Bill[/TD]
[TD]Chris[/TD]
[TD]John[/TD]
[TD]Mike[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Bill[/TD]
[TD]Chris[/TD]
[TD]Charles[/TD]
[/TR]
[TR]
[TD]Daniel[/TD]
[TD]Daniel[/TD]
[TD]Daniel[/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD]Ken[/TD]
[TD]Michael[/TD]
[TD]James[/TD]
[TD]Steve[/TD]
[/TR]
[TR]
[TD]Ed[/TD]
[TD]Kevin[/TD]
[TD]Jason[/TD]
[TD]Gary[/TD]
[/TR]
[TR]
[TD]Larry[/TD]
[TD]Jeffrey[/TD]
[TD]Steve[/TD]
[TD]Ed[/TD]
[/TR]
</tbody>[/TABLE]

One of the primary characteristics of this data is that one name will not be present in all four columns. So, for example, "Daniel" is in Columns 1, 2, and 3, but he will never exist in Column 4.

I need some way to conditionally format this information to see where these gaps are located. For example, "Bill" is in Column 1 and 2, but how can I know that he's not in columns 3 and 4? I know "Larry" is in column 1, but how can find out if he's in any other column? I need to standardize this list so these names are present in all four columns. The data in each column varies as Column B has 1364 values and Column D has 471.

I'm thinking there may be a conditional formatting solution, but I'm not sure what the best route to use for mapping this data, Appreciate any help!

 

[hotabae]

This is a pretty messy solution, but seems to work for me.

If you have a list of all unique values found in your columns, you could use a "=COUNTIF" formula to see how many "Mike" values are in each column. If it returns zero, then "Mike" is missing from the column

If you don't already have the complete list, you could copy each column one at a time and then remove duplicates (data ribbon).

My sample looks like this:

[TABLE="width: 640"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]
[TD="width: 64, bgcolor: transparent"]E[/TD]
[TD="width: 64, bgcolor: transparent"]F[/TD]
[TD="width: 64, bgcolor: transparent"]G[/TD]
[TD="width: 64, bgcolor: transparent"]H[/TD]
[TD="width: 64, bgcolor: transparent"]I[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]col1[/TD]
[TD="bgcolor: transparent"]col2[/TD]
[TD="bgcolor: transparent"]col3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]John[/TD]
[TD="bgcolor: transparent"]Peter[/TD]
[TD="bgcolor: transparent"]Terry[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]John[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]Terry[/TD]
[TD="bgcolor: transparent"]John[/TD]
[TD="bgcolor: transparent"]Peter[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Peter[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]Peter[/TD]
[TD="bgcolor: transparent"]Mike[/TD]
[TD="bgcolor: transparent"]Mike[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Terry[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Mike[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
</tbody>[/TABLE]

With G2 having the formula

=COUNTIF(A$2:A$4,$F2)

and applying the formula to the other names/columns

Edit: Rereading your question, you mentioned that there are an uneven number of rows - Just use (A:A,$F2) instead to make sure you get everything.

 

[SomethngWicked]

This will work fine I believe. Thanks!