Identifying Data Discrepancies

[vk7]

In the attached Excel sheet Data.xlsx, I have three rows including the column headers. However, upon closer inspection, I noticed that although the data in Row 2 and Row 3 appears identical, there are subtle differences due to extra spaces in the "Name" and "Country" columns of Row 2.

I'm seeking advice on how to use Excel features to highlight columns that may contain extra whitespace, making it easier to identify and resolve these discrepancies in the data.

 

[ETAF]

you could use
=OR(ISNUMBER(SEARCH(" ",RIGHT(A2,1),1)),ISNUMBER(SEARCH(" ",LEFT(A2,1),1)))
to test if the first or last character is a space
you could search for a space anywhere in the text BUT that would highlight names with spaces between first and last name
and apply with conditional formatting

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2:C100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=OR(ISNUMBER(SEARCH(" ",RIGHT(A2,1),1)),ISNUMBER(SEARCH(" ",LEFT(A2,1),1)))

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[vk7]

Is it possible to have different colors so that it would be easy to understand which portion of the text has extra characters?

For example if,
- Right Side of the Data has extra characters - Pink
- Right Side of the Data has extra characters - Green
- Both Left Side and Right side of the Data has extra characters - Red

 

[vk7]

The approach that you have suggested seems to be not helping in many scenarios.. The goal is to compare data in Row2 and Row3 and finding the discrepancies.. Lets say if there is a column (i.e A2) with a single space in it (i.e no data but a single space) and (i.e A3) with two spaces in it, I would like to easily identify such differences.

 

[vletm]

vk7​

Modify number of spaces with rows two and three.
You could see those with rows four and five:
Red ... spaces left
Green ... data
Blue ... spaces right

 

[vk7]

Hello @vletm,

Thank you so much, I was looking for something like this. However, I observe a small challenge.. If the value in the column itself is different then also can it highlight somehow? For example, the value in C2 and C3 are now different and it doesn't highlight.

Also the number of columns may vary, is there a way to dynamically find the data range? Mostly the data will grow horizontally.

 

[vletm]

vk7​

Your original is:
data in Row 2 and Row 3 appears identical, there are subtle differences due to extra spaces in the "Name" and "Country" columns of Row 2.
+ reply #2 & #3
I read based writings that You're looking for extra pre/post spaces from A- & B-columns.
You seemed add there one space ... but if there is extra number or what ever?
If there are any of those extras ... aren't all of those also different.
A small challenge is that You should able to write down clear rules.
Mostly the data will grow horizontally.
Should above read too that the data could grow other way too?

 

[vk7]

vk7​

Your original is:
data in Row 2 and Row 3 appears identical, there are subtle differences due to extra spaces in the "Name" and "Country" columns of Row 2.
+ reply #2 & #3
I read based writings that You're looking for extra pre/post spaces from A- & B-columns.
You seemed add there one space ... but if there is extra number or what ever?
If there are any of those extras ... aren't all of those also different.
A small challenge is that You should able to write down clear rules.
Mostly the data will grow horizontally.
Should above read too that the data could grow other way too?

Yeah, I apologize for the confusion and not mentioning all the rules in advance. I see the below condition fails if the column has no value and no space either in one of the row or both of the row. The are scenarios that the column is either blank in one of the row or both of the row, or simply a space must be there in one of the column but not in the other. Can you help with this rule alone pls?2

For Example, if column D2 is Blank (i.e with no space) but D3 has no Data but a Space then, I would like to highlight this finding using a different color. And if both D2 and D3 is blank without any spaces then do not highlight.

Do While Mid(chk, xx - c, 1) = " "

 

[vk7]

Please ignore the above request.. I was able to tweak the code as per the need. Thanks a lot @vletm for helping me with the initial logic.

 

[vletm]

You seemed to almost duplicate Your thread ...
... that meant Your code works different way than this sample code.

 

[urrah]

For the spaces you could use the TRIM function