Returning a value problem.

[dtman85]

I copied a big data file from two websites and pasted into two worksheets, over 10,000 rows of data.
Worksheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Year[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]2016[/TD]
[TD]1,500[/TD]
[/TR]
[TR]
[TD]Eric Smith[/TD]
[TD]2015[/TD]
[TD]2,500[/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Name[/TD]
[TD]Salary[/TD]
[TD]win[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]John Smith[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Eric Smith[/TD]
[TD][/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

In worksheet 2, under the salary column, I used the sumifs formula to return the value of salary from worksheet one to two but I get a value of "0"for each cell. When I retyped the pasted names "John Smith" to "John Smith" on worksheet 2, I get the correct return value. Both cells from worksheet 1 & 2 are in the general format.

How can I solve this because is not ideal to retype all the pasted names?

Thanks in Advance.

 

[mrshl9898]

Are these the actual values? They are identical so it's hard to say what your issue is.

You could try =TRIM() or paste in the cell values here.

 

[FDibbins]

If you are copying this data from another application, you may be getting extra trailing or leading spaces in the text. Also, often, values brought in that way, are brought in as text, not numbers, so you need to test both of those.

For the extra spaces, do a quick test using EXACT(cell-1, cell-2)
For the numbers, use =ISNUMBER(cell-1)

 

[dtman85]

If you are copying this data from another application, you may be getting extra trailing or leading spaces in the text. Also, often, values brought in that way, are brought in as text, not numbers, so you need to test both of those.

For the extra spaces, do a quick test using EXACT(cell-1, cell-2)
For the numbers, use =ISNUMBER(cell-1)

I got a "false" result for the Exact test for the name columns but a “True” for the numbers test for the salary column. I tried to trim both columns and still got a “false”result.

 

[dtman85]

Are these the actual values? They are identical so it's hard to say what your issue is.

You could try =TRIM() or paste in the cell values here.

Here is the two values from WS1 and WS2.

[TABLE="width: 148"]
<tbody>[TR]
[TD="width: 148"]Brad Ziegler
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Brad Ziegler[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

There may be a hidden character that one of the websites uses for formatting.
Check the length of each text string with the LEN function to see if they are different.

 

[dtman85]

There may be a hidden character that one of the websites uses for formatting.
Check the length of each text string with the LEN function to see if they are different.

One cell is 13 and the other is 12 strings with the Len function. How can I get them to match?

 

[AhoyNC]

The next step is to try and find out what the hidden character is.
See the example below that will break the string into its parts (formula in B1) then use the formula in (C1) to get the code number. Copy formulas down the column. Once you have that you should be able to use the FIND & Replace to remove it. Sometimes the CLEAN function in Excel will remove the character, but often it doesn't.

Excel Workbook
	A	B	C
1	Brad Ziegler	B	66
2		r	114
3		a	97
4		d	100
5			32
6		Z	90
7		i	105
8		e	101
9		g	103
10		l	108
11		e	101
12		r	114
Sheet

 

[AhoyNC]

Looks like the problem is the CODE(10).
The CLEAN function should remove that code for you.

Excel Workbook
	A	B	C	D	E	F
1	Brad Ziegler	B	66		Brad Ziegler	12
2		r	114
3		a	97
4		d	100
5			32
6			10
7		Z	90
8		i	105
9		e	101
10		g	103
11		l	108
12		e	101
13		r	114
Sheet

 

[dtman85]

Looks like the problem is the CODE(10).
The CLEAN function should remove that code for you.

	A	B	C	D	E	F
Brad Ziegler	B		Brad Ziegler
	r
	a
	d
	Z
	i
	e
	g
	l
	e
	r

<colgroup><col style="width:30px; "><col style="width:80px;"><col style="width:71px;"><col style="width:64px;"><col style="width:64px;"><col style="width:99px;"><col style="width:49px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]66[/TD]

[TD="align: right"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]114[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]97[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]100[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]32[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]90[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]105[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]101[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]103[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="align: right"]108[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="align: right"]101[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="align: right"]114[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =MID($A$1,ROWS($1:1),1) C1 =CODE(B1) E1 =CLEAN(A1) F1 =LEN(E1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

The two worksheets strings match now. Thanks for all the great tips!