Any other ways to loop without using VBA?

[mean3]

Hi all,

I have a set of fixed asset data where the categories the fixed asset are only showed at the end of each category (instead of the beginning).
I would need to have the category to be in a new column beside every asset number.
There are over 10,000 rows of asset and 20+ categories combined, so I would prefer not to consider copy pasting or auto filling upwards as an option.
Are there any possible steps + formula to get the categories to be beside each asset number at each row without using VBA?

TIA!

 

[jon1315]

Morning
If I was to quickly complete, I would probably type "1000 fixtures and fittings" into cell D2
Then, in D3, I'd type =if(iserror(search(" ",b3),d2,b3))
Drag the formula down, and it should work

Thanks
Jon

 

[jon1315]

Then it looks like column E is just a repetition of column B?

 

[mean3]

Morning Jon,

On picture, column D&E is an illustration of how I want my data to turn out..
Column B is how my raw data currently looks like.
So I would like to know if there's any other faster way to make my data turn out to be like column D&E

Apologies for the confusion.

 

[jon1315]

Which columns do you want the split to end in?
At the moment it's all in column B - do you want it to spread over A & B? B/C? Or sit in D & E?
Thanks
Jon

 

[jon1315]

On the solution above, the formula part:

"I would probably type "1000 fixtures and fittings" into cell D2
Then, in D3, I'd type =if(iserror(search(" ",b3),d2,b3))"

Can be used in any column, you'll just have to change the references in D to the required column.
And then to get E, you'd just copy and paste to where you need it.
Does this make sense?

Let me know if I'm not explaining too well!
Cheers
Jon

 

[mean3]

Ooops, I think my screenshot can be a bit confusing. Let me re-upload the screenshot.

Column B is the raw data.
Every number form Cell B2 to B10 is under 1000 Furniture & Fittings' category.
Every number form Cell B12 to B22 is under 2000 Motor Vehicle's category.

What steps + formula can I use in column A for it to reflect 1000 Furniture & Fittings for cell B2 to B11 and changes to 2000 Motor Vehicle as soon as it reaches A12?

Thank you

 

[jon1315]

Yep - get you now
So - cell A1 - type "1000 fixtures and fittings"
Then, in a2, I'd type =if(iserror(search(" ",b2),a1,b2))

Drag the formula down to the bottom of the data

Has that done it?

Thanks
Jon

 

[mean3]

Yeap, I got it.
But the cell A11 to 22 does not reflect 2000 Motor Vehicle as it should.. hmm..

 

[jon1315]

Aah - that's my fault to be fair, I assumed everything underneath the header was part of that FA subdivision, but it's everything above?