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 PearsOrangesApplesOrangesApples 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:
ApplesOrangesPearsApplesOrangesPearsApplesOranges 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.
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 PearsOrangesApplesOrangesApples 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: ApplesOrangesPearsApplesOrangesPearsApplesOranges 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. Read More