Cell value dependent on adjacent cell on financials

[rcmii]

I have a financial spreadsheet of approximately 5000 rows. It has line(s) of costs with the parent entity at the bottom. A line skips and then it goes to the next line(s), etc.

What I want to do is have that parent entity to the right of the cost designations but then start anew when the next section comes up. I've wracked my brain and my Jelen book but I'm not getting what I want.

Any thoughts?

Below the "What I want" are new rows, maybe Franklin & C, maybe another company and with a varying amount of lines above it for the lines of financials.

If I only had a hundred rows, I could force it through and be done. However, looking to learn for long-term and also solve this much faster than a copy/paste back and forth mumbo jumbo for 5000+ rows of data.

 

[rcmii]

Wait...you replied before I sent the last message....let me read yours first...

 

[rcmii]

OK. So I can replicate that and it works. However, it seems that I have to adjust for each sub-section depending on the number of rows, rather than copy/paste all of the way down column C. Is that correct and there is not a reasonable way to combine to one formula (unless I do a VBA, which may be what you suggested the first time)?

 

[jsb1921]

Book1
	A	B	C
1
2	1400220 RM request EG		Franklin & Co
3	1400250 RM request EG without ID		Franklin & Co
4	Franklin & Co		Franklin & Co
Sheet1

Cell Formulas
Range	Formula
C1:C4	C1	=IF(ISBLANK(A1),"",IF(ISNUMBER(MID(TRIM(A1),1,6)*1),C2,A1))

No need to adjust. just copy c1 and paste in c2 thru c6000. no need to change any formula

 

[jsb1921]

.

 

[rcmii]

That's fantastic! It works. I found a few lines, hundreds of rows into the sheet that there is actually wording above the lines that is just a header, but I'll figure out that as a one-off

Thank you so much!

 

[jsb1921]

Glad we made it work and thx for your patience to work through the issues. in future use the xl2bb tool to paste your cells, instead of images, so that we can use it to test our suggestion before replying to you. it will take a long time for us to recreate the data. also mark the post 13 as a solution instead of 14. thx.

 

[alansidman]

Here is an alternate means to the end. Power Query solution:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Value.FromText(Text.Start([Column1],1))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each try Number.From([Custom]) is number otherwise [Column1]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom1",true,null,Replacer.ReplaceValue,{"Custom.1"}),
    #"Filled Up" = Table.FillUp(#"Replaced Value",{"Custom.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Up",{"Column1", "Custom.1", "Column2", "Column3", "Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Column2", "Custom"})
in
    #"Removed Columns"

Book3
	A	B	C	D	E	F	G
1	Column1	Column2	Column3		Column1	Custom.1	Column3
2	660000 RM Stock of Work in Progress		960252.03		660000 RM Stock of Work in Progress	Franklin & Co	960252.03
3	665000 RM Stock of HF Assembly		7718492.33		665000 RM Stock of HF Assembly	Franklin & Co	7718492.33
4	665000 RM Stock of HF in Assembly		0		665000 RM Stock of HF in Assembly	Franklin & Co	0
5	Franklin & Co		8678744.36		Franklin & Co	Franklin & Co	8678744.36
6	660000 RM Stock of Work in Progress		660252.03		660000 RM Stock of Work in Progress	Jacks Co	660252.03
7	665000 RM Stock of HF Assembly		718492.33		665000 RM Stock of HF Assembly	Jacks Co	718492.33
8	665000 RM Stock of HF in Assembly		45		665000 RM Stock of HF in Assembly	Jacks Co	45
9	Jacks Co		1378789.36		Jacks Co	Jacks Co	1378789.36
Sheet1

Cell Formulas
Range	Formula
C5,C9	C5	=SUM(C2:C4)

 

[rcmii]

Glad we made it work and thx for your patience to work through the issues. in future use the xl2bb tool to paste your cells, instead of images, so that we can use it to test our suggestion before replying to you. it will take a long time for us to recreate the data. also mark the post 13 as a solution instead of 14. thx.

Thanks again for your help yesterday. I'm embarrassed to ask this question, but what does the *1 do in the formula you've written?

=IF(ISBLANK(A1),"",IF(ISNUMBER(MID(TRIM(A1),1,6)*1),C2,A1))

I understand it's looking for and trimming a number in A1, if it's a number, starting at digit 1 and moving 6 spaces), and depending on the outcome, referencing C2 if true and A1 if not. But what does the *1 do exactly, other than multiplying the total by 1? Does that change/fix the data to be better "viewed" by the formula for calculation purposes?

 

[jsb1921]

there is no need to be embarrassed. We all learn something every day. I missed the *1 in my earlier solution and that is why my previous solution did not work.
In most of the places when a number is required, excel converts "19" to number 19. But in ISNUMBER function excel does not do any type conversion.
ISNUMBER("19") will give false. to force it to do conversion to number, "19" * 1, it will convert text "19" to numerical 19.
Hope this helps.