Updated data not being affected by formula

[murp5972]

Hi, I hope this is fairly easy to follow.
I've got an INDEX-ROW formula that searches through a sheet of data to match for a company name, lets say in sheet 2 column 1. Once a match is found in sheet 2 column 1 (often several matches for the same company repeated with new data) the formula copies the data for that match. The copied data from all the matches in sheet 2 column 1 are placed into a new sheet, sheet 1 Column 3. The sheet of data is updated every month. However I'm finding an issue when updating my data. The existing company names are working properly, meaning the NEW monthly data for existing companies is being returned properly as I update. However, any NEW companies that are added to the sheet, when I search for that company through the formula, it isn't returning any data, as if it's not finding any matches.

Also, all of the data for the sheet is copied and pasted in. For some reason, although the NEW companies are not returning data when they are pasted in, if I delete the NEW company name, and re-type it exactly the same, it then returns the data properly.

Any ideas? Here is my formula:

Please Login or Register  to view this content.

[natefarm]

There must be some difference between what is copied in and what is typed in. Possibly a leading or trailing space?

[murp5972]

It appears that is the issue, there are extra spaces in certain cells. Previously I was simply doing CTRL+A and pasting. Is there a way I can easily copy all of my data but to have it be trimmed of any extra spaces throughout my data? Thanks!

Edit: It only works when I double click into the cell and highlight just the text, with no extra spaces before and after, and paste with text only. I cannot just click the cell and copy paste values only, that doesn't seem to get the match properly. Obviously I dont want to go through all my cells this way, so if there's a way i can simply just copy the text in all cells with no spaces, that would do the trick I'm guessing.

Thanks!

[natefarm]

You could use the TRIM function in an adjacent column, then Copy - Paste Special (Values) the results back over the original.

[ChemistB]

If there are not supposed to be any spaces in your data, then you can simply do a find and replace on that column. I suppose that would be too easy?

[murp5972]

This seems to do the trick! Although its another step, do you think this is the easiest solution? Thanks again!