Formula to use in complicated replace

[njelezn]

Hi guys, i just hope that someone would have an idea of which formula is best to use in this case...

I have a spreadsheet (the whole lot, just the top is here)


2059251 On order
Item code : 1006668HD


2059789 On order
2059867 On order
Item code : 1043628

2059866 On order
Item code : 1045841HD



basically what i need to do is: replace on order with the next value, so the final look should be:
2059251 1006668HD
Item code : 1006668HD


2059789 1043628
2059867 1043628
Item code : 1043628

2059866 1045841HD
Item code : 1045841HD


Any ideas on even which formula to use and how to specify it? Can not do it manually, as the report is huge...

[FDibbins]

Hi, welcome to the forum

I did this with a helper column...

	A	B
1	2059251 On order	2059251 1006668HD
2	Item code : 1006668HD	Item code : 1006668HD
3
4
5	2059789 On order	2059789 1043628
6	2059867 On order	2059867 1043628
7	Item code : 1043628	Item code : 1043628
8
9	2059866 On order	2059866 1045841HD
10	Item code : 1045841HD	Item code : 1045841HD

B1=IF(A1="","",IF(ISNUMBER(--LEFT(A1,1)),SUBSTITUTE(A1,"On order",MID(OFFSET(A1,MATCH("Item Code*",A1:A3,0)-1,0),13,99)),A1))

If needed, you can then copy/paste values the helper column back over he original data, then delete the helper

This will work if you have a max of 3 lines per item

[njelezn]

Hi FDibbins,

Thank you

And thanks for the prompt reply

But look what happens for me:

Capture.jpg

Looking at your result i thought that after I've got the result that your screen shot shows, I'll just divide the result by space into two different columns, copy and paste and it's done
But it substitutes my cell with the one of the left, not down, and unfortunately I am not able to modify the formula because I can't get my head around what's what...
Sorry, can you please let me know if i do something wrong...

[FDibbins]

OK, you didnt say you wanted them in 2 columns TRy this...
B1=IF(A1="","",LEFT(A1,IF(LEFT(A1,1)="I",11,FIND(" ",A1,1)-1)))
C1=IF(A1="","",MID(OFFSET(A1,MATCH("Item Code*",A1:A3,0)-1,0),13,99))

[njelezn]

FDibbins, it doesn't do anything now... at all...

[njelezn]

Capture.jpg

Sorry and this is the screenshot... maybe i do something wrong?

[FDibbins]

See the attached

[njelezn]

Thanks you very much! It worked!!! :D

Thanks a lot for your help!!!

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)