Forum Discussion
Category seperation
Put this formula into cell B3 (after inserting an empty column B) and copy it down.
=IF(AND(ISNUMBER(A3),ISTEXT(A2)),A2,B2)
It works because fortunately the fields in Column A with the colon are text, whereas the others are numbers. What the logic is saying is
If the cell to the immediate left of the current cell (B3 at the start) is a number and the cell right above it is text, then put that text here in B3 (at the start). If, on the other hand, the cell to the immediate left is a number and the cell above is a number also, then repeat what is in Column B immediately above the current cell.
Then when, many rows later it suddenly encounters another text, it changes to that new text and continues with it.
It's not a perfect solution, but should serve.
Just out of curiosity, what does that data reflect?
For future reference, you really didn't need to upload that entire file; it is over 24 meg in size. It's good to have an actual file, but it could have been a representative subset and saved everybody a lot of bandwidth. That's why I'm only giving you the formula back.
Yes. I have attached the file with a same example for a short data below. mathetes
Raw data:
Required data:
Put this formula into cell B3 (after inserting an empty column B) and copy it down.
=IF(AND(ISNUMBER(A3),ISTEXT(A2)),A2,B2)
It works because fortunately the fields in Column A with the colon are text, whereas the others are numbers. What the logic is saying is
If the cell to the immediate left of the current cell (B3 at the start) is a number and the cell right above it is text, then put that text here in B3 (at the start). If, on the other hand, the cell to the immediate left is a number and the cell above is a number also, then repeat what is in Column B immediately above the current cell.
Then when, many rows later it suddenly encounters another text, it changes to that new text and continues with it.
It's not a perfect solution, but should serve.
Just out of curiosity, what does that data reflect?
For future reference, you really didn't need to upload that entire file; it is over 24 meg in size. It's good to have an actual file, but it could have been a representative subset and saved everybody a lot of bandwidth. That's why I'm only giving you the formula back.
- Thanks alot mathetes. I was trying and ended up using =IF(IFERROR(FIND(":",[@Movie]),"0")>=7,B2,[@Movie]) which was working but in a messy way. Your formula look way more clean and understandable. And yes, this dataset is of movies watched by customers and the rating that they gave for it. Trying to make a prediction model out of it.
Thank you