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.

 

[jsb1921]

Code not tested. if you have used xl2bb tool and pasted the cells, I could have tested it before replying. 1. identify what is different between the cells you want to paste and the cells you do not want to paste and pinpoint where you want to operate. I assume first 6 char of the cells you want to paste are numeric. verify that is acceptable.
2. decide what you want to copy and what you want to paste.
if this helps you, pl mark it as a solution for the benefit of future readers of the thread.

Sub Macro1()
Dim lastrow1 As Integer
lastrow1 = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Dim int1 As Integer, acnum As String
For int1 = lastrow1 To 2 Step -1
    If Len(Trim(LTrim(Cells(int1, "A").Value))) < 1 Then
    '       a is blank
    Else
        acnum = Mid(Trim(LTrim(Cells(int1, "A").Value)), 1, 6)
        If IsNumeric(acnum) Then
        '   a starts with numeric.  copy col c from bottom row
            If Len(Trim(LTrim(Cells(int1, "C").Value))) < 1 Then
                Cells(int1, "c").Value = Cells(int1 + 1, "C").Value
            End If
        Else
            ' 6 chars not numeric.  not blank. so something like Franklin.  Put Franklin from col A to col C
            If Len(Trim(LTrim(Cells(int1, "C").Value))) < 1 Then
                Cells(int1, "c").Value = Cells(int1, "a").Value
            End If
        End If
    End If
Next int1
End Sub

 

[rcmii]

I have never used VBA...that's next on my learning list. Is there a formula solution?

In the mean time, I'll try to figure out how to insert the VBA code and see what happens.

Thanks.

 

[jsb1921]

I am sure one of our mr.excel experts will post a cute one line formula

 

[jsb1921]

in cell c1, try =IF(ISBLANK(A1),"",IF(VALUE(MID(TRIM(A1),1,6))>0,C2,A1))
and copy c1 formula to all cells of column c

 

[rcmii]

Thank you. It did not work but I'll try to keep working with it. I appreciate your help.

 

[rcmii]

I edited and it did work in one cell but once I copied down, it stopped working. Here is the screenshot. Read line points the formula from the cell in which it is located.

 

[jsb1921]

fromula in c2 should not say a4 it should say c3
do not edit the formula
in my orig post, I asked you to copy my formula =IF(ISBLANK(A1),"",IF(VALUE(MID(TRIM(A1),1,6))>0,C2,A1)) in c1 and copy c1 and paste in c2 through c999

 

[jsb1921]

Sorry. there is a mistake in my formula. I have to work on it. Meanwhile hope someone else reply to you with a cute solution.

 

[rcmii]

Here are the formulas and then the results. Final screenshot is desired results.