Forum Discussion

neadst's avatar
neadst
Copper Contributor
Apr 24, 2024

Excel formula no longer returning values to multiple columns

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  
PearsOrangesApples
OrangesApples 
OrangesPears 

 

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:

 

ApplesOrangesPears
ApplesOrangesPears
ApplesOranges 
 OrangesPears

 

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:

 

ApplesOrangesPears
  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.

  • neadst's avatar
    neadst
    Apr 25, 2024

    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.

  • 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?

    • neadst's avatar
      neadst
      Copper Contributor

      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.

Resources