Excel formula no longer returning values to multiple columns

[neadst]

Hi all. Currently having trouble with a formula that seemed to work perfectly before - I'm not particularly well versed in Excel so would appreciate simple answers/explanations if possible!

At the moment, the formula I'm using is as follows:

=IF(COUNTIF(Sheet1!2:2,A$1)>0,A$1,"")

I'll be honest, I don't really understand how it works, but in previous workbooks, this has allowed me to take data that I've separated out via Text to Columns in the first sheet, and organise it into the corresponding column in the second sheet automatically.

For example, the first sheet might look like this, each row has unique data, which I've split into columns, but it's not organised at all:

Fruit
Pears	Oranges	Apples
Oranges	Apples
Oranges	Pears

And on the second sheet, there are columns with headers; the formula would organise the data in the rows into the matching columns, as follows:

Apples	Oranges	Pears
Apples	Oranges	Pears
Apples	Oranges
	Oranges	Pears

So the data in each row remains the same, it's just organised into the right column.

This has worked without issue for the last two months. However, I've created a new spreadsheet as normal, and placed this months' data in to Sheet1, and now the spreadsheet is only returning the first item in each row, like this:

Apples	Oranges	Pears
		Pears
	Oranges
	Oranges

As far as I can see, the formula references are correct and identical in setup to the previous month; the formatting on the fields seems to have no effect either. Really at a loss as to why this has changed and what could be causing it.

Advice and help much appreciated, thank you in advance.

[#2]

@neadst 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3 · Accepted solution]

Hi Hans - thank you for your response. Whilst I was trying to put together an example, I came across the solution entirely by accident, so thank you for putting me on the right path!

Solution here in case anyone else has this issue:
The original data was in a single cell, separated by a comma ',' and a space ' ' - when Excel moved data into Columns using the delimited ',' option, it left that single blank space at the start of every column except the first one.

This meant that when the formula looked for an exact match to the column header, it couldn't find any, because of the extra space at the start.

Once the space was removed, everything appeared as it should have done. Hopefully this is helpful to anyone else who missed the obvious error like I did.

[#4]

@neadst 

Thanks for the feedback.